Not the answer you need?
Register and ask your own question!

How to profile insert or update queries in MySQL 5.1?

jdlawriejdlawrie EntrantCurrent User Role Contributor
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?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    You can use show profile http://dev.mysql.com/doc/refman/5.5/...w-profile.html 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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.