MySQL 5 Optimization and Tuning Guide

my.cnf: If you have installed or upgraded to MySQL 5, chances are that you may not find the /etc/my.conf file. Don’t worry as there are several sample configuration files that come with MySQL. You can invoke locate my-large.cnf to find the sample configuration file for MySQL 5 on your server. By default, on Red Hat Enterprise, it can be located at the following location.

Setting MySQL 5 global options:
To set global MySQL5 options, we will need to copy my-large.cnf to /etc/my.cnf
Setting MySQL 5 server-specific options:
By default, MySQL5 data directory is /var/lib/mysql. To set server-specific options for MySQL5, we can move the sample MySQL configuration file to our data directory.
For our MySQL5 optimization and tuning tutorial, we will be setting global MySQL5 configurations by copying the my.cnf file to /etc/ directory. In case, you are waiting for me to give you the command:
cp -p /usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf /etc/my.cnf
Getting information about current values:
We can obtain information about the currently set values by running the following command. Abbreviated output is displayed for reference.
# mysqladmin variables -uroot -p”PASS”
Decrease the value of connect_timeout:
By default, the wait_timeout value is 28800. Unless you want MySQL to keep waiting for 28800 seconds (480 minutes or 8 hours), please decrease its value according to your application needs. On the test server, I have the wait_timeout set to 60.
Open the file /etc/my.cnf and under [mysqld], place a line similar to following
Increase connect_timeout from 5 to 10
Decrease interactive_timeout from 28800 to 100
Increase join_buffer_size from 131072 to 1M
Increase query_cache_size from 0 to 128M
Increase query cache limit from 1048576 to 2M
Increase max allowed packet size from 1M to 16M
Increase table cache cache from 256 to 1024
Increase sort buffer size from 1M
Increase read buffer size from 1M
Increase read_rnd_buffer_size to 4M
Other settings
key_buffer = 256M
MySQL Query Cache
To maximize use of MySQL query cache, similar queries used by an application should be identical, i.e. their casing must mache. From MySQL:
Queries are compared before parsing, so the following two queries are regarded as different by the query cache:
SELECT * FROM tbl_name
Select * from tbl_name
Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.
How to optimize disk seek time on your MySQL 5 server?
To optimize disk seek time on your server, you can try distributing the data onto more than one hard disk drive.
Using BENCHMARK() function (Output shows 10000000 simple additions can be performed in 0.93 seconds)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 558 to server version: 5.0.15-standard
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> SELECT BENCHMARK(1000000,1+1);
| BENCHMARK(1000000,1+1) |
| 0 |
1 row in set (0.07 sec)
mysql> SELECT BENCHMARK(10000000,1+1);
| BENCHMARK(10000000,1+1) |
| 0 |
1 row in set (0.93 sec)