在启用所有PS性能模式工具的情况下，缓慢的数据库、高cpu使用率和许多InnoDB信号量等 …

问题描述​

启用所有性能模式工具后，会观察到以下一个或多个症状:

• MySQL的性能比较慢。
• CPU使用率高于正常水平。
• InnoDB信号量等待的次数高于正常情况，尽管显示引擎INNODB状态不一定显示任何信号量等待。

​问题原因

这是由于启用了所有性能模式工具和使用者:

SELECT ROUND((
SELECT COUNT(*)
FROM performance_schema.setup_instruments
WHERE ENABLED = 'YES'
)/(
SELECT COUNT(*)
FROM performance_schema.setup_instruments
)*100, 2
) AS 'InstrumentsEnabledPct',
ROUND((
SELECT COUNT(*)
FROM performance_schema.setup_instruments
WHERE TIMED = 'YES'
)/(
SELECT COUNT(*)
FROM performance_schema.setup_instruments
)*100, 2
) AS 'InstrumentsTimedPct',
ROUND((
SELECT COUNT(*)
FROM performance_schema.setup_consumers
WHERE ENABLED = 'YES'
)/(
SELECT COUNT(*)
FROM performance_schema.setup_consumers
)*100, 2) AS 'ConsumersEnabledPct';

特别是启用互斥等待工具会导致高性能工作负载的性能显著下降。

附注:对于MySQL 5.6中的一些工作负载，在默认的性能模式设置下也会出现类似的问题。一个症状是gdb(或类似的)跟踪显示许多线程的堆栈跟踪包含:

• #0 in my_timer_cycles () at my_rdtsc.c
• #1 in end_table_io_wait_v1 pfs.cc

这个问题在MySQL 5.7中不太可能出现，在MySQL 5.7中，对于批量I/O操作(例如表或索引扫描的行提取)，性能模式现在可以报告N行的单个事件，而不是报告N次单行事件。

解决方案

重置性能模式工具和使用者

sys模式

CALL sys.ps_setup_reset_to_default(FALSE);

如果您没有sys模式安装后，您可以在MySQL 5.6中使用以下查询来重置性能模式设置:

DELETE
FROM performance_schema.setup_actors
WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%');
​
INSERT IGNORE INTO performance_schema.setup_actors VALUES ('%', '%', '%');
​
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO', TIMED = 'NO'
WHERE NAME NOT LIKE 'wait/io/file/%'
AND NAME NOT LIKE 'wait/io/table/%'
AND NAME NOT LIKE 'statement/%'
AND NAME NOT IN ('wait/lock/table/sql/handler', 'idle');
​
UPDATE performance_schema.setup_consumers
SET ENABLED = IF(NAME IN ('events_statements_current', 'global_instrumentation', 'thread_instrumentation', 'statements_digest'), 'YES', 'NO');
​
DELETE
FROM performance_schema.setup_objects
WHERE NOT (OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = '%'
AND (OBJECT_SCHEMA = 'mysql'AND ENABLED = 'NO'AND TIMED = 'NO' )
OR (OBJECT_SCHEMA = 'performance_schema' AND ENABLED = 'NO'AND TIMED = 'NO' )
OR (OBJECT_SCHEMA = 'information_schema' AND ENABLED = 'NO'AND TIMED = 'NO' )
OR (OBJECT_SCHEMA = '%'AND ENABLED = 'YES' AND TIMED = 'YES'));
​
INSERT IGNORE INTO performance_schema.setup_objects
VALUES ('TABLE', 'mysql' , '%', 'NO' , 'NO' ),
('TABLE', 'performance_schema', '%', 'NO' , 'NO' ),
('TABLE', 'information_schema', '%', 'NO' , 'NO' ),
('TABLE', '%' , '%', 'YES', 'YES');
​
UPDATE performance_schema.threads
SET INSTRUMENTED = 'YES';

禁用“长历史”性能模式表

如果不想重置整个性能模式配置，至少要检查“历史长”表是否与同步对象(如互斥体和rwlocks)的性能模式工具一起使用。因为在检测同步对象时，访问性能模式的“历史长”表可能会对性能产生非常不利的影响。当实例的性能不好时，通过发出以下查询来检查它们是否已启用:

select * from performance_schema.setup_instruments where name like 'wait/sync%' and (enabled='yes' or timed='yes');
select * from performance_schema.setup_consumers where name like '%_history_long' and enabled='yes';

如果启用了它们，请尝试使用以下查询禁用它们，并监视性能:

update performance_schema.setup_instruments set enabled='no', timed='no where name like 'wait/sync%';
update performance_schema.setup_consumers set enabled='no' where name like '%_history_long';

​如果您需要PS工具来进行统计分析，那么至少要考虑禁用“长历史”消费者。