Continuing with the previous article about MySQL optimization (I), we are going to start with one of the suggestions provided by the tunning-primer.sh script: The slow query log is NOT enabled.

The queries which spend a lot of CPU (its running time is very high, for example more than 5 seconds) are named slow queries, and it is appropriate to register them in order to be optimized by the developers.

Other good measure can be to activate the logging of those queries which do not use indexes, since this kind of query increases the computer resources consumption because it is necessary more time to loop through the tables. This sort of query should be treated too.

Other variable showed is related with the thread cache (thread_cache_size), which indicates us that seems to be fine.

The size of this parameter depends on the speed with which the new threads are created (Threads_created). For the case that we are discussing (Zabbix database), many threads are not generated quickly, thus we will enable this cache for safety and we will set a low value, such as 32.

Another parameter offered by the script which seems to be also properly configured is the maximum number of allowed connections (Your max_connections variable seems to be fine). In order to see the maximum number of connections which have been used, we can run the following command:

Two other parameters to consider are wait_timeout (when this time is exceeded by an idle connection, it will be closed) and max_connect_errors (maximum number of times that a connection can abort or fail – 10 by default).

Another recommendation given by the script with regard to the InnoDB data storage engine, is to set the innodb_buffer_pool_size variable around 60-70% of the total system memory. For the installation of Zabbix, we will allocate 1024 MB because the computer has got 2 GB.

In the case of tables created by the MyISAM engine, the key parameter is key_buffer_size, which is already correctly adjusted (Your key_buffer_size seems to be fine) because the Zabbix database does not use this kind of tables.

For databases which utilize this sort of search engine with its tables, it is recommended to set this parameter around 25% of the total system memory.

Another way to adjust it is consulting the key_read_requests and key_reads values. The first of them indicates the number of requests which have used the index (memory) and the second, the number of requests made directly from the disk. Then it is clear that is interesting that key_reads is as low as possible and key_read_requests as high.