Performance Schema

The Performance Schema instruments statements, stages, waits, memory and I/O. It is on by default (performance_schema is startup-only), and two tables steer it at runtime: setup_instruments (here 1,277 instruments, 796 enabled, 285 timed) and setup_consumers, which has statement history and digests on but the events_waits_* tables off. To study mutex waits, UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'wait/synch/mutex/innodb/%' and enable the events_waits_current consumer; option-file lines such as performance-schema-instrument make that permanent.

events_statements_summary_by_digest groups statements by normalized text (literals become ?) and times them in picoseconds. After a TRUNCATE, eight mysqlslap clients cycled 480 times through five shop queries, one of them a count on the unindexed page_views.customer_id:

SELECT LEFT(DIGEST_TEXT, 38) AS digest, COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 1) AS total_s, ROUND(AVG_TIMER_WAIT / 1e9, 1) AS avg_ms,
       SUM_ROWS_EXAMINED DIV COUNT_STAR AS rows_avg, SUM_NO_INDEX_USED AS no_idx
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'shop' ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

+----------------------------------------+-------+---------+--------+----------+--------+
| digest                                 | calls | total_s | avg_ms | rows_avg | no_idx |
+----------------------------------------+-------+---------+--------+----------+--------+
| SELECT COUNT ( * ) FROM `page_views` W |    96 |   112.1 | 1167.8 |  3000000 |     96 |
| SELECT `id` , `viewed_at` FROM `page_v |    96 |     2.6 |   26.6 |       10 |      0 |
| SELECT `o` . `id` , `o` . `status` , S |    96 |       0 |    0.5 |       25 |     96 |
| SELECT NAME , `email` FROM `customers` |    96 |       0 |    0.3 |        1 |      0 |
| SELECT `title` , `price` FROM `product |    96 |       0 |    0.2 |       13 |     96 |
+----------------------------------------+-------+---------+--------+----------+--------+

One shape took 97% of the time, reading 3 million rows per call to return one number: the query to EXPLAIN (Reading EXPLAIN Output) and index (Composite Indexes). Rank by total time, not by the slowest call.