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;)
Links for more info
- http://dev.mysql.com/doc/mysql/en/query-cache-configuration.html
- http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
Popularity: 3%
Most Popular Posts
{ 1 comment… read it below or add one }
Hello, I am having issues with a mysql server that is reporting “Joins performed without indexes:”. Can you help me out?