테이블 플러시를 기다리는 중 MySQL 스레드 분석
- 大家讲道理원래의
- 2017-08-19 10:47:472869검색
최근에 많은 쿼리가 차단되고 결과가 반환되지 않는 경우가 발생했습니다. show processlist를 사용하여 확인한 결과 많은 MySQL 스레드가 테이블 플러시 대기 중 상태인 것을 발견했습니다. 쿼리 문은 항상 차단되었으며 Kill 프로세스로만 해결할 수 있습니다. 먼저 Waiting for tablelush에 대한 공식 설명을 살펴보겠습니다: https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html
스레드가
FLUSH TABLES을 실행 중이고 모든 스레드가 테이블을 닫을 때까지 기다리고 있거나 스레드가 테이블의 기본 구조가 변경되어 테이블을 다시 열어야 한다는 알림을 받았습니다. 그러나 테이블을 다시 열려면 다른 모든 스레드가 문제의 테이블을 닫을 때까지 기다려야 합니다.
이 알림은 다른 스레드가 FLUSH TABLES 또는 문제의 테이블에 다음 문 중 하나를 사용한 경우 발생합니다. FLUSH TABLES tbl_name, ALTER 테이블 , RENAME TABLE, REPAIR TABLE, ANALYZE TABLE 또는 테이블 최적화.
다음과 같이 상황을 시뮬레이션해 보겠습니다. 첫 번째 세션 연결(연결 ID=13)에서는 잠금 테이블을 사용하여 테이블을 잠그는 테스트를 진행했습니다. mysql> use MyDB;
Database changed
mysql> select connection_id();
+-----------------+
mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
<br>
Database changed
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 17 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> flush table test;
세 번째 세션/연결에서 MyDB로 전환하면 “이 기능을 끄면 -A로 더 빠르게 시작할 수 있습니다. ”현재 차단된 내용입니다. 이때 세션을 종료하고 -A 매개변수를 사용하여 데이터베이스에 로그인합니다. 테스트 테이블을 쿼리하면 차단됩니다(물론 다른 테이블에 대한 쿼리는 차단되지 않습니다). 아래와 같이
mysql>use MyDB;
테이블 정보 읽기 열 이름
-A
을 사용하면 이 기능을 꺼서 더 빠르게 시작할 수 있습니다.
mysql> use MyDB; mysql> select * from test;
상태를 보려면 프로세스 목록을 표시하세요. 현재 데이터베이스의 모든 연결 스레드는 17과 18 모두 대기 중 테이블 플러시 상태인 것을 볼 수 있습니다. 아래 스크린샷에 표시된 대로:
프로덕션 환경에서는 잠금 테이블 읽기로 인해 차단이 발생하지 않는 경우가 많습니다. 그러나 느린 쿼리로 인해 플러시 테이블이 테이블을 닫지 못하고 대기 상태에 있었습니다. 예를 들어 아래 테스트 사례에서는 동일한 대형 테이블을 사용하여 느린 쿼리를 시뮬레이션했습니다. 다른 작업은 동일하며 아래와 같이 Waiting for tablelush도 생성되는 것을 볼 수 있습니다. mysql> TEST1 T, TEST1 L에서 T.*를 선택하세요. 个 또 인터넷에 MYSQLDUMP를 백업할 때 매개변수를 사용하지 않는 경우 SINGLE-TRANSACTION을 사용하거나 Flush-Logs를 사용하고 — 단일 트랜잭션 두 매개변수로 인해 이러한 대기 시나리오가 발생할 수도 있습니다. 이 두 매개변수를 함께 사용하면 데이터 덤프를 시작하기 전에 FLUSH TABLES 작업이 수행됩니다. ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ 해결책:
테이블 플러시를 기다리는 중이 나타나면 일반적으로 잠겨 있는 테이블이나 플러시 테이블이 대기하고 테이블을 닫을 수 없게 만드는 느린 쿼리를 찾아야 합니다. 그런 다음 해당 스레드를 종료하지만 이를 정확하게 찾는 방법은 특히 프로덕션 환경에서 어려운 일입니다. show processlist를 사용하면 많은 수의 스레드가 표시됩니다. 현기증이 난다. 문제를 한 번에 찾는 방법은 무엇일까?
느린 쿼리로 인해 테이블 플러시 대기 중 상태인 경우: 볼 수 있습니다. 프로세스 목록에서 시간 값이 큰 스레드를 표시합니다. 그런 다음 심사 및 확인 후 종료합니다. 위 스크린샷에 표시된 것처럼 세션 연결 14가 차단을 유발하는 소스 SQL입니다. 이 스레드의 Time 열 값은 차단된 스레드의 값보다 높아야 한다는 규칙이 있습니다. 이렇게 하면 많은 레코드를 필터링할 수 있습니다.
잠금 테이블 읽기로 인해 테이블 플러시 대기 상태에 있는 상황의 경우: 실험에서 lock table read를 사용한 경우, 해당 세션은 Sleep 상태일 수 있으며 show Engine innodb status G 명령어의 출력 정보에는 나타나지 않습니다. in_use >=1;인 show open tables가 어떤 테이블이 잠겨 있는지 알아내더라도 특정 스레드(연결)를 찾을 수는 없습니다. 그러나 inntop을 사용하여 아래에 표시된 대로 스레드 17이 테이블 테스트를 잠갔고 스레드 17을 innotop에서 찾을 수 있습니다. 속담처럼, 일을 잘하고 싶다면 먼저 도구를 갈고 닦아야 합니다! ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ
또한 공식 문서에는 ALTER TABLE, RENAME TABLE,REPAIR TABLE, 테이블 분석, 또는 OPTIMIZE TABLE로 인해 이러한 종류의 대기가 발생할 수 있습니다. 다음은 아래와 같이 몇 가지 간단한 테스트입니다. 테이블 플러시를 기다리는 또 다른 장면 会话连接（connection id=18)执行下面SQL语句，模拟一个慢查询SQL 会话连接（connection id=6)执行下面SQL语句，分析表test 会话连接（connection id=8)执行下面SQL语句 查看线程的状态，你会发现被阻塞的会话处于 Waiting for table flush状态。 因为当对表做了ANALYZE TABLE后，后台针对该表的查询需要等待，因为MySQL已经检测到该表内部变化，需要使用FLUSH TABLE关闭然后重新打开该表，所以当你查询该表时，就会处于 Waiting for table flush Waiting for table metadata lock 会话连接（connection id=17)执行下面SQL语句，模拟一个慢查询SQL 会话连接（connection id=6）执行下面SQL语句， 修改表结构操作 会话连接（connection id=8）执行下面SQL语句，查询表test 查看线程的状态，你会发现被阻塞的会话处于 Waiting for table metadata lock状态。 mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| 13 | root | localhost | MyDB | Sleep | 90 | | NULL |
| 14 | root | localhost | NULL | Query | 0 | init | show processlist |
| 17 | root | localhost | MyDB | Query | 52 | Waiting for table flush | flush table test |
| 18 | root | localhost | MyDB | Query | 9 | Waiting for table flush | select * from test |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
4 rows in set (0.00 sec)
<br>
mysql>
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| 13 | root | localhost | MyDB | Sleep | 90 | | NULL |
| 14 | root | localhost | NULL | Query | 0 | init | show processlist |
| 17 | root | localhost | MyDB | Query | 52 | Waiting for table flush | flush table test |
| 18 | root | localhost | MyDB | Query | 9 | Waiting for table flush | select * from test |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
4 rows in set (0.00 sec)
<br>
mysql>
mysql>
mysql>
mysql>
mysql> show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| MyDB | test | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
<br>
mysql> kill 17;
Query OK, 0 rows affected (0.00 sec)
<br>
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| 13 | root | localhost | MyDB | Sleep | 442 | | NULL |
| 14 | root | localhost | NULL | Query | 0 | init | show processlist |
| 18 | root | localhost | MyDB | Query | 361 | Waiting for table flush | select * from test |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
3 rows in set (0.00 sec)
<br>
mysql> kill 13;
Query OK, 0 rows affected (0.00 sec)
<br>
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 14 | root | localhost | NULL | Query | 0 | init | show processlist |
| 18 | root | localhost | MyDB | Sleep | 427 | | NULL |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
<br>
mysql>
13. 스레드 죽이기 17로는 문제가 해결되지 않습니다. ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ 
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 18 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select name, sleep(64) from test;
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 6 |
+-----------------+
1 row in set (0.00 sec)
mysql> analyze table test;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| MyDB.test | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.04 sec)
<br>
mysql>
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select * from test;
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------------+----------------------------------+
| 6 | root | localhost | MyDB | Sleep | 22 | | NULL |
| 8 | root | localhost | MyDB | Query | 14 | Waiting for table flush | select * from test |
| 15 | root | localhost | NULL | Sleep | 3 | | NULL |
| 16 | root | localhost | NULL | Query | 0 | init | show processlist |
| 18 | root | localhost | MyDB | Query | 46 | User sleep | select name, sleep(64) from test |
+----+------+-----------+------+---------+------+-------------------------+----------------------------------+
5 rows in set (0.00 sec)
<br>
mysql>
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 17 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select name, sleep(100) from test;
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 6 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> alter table test add tname varchar(10); // rename table test to kkk 同样会引起Waiting for table metadata lock
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select * from test;
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
| 6 | root | localhost | MyDB | Query | 19 | Waiting for table metadata lock | alter table test add tname varchar(10) |
| 8 | root | localhost | MyDB | Query | 6 | Waiting for table metadata lock | select * from test |
| 15 | root | localhost | NULL | Sleep | 8 | | NULL |
| 16 | root | localhost | NULL | Query | 0 | init | show processlist |
| 17 | root | localhost | MyDB | Query | 55 | User sleep | select name, sleep(100) from test |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
5 rows in set (0.00 sec)
<br>
mysql>
위 내용은 테이블 플러시를 기다리는 중 MySQL 스레드 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!