Ebroch: Created page with "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..."

2024-03-16T16:54:00Z

Created page with "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..."

New page

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

MySQL - Tuning for QMT - Revision history