MariaDB Performance Tuning and MySQL Performance Tuning

One of the most popular database servers is MariaDB, which was forked from the MySQL project in 2009. MariaDB is a robust database, and like all complex systems it needs tuning to run at its best. 

History of MySQL and MariaDB

In the world of technology, MySQL is among the most popular open-source databases. In the past, MySQL was an independent company that provided both open-source and commercial databases. MySQL was acquired by Sun Microsystems, and Sun was then acquired by Oracle several years later. As a result of Oracle’s acquisition of Sun, the original MySQL developers forked a project called MariaDB, which was supported by a company called SkySQL. The move was prompted by fears that MySQL might become closed source after Oracle acquired it. MariaDB has since become widely supported and adopted by a large audience in the open source database community.

MySQL and MariaDB Performance tuning

MySQL & MariaDB performance is a vast subject that is heavily influenced by the workload of the application. Although some principles are universal, there are a few standards. Start by checking your server’s hardware. Fast disk IO and a good amount of RAM (physical memory) are essential for databases. Your database server will be happier if it has more RAM and a faster disk. This will also dictate the settings used for the system’s memory consumption. There are a number of oversimplified rules that can be dangerous to follow blindly.

Oversimplified rule : You should adjust InnoDB buffer pool size = 80% RAM.

Reality: While the buffer pool size is probably the most important thing you should adjust, on a dedicated database server you should make sure that the maximum theoretical memory usage of mysqld (or mariadbd) doesn’t exceed about 90% of RAM. You can approximately calculate this based on sizes of various database memory buffers and mysql max connections using the following query:

SELECT (
           (
               @@binlog_file_cache_size +
               @@innodb_buffer_pool_size +
               @@innodb_log_buffer_size +
               @@key_buffer_size +
               @@query_cache_size +
               ( @@max_connections *
                   ( @@binlog_cache_size +
                     @@binlog_stmt_cache_size +
                     @@bulk_insert_buffer_size +
                     @@join_buffer_size +
                     @@max_allowed_packet +
                     @@read_buffer_size +
                     @@read_rnd_buffer_size +
                     @@sort_buffer_size +
                     @@thread_stack +
                     @@tmp_table_size
                   )
               ) +
               ( @@slave_parallel_threads * 
                 ( @@slave_parallel_max_queued )
               ) +
               ( @@open_files_limit * 1024 )
           ) / 1024 / 1024 / 1024) AS max_memory_GB;

It is worth mentioning that the query above assumes a worst case scenario. For example, the reason why max_allowed_packet is multiplied by max_connections is because this is the maximum that will be allowed. Network connections will allocate by default a net_buffer_length memory buffer, but if you start sending enormous queries, this can be stretched all the way up to max_allowed packet.

The number of client connections matters because various memory buffers are allocated per connection. Be careful to not oversize various buffers because mysql swapping is something to be avoided if at all possible.

For achieving high throughput with a large MySQL / MariaDB database system, SSD storage in RAID 10 mode is highly recommended. The server’s response times can be improved by orders of magnitude by just switching to a SSD from a mechanical hard drive. This will seriously increase the disks data rate, allowing faster disk read and write speeds. This is great for disk access requests, and general database operations. It’s also a good idea to store MySQL database data on a separate volume. Again SSD storage being the best option.

The design of the database schema and the use of optimal queries are essential for good database performance. A user’s details such as name, email address, and user name can be stored in a table, but if you run a query searching for the user name without an index on that field, then the performance will be poor since the database server will have to perform a full scan of the table for each such query.

Slow Query Logging – Log Files

Slow query logging is a great way of discovering what queries require attention. This is especially important as; the entire database can be slowed down by a poorly-structured MySQL or MariaDB query. Any query that takes longer than the specified number of seconds can be logged by MySQL / MariaDB. You can then track of these queries and correct them accordingly.

To enable slow query logging, log in to MySQL/MariaDB: mysql -u root -p mixed Enter the following command to enable logging: SET GLOBAL slow_query_log = ‘ON’; mixed The default threshold is 10 seconds. Use the following command to enable logging for any query that takes longer than 1 second:

SET GLOBAL long_query_time = 1

Slow logging options can be adjusted without requiring the restart of the mysql service.

MySQL configuration file

A configuration file .cnf can be edited to change server system variables. For example: to increase the size of the maximum memory table you can use the following line to the config file, in the [mysqld] section: max_heap_table_size=64M. To make changes in the configuration file take effect, the mysql service needs to be restarted.

Query Cache – More Harm Than Good

This may sound counter-intuitive at a glance, but while the query cache may have appeared to be a good idea a long time ago, it proved to do more harm than good in most cases. So much so that the feature was deprecated in MySQL 5.7 and completely removed in MySQL 8.0. The problem with this feature is that whenever any of the underlying tables was changed, all cached query data that refers to any of those tables had to be expunged from the query cache. On most OLTP databases this creates a massive hot-spot in the query cache, and the negative impact of this typically exceeds the positive impact of the cache. Unless, your database is almost entirely read only and runs very slow queries that return small amounts of data. In most cases, the correct solution is writing better queries rather than relying on the query cache.

Use EXPLAIN

When creating a MySQL query, it is important to have optimal queries. MySQL will provide details of the execution plan of a query given to EXPLAIN. If EXPLAIN says that some tables are scanned, or that the query requires scanning of a large number of rows, you probably need to improve indexing for that query on the affected tables.

How Many Concurrent Connections

The maximum number of connections allowed is set using the max_connections parameter (an extra connection above this is allowed for the root user), so that even when all connections are exhausted, the root account can still get in and try to fix it.

Connection limits can also be defined on each user account independently of the overall maximum set by max_connections, e.g.

CREATE USER ‘user’@’host’ IDENTIFIED BY ‘password’ WITH MAX_USER_CONNECTIONS 10;

Once these limits are reached, any new connections are not permitted and this message displays: “User has already more than ‘max user_connections’ active connections” or “Warning: mysql_connect(): Too many connections in /home/www/… “.

If you need to increase the number of connections from the default value, you should make sure that you have enough RAM as discussed earlier. The root user has an extra connection allowed for it beyond the maximum, so in an emergency you can connect as root and set mysql max connections to a higher value.

MySQL Performance Tuning Server

MySQL Server / MariaDB Server Top Tips (unless you know exactly what you are doing)

  1. Adjust your buffer pool size, even if most other settings rarely need changing. Default buffer pool size is almost never appropriate, unless your server and database are tiny. 
  2. The default storage engine in all currently supported versions of MySQL is InnoDB. You should be using the InnoDB engine instead of MyISAM unless you have an overwhelmingly good reason.
  3. Use innodb file per table. This will make each new table created as separate ibd data file.
  4. Keep an eye on your table caches – if you regularly see queries stuck with opening and closing tables statuses, you need to increase your table cache. You may also as a consequence need to increase open files limit.
  5.  Have AUTO_INCREMENT integer primary keys on your table
  6.  Benchmark your queries
  7.  Check your indexes
  8.  Use prepared statements if you are going to call them multiple times
  9. Stick with the default paths unless there is an overwhelmingly good reason to do so. That means /var/lib/mysql. Use a bind mount if necessary.
  10. Disable atime on the file system where /var/lib/mysql resides. By default, most file systems update the access time on every read, leading every read to result in a write. This is in most cases an unnecessary overhead.
  11. Unless you are granting privileges by server name (which you probably shouldn’t) rather than IP, set skip_name_resolve=1 in your config file. This will avoid the delay of making a DNS lookup against the DNS server every time a client connection is initiated.
  12. Take regular backups of your mysql databases