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.


  • 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


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.

One thought on “MySQL Tuning Server Parameters”

Leave a Reply

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