在啟用所有PS效能模式工具的情況下，效能損耗過高，怎麼辦

問題描述

啟用所有效能模式工具後，會觀察到以下一個或多個症狀:

• 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次單行事件。

解決方案

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

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工具來進行統計分析，那麼至少要考慮禁用“長曆史”消費者。