Fault analysis | federated storage engine table causes the monitoring thread to be in the opening table state

author ： Peng Bo Li

Love can be born DBA Team members , Mainly responsible for MySQL Troubleshooting and SQL Audit optimization . A commitment to technology , Be responsible for the customer .

In this paper, the source ： Original contribution

* Produced by aikesheng open source community , Original content is not allowed to be used without authorization , For reprint, please contact the editor and indicate the source .

--

Recently found a customer's one MySQL 5.7.32 The monitoring thread state of the instance has been in Opening table state , And it's all right information_schema.tables Table to do related queries , Pictured ：

adopt show open tables ; Statement discovery opened tables Not too much ：

The relevant parameters are not unreasonable ：

although ulimit The setting is not very big , But it won't have any impact

see MySQL Error There are no exceptions found in the log .

So you can only use pstack Tool pair MySQL Stack for analysis , The stack log is as follows ：

By analyzing the stack log, it is found that , When the problem occurs, the query will use Federated The storage engine table queries the remote instance .

View database usage Federated The table that stores the engine , I found that two tables used Federated Storage engine ：

By on the instance server Telnet Feferated Instance of the server IP And port discovery don't work ：

So I guess the reason for the problem is ： Monitoring thread in query information_schema.tables Table time , When you need to get Federated When storing the information of the engine table, you need to connect to the remote server Server , When you can't connect due to network or other reasons , This will cause the local monitoring thread to be in Opening table state .

Next, we design experiments to verify our ideas ：

1. Enable Federated Storage engine

1. Create a Federated The table that stores the engine , Connected server non-existent

1. Inquire about information_schema.tables surface , Thread stuck

1. Thread state is in Opening table state

This just proves that our idea is correct .