What should Mysql do when a large number of select jams from the library are in Waiting for table flush state?
What this article shares to you is about Mysql from the library a large number of select jams in the Waiting for table flush state how to do, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.
Background:
Mycat read and write separation, apply a large number of select timeout
1. Check
Through the inspection found that a large number of select in the Waiting for table flush state, take a closer look at the processlist and the time period, we can conclude that it is caused by backup plus select slow query!
two。 Reproduce the environment
Session1
Session2
View the processlist status at this time
Mysql > show full processlist
+ -+
| | Id | User | Host | db | Command | Time | State | Info |
+ -+
| | 2 | repl | 47.93.243.162Binlog Dump 43700 | NULL | Binlog Dump | 1527333 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| | 9140 | root | localhost | devops | Query | 564 | User sleep | select *, sleep (1000) from operation_log limit 100 |
| | 9141 | root | localhost | NULL | Query | 0 | init | show full processlist | |
| | 9143 | root | localhost:56880 | NULL | Query | 509 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |
Terminal 2 performs xtracebackup backup
.
.
> log scanned up to (768745274)
. Backup jam
Terminal one executes a slow query
Mysql > select *, sleep (1000) from operation_log limit 100
Session3
Terminal 3 queries the tables involved in the slow query.
[root@iZ2ze66bhrbxkc31nljgjnZ] # mysql-uroot-p *-e "select * from operation_log limit 10" devops
Warning: Using a password on the command line interface can be insecure.
... Blocking state
Processlist status at this time
Mysql > show full processlist
+ -+
| | Id | User | Host | db | Command | Time | State | Info |
+ -+
| | 2 | repl | 47.93.243.162Binlog Dump 43700 | NULL | Binlog Dump | 1527460 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| | 9140 | root | localhost | devops | Query | 691 | User sleep | select *, sleep (1000) from operation_log limit 100 |
| | 9141 | root | localhost | NULL | Query | 0 | init | show full processlist | |
| | 9143 | root | localhost:56880 | NULL | Query | 9143 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |
| | 9150 | root | localhost | devops | Query | 454 | Waiting for table flush | select * from operation_log limit 10 |-- query blocked |
+ -+
Step 1 blocks step 2, which causes step 3 to wait for step 1.
Session4
Terminal 4 queries the tables in other non-slow queries (no blocking).
[root@iZ2ze66bhrbxkc31nljgjnZ] # mysql-uroot-pESBecs00-e "select * from role limit 10" devops
Warning: Using a password on the command line interface can be insecure.
+-+
| | id | role_name | description | status | |
+-+
| | 1 | Super Admin | all permissions | 1 |
| | 3 | Development engineer | Development engineer | 1 |
| | 4 | Operation and maintenance engineer | Transport curtain engineer | 1 |
+-+
[root@iZ2ze66bhrbxkc31nljgjnZ] # mysql-uroot-pESBecs00-e "select * from module limit 10" devops
Warning: Using a password on the command line interface can be insecure.
+-+
| | id | module_name | status | list_order | |
+-+
| | 100 | system management | 1 | 2 | |
| | 600 | Environmental Management | 1 | 3 |
+-+
Solution:
Just kill the original slow query sql!
Xtrace version 2.2 can add a parameter-- lock-wait-query-type=all
Xtrace version 2.4Additive parameter-ftwrl-wait-query-type
This option means that that kind of query is allowed to complete before the global lock is acquired, which is ALL by default and optional update.
Reason:
Before flush tables with read lock successfully acquires the lock, you must wait for all statement execution (including SELECT) to complete. So if there is a slow query executing, or an open transaction, or some other process holding a table lock, flush tables
The with read lock is blocked until all locks are released.
The thread got a notification that the underlying structure for a table has changed
And it needs to reopen the table to get the new structure.
However, to reopen the table
It must wait until all other threads have closed the table in question.
This notification takes place if another thread has used FLUSH TABLES
Or one of the following statements on the table in question:
FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, orOPTIMIZE TABLE.
This is what Mysql should do when a large number of select jams from the library are in the Waiting for table flush state. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
