MariaDB Performance Tuning

MariaDB performance

The performance of MariaDB is something that a multitude of uses are now interested in improving. Since it emerged as a fork of MySQL it’s seen a big acceleration in uptake by the open-source database community. Starting life as a drop-in replacement, MariaDB has begun to distinguish itself from MySQL, and particularly so since MariaDB 10.2 was released.

But that aside, there are still no obvious differentiators between MariaDB and MySQL, as both use mutually compatible engines that can run natively with each other. That means it should come as no surprise that MariaDB performance tuning mirrors that of MySQL. Let’s look at boosting MariaDB performance, and that of systems that run in a Linux environment.

MariaDB – Optimizing System and Hardware

You can increase the performance of MariaDB by improving your hardware. Here’s the right order to do it in:

Memory

If you want to adjust Server System Variables to give you larger key and table caches, then memory (and lots of it) is what you need. More memory means less disk caching, which is the considerably slower option.

But do bear in mind that just adding in extra memory might not give you the spectacular MariaDB performance improvements that you were hoping for if you don’t set the server variables correctly to take advantage of it.

Also, remember that filling additional RAM slots on the motherboard elevates the bus frequency, which means more latency between the CPU and the RAM. Better to use the biggest RAM sticks you can find for each slot instead.

Disks

Disks have always been a system bottleneck in comparison to RAM because they’re just not as fast. But that doesn’t mean you can’t improve their speediness. The most important figure to be aware of is disk seek time (which tells you how quickly the read head can move to get to the data) so choose disks with the lowest seek times you can find, and think about using dedicated disks for transaction logs and temporary files, too.

Fast Ethernet

In tandem with your internet bandwidth, fast ethernet lowers your client request response times, and your replication response times for reading binary logs across slaves. Lower response times are particularly significant with Galera-based clusters.

CPU

It’s hard to conceive of a situation where getting a faster processor wouldn’t be a good thing, as tearing through computations more quickly means more data is delivered to the client sooner. But while processor speed is very important, so are bus speed, cache size, and core count.

 

Setting Your Disk I/O Scheduler for MariaDB Performance

An I/O scheduler optimizes disk access requests by grouping I/O requests in similar areas on the disk. This speeds things up because the disk drive only has to go to one location to find what it’s looking for, so expect fewer disk operations and a big drop in response time. The recommended tools for managing the I/O performance of MariaDB are noop and deadline.

noop lets you check if complex I/O scheduling decisions of other schedulers are responsible for regressions in I/O. In some instances, it can be useful for devices that do I/O scheduling themselves, as intelligent storage, or devices that don’t rely on mechanical movements, like SSDs. Usually, the DEADLINE I/O scheduler is a more appropriate choice for these devices, but because of less overhead NOOP may realize better performance of MariaDB with particular workloads.

DEADLINE is an I/O scheduler that’s orientated towards latency. Each I/O request has an assigned deadline, and these requests are usually stored according to sector numbers in queues (read and write). The DEADLINE algorithm maintains two extra queues (read and write) where the requests are also sorted according to the deadline. As long as no requests have timed out, the “sector” queue is used. If timeouts do occur, then requests from the “deadline” queue are served until expired requests have been gone through. The algorithm usually favors reads more than writes.

For PCIe devices (NVMe SSD drives), they have their own lengthy internal queues and fast service so they don’t gain any benefits from an I/O scheduler. We recommend you don’t have any explicit scheduler-mode configuration parameter.

You can check your scheduler setting with:

cat /sys/block/${DEVICE}/queue/scheduler

It resembles this example output:

cat /sys/block/sda/queue/scheduler

[noop] deadline cfq

In order to make it permanent, you need to edit the /etc/default/grub configuration file. Look for the variable GRUB_CMDLINE_LINUX adding “elevator” like this:

GRUB_CMDLINE_LINUX="elevator=noop"

Increase Open Files Limit

For optimum server performance of MariaDB, you need to keep the total number of client connections, database files, and log files below the operating system’s maximum file descriptor limit (ulimit -n). Linux systems limit the number of file descriptors that any single process can open to 1,024. On active database servers (and production ones in particular) it’s easy to reach the default system limit.
To create more headroom, edit /etc/security/limits.conf and add or specify this:

mysql soft nofile 65535

mysql hard nofile 65535

You’ll need to do a system restart, then give confirmation by running the following:

$ ulimit -Sn

65535

$ ulimit -Hn

65535

Alternatively, you might want to set this by using mysqld_safe if you are starting the mysqld process thru mysqld_safe,

[mysqld_safe]

open_files_limit=4294967295

or if you are using systemd,

sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF
[Service]

LimitNOFILE=infinity

EOF

sudo systemctl daemon-reload

Setting Swappiness on Linux for MariaDB

