Skip to content

Instantly share code, notes, and snippets.

@morgo
Created April 1, 2020 20:48
Show Gist options
  • Save morgo/657b6a130db7d7135fdba424b3309544 to your computer and use it in GitHub Desktop.
Save morgo/657b6a130db7d7135fdba424b3309544 to your computer and use it in GitHub Desktop.
Performance Schema Histogram
WITH histogram AS (
SELECT
digest,
CASE
WHEN bucket_timer_low > 10000000000000 THEN '10s'
WHEN bucket_timer_low > 1000000000000 THEN '1s'
WHEN bucket_timer_low > 100000000000 THEN '100ms'
WHEN bucket_timer_low > 10000000000 THEN '10ms'
WHEN bucket_timer_low > 1000000000 THEN '1ms'
WHEN bucket_timer_low > 100000000 THEN '100us'
WHEN bucket_timer_low > 10000000 THEN '10us'
ELSE '1us'
END as bucket_name,
FLOOR(SUM(count_bucket) / sum(SUM(count_bucket)) OVER (PARTITION BY digest)*100) as pct
FROM performance_schema.events_statements_histogram_by_digest
GROUP BY digest, bucket_name
),
ascii AS (
SELECT digest, GROUP_CONCAT(CONCAT_WS(' ', '\n', bucket_name, ' ', REPEAT('#', pct)) SEPARATOR '') as asciiart FROM histogram GROUP BY digest
)
SELECT sa.*, ascii.asciiart AS histogram FROM sys.statement_analysis sa INNER JOIN ascii USING (digest)
ORDER BY exec_count DESC LIMIT 100
\G
..
*************************** 1. row ***************************
query: SELECT * FROM `city` WHERE `id` > ?
db: world
full_scan:
exec_count: 5
err_count: 0
warn_count: 0
total_latency: 17.83 ms
max_latency: 7.32 ms
avg_latency: 3.57 ms
lock_latency: 1.02 ms
rows_sent: 10116
rows_sent_avg: 2023
rows_examined: 10116
rows_examined_avg: 2023
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 035932271023f7be929ccbcf6e32a50aff3680aaeebba7bca35f16b3ef3161ad
first_seen: 2020-04-01 14:46:17.412329
last_seen: 2020-04-01 14:46:33.321191
histogram:
1s
10s
100ms
10ms
1ms ############################################################
100us ########################################
10us
1us
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment