MySQL Performance Tuning

« Apache Dying Weekly | Home | Micropayments »

6/11/2005

Every month or two I take a project to increase the performance of a web application and/or server setup. Most of these can be easily reworked by adding a few simple (or compound) indices on key tables.

I am increasingly adding more and more clients who require a multi-server setup to handle their server load. The easiest setup is a single database server behind a couple of fast web only servers (more in depth on that in a later post). These setups sometimes require tweaking every last bit of performance out of the database server to prevent the jump to a multi-database server setup.

Notes from today:

Opened_tables: Number of tables opened since this server process started. If the value is large, increase table_cache. Typically you would want this to be less than 1 or 2 opened tables per second.
Qcache_hits: This statistic shows the number of accesses to the new MySQL query cache. The query cache stores the results of frequently-used queries so as to speed up response time; a high value here means that MySQL is working efficiently by using the cache instead of rebuilding result sets every time the same query is fired.
Select_full_join: Joins performed without indexes. This should be zero. Easiest performance issue to fix, caused by development errors.
Select_scan: Number of queries that performed a full table scan. In some cases these are OK but their ratio to all queries should be constant. if you have the value growing it can be a problem with the optimizer, lack of indexes or some other problem.
Slow_queries: Number of queries longer than –long-query-time or that are not using indexes. These should be a small fraction of all queries. If it grows, the system will have performance problems.
Threads_created: This should be low. Higher values may mean that you need to increase the value of thread_cache or you have the amount of connections increasing, which also indicates a potential problem.

Other Commands:

tail -f /var/log/mysql-slow.log

take each slow query and run an

EXPLAIN [query]

in a query window to see what indexes are being used
mysql SHOW FULL PROCESSLIST;
http://jeremy.zawodny.com/mysql/mytop/
vmstat 1
mysqldumpslow [slow-query-logfile]

command to get a summary of the queries that appear in the log.
mysqladmin extended -i10 -p
mysqladmin extended -i10 -p | grep Threads_cached
SELECT @@global.[any-system-variables];

(ie.

SELECT @@global.thread_cache_size;

)