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
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