MySQL - Tuning for QMT

For those who aren't MySQL DBA's, here is a good start to tuning the QMT MySQL.

Here is a guy that wrote a nice script for testing the current performance and makes recommendations for improvement.

http://www.day32.com/MySQL

and for additional tool you can use MySQLTuner from

http://mysqltuner.com

In order for the script to have a good snapshot of what is happening, your MySQL should be running for at least 48 hours.

When I ran it, it recommended the following: long_query_time = 5 << WAS 10
query-cache-type = 1 << WAS NOT RUNNING
query-cache-size = 20M << JUST A STARTING POINT
table_cache = 100 << WAS RUNNING 64 AND FULL. There are 78 tables in my database so I just picked 100.

Also, Randy added: I recommend adding a query-cache-limit = 1M so that large, infrequent queries don't hog your cache. Also, make sure your key-buffer is set properly. Look at top or vmstat and see if you're using any swap space. If so, keep all your buffers small until you add more RAM. In fact, make sure your buffers total no more than 80% available RAM. Your problem could be that there's too much unnecessary disk thrashing.

As Randy pointed out, tuning MySQL or just about anything in QMT is pretty much an art instead of a science. Because of hardware, installed apps, system load, etc differences these numbers might not work for you but they are a good start (Your mileage may vary!). You should test thoroughly.

Randy's no brainer my.cnf for QMT
set-variable = max_connections=400
set-variable = query_cache_type=1
set-variable = query_cache_limit=1M
set-variable = query_cache_size=16M
set-variable = thread_cache_size=8
log-slow-queries
long_query_time=2
set-variable = key_buffer_size=512M
set-variable = table_cache=256
set-variable = sort_buffer_size=4M
set-variable = read_buffer_size=1M
To do: ...MySQL5 my.cnf