I’m trying to look into an intermittent issue where an INSERT statement into a busy but relatively small InnoDB table (around 6k rows) with relatively small data (no blobs) sometimes takes 8-10 seconds. When I replicate it on an identical test table (on the same server) it doesn’t ever stall. As far as I know there’s no way to use EXPLAIN on an INSERT so how can I determine where it’s spending the time?
Hi,
You can use show profile [URL=“MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.30 SHOW PROFILE Statement”]http://dev.mysql.com/doc/refman/5.5/...w-profile.html[/URL] to check where exactly query taking time. i.e
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql>insert into test values (1,'nilnandan');
Query OK, 1 row affected (0.04 sec)
mysql> insert into test values (2,'nilnandan');
Query OK, 1 row affected (0.04 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------+
| 1 | 0.04051575 | insert into test values (1,'niljoshi') |
| 2 | 0.03764025 | insert into test values (2,'niljoshi') |
+----------+------------+-----------------------------------------+
2 rows in set (0.00 sec)
mysql> show profile for query 2;
+------------------------------+----------+
| Status | Duration |
+------------------------------+----------+
| starting | 0.000082 |
| checking permissions | 0.000012 |
| Opening tables | 0.000034 |
| System lock | 0.000011 |
| init | 0.000016 |
| update | 0.000129 |
| Waiting for query cache lock | 0.000005 |
| update | 0.000019 |
| end | 0.000004 |
| query end | 0.040075 |
| closing tables | 0.000070 |
| freeing items | 0.000052 |
| logging slow query | 0.000005 |
| cleaning up | 0.000005 |
+------------------------------+----------+
14 rows in set (0.00 sec)
Sometime, if possible you can also convert INSERT/UPDATE query to SELECT query and check the EXPLAIN plan but its not possible for all queries.