MariaDB

What’s in a name?

MySQL is the Kleenex of databases. While it is the name of a specific database, it has also become a generic term used to refer to all databases that are compatible with the original MySQL database.

Per Debian defaults, we run MariaDB as our “MySQL” database.

And, throughout our code and documentation, we refer to MySQL the same way we refer to Web server, Email server and other generic services: as a generic service that we fulfil with MariaDB.

[We also provide Postgres databases, which are different.]

Architecture

Most of our MariaDB databases run through Proxysql, meaning that the client connecting to the database can access the database via the host “localhost” - even though the database is not running on that server.

Instead, our MariaDB databases run on limited number of dedicated server.

Configuration tweaking

There are some useful tools to help you gauge whether settings should be change:

  • SHOW VARIABLES OR SHOW VARIABLES LIKE 'name': shows what the current variable is set to.
  • SET GLOBAL insert_variable_name = insert_variable_value: set a global variable without restarting the service.
  • SHOW GLOBAL STATUS or SHOW GLOBAL STATUS LIKE 'name': shows statistics useful for determining if a variable should be changed.

In keeping with our principle of minimizing divergence from Debian defaults, only a few variables are changed from the shipped defaults.

Some are always set on all servers and cannot be changed:

  • ignore-db-dir=lost+found: ensures that the ext4 lost+found directory won’t be confused for a database.
  • innodb-flush-log-at-trx-commit=2: flush to disk every 1 second rather then with each commit. A tiny risk of data loss in exchange for a big improvement in disk i/o.
  • open_files_limit=65536: intentionally set high, this will be restricted by ulimits on the host. If you get mysql err 24, it means you need to increase the open files limit via /etc/security/limits.d/local.conf
  • skip-name-resolve: don’t try to resolve IP addresses to domain names - we only use IP addresses for permissions so it’s not necessary.
  • tmp_disk_table_size=8589934592: don’t let a single temporary table exceed 8GB. Be sure to give all MariaDB serveres about 50GB of SSD space in /tmp. These two settings combined ensure you won’t have multiple databases writing out giant temp files and using up all the space.
  • character-set-server = utf8 and collation-server=utf8_unicode_ci: It’s about time we make things work for all languages and all character sets.

In addition, there are a few that can be changed from server to server by changing ansible variables.

The most important are RAM related.

For general information, please see the MariaDB page on memory and configuration settings.

  • innodb_buffer_pool_size=256MB
  • mariadb_innodb_log_file_size=48MB

You definitely want to change innodb_buffer_pool_size to 50 - 70% of the RAM on the database server. The log file size can be up to 25% of the buffer pool size BUT BE CAREFUL when changing the log file size. You can to first cleanly shutdown the database, then move your existing /var/lib/mysql/ib_logfile* files out of the way, then start the database.