kieranbarnes

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

This entry was posted on Friday, July 13th, 2007 at 16:54 and is filed under Linux, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Was this post useful to you? Let me know, buy me a beer!
Alternatively, if you're feeling impecunious, you may like to subscribe to my RSS feed, or see other articles in the Linux, MySQL category.