High-Performance MySQL reading notes: find out who holds the lock _ MySQL

High-Performance MySQL reading notes: find out who holds the lock bitsCN.com

High-Performance MySQL reading notes: find out who holds the lock

Background: in actual use of MySQL, if the access volume is large, a large number of Locked processes may occur, but it is difficult to identify which SQL statements are causing problems, when many people encounter such problems, they mostly query suspicious SQL statements through PhpMyAdmin and KILL them. However, the problem is that there may be many suspicious SQL statements, which is too clumsy to try one by one, some people may restart MySQL in anger, but such a solution is certainly not desirable.

Start the experiment. create a test table foo in the test Database (note: it is the MyISAM table type) and add some data:

Create table if not exists 'foo '(

'Id' int (10) unsigned not null AUTO_INCREMENT,

'Str' varchar (100) not null,

Primary key ('id ')

) ENGINE = MyISAM;

Insert into 'foo' ('id', 'str') VALUES

(1, 'A '),

(2, 'B ');

Open a MySQL command line terminal:

Mysql> USE test;

Mysql> select sleep (12345) FROM foo;

Open a MySQL command line terminal:

Mysql> USE test;

Mysql> UPDATE foo SET str = 'bar ';

Execute show processlist, and you can see that the Locked phenomenon has occurred:

10 User sleep SELECT sleep (12345) FROM foo

20 Locked UPDATE foo SET str = 'bar'

Of course, we know that SLEEP is blocking UPDATE. but if we don't use this experiment, how can we judge whether hundreds of SQL queries come into view at the same time? At this time, no one can pack the ticket, but it can only be blind. experience is sometimes very important, but we still need a clear command here:

Mysqladmin debug

Note: If you have not set the ". my. cnf" configuration file, you may need to enter the user name and password parameters.

After the command is executed, there will be no clear output. do not worry. valuable items have been saved to the error log:

Mysql> show variables like 'log _ error ';

Find the specific path of the error log, open it, and view the last part of the log:

10 test. foo Locked-read Low priority read lock

20 test. foo Waiting-write High priority write lock

In this way, we can see that the SQL statement with id 10 blocks the SQL statement with id 20. As for the specific SQL statement, you can check it in SHOW PROCESSLIST.

BitsCN.com