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
Related posts
- cPanel MySQL Tuning
The default configuration for MySQL provided by cPanel is pretty barren. /etc/my.cnf Thats it. One... - SQLyog Query Profiler
MySQL has always lacked the sophisticated profiling tools shipped with proprietary databases like SQL Server,... - MySQL Server on CentOS, what the RPM doesn’t do for you
Installing MySQL via YUM Once the updates have finished we will be ready to install... - [Snippet] Tuning / Tweaking MySQL
There are a couple of good scripts out there that will help you to tune... - MySQL 5.0 or 5.1 to 5.5 Upgrade Traumas on CentOS
Ignoring all the panic-mongers on the rest of the internet upgrading MySQL from 5.0 or 5.1...
