问题:
MySQL5.7社区版如何开启metadata_locks
参考:https://dev.mysql.com/doc/refman/5.7/en/performance-schema-quick-start.html
参考:https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
performance_schema默认是启用的,但是默认只启用了部分工具(社区版MySQL5.7.28的performance_schema中目前共有1021个表)。
若要将全部工具和消费者开启,则操作如下:
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 560 rows affected (0.04 sec)
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES';
Query OK, 10 rows affected (0.00 sec)
若仅想在线开启metadata_locks,则操作如下:
--UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
--此值默认已开启了,可检查确认。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
若可停库,则my.cnf中添加如下:
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
开启后用途:
如,可查询全局读锁(flush tables with read lock;)
用如下SQL查询:
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';