Optimize MySQL on a Large Database Server
On a large server which run primarily mysql service only, the first step you should take is to copy the proper config file which is already optimized for large systems:
# mv /etc/my.cnf/my.cnf_OLD
# cp /usr/share/doc/mysql-server-5.0.44/my-huge.cnf /etc/my.cnf
I’m using 5.0.44 version here on a CentOS 5.2 box. You need to adjust path as per your mysql version.
Next, find out which database engines you are NOT using, you should disable them in config file. For example,let’s say all of our tables are in MyISAM format only, so we can safely disable InnoDB and BDB engines, add following statements in the [mysqld] section of /etc/my.cnf file:
## turn off non used db engines:
skip-innodb
skip-bdb
As most of the today’s servers have plenty of RAM (average 8GB), you can further adjust the values of several variables in [mysqld] section of your config file:
## new values of variables are given
max_connections = 1000
query_cache_size = 128M
table_cache = 2048M
open_files_limit = 2000
There’s no perfect values for variables, its entirely depends on machine resources and your usage. You can experiment further by increasing values until you feel comfortable that mysql is now able to give peak performance and fully utilize available resources.
Many times, badly designed queries slow down the performance, you need to enable slow query log to find out those one:
log-slow-queries
# or you can also provide path
log-slow-queries = /var/log/mysql/slow-queries.log
Table fragmentation also slow down the performance. You should execute Optimize Table command on tables to keep them non-fragmented.
If you’re making connections from localhost only, then enable ’skip-networking’ to disallow connctions from network, this will also enhance the security.
Always try to make connections using IP Address of db server, avoiding hostnames to get rid of DNS resolution.
Apart from these remedies, if you still think that your MySQL Server is running slow, then you may want to troubleshoot it with some issues descriebd in my earlier post.