How To: Optimize MySQL response time

High loaded website can get slow to respond when a lot of different visitors visit sites querying the same mysql database server, making it slow to respond.

There is many ways you can improve mysql server response time: by modifying the cache size, stopping dns resolution ….

Let’s see how to do that.

I remember, once at work, we were having troubles with our databases system. The mysql servers were slow to respond, but when we were logging into those machines, the load was fine, there were quite a few queries going on, but mysql didn’t report it was overwhelmed.

1. Disable DNS Hostname Lookup

After seeking out the reason why the traffic wasn’t going flawlessly, we determine that the mysql server was doing loads of name resolution queries!!!! What for? Why would that machine to a hostname resolution when only local network machines connect to it.

Seeking out in mysqld manual page, we found that this could be disabled by adding the –skip-name-resolve switch.

Under debian based system, such as ubuntu, knoppix … and on most linux distribution, mysql configuration files are located in /etc/mysql/my.cnf.

In order to apply the –skip-name-resolve switch when you start mysqld, simply add:

[mysqld]
.....
......
skip-name-resolve

WARNING: When this option is activated, you can only use IP numbers in the MySQL Grant table.

Here is a tiny benchmark:

With DNS hostname resolution:

$ date; mysql -u root -h 192.168.1.4 ; date
Fri Jul 21 23:56:58 CEST 2006
ERROR 1130 (00000): Host '192.168.1.3' is not allowed to connect to this MySQL server
Fri Jul 21 23:57:00 CEST 2006

it take 2-3 seconds before the server reply that the client IP is not allowed to connect.

Once DNS hostname lookup is disabled:

date; mysql -u root -h 192.168.1.4 ; date
Fri Jul 21 23:56:37 CEST 2006
ERROR 1130 (00000): Host '192.168.1.3' is not allowed to connect to this MySQL server
Fri Jul 21 23:56:37 CEST 2006

The server is replying instantly.

2. Activate Query Cache

After we resolved that issue, we started seeing the database server load increasing, the response time was good after the previous change, but now, we had to lighten a bit the mysql database server’s load.

By checking the Query cache memory:

mysql> SHOW STATUS LIKE 'Qcache%';

we could see that no query cache memory was left. It was neccessary to increase the query cache size.

To get an overview of your query_cache variables state, use the following syntax:

mysql> SHOW VARIABLES LIKE '%query_cache%';

You need to have the query cache enabled in the first place (have_query_cache | YES) and make sure that query_cache_type is set to ON. This is usually activated by default on most linux distribution.

Now, you can increase the query cache size (let say you want 50M) using:

mysql> SET GLOBAL query_cache_size = 52428800;

If you want this setting to be kept when restarting mysql, add:

[mysqld]
...
...
query_cache_size = 52428800;
query_cache_type = 1

3. Conclusion

After doing those changes, there were much more queries resolved from the cache, the effect was that the server was responding quickly without calculating too much has most of the queries where cached.