Java Developer

Configuring MySQL for faster development performance

The default configuration of MySQL is not very fast. They made it generic so that it can run on servers with small memory (For example, a VPS with 256Mb RAM). But desktop nowadays have so much memory and disk space that we can trade off for faster performance.
Performance is important because it affects your productivity. I have experimented with many configurations, but below is the simplest that I came up with that gave me good results.

Again I assume you are using a Debian/Ubuntu based distribution. For the steps below, my operating system is Linux Mint 14 with MySQL 5.5

First shutdown MySQL

service mysql stop

Assuming you don't care if you loose any data, like on a fresh install, you need to remove some files in preparation for the changes in config files.

cd  /var/lib/mysql
rm ibdata1
rm ib_logfile0
rm ib_logfile1

Then edit /etc/mysql/my.cnf and place the following at the bottom of [mysqld] section. It should be below the line where you can see "# ssl-key=/etc/mysql/server-key.pem"

innodb_file_per_table
innodb_autoextend_increment = 50
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 8M
innodb_log_buffer_size = 8M

Then start your mysql service

service mysql start

This should speed up many of your queries and bulk updates. I am not very sure if the above setups have any pitfalls for production use. In such cases, consider to consult a MySQL expert. But for development, this is good enough.
Tags: database, linux, mysql