MySQL optimization (III)

Let's go on with the series of articles about MySQL tunning. Remember that in the previous issue, MySQL optimization (II), we got going to break down the suggestions provided by the Tunning Primer Script.

Now, we are going to continue regarding the query cache, since the script shows us which is disabled (Query cache is supported but not enabled).

When a query is executed, the database engine always performs the same task: processes the query, determines how to run it, loads the information from the disk and returns the value to the client. Through this cache, MySQL saves the result of a particular query in memory, so that in many cases the system performance can be significantly improved.

In order to display the query cache status, we can run the following order:

mysql> show status like 'qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

The most important values are Qcache_free_memory (free cache memory), Qcache_inserts (insertions performed in the cache), Qcache_hits (successful insertions) and Qcache_lowmem_prunes (number of times that the cache runs out of memory and must be cleaned).

The result of Qcache_inserts/Qcache_hits division is known as percentage of losses. If the value of this ratio is for example 20%, it means that the 80% of the queries are attended from the cache.

Other important parameter is Qcache_free_blocks, which indicates us that the memory is fragmented whether it has got a high value. To defragment the non contiguous memory blocks, we can run the following command (in fact, there should be set a cron job to run this command every 4 or 8 hours).

[root@centos ~]# mysql -u root -p -e "flush query cache"

[root@centos ~]# crontab -e
0 */4 * * * mysql -u root -pxxxxxx -e "flush query cache"

The parameter wich allows us to adjust the cache size is Qcache_lowmem_prunes, since the larger it is, the more times the cache must be restarted. In order to fit the query cache size, we must set a value for the query_cache_size parameter, inside the MySQL configuration file.

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
query_cache_size  = 128M
query_cache_limit = 4M
query_cache_type  = 1

The query_cache_limit parameter establishes the maximum result wholes size stored in the query cache. If we want whenever possible a query is cached, we must activate the query_cache_type variable.

Another recommendation is related to the table cache (You should probably increase your table_cache). The table_cache variable indicates how many tables can be simultaneously opened. Each table is represented by one disk file (descriptor) and it must be opened before being read.

In order to adjust this parameter, we must take a look at the Open_tables (currently open tables) and Opened_tables (tables wich have been opened) variables.

mysql> show global status like 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 64    |
| Opened_tables | 30    |
+---------------+-------+
2 rows in set (0.00 sec)

If the Opened_tables grows up very quicly, it means that are opening and closing tables for lack of descriptors. In that case, we should increase the table_cache value.

To modify this value in MySQL, we have to edit its configuration file. As well we have to take into account that this variable has to be always less than open_files_limit. Otherwise, we must change it.

And besides, we can also set the table_definition_cache variable, which represents the number of table definitions that can be stored in the definition cache (it should be normally the same as table_cache) and unlike the table_cache, it does not use file descriptors.

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
table_cache = 512
table_definition_cache = 512

open_files_limit = 1024

The script also shows us that around 30% of the temporary tables are created in the disk, with what we could increase the size of the tmp_table_size (if a temporary table in memory exceeds this size, it is automatically moved to disk) and/or max_heap_table_size (maximum value that the tables can grow up in memory) variables.

So as to set correctly these values, you can analyze the Created_tmp_disk_tables (number of temporary tables created on disk) and Created_tmp_tables (number of temporary tables created in memory).

mysql> show status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

As you can see in the previous results, no table is made on disk, then this situation does not correspond with the 30% data provided by the script. This is due to the script does not check the real value of the temporary tables created on disk, since looking its code we can confirm that the script runs a benchmark to generate 5000 aleatory registers and measures its performance ("show /*!50000 global */ status like...").

In the MySQL configuration file, we can change the tmp_table_size and max_heap_table_size values.

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
tmp_table_size = 64M
max_heap_table_size = 32M