Performance Tuning Mysql for Load

Posted by Miles Evans

On one of the largest Vbulletin based forums I operate we just finished a record month with a little over 217,000 uniques with just shy of half a million total users. As anyone else running a big board knows hitting these types of figures while maintaining a smooth user experience is not always easy. Vbulletin, although likely the best forum software around, pumps a helluva lot of queries.

After 3 months of tweaking here is how I optimized to pull off these numbers with only a single dual Xeon 3.2ghz server with 4GB of memory. This is a very simplified guide and I am very far from being an expert - so this only reflects my personal success with this configuration. At the bottom of the article is a script I use to test performance.

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 and dull performance tuning whitepaper.

Posted Jul 01, 2006 at 03:55 AM | Permalink | Trackback URL | Del.icio.us | DIGG!