文章
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 两次返回结果集结构不同
- ### 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)
(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内容,所以要求开发人员:
(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 对上述表进行设置
- [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;
来源: https://yq.aliyun.com/articles/191384