MariaDB (en)

my.cnf

This is an excerpt¹ of my.cnf, again, the location of this file might depend on your distribution. The settings below will be discussed in detail.

/etc/mysql/mariadb.d/my.cnf

[mariadb]
plugin_load_add                                 = provider_lz4
character-set-server                            = utf8mb4
max_connections                                 = 501
max_statement_time                              = 60
max_allowed_packet                              = 256M
expire_logs_days                                = 3
slave_connections_needed_for_purge              = 0
binlog_format                                   = mixed
log_bin_compress                                = ON
server-id                                       = 1
innodb_buffer_pool_size                         = 3994M
innodb_file_per_table                           = ON
innodb_compression_default                      = ON
innodb_compression_algorithm                    = lz4
query_cache_type                                = OFF
query_cache_size                                = 0
performance_schema                              = ON
tmp_table_size                                  = 32M
max_heap_table_size                             = 32M
table_definition_cache                          = 512

InnoDB Page Compression

I’ve found out rather recently that MariaDB has got the ability to store it’s database compressed on the file system, which I enabled and was rather astonished. The facts: The database - uncompressed - is about 150 GB. But it uses only 44 GB storage, due to InnoDB Page Compression. It’s a great way to save space and also increases performance, because only a fraction of the database’s size - the compressed part - has to be saved on disk.

The three settings

• plugin_load_add
• innodb_compression_default
• innodb_compression_algorithm

Are used to enable this. I’m using lz4 as the compression algorithm, which - although not the most efficient one - is fast and doesn’t use much CPU, so there’s no performance loss at all².

Warning

The bundled - default - compression algorithm is zlib. Again, depending on your distribution, you may have to install the plugin. In any case, before using it you have to enable it, hence the plugin_load_add

Misc settings

• max_statement_time: Again, done because occasionally the queue workers create queries which won’t terminate and cause a jam because other queue workers join the fray. So, this is a safe guard against this kind of behavior.
• innodb_buffer_pool_size: This oddly precise number is due to ansible 😀 I’m using a quarter of the machine’s memory (about 16 GB) for innodb’s buffer pool size. Generally, the more memory you dedicate to that the better. However, since I have to take apache, PHP, Redis and Gentoo’s compiling into account I play it safe by giving only a quarter to the buffer pool³.
• query_cache_type and query_cache_size: This is old news, on a multi-core machine query cache is a very bad idea - it will slow the database down and it’s long deprecated.
• slave_connections_needed_for_purge: This is a kind of gotcha in newer versions. The default ist 1. That means if you haven’t got a secondary/slave setup your binlogs won’t be purged. ever. Since I don’t use a secondary, this is set so the binlogs can be safely purged by MariaDB.

Backup

See MariaDB

Footnotes

1. Lots of standard stuff not worth documenting. I’ve only included the interesting settings ↩

2. There’s a feature request for zstandard/zstd. ↩

3. At 150 GB of database it’s not possible to keep the whole database into RAM anyway. ↩