Oct 3, 2010

MySQL optimization (II)

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.

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
log-slow-queries
long_query_time = 5
log-queries-not-using-indexes

Other variable showed is related to 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.

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
thread_cache_size = 32

So as to display the threads state, we can run the following order:

mysql> show status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 15    |
| Threads_created   | 23428 |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

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:

mysql> show status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 21    |
+----------------------+-------+
1 row in set (0.00 sec)

If we would want to increase the maximum number of allowed connections (100 by default), we could edit the max_connections parameter in the MySQL configuration file:

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
max_connections=200

wait_timeout = 10
max_connect_errors = 100

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.

[root@centos ~]# cat /etc/my.cnf
[mysqld]
...
innodb_buffer_pool_size = 1024M

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.

mysql> show status like '%key_read%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_read_requests | 242148 |
| Key_reads         | 35618  |
+-------------------+--------+
2 rows in set (0.00 sec)

An optimal ratio should be around 1% (for each disk request made, 100 are performed from the buffer in memory).

If we want to fit this variable, we have to set its value into the my.cnf file.

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


No comments:

Post a Comment