Optimize MySQL on a Large Database Server

• Tweet
• Sharebar

• Tweet

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.

You may also like to read:
* Quick and simple php based script to check replication status of multiple MySQL Servers
* Setup multiple MySQL database servers in a single Linux host
* Recover or reset root password of MySQL and PostgreSQL Servers
* How to setup MySQL Cluster 7.0 in Redhat based Linux
* Optimize and fix MySQL Server running slow without any load
* How to find out the clients connecting to your MySQL server
* Quickly repair huge corrupted or crashed table in MySQL
* Install and configure PhpMyAdmin to manage multiple MySQL Servers

Tagged as: mysql, optimization