The Performance Schema maintains statement event summary tables that contain information about minimum, maximum, and average statement latency (see Section 29.12.20.3, “Statement Summary Tables”). Those tables permit high-level assessment of system performance. To permit assessment at a more fine-grained level, the Performance Schema also collects histogram data for statement latencies. These histograms provide additional insight into latency distributions.
Section 29.12.6, “Performance Schema Statement Event Tables” describes the events on which statement summaries are based. See that discussion for information about the content of statement events, the current and historical statement event tables, and how to control statement event collection, which is partially disabled by default.
Example statement histogram information:
mysql> SELECT *
FROM performance_schema.events_statements_histogram_by_digest
WHERE SCHEMA_NAME = 'mydb' AND DIGEST = 'bb3f69453119b2d7b3ae40673a9d4c7c'
AND COUNT_BUCKET > 0 ORDER BY BUCKET_NUMBER\G
*************************** 1. row ***************************
SCHEMA_NAME: mydb
DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
BUCKET_NUMBER: 42
BUCKET_TIMER_LOW: 66069344
BUCKET_TIMER_HIGH: 69183097
COUNT_BUCKET: 1
COUNT_BUCKET_AND_LOWER: 1
BUCKET_QUANTILE: 0.058824
*************************** 2. row ***************************
SCHEMA_NAME: mydb
DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
BUCKET_NUMBER: 43
BUCKET_TIMER_LOW: 69183097
BUCKET_TIMER_HIGH: 72443596
COUNT_BUCKET: 1
COUNT_BUCKET_AND_LOWER: 2
BUCKET_QUANTILE: 0.117647
*************************** 3. row ***************************
SCHEMA_NAME: mydb
DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
BUCKET_NUMBER: 44
BUCKET_TIMER_LOW: 72443596
BUCKET_TIMER_HIGH: 75857757
COUNT_BUCKET: 2
COUNT_BUCKET_AND_LOWER: 4
BUCKET_QUANTILE: 0.235294
*************************** 4. row ***************************
SCHEMA_NAME: mydb
DIGEST: bb3f69453119b2d7b3ae40673a9d4c7c
BUCKET_NUMBER: 45
BUCKET_TIMER_LOW: 75857757
BUCKET_TIMER_HIGH: 79432823
COUNT_BUCKET: 6
COUNT_BUCKET_AND_LOWER: 10
BUCKET_QUANTILE: 0.625000
...
For example, in row 3, these values indicate that 23.52% of queries run in under 75.86 microseconds:
BUCKET_TIMER_HIGH: 75857757
BUCKET_QUANTILE: 0.235294
In row 4, these values indicate that 62.50% of queries run in under 79.44 microseconds:
BUCKET_TIMER_HIGH: 79432823
BUCKET_QUANTILE: 0.625000
Each statement histogram summary table has one or more grouping columns to indicate how the table aggregates events:
events_statements_histogram_by_digest
hasSCHEMA_NAME
,DIGEST
, andBUCKET_NUMBER
columns:The
SCHEMA_NAME
andDIGEST
columns identify a statement digest row in theevents_statements_summary_by_digest
table.The
events_statements_histogram_by_digest
rows with the sameSCHEMA_NAME
andDIGEST
values comprise the histogram for that schema/digest combination.Within a given histogram, the
BUCKET_NUMBER
column indicates the bucket number.
events_statements_histogram_global
has aBUCKET_NUMBER
column. This table summarizes latencies globally across schema name and digest values, using a single histogram. TheBUCKET_NUMBER
column indicates the bucket number within this global histogram.
A histogram consists of N
buckets,
where each row represents one bucket, with the bucket number
indicated by the BUCKET_NUMBER
column.
Bucket numbers begin with 0.
Each statement histogram summary table has these summary columns containing aggregated values:
BUCKET_TIMER_LOW
,BUCKET_TIMER_HIGH
A bucket counts statements that have a latency, in picoseconds, measured between
BUCKET_TIMER_LOW
andBUCKET_TIMER_HIGH
:The value of
BUCKET_TIMER_LOW
for the first bucket (BUCKET_NUMBER
= 0) is 0.The value of
BUCKET_TIMER_LOW
for a bucket (BUCKET_NUMBER
=k
) is the same asBUCKET_TIMER_HIGH
for the previous bucket (BUCKET_NUMBER
=k
−1)The last bucket is a catchall for statements that have a latency exceeding previous buckets in the histogram.
COUNT_BUCKET
The number of statements measured with a latency in the interval from
BUCKET_TIMER_LOW
up to but not includingBUCKET_TIMER_HIGH
.COUNT_BUCKET_AND_LOWER
The number of statements measured with a latency in the interval from 0 up to but not including
BUCKET_TIMER_HIGH
.BUCKET_QUANTILE
The proportion of statements that fall into this or a lower bucket. This proportion corresponds by definition to
COUNT_BUCKET_AND_LOWER / SUM(COUNT_BUCKET)
and is displayed as a convenience column.
The statement histogram summary tables have these indexes:
events_statements_histogram_by_digest
:Unique index on (
SCHEMA_NAME
,DIGEST
,BUCKET_NUMBER
)
events_statements_histogram_global
:Primary key on (
BUCKET_NUMBER
)
TRUNCATE TABLE
is permitted for
statement histogram summary tables. Truncation sets the
COUNT_BUCKET
and
COUNT_BUCKET_AND_LOWER
columns to 0.
In addition, truncating
events_statements_summary_by_digest
implicitly truncates
events_statements_histogram_by_digest
,
and truncating
events_statements_summary_global_by_event_name
implicitly truncates
events_statements_histogram_global
.