What is FTWL
FTWRL is short for FLUSH TABLES WITH READ LOCK (FTWRL). This command is mainly used to ensure backup consistency. In order to achieve this goal, it needs to close all table objects, so this command is very lethal and easy to cause hang when executing the command. If it is executed in the main library, the business cannot access it properly; if it is in the standby, it will cause the SQL thread to be stuck and the main standby to be delayed. FTWRL holds the following two global MDL locks:
- Lock_global_read_lock causes all update operations to be blocked
- Global COMMIT locks (make_global_read_lock_block_commit) cause all active transactions to be uncommitted
After FLUSH TABLES WITH READ LOCK is executed, the whole system will remain read-only until the display executes UNLOCK TABLES. Keep that in mind.
How to Locate FTWL Execution Session Efficiently
Since FTWL holds an MDL lock, once it is executed, you will not be able to locate it by locating the DML lock. That is, no clues can be found in the tables related to the results of the show process list and information_schema. Let's look at the following example:
[test]> flush tables with read lock; Query OK, 0 rows affected (0.06 sec) [test]> show full processlist\G *************************** 1. row *************************** Id: 10 User: root Host: localhost db: test Command: Query Time: 0 State: init Info: show full processlist Progress: 0.000 *************************** 2. row *************************** Id: 11 User: root Host: localhost db: test Command: Query Time: 743 State: Waiting for global read lock Info: delete from t0 Progress: 0.000 2 rows in set (0.00 sec) [test]> select * from information_schema.processlist\G *************************** 1. row *************************** ID: 11 USER: root HOST: localhost DB: test COMMAND: Query TIME: 954 STATE: Waiting for global read lock INFO: delete from t0 TIME_MS: 954627.587 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 67464 EXAMINED_ROWS: 0 QUERY_ID: 1457 INFO_BINARY: delete from t0 TID: 8838 *************************** 2. row *************************** ID: 10 USER: root HOST: localhost DB: test COMMAND: Query TIME: 0 STATE: Filling schema table INFO: select * from information_schema.processlist TIME_MS: 0.805 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 84576 EXAMINED_ROWS: 0 QUERY_ID: 1461 INFO_BINARY: select * from information_schema.processlist TID: 8424 2 rows in set (0.02 sec)
From the above output, we only find that session 11 is waiting for a global read lock. But who owns the lock, we can't find any clues from this output. Now let me look at the INNODB STATUS output:
... ------------ TRANSACTIONS ------------ Trx id counter 20439 Purge done for trx's n:o < 20422 undo n:o < 0 state: running but idle History list length 176 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 11, OS thread handle 0x7f7f5cdb8b00, query id 1457 localhost root Waiting for global read lock delete from t0 ---TRANSACTION 0, not started MySQL thread id 10, OS thread handle 0x7f7f5ce02b00, query id 1462 localhost root init show engine innodb status -------- ...
We didn't find any clues from the engine layer. This undoubtedly points out the MDL locks that FTWL holds at the beginning of this article.
Of course, because there are only two sessions in this example, you can see at a glance who owns the global read lock. If it's an online environment, there will be hundreds of conversations. What about that? Please keep looking down. So how can we quickly locate FTWL locks? There are three main methods:
-
If you use Mysql 5.7, you can use performance_schema.metadata_locks
-
If you use Mysql 5.6, you can use performance_schema.events_statements_history.
-
If you're using an older version of Mysql, you can use genearal log or some sql audit log to locate it.
The above three methods are to be opened, and by default they are not. So in our work, we often encounter this situation.
The whole library is blocked. There are a lot of Waiting for Global Readlock waiting in the database. But the three methods mentioned above do not apply to us. So next I'll show you a session that uses gdb to quickly locate and execute FTWL. Let's look at the following examples:
Conversation 1: flush tables with read lock; Query OK, 0 rows affected (0.00 sec) //Conversation 2: mysql> delete from t; --cover hang live //Conversation 3: mysql> show processlist; +----+------+-----------+------+---------+------+------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+------------------------------+------------------+ | 7 | root | localhost | test | Query | 227 | Waiting for global read lock | delete from t | | 8 | root | localhost | NULL | Sleep | 215 | | NULL | | 9 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+------------------------------+------------------+
Because session 1 executes FTWL, the DML in session 2 cannot be executed. Next, we demonstrate how to locate and execute FTWL sessions through gdb. See the following steps
-
Find the process id of myql, ps-ef grep mysql
root 7743 2366 0 05:07 ? 00:00:01 /u02/mysql/bin/mysqld
2. Using GDB to track mysql process to execute gdb-p 7743
3. In mysql, the connected session is stored in a global variable called global_thread_list, in which threads have a variable called global_read_lock to represent the holding of locks. So we can only find threads in gdb that are not empty for global_read_lock. So we execute the following statement in gdb
(gdb) pset global_thread_list THD* elem[0]: $1 = (THD *) 0x4a55de0 elem[1]: $2 = (THD *) 0x4a5cf10 elem[2]: $3 = (THD *) 0x4b24aa0 Set size = 3
The above command outputs the memory addresses of three sessions. Next, we look for the global_read_lock corresponding to each session based on these memory addresses.
4. Print the values of global_read_lock and thread_id in the above three sessions in dgb in turn
(gdb) p ((THD *) 0x4a55de0)->global_read_lock $4 = { static m_active_requests = 1, m_state = Global_read_lock::GRL_NONE, m_mdl_global_shared_lock = 0x0, m_mdl_blocks_commits_lock = 0x0 } //The Global_read_lock for this session is empty, not what we are looking for. (gdb) p ((THD *) 0x4a5cf10)->global_read_lock $5 = { static m_active_requests = 1, m_state = Global_read_lock::GRL_NONE, m_mdl_global_shared_lock = 0x0, m_mdl_blocks_commits_lock = 0x0 } //The Global_read_lock for this session is also empty, not what we are looking for. (gdb) p ((THD *) 0x4b24aa0)->global_read_lock $6 = { static m_active_requests = 1, m_state = Global_read_lock::GRL_ACQUIRED_AND_BLOCKS_COMMIT, m_mdl_global_shared_lock = 0x7f6034002bb0, m_mdl_blocks_commits_lock = 0x7f6034002c20 } //The Global_read_lock of this session is not empty. GRL_ACQUIRED_AND_BLOCKS_COMMIT means global read lock and commit lock. That's what we want. I'll print out its thread_id next. p ((THD *) 0x4b24aa0)->thread_id $7 = 8 //Session 8 executes FTWL
5. We can release the global lock by executing kill 8 to end the session. Keep the blocked session running.
In the new mysql session, execute the following statement mysql> kill 8 Previously blocked conversations show the following results mysql> delete from t; Query OK, 0 rows affected (40 min 20.73 sec)
Summary
Because FTWL holds MetaDataLock type locks, it is very difficult for us to locate the source of the problem. Many students will kill the longest running conversations when they solve similar problems. This method is not desirable. Because the source of the congestion has not been found. So I provide you with a debugging tool to grab mysql internal state variables to locate the source of such problems. I hope you like it.