Optimized MySQL 101

Introduction to Optimizing MySQL

The default settings in MySQL are on the conservative side in order to be compatible with initial installations. While there are many tuning options, it is not advisable to make adjustments unless you know they will be helpful. We've seen many people blindly double buffer sizes because they want their database to be faster. What happens is that MySQL allocates more RAM, but it's not allocated to the right places, and performance actually suffers.

The intended audience of this manual is those who have a MySQL database and just want it to work without doing too much research.

MySQL Settings

MySQL can use different engines for table. Most people reading this article will be using either InnoDB or MyISAM.

--here is how you figure it out--

InnoDB

The only configuration changes that people should start with are:

innodb_buffer_pool_size innodb_log_file_size

The innodb_buffer_pool_size tells MySQL how much memory to allocate for InnoDB. Every operation uses the buffer pool, so this is an important value. General guidelines for a dedicated database host is around 75% of RAM. If you run other services on the host, you will need to adjust accordingly. LAMP stacks running Apache and PHP can use a decent amount of RAM per process, so over allocating RAM to MySQL may decrease performance in other areas.

The innodb_log_file_size is where MySQL logs transactions before modifying the actual InnoDB data files. Setting this to 256M is a good start, which will create two 256MB files in your data directory. To do so, shutdown down MySQL, delete the ib_logfile0 and ib_logfile1 files, change the innodb_log_file_size value to 256M, and restart MySQL.