439 阿裏雲技術社區[雲棲]

metadata lock的解決方案

文章
https://yq.aliyun.com/articles/175039?spm=5176.100239.0.0.jNFOsC 提出了會發生Metadata-lock場景以及如何在數據庫運維方麵避免產生的建議，但是其實5.6/5.7版本已經提供了一種直接定位該問題的方法：啟用performance_schema功能，5.7更是針對Metadata-lock，提供了表來定位SQL。
再來回顧一下：
Metadata-Lock的引入是為了在並發條件下，防止session1的查詢事務未結束的情況下，session2對表結構進行修改，以保護元數據的一致性。在session1持有 metadata-lock的情況下，session2處於等待狀態：Waiting for table metadata lock

(1)sesion1:S1 S3; session2:S2

(2)在沒有metadata-lock 鎖的情況下，session2在session1執行過程中對元數據進行了修改，將導致session1 兩次返回結果集結構不同

哪些操作會獲取metadata-lock?

表結構的更改（alter ）
創建刪除索引
刪除表
獲取表上表級寫鎖（lock table tab_name write）

哪些操作會引起其他事務獲取metadata-lock

慢查詢
顯示或者隱式的開啟事務後未提交或回滾：begin開始的事務沒有及時提交
表上有失敗的查詢事務（？）

### session1
root@10.20.200.190:working 03:05:30> begin;
Query OK, 0 rows affected (0.00 sec)

root@10.20.200.190:working 03:05:33> select * from test;
+------+---------------------+
| id   | gmt_modified        |
+------+---------------------+
|    1 | 2015-04-01 00:00:00 |
|    1 | 2015-04-02 00:00:00 |
|    1 | 2015-03-02 00:00:00 |
|    1 | 2015-03-05 00:00:00 |
|    1 | 2015-02-05 00:00:00 |
|    1 | 2014-02-05 00:00:00 |
+------+---------------------+
6 rows in set (0.02 sec)

### session2
root@localhost:working 03:05:20> alter table test change id id int(20);

root@localhost:performance_schema 03:04:52> show processlist;
+-------+----------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------+-----------+---------------+
| Id    | User     | Host                 | db                 | Command     | Time  | State                                                         | Info                                  | Rows_sent | Rows_examined |
+-------+----------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------+-----------+---------------+                            |         0 |             0 |
| 18225 | root     | localhost            | performance_schema | Query       |     0 | starting                                                      | show processlist                      |         0 |             0 |
| 20981 | root     | localhost            | working            | Query       |  1003 | Waiting for table metadata lock                               | alter table test change id id int(20) |         0 |             0 |                                                              | NULL                                  |         0 |             0 |
| 25323 | root     | 10.20.200.170:63407  | working            | Sleep       |  1087 |                                                               | NULL                                  |         0 |             0 |
+-------+----------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------+-----------+---------------+
3 rows in set (0.00 sec)

怎麼處理 metadata_lock?

(1)找到並殺掉引起medatdata_lock的process id(不能精確定位)

select concat('kill ',id,';') from information_schema.processlist where time> (select time from information_schema.processlist where state = 'Waiting for table metadata lock') and db=(select db from information_schema.processlist where state = 'Waiting for table metadata lock') and user = 'root';

也可以通過show engine innodb status查詢到持鎖的會話信息，例如：
mysql -uroot -p*** -hxxx -P3309 -e "show engine innodb status \G;"|grep cleaning

(2)5.6.6之後提供了performance_schema性能庫，用戶監測數據庫性能

mysql> select a.SQL_TEXT,a.CURRENT_SCHEMA,b.PROCESSLIST_USER,b.PROCESSLIST_HOST,b.PROCESSLIST_TIME,b.PROCESSLIST_ID from performance_schema.events_statements_current a join performance_schema.threads b on a.THREAD_ID = b.THREAD_ID where b.PROCESSLIST_TIME > 10\G
*************************** 1. row ***************************
        SQL_TEXT: update t1 set name = 'c' where id = 1
  CURRENT_SCHEMA: test
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_TIME: 390
  PROCESSLIST_ID: 38
1 row in set (0.00 sec)

