Slow Query Log

The slow query log (Server Logs) writes each statement slower than long_query_time (seconds, fractions allowed, default 10) to slow_query_log_file once slow_query_log is on. log_slow_extra adds rows read, sorts and temporary tables per entry, and log_queries_not_using_indexes adds unindexed statements of any speed. The Performance Schema load ran with long_query_time = 0, which logs everything: 490 entries in 15 seconds, so keep that to short windows.

pt-query-digest from Percona Toolkit 1,555 (github.com/percona/percona-toolkit (https://github.com/percona/percona-toolkit 1,555 ), GPL-2.0; sudo apt install percona-toolkit gives 3.7.1) groups entries by fingerprint and ranks them. The cut only shortens query IDs to fit the page:

pt-query-digest --limit=100%:5 --report-format=profile slow.log | cut -c 1-24,43-

# Profile
# Rank Query ID          Response time  Calls R/Call V/M
# ==== ================= ============== ===== ====== ===
#    1 0x6D35C40A4CEB43A 112.1023 97.7%    96 1.1677  0.08 SELECT page_views
#    2 0x0EEBB85A118EEC5   2.5522  2.2%    96 0.0266  0.00 SELECT page_views
#    3 0xD777DBF59B6F1E9   0.0411  0.0%    96 0.0004  0.00 SELECT orders order_items
#    4 0xE3B3C0F9E64EFD2   0.0216  0.0%    96 0.0002  0.00 SELECT customers
#    5 0x5CA6F8547AE7552   0.0172  0.0%    96 0.0002  0.00 SELECT products
# MISC 0xMISC              0.0001  0.0%    10 0.0000   0.0 <2 ITEMS>

The ranking matches Performance Schema. V/M (variance to mean) flags erratic shapes. The full report adds per-query percentiles and a sample statement to paste into EXPLAIN. It also reads general logs and tcpdump captures (--type).