MySQL tuning

At my work in the past I needed to trouble shoot and tune MySQL installations. Here are some best practises I have used.

By Dennis Kruyt Sep 24, 2019
MySQL tuning

innodb_buffer_pool_size

The default innodb_buffer_pool_size after a MySQL installation is too small, maybe  10M, this value is standard and too small for a dedicated large production environment.

jump
Photo by Etienne Girardet / Unsplash

To determine the maximum minimum value, the InnoDB data and indexes must be looked at. The following query gives the RIBPS, Recommended InnoDB Buffer Pool Size with an additional 30% growth. This value may be set to 80% of the memory. But this is only allowed if InnoDB is the only storage engine being used. I the same database is also using for example MyISAM, then this value must not be too high of the available memory and there must also be memory left for the MyISAM engine.

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.3/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     5 |
+-------+
1 row in set (4.31 sec)

mysql>

With this output we can make the following adjustment in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=5G

MySQL needs to be restarted after this

After a few weeks run the following query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status HERE variable_name='Innodb_buffer_pool_pages_data') A,(SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name='Innodb_page_size') B;

This gives the number of pages of InnoDB data that is located in the InnoDB Buffer Pool, to which the innodb_buffer_pool_size can possibly be adjusted after this.

The following query can be used to investigate the performance of the InnoDB buffer.

SHOW INNODB STATUS;

To view the Buffer pool hit ratio, it is best to get as close to 100% as possible. For larger databases this will be approximately 95 ~ 99%. Note that if the value is too small, it will almost always be 100%.

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 47662558; in additional pool allocated 1048576
Dictionary memory allocated 22312480
Buffer pool size   640
Free buffers       0
Database pages     610
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 528736601, created 1244044, written 19482608
0.00 reads/s, 0.00 creates/s, 8.00 writes/s
Buffer pool hit rate 1000 / 1000

MyISAM Key Buffer

The MyISAM storage engine uses a key buffer for caching index data from disk. A correct value can improve performance.

Photo by Tim Johnson / Unsplash

The key counters can be read with the following query.

mysql> show status like 'key%';
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_blocks_not_flushed | 0           |
| Key_blocks_unused      | 346749      |
| Key_blocks_used        | 346980      |
| Key_read_requests      | 43530411625 |
| Key_reads              | 3947698     |
| Key_write_requests     | 16170948756 |
| Key_writes             | 15316984    |
+------------------------+-------------+
7 rows in set (0.00 sec)

Here the following values are important for the key_buffer.

Key_read_requests The number of requests to read a key block from the cache.
Key_reads The number of physical reads or a key block from disk.

With these values you can determine the miss ratio. However, these values are the counters from the moment the database runs. It is better to collect and store metrics over time and put them in a graph.

The current key_buffer is 384M, this seems to be sufficient for now, possibly it can go to 512M. Raising it too high has no adverse effect because MySQL will allocate the memory only with actual use, with InnoDB buffer pool size it will allocate everything immediately.

MySQLTuner

To generate an overview of recommendations for a running database, MySQLTuner-perl can be used. However, it is advisable to first test these recommendations on an acceptance database and todo some performance test on them before taking into production.

Macro guitar frets
Photo by Marcin Nowak / Unsplash

MysqlTunner-perl can be downloaded from the following website
http://mysqltuner.com/

Slow query logging

To gain insight into which queries last longer than X seconds, you can activate a slow query. In the case of a production database, this should only be used for troubleshooting, as this affects the performance of the database. So by default on production slow query logging must be switched off!

Photo by Yuliya Kosolapova / Unsplash

You can turn this on / off without restarting the database.

First check where the logging will be placed.

SELECT @@global.slow_query_log_file;

You can adjust this to another location.

SET @@global.slow_query_log_file = '/var/log/mysql-slow.log';

See which queries will be logged (> X seconds).

SELECT @@global.long_query_time;

You can adjust this to a different number (at least 1).

SET @@global.long_query_time = 5;

Then turn on the slow query logging (and later off).

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL slow_query_log = 'OFF';

Optionally, you can do a flush of the logs.

FLUSH LOGS;

Performance monitoring

Monitoring is key, always collect, store and analyse your metrics. Without this you don't know what impact your changes will have on your database server, linux system and eventually on your application.

Photo by Miguel A. Amutio / Unsplash

There are many opensource tooling that can do this. Some tools I have used in the past that can mesure MySQL metrics are Observium and the TIG stack, TeleGraf, InfluxDB and Grafana.

Barnyard2: Data too long
1 min read

GO TOP