QmailtoasterMain Page | About | Help | FAQ | Special pages | Log in

Printable version | Disclaimers | Privacy policy

MySQL - Tuning for QMT

From Qmailtoaster

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

Retrieved from "http://wiki.qmailtoaster.com/index.php/MySQL_-_Tuning_for_QMT"

This page has been accessed 4,029 times. This page was last modified on 29 June 2009, at 06:43. Content is available under GNU Free Documentation License 1.2.


Find

Browse
Main page
Community portal
Current events
Recent changes
Random page
Help
Edit
View source
Editing help
This page
Discuss this page
New section
Printable version
Context
Page history
What links here
Related changes
My pages
Log in / create account
Special pages
New pages
File list
Statistics
More...