Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.