如何使用performance_schema的等待事件来排查数据库性能瓶颈

配置表启用等待事件的采集与记录

修改setup_instruments的enabled和timed字段为yes，表示启动对应的instruments。
修改setup_consumers的enabled字段为yes，启动等待事件的consumers。

mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update setup_instruments set enabled='yes',timed='yes' where name like 'wait/%';
Query OK, 331 rows affected (0.01 sec)
Rows matched: 385  Changed: 331  Warnings: 0

mysql> update setup_consumers set enabled='yes' where name like '%wait%';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

复制

[root@balabala ~]# sysbench    --db-driver=mysql --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-db=sbtest --mysql-user='sbtest' --mysql-password='123' --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-ize=5000000 --oltp-tables-count=8 --num-threads=16 --max-time=1800 --max-requests=0  --report-interval=3 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 10000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 10000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 10000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
复制

[root@balabala ~]# sysbench    --db-driver=mysql --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-db=sbtest --mysql-user='sbtest' --mysql-password='123' --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-ize=5000000 --oltp-tables-count=8 --num-threads=16 --max-time=1800 --max-requests=0  --report-interval=3 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Report intermediate results every 3 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 3s ] thds: 16 tps: 754.15 qps: 15175.54 (r/w/o: 10629.08/2125.22/2421.24) lat (ms,95%): 37.56 err/s: 0.99 reconn/s: 0.00
[ 6s ] thds: 16 tps: 748.72 qps: 14980.83 (r/w/o: 10489.82/2557.87/1933.15) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 744.99 qps: 14904.87 (r/w/o: 10432.24/2559.98/1912.65) lat (ms,95%): 38.25 err/s: 0.33 reconn/s: 0.00
[ 12s ] thds: 16 tps: 733.68 qps: 14666.92 (r/w/o: 10264.84/2534.38/1867.70) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
复制

此时可以通过 iostate -x 2 5 ，top 等查询系统性能，但是这些在数据库内部事件信息是如何体现的呢？

为例方便等待事务的统计，先创建一个试图，用于实时统计当前的等待事件（非历史数据）
mysql> create view sys.test_wait as  select sum(timer_wait) as timer_wait,    sum(number_of_bytes) as number_of_bytes ,            event_name,operation from events_waits_current  where  event_name!='idle'
      group by event_name,operation;
Query OK, 0 rows affected (0.02 sec)
复制

也可以直接查询event_waits_current表 ，返回的数据会比较多且对凡蝴蝶结过没有分组聚合。

可以通过这个方法看到再MySQL数据库内部最为耗时的部分，并为此进行性能优化。

总结

在MySQL上线之前，可以通过类似的方法进行MySQL数据的性能测试，压测的时候可以大致确定MySQL的性能瓶颈方向。

复制