21.7 Query Analysis Advisors

This section describes the Query Analysis advisors.

Average Statement Execution Time Advisor

Monitors the average execution time of a normalized SQL statement and generates events if the execution time exceeds the defined thresholds.

This advisor has the following parameters:

  • Average Execution Time Thresholds: Generates events if the average execution time exceeds the defined thresholds.

  • Minimum Execution Count: Minimum number of times a normalized statement must be executed before it can generate an event.

  • One Alert per Query: Specify how events are generated. The possible values are:

    • Yes: generate an event for each normalized query that exceeds a threshold

    • No: generate a single event per MySQL Server summarizing all queries that exceed the thresholds. This is the default behavior.

  • DML Statements Only: Specify for which statements events are generated. The possible values are:

    • Yes: generate events for DML statements only.

    • No: generate events for all SQL statements.

Query Pileup Advisor

Alerts when query pileups occur, when the number of threads running increase rapidly over a short period of time. For example, based on the defaults for this advisor, if the exponential moving average of Threads_running has increased by 50% or more, but less than 80%, over the last 1 minute, it raises a Warning alert.

  • Window Size: duration of the moving average window over which monitoring is done.

  • Growth Rate Thresholds: percentage growth rate of the running statements during the defined moving average window.

  • Minimum Running Threads: the minimum number of running threads before an event is generated.

SQL Statement Generates Warnings or Errors

Generates events when a normalized SQL statement generates errors or warnings over a period of time.

  • One Alert Per Query: generate events for queries which return errors or warnings. Possible values are:

  • Yes: generate an event for each normalized query which returns an error or warning.

  • No: generate a single event, per MySQL server, summarizing all queries which generated errors or warnings.

Query Analysis Reporting

Enables capturing and reporting of query analysis data.

  • Enable Example Query: provides detailed data about the queries and their parameters. Enabling this parameter results in an increase in the RAM used by the monitoring agent.


    This feature requires events_statement_history_long be enabled in performance_schema.setup_consumers. This is disabled by default in MySQL 5.6.

  • Enable Example Explain: executes EXPLAIN on the selected statement. This is executed for statements whose runtime exceeds the value defined in Auto-Explain Threshold.

  • Auto-Explain Threshold: Explains are executed for statements whose runtime is longer than the value defined here.


Explains are generated for query data supplied by the MySQL Enterprise Monitor Proxy and Aggregator, Connector/J plugin, and Performance Schema sources.

Explain is supported for all DML statememts on MySQL 5.6.3 or higher. On earlier versions, only SELECT is supported.