开启MySQL performance_schema后对性能的影响

原文地址

先占坑，还需要进一步测试

接上篇
使用 sysbench 对数据库进行压测，观察开启performance_schema后，对数据库性能的影响

压测计划

数据库版本为：percona mysql 5.6.35
prepar数据量：1000W
压测时长：3600S
并发：200/500/800

压测步骤

不开启performance_schema

mysql> show variables like "%performance_schema%"
    -> ;
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema                                     | OFF   |
| performance_schema_accounts_size                       | -1    |
| performance_schema_digests_size                        | -1    |
| performance_schema_events_stages_history_long_size     | -1    |
| performance_schema_events_stages_history_size          | -1    |
| performance_schema_events_statements_history_long_size | -1    |
| performance_schema_events_statements_history_size      | -1    |
| performance_schema_events_waits_history_long_size      | -1    |
……
+--------------------------------------------------------+-------+
32 rows in set (0.01 sec)

使用sysbench 对数据库进行压测

sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=192.168.3.26 --mysql-port=3312  --mysql-db=sbtest --oltp-table-size=10000000 --oltp_tables_count=3 --mysql-user=root --mysql-password=123456 prepare
sysbench --mysql-host=192.168.3.26 --mysql-port=3312 --mysql-user=root --mysql-password=123456 --test=./tests/db/oltp.lua --num-threads=200 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=3600 --max-requests=0 --percentile=99 run
sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=192.168.3.26 --mysql-port=3312  --mysql-db=sbtest --oltp-table-size=10000000 --oltp_tables_count=3 --mysql-user=root --mysql-password=123456 cleanup

开启performance_schema

SELECT * FROM setup_instruments WHERE NAME LIKE "statement/%";
update  setup_instruments set ENABLED="NO",TIMED ="NO" ;
update  setup_instruments set ENABLED="YES",TIMED ="YES" WHERE NAME LIKE "statement/%";
update setup_consumers  set ENABLED ="NO"; 
update setup_consumers set ENABLED ="YES" WHERE NAME LIKE "events_statements_current";
update setup_consumers set ENABLED ="YES" WHERE NAME LIKE "statements_digest";
update setup_consumers set ENABLED = "YES" where name like "%instrumentation";
SELECT * FROM setup_timers WHERE NAME = "statement";

使用sysbench 对数据库进行压测

sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=192.168.3.25 --mysql-port=3312  --mysql-db=sbtest --oltp-table-size=10000000 --oltp_tables_count=3 --mysql-user=root --mysql-password=123456 prepare
sysbench --mysql-host=192.168.3.25 --mysql-port=3312 --mysql-user=root --mysql-password=123456 --test=./tests/db/oltp.lua --num-threads=200 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=3600 --max-requests=0 --percentile=99 run
sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --mysql-host=192.168.3.25 --mysql-port=3312  --mysql-db=sbtest --oltp-table-size=10000000 --oltp_tables_count=3 --mysql-user=root --mysql-password=123456 cleanup

测试结果收集

结论

（1）开启MySQL5.6的performance_schema 性能监控功能，对数据库性能影响有限
（2）performance_schema提供了

原文地址