Linux Swap is important for MariaDB performance because it does the processing equivalent of a tire change. Memory leaks can have a negative impact on your work, slowing the machine down, but it will usually still be able to get through its assigned job.
To make changes to swappiness, just run:

sysctl -w vm.swappiness=1

This happens dynamically, with no need to reboot the server. To make it persistent, edit /etc/sysctl.conf and add the line,

vm.swappiness=1

It’s pretty common to set swappiness=0, but changes have been made following new kernel releases (i.e. kernels > 2.6.32-303), so you need to set vm.swappiness=1.

Filesystem Optimizations for MariaDB

Ext4 and XFS are the file systems most commonly used in Linux environments running MariaDB. Certain setups are also available for implementing an architecture using ZFS and BRTFS (as referenced in MariaDB’s documentation).
As well as this, the majority of database setups don’t need to record file access time, so if you’d like to turn it off when you mount the volume into the system, edit the file /etc/fstab. For example, on a volume named /dev/md2, it will look like this:

/dev/md2 / ext4 defaults,noatime 0 0

Set Your max_allowed_packet

MariaDB handles packets in a similar way to MySQL. It splits data into packets, which means that the client needs to be conscious of the max_allowed_packet value, which defines the maximum size of the packet which can be sent. The server stores the body in a buffer and its maximum size will correspond to that value. Exceed that limit and the socket closes, limiting MariaDB performance.

If it’s set too low, then the query will be triggered which means the client connection will be stopped and closed. That’s why you may see errors like ER_NET_PACKET_TOO_LARGE or find that your connection to the MySQL server has been interrupted during the query. So, what’s an ideal setting? We suggest starting out with 512MiB. If the application is only low demand, go with the default value (which has been 16MiB since MariaDB 10.2.4) and only alter it (via session) when your data need is likely to go higher, as with more demanding workloads where large packets need to be processed. Note that if the max_allowed_packet value is too small on the slave, this can also cause the slave to cut the I/O thread.

Using Threadpool

In some situations, this kind of tuning might not always be right for the best MariaDB performance. Thread pools work best when queries are fairly short and the load is CPU bound (OLTP workloads). If it isn’t CPU bound, you may still need to place a limit on the number of threads to conserve memory for the database memory buffers.

Threadpool is ideally suited to situations where you are looking for ways to reduce context switching on your system and sustain fewer threads than there are clients. But this number also shouldn’t be too low, because we don’t want to limit the use of the available CPUs. Consequently, the ideal for boosting MariaDB performance would be to have one active thread for each CPU.

Setting the thread_pool_max_threads, thread_pool_min_threads for maximum and minimum thread numbers is something you can’t do in MySQL, it’s unique to MariaDB.

The variable thread_handling sets how the server takes care of threads for client connections. As well as threads for client connections, it also applies to some internal server threads, including Galera slave threads.

Tuning Your Table Cache + max_connections

If you find that you’re sometimes seeing events in the process list to do with Opening tables and Closing tables statuses, this might mean that your table cache needs to be increased. You can also monitor this via the mysql client prompt by running SHOW GLOBAL STATUS LIKE ‘Open%table%’; and monitor the status variables.
For max_connections, if your application has need of multiple concurrent connections, set this to 500 to start with.
For table_open_cache, go with your total number of tables plus extra ones to account for the temporary tables that may need to be cached as well. So, if you’ve got 100 tables, it makes sense to specify 300.
Set your table_open_cache_instances variable to 8. This can reduce contention among sessions and so improve scalability. You can partition the open tables cache, dividing them into several smaller cache instances using table_open_cache / table_open_cache_instances as your guide.

For InnoDB, table_definition_cache places a soft limit on the total number of open table instances in the cache of the InnoDB data dictionary. The value that you set will determine how many table definitions can be stored in the definition cache. If you use multiple tables, you can create a sizeable table definition cache to make tables open faster. The table definition cache uses less space and doesn’t use file descriptors, in contrast to the normal table cache. The lowest possible value is 400. The default value is derived from the formula below, and is limited to 2000:

MIN(400 + table_open_cache / 2, 2000)

If the number of open tables is greater than the value in the table_definition_cache, the LRU mechanism will start earmarking any instances of tables that need to be removed and eventually evicts them from the data dictionary cache. Having a cache limit like this helps to minimize occasions when memory-hogging tables that aren’t used very much take up too much space unnecessarily, and so is key to improving Maria DB performance. It’s possible that there could be more table instances with cached metadata than is allowed by the limit set in table_definition_cache, because parent and child table instances with foreign key relationships aren’t put on the LRU list and so will not be liable for eviction from memory.In contrast to the table_open_cache, the table_definition_cache doesn’t use file descriptors and is a lot smaller.

Dealing with Query Cache

