##How to Identify MySQL Slow Queries and related concerns
###% 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)
###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)
###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 |
+-------------------------------------+