Thursday 19 April 2012

Configuring MySQL

The defaults that MySQL comes with are probably not what you want.

We got caught by this at work and followed the instructions given in How to calculate a good InnoDB log file size

Edit /etc/mysql/my.cnf to reflect:
query_cache_limit = 4M
query_cache_size  = 32M
table_cache       = 768

innodb_open_files = 512

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_concurrency_tickets = 500
innodb_lock_wait_timeout=200
innodb_autoinc_lock_mode= 2
innodb_commit_concurrency=4
innodb_flush_log_at_trx_commit=2
innodb_support_xa=false
innodb_checksums=0
innodb_doublewrite=0
innodb_max_dirty_pages_pct=15

Discover the location of datadir typically datadir = /var/lib/mysql

service mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0_old
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1_old
service mysql start