Configuring MariaDB for Optimal Performance
This article is to help you configure MariaDB for optimal performance.
Note that by default MariaDB is configured to work on a desktop system and should because of this not take a lot of resources. To get things to work for a dedicated server, you have to do a few minutes of work.
For this article we assume that you are going to run MariaDB on a dedicated server.
Feel free to update this article if you have more ideas!
MariaDB is normally configured by editing the my.cnf file.
The following my.cnf example files were included with MariaDB until MariaDB 10.3.0. If present, you can examine them to see more complete examples of some of the many ways to configure MariaDB and use the one that fits you best as a base. Note that these files are now quite outdated, so what was huge a few years ago may no longer be seen as such.
InnoDB & XtraDB Storage Engine
InnoDB or XtraDB is normally the default storage engine with MariaDB.
- You should set innodb_buffer_pool_size to about 80% of your memory. The goal is to ensure that 80 % of your working set is in memory!
The other most important InnoDB variables are:
Some other important InnoDB variables:
Aria Storage Engine
- MariaDB uses by default the Aria storage engine for internal temporary files. If you have many temporary files, you should set aria_pagecache_buffer_size to a reasonably large value so that temporary overflow data is not flushed to disk. The default is 128M.
- If you don't use MyISAM tables explicitly, you can set key_buffer_size to a very low value, like 64K.
Lots of Connections
A Lot of Fast Connections + Small Set of Queries + Disconnects
- If you are doing a lot of fast connections / disconnects, you should increase back_log and if you are running MariaDB 10.1 or below thread_cache_size.
- If you have a lot (> 128) of simultaneous running fast queries, you should consider setting thread_handling to
Connecting From a Lot of Different Machines
- If you are connecting from a lot of different machines you should increase host_cache_size to the max number of machines (default 128) to cache the resolving of hostnames. If you don't connect from a lot of machines, you can set this to a very low value!
- MariaDB Memory Allocation
- Full List of MariaDB Options, System and Status Variables
- Server system variables
- mysqld options
- Performance schema helps you understand what is taking time and resources.
- Slow query log is used to find queries that are running slow.
- OPTIMIZE TABLE helps you defragment tables.