MySQL update/write query analysis (query profiling)

Do you have a slow MySQL update/insert/delete query?

Obviously, for ‘SELECT’ queries you can prepend the query with “EXPLAIN ” – however that doesn’t work for the other query types (UPDATE/INSERT/DELETE).

So, one solution which may explain why the query is slow is to turn on MySQL’s profiling functionality, like in the following example :

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> update table_b set field_1 = 'something' where id = 5;
Query OK, 1 row affected (9.27 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show profile;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000054 |
| checking permissions         | 0.000005 |
| Opening tables               | 0.000017 |
| System lock                  | 0.000005 |
| Waiting for table level lock | 9.235670 |
| System lock                  | 0.000030 |
| init                         | 0.000053 |
| Updating                     | 0.000066 |
| end                          | 0.000004 |
| Waiting for query cache lock | 0.000135 |
| end                          | 0.032402 |
| query end                    | 0.000005 |
| closing tables               | 0.000022 |
| freeing items                | 0.000012 |
| logging slow query           | 0.000002 |
| cleaning up                  | 0.000002 |
+------------------------------+----------+
16 rows in set (0.00 sec)

In the above case, you can see the query was waiting for a table level lock for nearly 10 seconds.

As it turned out, the server was moderately busy and often running SELECT/read queries which included our table_b as a join, and these queries were often taking 20-30 seconds to complete.

As ‘table_b’ is of type MyISAM, MySQL waits until it can get a write lock on the table – and it can’t do this until the SELECT query has finished.

So, possible solutions :

1. Remove the join on ‘table_b’ in the time consuming query – perhaps by rewriting the long running query
2. Change ‘table_b’ to InnoDB using “ALTER TABLE table_b Engine=InnoDB”