21.10 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.

• Notification level when discovering queries with errors: select the desired notification level for queries with errors.

• Notification level when discovering queries with warnings: select the desired notification level for queries with 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 increases the RAM used by the monitoring agent.

  Important

  This feature requires events_statements_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.

Important

Explains are generated for query data supplied by the Performance Schema.

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