background

When backup tools such as xtrabackup are used for backup, there will be global lock. Normally, lock occupancy time is very short, but occasionally long lock occupancy will lead to system write blocking. The phenomenon is that show process list sees many sessions showing wait global read lock, which may have a great impact on business. And show process list can't see which session holds the global lock. If the backup process is killed directly, the process may be killed, but the lock is still not released, and the database can't be written. At this point, we need to have a fast way to locate the session holding the global lock, killing the corresponding session database and returning to normal.

Usually this kind of emergency happens, which requires DBA to have the ability to quickly restore business. If there is no reserve in peacetime, it is certainly too late to find a way now. So I sorted out several ways to help me quickly locate the lock session in the actual failure to restore business, which is very effective and can be shared with you.

Method

Method 1: Using metadata_locks view

This method only applies to MySQL version 5.7 or above, which adds metadata_locks to performance_schema. If metadata locks are enabled before locking (default is not enabled), it is easier to locate global lock sessions. The process is as follows.

Open the probe corresponding to the metadata lock
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

//Analog locking
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140613033070288 | SHARED              | EXPLICIT      | GRANTED     | lock.cc:1110      |          268969 |             80 |
| COMMIT      | NULL               | NULL           |       140612979226448 | SHARED              | EXPLICIT      | GRANTED     | lock.cc:1194      |          268969 |             80 |
| GLOBAL      | NULL               | NULL           |       140612981185856 | INTENTION_EXCLUSIVE | STATEMENT     | PENDING     | sql_base.cc:3189  |          303901 |            665 |
| TABLE       | performance_schema | metadata_locks |       140612983552320 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6030 |          268969 |             81 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.01 sec)

OBJECT_TYPE=GLOBAL  LOCK_TYPE=SHARED Represents global locks


mysql> select t.processlist_id from performance_schema.threads t join performance_schema.metadata_locks ml on ml.owner_thread_id = t.thread_id where ml.object_type='GLOBAL' and ml.lock_type='SHARED';
+----------------+
| processlist_id |
+----------------+
|         268944 |
+----------------+
1 row in set (0.00 sec)

Locate the lock session ID and kill the session directly.

Method 2: Using events_statements_history view

This method is suitable for MySQL version 5.6 or above, enabling performance_schema.events_statements_history (5.6 is not enabled by default, 5.7 is enabled by default). This table will execute the SQL history. If there are too many requests, it will automatically clean up the early information, and it is possible to clean up the information of the locked session. The process is as follows.

mysql> update performance_schema.setup_consumers set enabled = 'YES' where NAME = 'events_statements_history'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.events_statements_history where sql_text like 'flush tables%'\G
*************************** 1. row ***************************
              THREAD_ID: 39
               EVENT_ID: 21
           END_EVENT_ID: 21
             EVENT_NAME: statement/sql/flush
                 SOURCE: socket_connection.cc:95
            TIMER_START: 94449505549959000
              TIMER_END: 94449505807116000
             TIMER_WAIT: 257157000
              LOCK_TIME: 0
               SQL_TEXT: flush tables with read lock
                 DIGEST: 03682cc3e0eaed3d95d665c976628d02
            DIGEST_TEXT: FLUSH TABLES WITH READ LOCK
...
    NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)

mysql> select t.processlist_id from performance_schema.threads t join performance_schema.events_statements_history h on h.thread_id = t.thread_id where h.digest_text like 'FLUSH TABLES%';
+----------------+
| processlist_id |
+----------------+
|             12 |
+----------------+
1 row in set (0.01 sec)

Method 3: Using gdb tool

If both of the above are not used or can not be enabled in time, you can try the third method. Use gdb to find all thread information, view the global lock object held in each thread, output the corresponding session ID, in order to facilitate rapid positioning, I wrote a script form. gdb interaction mode can also be used, but after attach mysql process, mysql will be completely hang, read requests will also be affected, it is not recommended to use interactive mode.

#!/bin/bash
set -v

threads=$(gdb -p $1 -q -batch -ex 'info threads'| awk '/mysql/{print $1}'|grep -v '*'|sort -nk1)
for i in $threads; do
  echo "######## thread $i ########"
  lock=`gdb -p $1 -q -batch -ex "thread $i" -ex 'p do_command::thd->thread_id' -ex 'p do_command::thd->global_read_lock'|grep -B3 GRL_ACQUIRED_AND_BLOCKS_COMMIT`
  if [[ $lock =~ 'GRL_ACQUIRED_AND_BLOCKS_COMMIT' ]]; then
    echo "$lock"
    break
  fi
done

# The thread_id variable, version 5.6 and 5.7 are different. Version 5.6 is thd - > thread_id, and version 5.7 is thd - > m_thread_id.

Script output

######## thread 2 ########
[Switching to thread 2 (Thread 0x7f610812b700 (LWP 10702))]
#0  0x00007f6129685f0d in poll () from /lib64/libc.so.6
$1 = 9 Here it is. mysql Conversation in ID
$2 = {static m_active_requests = 1, m_state = Global_read_lock::GRL_ACQUIRED_AND_BLOCKS_COMMIT, m_mdl_global_shared_lock = 0x7f60e800cb10, m_mdl_blocks_commits_lock = 0x7f60e801c900}

But the actual environment may be more complex, and you may not be able to get the information you want with gdb. Is that all right?

Method 4: show process list

If the backup program uses a specific user to perform the backup, if it is root user backup, the greater the time value is, the greater the probability of locking session. If the business also uses root access, the emphasis is that the state and info are empty. Here's a little trick to quickly filter. After screening, try kill ing the corresponding ID, and then observe whether or not. There is also a wait global read lock state session.

mysql>pager awk '/username/{if (length($7) == 4) {print $0}}'|sort -rk6
mysql>show processlist

If all of the above methods are ineffective, the ultimate release of the ultimate trick is to __________.

Method 5: Restart and try!

If you have a better way, you can leave a message to share.