| Russian Version |
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.
No:
mysql> show create table t\G
************** 1. row **************
Table: t
Create Table: CREATE TABLE `t` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=257 DEFAULT CHARSET=utf8
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.
mysql> SELECT * FROM PROCESSLIST\G
*************************** 1. row ***************************
ID: 955
USER: root
HOST: localhost
DB: information_schema
COMMAND: Query
TIME: 0
STATE: executing
INFO: SELECT * FROM PROCESSLIST
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 sveta_at_js-client_dot_com |