Failure Analysis | Federated storage engine table causes monitoring thread to be in Opening table state

Author: Li Pengbo

A member of the DBA team of Akson, mainly responsible for MySQL fault handling and SQL auditing optimization. Persistence in technology, responsible for customers.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

--

Recently, it was found that the monitoring thread status of a MySQL 5.7.32 instance of the customer has been in the Opening table state, and all related queries are made to the information_schema.tables table, as shown in the figure:

Through the show open tables ; statement, it is found that there are not too many open tables:

The relevant parameters are not too unreasonable:

Although the ulimit setting is not very large, it will not have any effect on this

Looking at the MySQL Error log also found no exceptions related to this.

Therefore, we can only use the pstack tool to stack MySQL for analysis. The stack log is as follows:

By analyzing the stack log, it was found that the problem occurs when the query is made using the Federated storage engine table to query the remote instance.

Looking at the tables in the database using the Federated storage engine, I found that there are two tables using the Federated storage engine:

It is not possible to discover through the instance IP and port of the Telnet Feferated server on the instance server:

Therefore, it is speculated that the cause of the problem is: when the monitoring thread queries the information_schema.tables table, it needs to connect to the remote server when it needs to obtain the information of the Federated storage engine table, and when it cannot connect due to network or other reasons, the local monitoring thread will be in Opening. table status.

Next, we design experiments to verify our ideas:

1. Enable Federated storage engine

2. Create a Federated storage engine table, the connected server does not exist

3. Querying information_schema.tables table, thread stuck

4. The thread state is in the Opening table state

This just verifies that our idea is correct.