Personal blog of Yzmir Ramirez

Please join Percona’s MySQL Database Administrator, Brad Mickel as he presents How to Analyze and Tune MySQL Queries for Better Performance on Thursday, June 21st, 2018, at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).

 

Query performance is essential in making any application successful. In order to finely tune your queries you first need to understand how MySQL executes them, and what tools are available to help identify problems.

In this session you will learn:

1. The common tools for researching problem queries
2. What an Index is, and why you should use one
3. Index limitations
4. When to rewrite the query instead of just adding a new index

Register Now

 

Brad Mickel

MySQL DBA

The post Webinar Thu 6/21: How to Analyze and Tune MySQL Queries for Better Performance appeared first on Percona Database Performance Blog.

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools.

Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that you use Percona’s pt-visual-explain toolkit. This is one of many Percona Toolkit tools that is useful for those who want to have a different view and an easier time understanding the EXPLAIN output aside from the usual table and vertical views.

As described in the documentation – http://www.percona.com/doc/percona-toolkit/2.2/pt-visual-explain.html#description

“pt-visual-explain reverse-engineers MySQL’s EXPLAIN output into a query execution plan, which it then formats as a left-deep tree – the same way the plan is represented inside MySQL.”

Our sample query:

SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)

In Table view the EXPLAIN output would look like:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)"
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref                | rows | Extra |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+
|  1 | SIMPLE      | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  183 |       |
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | world.Country.Code |    1 |       |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------+

While on vertical view, the EXPLAIN output would be:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Country
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 183
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: City
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: world.Country.Code
         rows: 1
        Extra:

Using the tool…

The EXPLAIN output in pt-visual-explain tree format, I will be showing you three different ways on how to use this handy toolkit.

Piping input into pt-visual-explain, like the one shown below:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" | pt-visual-explain

Parsing input to a file and then pt-visual-explain will reverse-engineer the EXPLAIN output to a query execution plan and formats it to a tree view, as shown below:

$ mysql -Dworld -e "explain SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" > explain.out
$ cat explain.out
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  Country ALL     PRIMARY NULL    NULL    NULL    183
1       SIMPLE  City    ref     CountryCode     CountryCode     3       world.Country.Code      1
$ pt-visual-explain explain.out

Create a file containing the query and then parse the file while pt-visual-explain connects to the mysql instance, like the example shown below:

$ echo "SELECT City.Name, Country.Name FROM City JOIN Country on (City.CountryCode=Country.Code)" > select.out
$ pt-visual-explain -Dworld --connect select.out

The tree structure below would be the result if you run any of the three methods shown above.

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          City
|  |  possible_keys  CountryCode
|  +- Index lookup
|     key            City->CountryCode
|     possible_keys  CountryCode
|     key_len        3
|     ref            world.Country.Code
|     rows           1
+- Table scan
   rows           183
   +- Table
      table          Country
      possible_keys  PRIMARY

So how do we interpret the EXPLAIN plan? Simple. The query will do a table scan on Country table for 183 rows. For each row, it will do an index lookup into the City.CountryCode index with the value from Country.Code, then do a bookmark lookup into the City table.

Please read related posts about limits and errors, and Extended EXPLAIN here and here for more in-depth understanding of MySQL EXPLAIN plans.

And here’s another example using an EXPLAIN DELETE statement (works on MySQL version 5.6.3 and above) :

$ mysql -Dworld -e "EXPLAIN DELETE from Cities where CountryCode IN (select Code from Country where LifeExpectancy < 70.0)" | pt-visual-explain
DEPENDENT SUBQUERY
+- Filter with WHERE
|  +- Bookmark lookup
|     +- Table
|     |  table          Country
|     |  possible_keys  PRIMARY
|     +- Unique subquery
|        key            Country->PRIMARY
|        possible_keys  PRIMARY
|        key_len        3
|        ref            func
|        rows           1
+- Filter with WHERE
   +- Table scan
      rows           581
      +- Table
         table          Cities

Alternatively, one can use – -format=dump option when parsing input into pt-visual-explain and it will create a Data::Dumper output which I think may be harder to understand but may be useable in some other cases.

Conclusion

The next time you want to check your execution plan information you can use pt-visual-explain to compare the result from the output of a regular EXPLAIN statement. You can also use MySQL Workbench, a GUI application that can produce beautified representation of the visual explain with colors, numbers on lines and other information.

For further reading please consult the sections under Modules and Algorithm on the pt-visual-explain documentation.

Other helpful information: pt-visual-explain is derived from Maatkit specifically mk-visual-explain. You can download Percona Toolkit from our download page – http://www.percona.com/downloads/ or get pt-visual-explain only by using $wget percona.com/get/pt-visual-explain.

The post Percona Toolkit collection: pt-visual-explain appeared first on MySQL Performance Blog.