MySQL Tuning Server Parameters

These are some of the handy MySQL 5.0 tweaks I do to get the most out a MySQL database;
I adjust the following to suit my server, I try to give MySQL as much resource as possible.


key_buffer = 48M
max_allowed_packet = 8M
table_cache = 128
sort_buffer_size = 48M
net_buffer_length = 8M
thread_cache_size = 4
query_cache_type = 1
query_cache_size = 4M

query_cache – this caches repeated SQL queries.
key_buffer – used for caching primary key indexes.
table_cache – tells mysql how many table files handles to keep open simultaneously.
thread_cache_size – this tells mysql to keep worker threads around which are expensive to start up, but cheap to maintain
sort_buffer_size – this value is used during queries to hold results in memory otherwise it creates temporary result tables on disk
net_buffer_length – should help on larger network based queries to improve throughput

Stolen and tweaked from the MySQL manual.
You can see your current cache usage by running the following.

SHOW STATUS LIKE "%cache"

  • If you don’t use replication comment out the log_bin feature. See manual.
  • If you don’t use InnoDB un-comment the skip-innodb feature. See manual.
  • If you don’t know what the above two features are, then do it!

I like to set a default charset too. It saves any confusion later on in life.
Add the following to my.cnf

collation_server=latin1_general_ci

Don’t forget to restart MySQL afterwards.

Finally, I always install Jeremy Zawodny’s mytop tool. If you use lazy-linux Ubuntu, you can apt-get it.

apt-get install mytop

Author: Kieran Barnes

Kieran is a PHP developer with 15 years commercial experience. Specialist in WordPress, CakePHP, CubeCart and all things PHP.

1 thought on “MySQL Tuning Server Parameters”

Leave a Reply

Your email address will not be published. Required fields are marked *