MariaDB has many options for both the client and server than can be adjusted (or tuned) to provide better performance. For most, the default settings are fine and never need to be adjusted. A few, however, should be adjusted for optimal performance. This is particular true for the InnoDB storage engine. The option innodb_buffer_pool_size, which sets the size of the in-memory buffer pool, is probably the most important option to boost performance. It can be set up to 80% of the total memory on the server, but the default is very inadequate 128 MB.

Another option is max_allowed_packet which is used by both client and the server. This sets the maximum size for a data packet or generated/intermediate string. It must be large enough to hold the largest record or BLOB. The client default is not too shabby at 1 GB, but the server default is an anemic 16 MB.

The MariaDB packages for Debian and Red Hat based distributions do install options files (also known as defaults files) for both the client and server, but they are largely placeholders, setting very few or no options. And they have no comments about any of the options. This makes it difficult to know which options should be adjusted for best performance. The options are documented on the MariaDB Documentation site, but this means wading through a long list of options trying to find the ones of interest.

Rather than trying to piece together a new options file every time I installed a MariaDB server, I decided to create template files with the options I might wish to adjust along with comments that describe each one and where to go for additional information. It also uses more optimized values for many options (for example, I increased max_allowed_packet to 128 MB). I then just replace the included options files with my templates, adjust the options that make sense – almost always innodb_buffer_pool_size – and the server is ready to go with much better performance than if the defaults were used.

I also included comments for options that are handled differently on Debian verses Red Hat packages. Where the various files – socket, pid, and log – are located are different for these distributions. And options that are ignored when systemd is used, which is becoming the default, are noted.

These are the options files I use when setting up MariaDB for client servers, so they’ve been tested in real-world production environments. Because MariaDB and MySQL have diverged since version 5 when MariaDB was a drop-in replacement for MySQL, they won’t work for MySQL without modification.

Client File: mariadb-client.cnf
Server File: mariadb-server,cnf