MySQL Enterprise Monitor 4.0.13 Manual
Query Analyzer data is automatically collected and displayed by monitoring MySQL Server 5.6.14 or greater, without any additional plugins required. This functionality is provided by the Performance Schema Statement Digests feature (Performance Schema Statement Digests), added in MySQL 5.6.
MySQL server versions prior to MySQL 5.6.14 are disabled due to a crashing bug within Statement Digests that could be triggered by collecting the data from the Agent.
Collecting Query Analyzer data from Performance Schema provides data about what the statements do to generate their result sets:
Table Lock time
How many rows were examined versus returned
How many temporary tables were created, and whether any were created on disk
Whether range scans were done, and in what form they were done
Whether sorting happened, how many rows were sorted, and what form the sort took
MySQL Enterprise Monitor Agent polls the
performance_schema.events_statements_summary_by_digest
table (every minute, by default) and continually computes the deltas
for each of the normalized statements that are exposed during the
snapshot window. This is dependent on the Performance Schema setup
having the "statements_digest" consumer enabled within
performance_schema.setup_consumers
, which is
enabled by default in MySQL 5.6:
mysql> SELECT * FROM performance_schema.setup_consumers WHERE name = 'statements_digest'; +-------------------+---------+ | NAME | ENABLED | +-------------------+---------+ | statements_digest | YES | +-------------------+---------+
If this is not enabled, then enable it with:
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'statements_digest';
MySQL Enterprise Monitor Agent does not TRUNCATE
the
performance_schema.events_statements_summary_by_digest
table each time it reads from it, as it is possible there may be
other processes/tools consuming this data. Because of this, the
Max Latency statistic that is reported per a normalized statement
within Query Analyzer is actually the maximum since either the
MySQL Server started, or since a TRUNCATE TABLE
performance_schema.events_statements_summary_by_digest
was executed.
The maximum space available for digest computation is 1024 bytes by default; queries exceeding this length are truncated.
As of MySQL 5.7.8, and later, and 5.6.26, and later, this value
can be changed at server startup by setting
the performance_schema_max_digest_length
system
variable. In MySQL 5.6.24, 5.6.24, 5.7.6, and 5.7.7,
use max_digest_length
instead.
For MySQL 5.7 versions prior to 5.7.6, the value cannot be
changed. Nor can it be changed for MySQL 5.6 versions prior to
5.6.24.
The
performance_schema.events_statements_summary_by_digest
table is a sized table in memory within the Performance Schema, and
its size is auto-configured. To check the current size:
mysql> SHOW GLOBAL VARIABLES LIKE 'performance_schema_digests_size'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | performance_schema_digests_size | 5000 | +---------------------------------+-------+
If your application executes more than this number of normalized
statements, then it is possible that you may begin losing some
statement instrumentation. You can monitor this situation with the
Performance_schema_digest_lost
system variable:
mysql> SHOW GLOBAL STATUS LIKE 'Performance_schema_digest_lost'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | Performance_schema_digest_lost | 0 | +--------------------------------+-------+
If you detect that this counter variable is growing, consider
increasing the performance_schema_digests_size
system variable. It is also possible that your statement profile has
changed over time, and that you are now executing different
statements than were originally tracked (this is especially possible
in very long running instances). In this case, you can simply
TRUNCATE TABLE
performance_schema.events_statements_summary_by_digest
,
and the Query Analyzer collection automatically starts again.
When the "Example Query" feature is enabled, Query Analyzer attempts
to get an example of the longest running statement during the
snapshot interval by doing a LEFT JOIN with a groupwise-max on the
performance_schema.events_statements_summary_by_digest
table to the
performance_schema.events_statements_history_long
table. Using this method does not guarantee that an example
statement is always provided because, by default, the
events_statements_history_long
table is a ring
buffer of the last 1000 statements executed. Data is collected in
this way from Performance Schema to minimize load on the monitored
instance rather than polling the
performance_schema.events_statements_history_long
table at too high a frequently to try and gather statistics.
A small subset (approximately 2MB of data) of the snapshot of
known prior values is retained in-memory, and the rest is spooled
to disk. The spool is stored in
$MYSQL_AGENT_HOME
/spool/queryAnalysis.
The "Example Query" feature requires that the
events_statements_history_long
table is enabled
within performance_schema.setup_consumers
(this
is disabled by default within MySQL 5.6):
mysql> SELECT * FROM performance_schema.setup_consumers where name = 'events_statements_history_long'; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_statements_history_long | NO | +--------------------------------+---------+
If this is not enabled, then enable it with:
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';
When "Example Query" and "Example Explain" are enabled, the MySQL Enterprise Monitor Agent attempts to run an EXPLAIN for each example statement that is discovered and ran for longer than the "Auto-Explain Threshold". Due to the way that Performance Schema exposes normalized statements, truncating any normalized statement that is longer than 1024 bytes due to memory concerns within the MySQL Server means it is possible that an EXPLAIN may fail because the truncated statements do not parse correctly when running the EXPLAIN.