Russian Version

Part 2. Concurrent queries.

Chapter 6. Locks and deadlocks.

In the last part we discussed how to find cause of the problem in case if it is always repeatable. But there are cases when problem occurs only under particular circumstances.

For example, such easy query can run long enough:

mysql> select * from t;
| a   |
|   0 |
| 256 |
2 rows in set (3 min 18.71 sec)

Frequently you suddenly find slow query in the slow query log. In this case 2 rows in the result set looks too smal for such easy query to run for 3 minutes.

Maybe this is some complicated table with many indexes? Although this should not affect such a case anyway.


mysql> show create table t\G
************** 1. row **************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
1 row in set (0.00 sec)

Lets run same query in mysql command line client:

mysql> select * from t;
| a   |
|   0 |
| 256 |
2 rows in set (0.00 sec)

0 seconds!

But why could this happen? Our main assistant is query SHOW PROCESSLIST:

mysql> show processlist\G
******************* 1. row *******************
     Id: 1311
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 35
  State: Locked
   Info: select * from t
******************* 2. row *******************
     Id: 1312
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 36
  State: User sleep
   Info: update t set a=sleep(200) where a=0
******************* 3. row ******************
     Id: 1314
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.00 sec)

In the result set we see query which we are interesting waiting for another query which needs more time for execution to finish.

What to do? Run queries at different times: don't let them to run in the same time at the application level.

Method #11: use query SHOW PROCESSLIST to see list of queries which run at the same time.

Since version 5.1 INFORMATION_SCHEMA contains table PROCESSLIST. You can also use this table to see list of user processes running simultaneously.

*************************** 1. row ***************************
     ID: 955
   USER: root
   HOST: localhost
     DB: information_schema
   TIME: 0
  STATE: executing
1 row in set (0.01 sec)

This is especially convinient if you have a lot of queries running simultaneously thus want output to be sorted.

Method #12: use table INFORMATION_SCHEMA.PROCESSLIST if you need list of simultaneous queries ordered by one of parameters.

Back Content Forward

Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov