Lecture
In the previous section, we looked at how to find the cause of the problem, if it always repeats. But there are cases when a problem occurs only under a certain set of circumstances.
For example, such a simple query can take quite a long time:
mysql> select * from t;
+-----+
| a |
+-----+
| 0 |
| 256 |
+-----+
2 rows in set (3 min 18.71 sec)
Most often you find an unexpectedly slow query in a slow query log. In this case, the number of rows in the result is confusing.
Maybe this is some kind of complex table with a lot of indexes? Although it should not play a role in this case.
Not:
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)
Let's try the same thing in the console:
mysql> select * from t;
+-----+
| a |
+-----+
| 0 |
| 256 |
+-----+
2 rows in set (0.00 sec)
0 seconds!
What's the matter? Here our main assistant is the SHOW PROCESSLIST team:
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)
As a result, we immediately see that the request we are interested in is waiting for another one that requires more time to be executed.
What to do? At the application level, dilute time requests: do not let them run simultaneously.
Reception # 11: use SHOW PROCESSLIST to view a list of simultaneous requests.
Starting with version 5.1 INFORMATION_SCHEMA contains the table PROCESSLIST. You can also use this table to view a list of running processes.
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 useful if you have a lot of queries at the same time and you want to sort the output.
Reception number 12: use the INFORMATION_SCHEMA.PROCESSLIST table if you need a list of simultaneous queries sorted by any parameter.
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application