Submit a ticketCall us

AnnouncementsAre You “Flying Blind?”

When it comes to your complex IT infrastructure, you want to ensure you have a good grasp of what’s going on to avoid any fire drills that result from guesswork. Read our white paper to learn how proactively monitoring your IT environment can help your organization while giving you peace of mind.

Get your free white paper.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > MySQL Live Plans feature in DPA

MySQL Live Plans feature in DPA

Table of contents

Updated January 17, 2018

Overview

This article describes the MySQL live plan feature in DPA and helps to explain why this feature requires certain permissions that might not be expected. In the permissions article for DPA monitoring MySQL one item listed is the following:

"To enable the retrieval of query execution plans, this privileged user must be able to grant the following permissions:

SELECT, INSERT, UPDATE, DELETE on *.*

This is only required when using the Live plan feature outlined in this article. If only say Select on *.* is granted then this feature will just not work for plan examples with INSERT, UPDATE, DELETE in them. 

Environment

  • DPA 10.0 and above monitoring a MySQL database instance

Detail

The Live Plans feature for MySQL obtains the execution plan of a provided SQL statement.

The plan is obtained by executing SQL query "EXPLAIN <sql-to-explain>" against the monitored instance and the plan is displayed in DPA in form of a table. Additional information for the execution plan is obtained by executing "SHOW WARNINGS" SQL query.

 

In order to run an explain plan, DPA needs to have a valid SQL query. On MySQL, DPA is operating with normalized SQL texts (SQLs stripped from numeric values, strings etc.) which aren't valid SQLs. This is why DPA also collects the raw SQL texts, so called SQL examples for each normalized SQL. The examples are then exposed in Live Plans UI and the user can run an explain plan with a SQL example.

 

The number of collected examples is configurable via a parameter.

 

In some cases DPA doesn't collect any example; therefore, it doesn't have a valid SQL text for a normalized SQL. DPA warns the user on the Live Plans page to edit the normalized SQL before running an explain plan.

When obtaining a live plan, the following options can be specified via a dialog:

Explain Plan Options - Radio buttons specifying whether DPA generates the live plan using "EXPLAIN <sql-to-explain>" or 

"EXPLAIN EXTENDED <sql-to-explain>"

or

 "EXPLAIN PARTITIONS <sql-to-explain>". Explains plans generated by the EXTENDED and PARTITION variants contain additional information for MySQL server versions 5.7.2 and bellow.

 

Show warnings - Check box specifying whether "SHOW WARNINGS" command should be executed on MySQL after generating the explain plan. MySQL manifests additional information for explain plans via the warnings (note: these warnings don't mean that something is wrong).
Database - Drop down menu to pick database in which to run the explain plan. You can also enter the database name manually if it is not present in the drop down.
Run Plan as User - Specify a user on the monitored MySQL instance to be used to generate the explain plan.

 

Historical charts
The Live Plans feature is also available on the Historical Charts page of a SQL query. When obtaining a live plan on this page, the options dialog doesn't contain Database drop down menu and the user always has to specify the database name by hand. Otherwise the feature works the same.

 

Keep drilling into a particular SQL statement to arrive at SQL details page:

1.png

 

Click the "Live Plans" tab to arrive at page bellow. The page contains the following pieces of information:

  • List of concrete SQL examples that were collected by DPA. By clicking an example, the SQL text is loaded into the text area labeled "Example SQL Text".
  • Warnings regarding the currently selected SQL example. There are two possible warnings:
    • The SQL text was truncated by MySQL. In this case, the SQL example most probably isn't a valid SQL statement and needs to be adjusted by hand before running explain. Otherwise syntax error will be raised by MySQL.
    • The "Example SQL Text" is not, in fact, a SQL example but the query in normalized form (without literal values etc.). This happens when DPA collects no example for given SQL. The normalized SQL most probably isn't a valid SQL query and needs to be adjusted by hand before running explain.
  • Text area containing SQL text of query that is to be explained. After editing, the original example text can be restored by clicking the "Reload" button.

2018-01-04_15-50-02.png

Click the "Run Live Plan" button to show options dialog:

2018-01-04_17-31-40.png

Click the "Run" button to execute explain plan and see the results:

2018-01-04_17-31-57.png

Note about errors
Most errors that occur while generating an explain plan are shown directly in DPA. That is, the user will see the same error text as on the MySQL server. The only exception, when DPA provides its own error text, are errors occurring due to insufficient permissions. By default, the DPA monitoring user can perform SELECTs only, but in order to generate explain plan for a SQL query, the user asking for the explain plan needs to have permissions sufficient for actually running the explained SQL statement.

 

If permissions are not granted as is Select, Insert, Update, Delete on *.* and the only grant selects then this feature will only work for plan examples for select. 

 

Last modified

Tags

Classifications

Public