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
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.
with
No comment yet, add your voice below!