(3)因為無法獲取引起metadata_lock的具體SQL內容，所以要求開發人員：

提交邏輯簡單的SQL
及時關閉事務
DBA及時發現並優化慢查詢SQL
對頻繁執行且更改不頻繁的數據做緩存

(4)MySQL5.7 新增performance_schema
5.7新增動態性能視圖，記錄數據庫運行狀態。
該功能以插件的形式存在於數據庫，需要在編譯的時候添加參數或者運行時打開該功能。

配置文件設置：

[mysqld]
performance_schema=ON
performance-schema-consumer-*consumer_name*=*value*
### *consumer_name* 可以在 setup_consumers 中找到

mysql> SHOW VARIABLES LIKE 'perf%';
+--------------------------------------------------------+---------+
| Variable_name                                          | Value   |
+--------------------------------------------------------+---------+
| performance_schema                                     | ON      |
| performance_schema_accounts_size                       | 100     |
| performance_schema_digests_size                        | 200     |
| performance_schema_events_stages_history_long_size     | 10000   |
| performance_schema_events_stages_history_size          | 10      |
| performance_schema_events_statements_history_long_size | 10000   |
| performance_schema_events_statements_history_size      | 10      |
| performance_schema_events_waits_history_long_size      | 10000   |
| performance_schema_events_waits_history_size           | 10      |
| performance_schema_hosts_size                          | 100     |
| performance_schema_max_cond_classes                    | 80      |
| performance_schema_max_cond_instances                  | 1000    |
...
###如：
[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

編譯時開啟：

shell> cmake . -DWITH_PERFSCHEMA_STORAGE_ENGINE=1
###或者隻使用部分功能
shell> cmake . -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
        -DDISABLE_PSI_STAGE=1 \
        -DDISABLE_PSI_STATEMENT=1

###驗證是否安裝成功
shell> mysqld --verbose --help
...
  --performance_schema
                      Enable the performance schema.
  --performance_schema_events_waits_history_long_size=#
                      Number of rows in events_waits_history_long.
...
###查看engine performance_schema是否安裝成功
mysql> SHOW ENGINES\G
...
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
...

運行時開啟：

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'performance_schema'
       AND TABLE_NAME LIKE 'setup%';
+-------------------+
| TABLE_NAME        |
+-------------------+
| setup_actors      |
| setup_consumers   |
| setup_instruments |
| setup_objects     |
| setup_timers      |
+-------------------+
###可以使用update 對上述表進行設置

以下是為了收集metadata_lock 的配置，配置之後需要重啟！！！

[mysqld]
performance_schema=1
performance-schema-instrument='wait/lock/metadata/sql/mdl=YES'
performance-schema-consumer-global_instrumentation=YES

mysql> update  performance_schema.setup_consumers set ENABLED = 'NO' ;
mysql> update performance_schema.setup_consumers set ENABLED = 'YES' where name in ('global_instrumentation');

mysql> update performance_schema.setup_instruments set ENABLED = 'NO',TIMED='NO' ;
mysql> update performance_schema.setup_instruments set ENABLED = 'YES',TIMED='YES' where name = 'wait/lock/metadata/sql/mdl';

重啟之後，需要等待一段時間，等數據庫收集完信息後會在 setup_* 表中查看到相關的信息

###session1
root@localhost:(none) 07:17:46> begin;
Query OK, 0 rows affected (0.00 sec)

root@localhost:(none) 07:17:48> select * from working.test;
+------+---------------------+
| id   | gmt_modified        |
+------+---------------------+
|    1 | 2015-04-01 00:00:00 |
|    1 | 2015-04-02 00:00:00 |
|    1 | 2015-03-02 00:00:00 |
|    1 | 2015-03-05 00:00:00 |
|    1 | 2015-02-05 00:00:00 |
|    1 | 2014-02-05 00:00:00 |
+------+---------------------+
6 rows in set (0.00 sec)

root@localhost:(none) 07:17:50> 

###session2
root@localhost:(none) 07:14:10> 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 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | performance_schema | metadata_locks |       140320629563808 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           57552 |              4 |
| TABLE       | working            | test           |       140320558091968 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           49983 |              8 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

root@localhost:(none) 07:18:06> 

###session3
root@localhost:working 07:19:45> alter table test change id id varchar(20);

###session2
oot@localhost:(none) 07:18:06> 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           |       140320582895264 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5559  |           58161 |              7 |
| SCHEMA      | working            | NULL           |       140320582895504 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5544  |           58161 |              7 |
| TABLE       | working            | test           |       140320582895664 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           58161 |              7 |
| BACKUP      | NULL               | NULL           |       140320582895744 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | lock.cc:1382      |           58161 |              7 |
| TABLE       | working            | test           |       140320582896144 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:3927       |           58161 |              7 |
| TABLE       | performance_schema | metadata_locks |       140320629563808 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           57552 |              5 |
| TABLE       | working            | test           |       140320558091968 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6314 |           49983 |              8 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
7 rows in set (0.00 sec)

root@localhost:(none) 07:20:12> show processlist;
+-------+-------------+---------------------+--------------------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+
| Id    | User        | Host                | db                 | Command     | Time  | State                                                         | Info                                      | Rows_sent | Rows_examined |
+-------+-------------+---------------------+--------------------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+
| 49946 | root        | localhost           | NULL               | Sleep       |   171 |                                                               | NULL                                      |         6 |             6 |
| 57515 | root        | localhost           | NULL               | Query       |     0 | starting                                                      | show processlist                          |         0 |             0 |
| 58124 | root        | localhost           | working            | Query       |    49 | Waiting for table metadata lock                               | alter table test change id id varchar(20) |         0 |             0 |
+-------+-------------+---------------------+--------------------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+
8 rows in set (0.00 sec)

以上為通過性能視圖查詢到的元數據鎖信息，找到之後需要盡快釋放鎖，使用以下SQL，快速定位引起鎖的SQL

select concat('kill ',b.PROCESSLIST_ID,';') from (select OWNER_THREAD_ID from performance_schema.metadata_locks where OBJECT_SCHEMA = '' and OBJECT_NAME = '' group by LOCK_STATUS having count(*) = 1 )a join performance_schema.threads b on a.OWNER_THREAD_ID = b.THREAD_ID ;

元數據鎖是為了保證查詢的一致性而加的，並無壞處；但是鎖的存在影響了並發的性能；SQL上線前需要嚴格審計，做足預防措施；線上觸發元數據鎖之後，盡快定位問題，殺掉問題session。

打開performance_schema，對性能的影響還需要測試

最後更新：2017-09-03 23:32:39

metadata lock的解決方案

哪些操作會獲取metadata-lock?

哪些操作會引起其他事務獲取metadata-lock

怎麼處理 metadata_lock?

以下是為了收集metadata_lock 的配置，配置之後需要重啟！！！

元數據鎖是為了保證查詢的一致性而加的，並無壞處；但是鎖的存在影響了並發的性能；SQL上線前需要嚴格審計，做足預防措施；線上觸發元數據鎖之後，盡快定位問題，殺掉問題session。

打開performance_schema，對性能的影響還需要測試

上一篇：專訪iDST華先勝：城市大腦，對城市的全量、實時認知和搜索

下一篇： ECS經典網絡實例支持升級到企業級實例

相關內容

熱門內容

最新內容

metadata lock的解決方案

哪些操作會獲取metadata-lock?

哪些操作會引起其他事務獲取metadata-lock

怎麼處理 metadata_lock?

以下是為了收集metadata_lock 的配置，配置之後需要重啟！！！

元數據鎖是為了保證查詢的一致性而加的，並無壞處；但是鎖的存在影響了並發的性能；SQL上線前需要嚴格審計，做足預防措施；線上觸發元數據鎖之後，盡快定位問題，殺掉問題session。

打開performance_schema，對性能的影響還需要測試

上一篇： 專訪iDST華先勝：城市大腦，對城市的全量、實時認知和搜索

下一篇： ECS經典網絡實例支持升級到企業級實例

相關內容

熱門內容

最新內容

上一篇：專訪iDST華先勝：城市大腦，對城市的全量、實時認知和搜索