220
開啟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提供了
最後更新：2017-09-14 09:33:17