Performance Tuning MySQL Server

Mysql provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options. Before we get started I suggest you get aquainted with the my.cnf file as well as the tuning parameters within it.

Here is the my.cnf I use:

[mysqld]
 back_log = 75
 skip-innodb
 max_connections = 500
 key_buffer = 384M
 myisam_sort_buffer_size = 64M
 join_buffer_size = 1M
 read_buffer_size = 1M
 sort_buffer_size = 2M
 table_cache = 1800
 thread_cache_size = 384
 wait_timeout = 7200
 connect_timeout = 10
 tmp_table_size = 64M
 max_heap_table_size = 64M
 max_allowed_packet = 64M
 max_connect_errors = 1000
 read_rnd_buffer_size = 524288
 bulk_insert_buffer_size = 8M
 query_cache_limit = 4M
 query_cache_size =128M
 query_cache_type = 1
 query_prealloc_size = 65536
 query_alloc_block_size = 131072
 default-storage-engine = MyISAM
  
[mysqld_safe]
 nice = -5
 open_files_limit = 8192
  
[mysqldump]
 quick
 max_allowed_packet = 16M
  
[myisamchk]
 key_buffer = 64M
 sort_buffer = 64M
 read_buffer = 16M
 write_buffer = 16M

Let’s just look at the important bits.

max_connections = 500 – I use a tool (see below) to check how many current connections I have, and under very heavy load (2000 simultaneous users) I rarely hit 400 concurrent connections to the database. This is because most connections only last for a few milliseconds.

key_buffer = 384M - When tuning a MySQL server, key_buffer_size is very important. This number works well for me and with the mysqlreport script I rarely use 50% of the available memory.

table_cache = 1800 – After key_buffer the next most important variable is your table cache. Again this is set for vBulletin so you may be able to significantly reduce this value depending on the number of tables in your database.

wait_timeout = 7200 – This variable determines the timeout in seconds before mysql will dump a connection. If set to low you will likely receive mySQL server has gone away errors in your log, which in vBulletin’s case is quite common.

max_allowed_packet = 16M – Again if set to low (the default is 8M) users will likely experience errors. 16M has always worked fine for my production environments.

You can grab a mySQL performance script from the guys at hackmysql.com. I use it to tell me how the database is performing under load. You can run this from any shell when you are loaded with traffic. Nothing fancy but should give you an idea.

If you run into problems mtop is a great tool for monitoring a live mySQL server. In particular mtop will show you the queries which are taking the most amount of time to complete. Thanks to konforce on digg for pointing that out.

Don’t forget mySQL’s own lengthy performance tuning whitepaper.