Announcement

Announcement Module
Collapse
No announcement yet.

How to profile insert or update queries in MySQL 5.1?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to profile insert or update queries in MySQL 5.1?

    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?

  • #2
    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

    Code:
    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.

    Comment

    Working...
    X