Identifying MySQL Performance Problems

Users report that your application is too slow. After determining there is no physical system resource bottleneck, you turn your attention to the MySQL database.

Finding a Slow SQL Statement

Looking at the currently running MySQL connections with the SHOW FULL PROCESSLIST command, you find the following details:

mysql> SHOW FULL PROCESSLIST\G
...
*************************** 6. row ***************************
     Id: 42
   User: appl
   Host: localhost
     db: NULL
Command: Query
   Time: 3
  State: Query
   Info: SELECT * FROM inventory WHERE item_id = 16102176

This information shows the SELECT statement in the Info column has been running for 3 seconds via the value in the Time column.

What do you do now?

Confirming the Slow Query

Your first step when identifying a potential slow query is to confirm that it is slow when repeated. Verify that it was not a unique instance that might have occurred because of other factors such as locking or a system bottleneck.

Run and Time Your SQL Statement

Re-running the SQL statement using the MySQL command-line client or other client tool is an easy approach for verification:

mysql> SELECT * FROM inventory WHERE item_id = 16102176;
Empty set (3.19 sec)

This confirms the query took more than 3 seconds to execute. When the query takes more than 10 milliseconds, the output from the MySQL command-line client is sufficient.

CAUTION

You should rerun only SELECT statements, because these do not modify any existing data. If your slow running query is an UPDATE or DELETE statement, you can simply rewrite this query as a SELECT statement for verification purposes. For example, if the SQL query was DELETE FROM inventory WHERE item_id = 16102176, you would have rewritten this query as the SELECT statement shown in this example to simulate the performance but not modify any information.

Generate a Query Execution Plan (QEP)

When MySQL executes an SQL query, it first parses the SQL query for valid syntax, and then it constructs a QEP that determines how MySQL will retrieve information from its underlying storage engines. To show the QEP the MySQL query optimizer is expected to construct for an SQL statement, simply prefix the SELECT statement with the EXPLAIN keyword like so:

mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 787338
        Extra: Using where

This vertical output is obtained using the \G statement terminator with the MySQL command line client. This is helpful for parsing output via automated operations and also for any printed form such as in this book. Using the semicolon (;) terminator provides a column orientated approach that is generally easier to read with multiple rows of output.

NOTE

In most cases, an EXPLAIN does not run the actual SQL statement; however, there are some exceptions when part of a SELECT statement might be executed for the optimizer to determine how to construct the QEP. An example is the use of a derived table in the FROM clause, which you would identify with the word DERIVED in the select_type column. You can find more information about these limitations in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html.

If you knew nothing about how to read a QEP, the first two columns you should scan are the indexes used and the number of rows affected. Any query that does not use an index signified by the key column in the preceding output can be considered a poorly tuned SQL query. The number of rows affected in evaluating this SQL statement, as signified by the rows column, contributes to an estimation of how much data is read and can directly correlate to the amount of time required to execute the query. The type column with a value of ALL is also an indicator of a potential problem.

NOTE

Depending on the underlying storage engine, the number of affected rows will be either an estimate or an exact number of rows to be examined. Even when the number of affected rows is an estimate (such as when the InnoDB storage engine manages the table storage), the estimate is typically adequate for the optimizer to make an informed decision.

In this EXPLAIN example no index value was found in the key column. Because this is a single table SELECT statement, this can be considered a full table scan to search for any rows that match the WHERE clause predicate. The rows value can then be considered an approximate value for the number of rows read in order to find the occurrences matching item_id=16102176.