What does the slow query log say? Does it include key queries of your application?
How long has the MySQL server executed queries in this state?
Has it always been slow or these queries ran fine until a few weeks ago?
What changed if anything?
You start by also following a few guidelines by Shlomi Noach.
He has a few helpful queries listed on this blog post.
- "The following query returns the total size per engine per database." -- Shlomi Noach.
- "See if some index is a prefix of another (in which case it is redundant)" -- Shlomi Noach.
"See if any two indexes are identical" -- Shlomi Noach.
So lets dig a little more into the slow query problem.
This is a simple example so your results will vary.
First gather some numbers:
% Slow Queries
Just demo data so your results will vary.
show status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 7 |
+---------------+-------+
show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 84 |
+---------------+-------+
1 row in set (0.01 sec)
SELECT (7 / 84) * 100 as "% Slow Queries";
+----------------+
| % Slow Queries |
+----------------+
| 8.3333 |
+----------------+
slow_query_log
Gather the location, which the full time DBA should already know but just in case:
show variables like '%slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
Start looking over these queries and running explains to see what might be the problem.
If you want you can review some tools to help.
- http://dev.mysql.com/doc/refman/5.6/en/mysqldumpslow.html
- ie: # mysqldumpslow -t 10 -s at -r mysql-slow.log
- http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html
- ie: # pt-query-digest mysql-slow.log
- http://hackmysql.com/mysqlsla
- ie: # ./bin/mysqlsla --log-type slow /var/lib/mysql/mysql-slow.log
- http://www.fromdual.com/tools
― George R.R. Martin, A Game of Thrones
“Somebody who only reads newspapers and at best books of contemporary authors looks to me like an extremely near-sighted person who scorns eyeglasses. He is completely dependent on the prejudices and fashions of his times, since he never gets to see or hear anything else.”
― Albert Einstein
For the best results, use more than one of these tools and ensure you get the big picture and understand what is being presented to you.
More information on explain can be found below:
- http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
- http://www.lornajane.net/posts/2011/explaining-mysqls-explain
- http://www.slideshare.net/myxplain/explaining-the-mysql-explain
- http://www.slideshare.net/ligaya/explain
- http://www.techrepublic.com/article/three-easy-ways-to-optimize-your-mysql-queries/6137168
Some additional concerns to help query performance.
Query Cache Efficiency
Just demo data so your results will vary.
> SELECT @@have_query_cache;
+--------------------+
| @@have_query_cache |
+--------------------+
| YES |
+--------------------+
>show status like '%Qcache_hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits | 32 |
+---------------+-------+
1 row in set (0.00 sec)
> show status like '%Com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 16 |
+---------------+-------+
1 row in set (0.00 sec)
> SELECT ( 32 / (16 + 32) ) * 100 AS "Query Cache Efficiency";
+------------------------+
| Query Cache Efficiency |
+------------------------+
| 66.6667 |
+------------------------+
1 row in set (0.00 sec)
I do not want to rewrite what Peter has already written so please refer to his blog posts.
Evaluate how efficient your query cache is. How deterministic are your queries ?
- http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
- http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
Joins that need an INDEX
Just demo data so your results will vary.
> show status like '%Select_range_check%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Select_range_check | 0 |
+--------------------+-------+
> show status like '%Select_full_join%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Select_full_join | 1 |
+------------------+-------+
> SELECT (0 + 1) AS "# of Joins that need an index";# This is used below as the numerator in "# of Joins that need an index today"
| # of Joins that need an index |
+-------------------------------+
| 1 |
+-------------------------------+
> show status like 'Uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 335243 |
+---------------+--------+
> SELECT (1/ (335243/86400 )) as " # of Joins that need an index today" ;
+-------------------------------------+
| # of Joins that need an index today |
+-------------------------------------+
| 0.2577 |
+-------------------------------------+
Hopefully you can evaluate the slow queries found with the log as well as review your Query Cache Efficiency as well as find the Joins that need an index all from the "select @@slow_query_log_file;"
Hopefully this has gotten you started on solving a very old concern within MySQL.
No comments:
Post a Comment
@AnotherMySQLDBA