We think that disabling the query cache to improve the performance of MariaDB is the preferred option. You need to make sure that query_cache_type=OFF and query_cache_size=0 so that the query cache is completely disabled. In contrast to MySQL, MariaDB still supports query cache and doesn’t plan to withdraw support for it anytime soon. There are those who think that using query cache gives them performance benefits, but as this post from Percona demonstrates, an enabled query cache increases overhead and reduces server performance.

If you want to use query cache, ensure that you monitor it by running SHOW GLOBAL STATUS LIKE ‘Qcache%’;. Qcache_inserts reports on how many queries have been added to the query cache, Qcache_hits shows how many have made use of it, and Qcache_lowmem_prunes contains the number of queries that have been dropped because of insufficient memory. Over time, using query cache may cause it to become fragmented. A high Qcache_free_blocks to Qcache_total_blocks ratio may point to increased fragmentation. To defragment it, run FLUSH QUERY CACHE. This will defragment the query cache without dropping any queries and improve MariaDB performance.

Always Keep an Eye on Your Servers

Monitoring your MariaDB nodes is critical for maintaining the optimum performance of MariaDB. Popular monitoring tools (such as Nagios, Zabbix, or PMM) for those who would rather use open source and free tools are well-liked, but for enterprise-level tools, we would like to point you towards ClusterControl. It not only offers monitoring, it will also give you performance advisories, alarms and alerts when it thanks that you could improve your MariaDB performance.

Conclusion

Improving the performance of MariaDB requires the same sort of approach as you’d take with MySQL, apart from a few differences associated with different versions. MariaDB has gone its own way and in doing so as established itself as a trustworthy option among the community, so tuning and optimizing for better MariaDB performance is something that more and more people will be interested in.

MySQL vs MariaDB – Can One Replace The Other?

MySQL vs MariaDB

With all MariaDB’s power under the hood, “MySQL vs MariaDB” was suddenly a legitimate topic of conversation. The two databases are so similar that MariaDB negates the need to upgrade mysql, because it can usually replace MySQL in a straight swap. Here’s an in-depth comparison of the two, their relation to Plesk and how to set it up.

So, what’s MySQL?

You may have thought that you might like to upgrade MySQL, so this might be one of the first questions that comes to mind. The short answer is, it’s an open source relational database management system that has its roots in SQL, or Structured Query Language. The long answer is that MySQL began life in 2008.

A Swedish company with the catchy name of MySQL AB came up with it, before Sun bought it in 2010. It’s backed up by Oracle and runs on the usual suspects: Linux, UNIX and Windows. Although it’s versatile enough to be used in all kinds of settings, it’s come to be best known to use with web apps and online publishing.

MySQL is a part of the LAMP web development platform, or ‘stack,’ which is the common term used for a bunch of software components used together for a particular purpose. LAMP is an acronym that looks like this:

  • Linux
  • Apache Web server
  • MySQL, the relational database management system
  • PHP, the object-oriented scripting language (Perl or Python are also sometimes used in place of PHP).

We can only guess that if Apache web server had been named Inuit, then LAMP would have been LIMP, and we wonder whether sales would have taken a nosedive as a result? But jokes aside, anyone can use MySQL thanks to its GNU General Public License (GPL). However, Oracle insists that enterprise users pay for the privilege of using their offering in commercial settings.

What is MariaDB?

How comparable is performance when we’re talking about MariaDB vs MySQL? Well first you need to know what MariaDB is. It’s a close relative MySQL, a software fork of MySQL that its developers came up with in 2009. After Oracle Corp picked up MySQL they wanted to create a stand-in.

MariaDB has SQL in its DNA and allows for ACID-style data processing with assured atomicity, durability, isolation, and consistency for transactions. It has lots of features (which developers at MariaDB Corp have worked hard to ensure are similar). They include support for JSON APIs, replication of parallel data and a variety of storage engines, like MariaDB, Aria, ColumnStore, InnoDB, MyRocks, Spider, TokuDB, and Cassandra.

But there are some areas that will be incompatible between versions. For instance, from version 10.1 on, MariaDB stores JSON data using a different format to MySQL 5.7. To get around this, users who want to copy columns of JSON objects from MySQL to MariaDB can either convert them to the format that the latter uses or run statement-based replication jobs with SQL.

MariaDB Corp. offers training, migration services, remote management, and various tailored products to subscribers. MariaDB Foundation, which was set up in 2012, looks after the source code to make sure that the software remains open source.

MySQL vs MariaDB comparison

MySQL MariaDB
Database Structure MySQL is an open-source relational database management system (RDBMS). As with every other relational database, MySQL users work with staple constituents such as tables, constraints, triggers, roles, stored procedures, and views. Each row, or record is identified with a primary key, and foreign keys are used to make sure that related tables can refer to each other accurately. MariaDB is a fork of MySQL, so the database structure and indexes of MariaDB are the same as they are with MySQL. The upshot of this is that your applications will still work if you switch, because the way that the data is organised doesn’t need to change.
Indexes MySQL also allows hash indexes and InnoDB engine utilises inverted lists for FULLTEXT indexes. Most MySQL and MariaDB indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are held in B-trees. The indexes on spatial data types use R-trees so they are an exception.
Databases Deployment MySQL is written in C and C++ and binaries are available for these systems: Microsoft Windows, OS X, Linux, AIX, BSDi, FreeBSD, HP-UX, IRIX, NetBSD, Novell Netware, and lots more. MariaDB is written in C, C++, Bash, and Perl and has binaries for these systems: Microsoft Windows, Linux, OS X, FreeBSD, OpenBSD, Solaris, and others.

Since MariaDB works as a straight swap for MySQL, you should have no trouble uninstalling MySQL, installing MariaDB in its place, then connecting (so long as the file versions are the same). Please be aware that you should run mysql_upgrade to conclude the upgrade process.

Replication / Clustering Replication with MySQL is asynchronous. One server acts as a master to the others’ slaves. You can replicate all databases, selected databases or even selected tables within a database.

MySQL Cluster is a technology providing shared-nothing (there isn’t one point of failure) clustering and auto-sharding (partitioning) for the MySQL database management system.

Internally, MySQL Cluster uses synchronous replication through a two-phase commit mechanism to ensure that data is written to many nodes. This contrasts with what is normally termed “MySQL Replication”, which is asynchronous.

MariaDB offers master-master and master-slave replication as well. MariaDB uses the Galera Cluster for multi-master. From MariaDB 10.1 onwards, Galera is included with MariaDB. To enable clustering you only need to activate the configuration parameters.
Support MySQL developers and support engineers give round-the-clock coverage for patches, updates and fixing bugs. This is part of Oracle’s lifetime support arrangement.

Oracle offers MySQL Premier Support, Extended Support, and Sustaining Support depending upon your needs.

MariaDB ‘s engineers give round the clock support under the enterprise subscription for mission-critical production systems. Support staff are well versed in both MariaDB and MySQL.
Connectors MySQL provides a variety of database connectors which include C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and Erlang. MariaDB provides various database connectors which include ADO.NET, C, C++, D, Java, JavaScript, ODBC, Perl, PHP, Python, Ruby, and Visual Studio plug-in.

MySQL and MariaDB under Plesk

Plesk keeps its own databases (which can sit locally on the same server as Plesk or remotely) for things like webmail service, its customers’ websites and their apps. It offers integration for MySQL, MariaDB and PostgreSQL database management engines and provides each of them with their own management tools.

Plesk has its own MySQL database on the local server that’s the key component of keeping it running. The good news is that if you have any version after and including 11.5 you can use MariaDB as a replacement. MariaDB vs MySQL performance shouldn’t concern you as MariaDB performs equally well.

Replacing MySQL with MariaDB in Plesk

  1. Backup the databases sitting on your MySQL server with one of these commands:
    • for backing up for backing up all databases:

# mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --all-databases | gzip > /root/mysql.all.dump.sql.gz

  • for just backing up the data which Plesk needs:

# mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --databases mysql psa apsc | gzip > /root/mysql.mysql-psa-apsc.dump.sql.gz

  1. Configure the MariaDB repository on your server. Make sure to choose a version of MariaDB which is equivalent to the version of MySQL which you are using. Plesk supports Maria DB versions 5.5, 10.0, and 10.1.

Note: after you’ve installed MariaDB, don’t disable this repository on your server. Plesk needs it to be enabled for updates.

  1. If the Watchdog Plesk extension is installed on your server then stop it, and also stop any other monitoring services that might start the mysql service after you’ve stopped it manually.
  2. Install MariaDB by following one of the methods mentioned here
  3. Turn on Watchdog and any other services that you stopped in step three.
  4. Tell Plesk about the changes in the MySQL component:

plesk sbin packagemng --set-dirty-flag

  1. (Optional) If you get any MySQL errors following this update, restore the backup you created in step 1:

zcat /root/mysql.all.dump.sql.gz | mysql -uadmin -p`< /etc/psa/.psa.shadow

MariaDB installation guide

Plesk uses MySQL as both admin and client RDBMS. Plesk 11.5 for Linux and higher allows for MySQL alternatives (such as MariaDB or Percona Server) to be used as drop-in replacements for it. Upgrade from installations with drop-in replacements is also supported.

This guide will explain in great detail how you can replace MySQL server on a Plesk installation with MariaDB server. The guide is aimed at administrators with intermediate experience and understanding of package management for their OS. Also basic experience with services management and MySQL maintenance required.

Continue reading