mysql性能优化等待事件
1.物理实体关系图
2.统计表层级
3.Performance_schema系统信息统计表
1.performance_timers表
TIMER_NAME: 计数器的名称，为NULL表示平台不支持，在SETUP_TIMERS表中可使用PERFORMANCE_TIMERS表字段不为NULL的计时器
TIMER_FREQUENCY: 表示每秒对应的计数器单位的数量
TIMER_RESOLUTION: 计时器的精度，表示每个计时器被调用时额外增加的值
TIMER_OVERHEAD: 使用计时器获取事件的开销最小周期值
2.setup_timers表
NAME: 计时器类型，对应事件类别
TIMER_NAME: 计时器类型名称，对应PERFORMANCE_TIMERS表的TIMER_NAME，即主表的这个字段不为NULL，即可在外键表上面使用，这个字段在此表上面可以修改，并且可以立即影响监控信息
3.setup_consumers表
NAME: consumers的名称
ENABLED: 是否启用consumers
4.setup_instruments表
NAME: instruments名称
ENABLED: 是否启用instruments
TIMED: 是否收集时间信息
Idle: socket空闲的时间
Stage: 语句的每个执行阶段的统计
Statement: 统计语句维度的信息
Wait: 统计各种等待事件，比如IO，mutux，spin_lock,condition
每类的instrument数目，idle包含1，memory包含376个，stage包含129个，statement包含193个，transaction包含1个，wait包含321个
每类的数目随着mysql版本的更新会不断变化
5.setup_objects表
默认mysql，performance_schema，information_schema系统schema下的表和其他对象不在监控范围，其他数据库表是监控的
6.setup_actors表
Setup_actors表配置是否为新的前台server线程启动监视和历史事件日志记录
表存储行数受限于performance_schema_setup_actors_size系统变量的限制
HOST: IP主机名或者DNS解析，%代表可以接受任何远程访问过来的主机
USER: 代表用户信息，%接收任何合法的用户客户端连接
ROLE: 5.7版本不支持，8.0以后使用
ENABLED: 是否启用对于前面HOST,USER,ROLE组合的客户端线程的监控YES OR NO
7.threads表
监控server线程都会生成一行相关信息
4.Mysql性能问题-等待事件
Performance_schema配置表启用等待事件信息
启用等待事件setup_instruments
update setup_instruments set enabled='yes',timed='yes' where name like 'wait/%';
查看等待事件setup_instruments
select * from setup_instruments where name like 'wait/%';
启用等待事件setup_consumers
update setup_consumers set enabled='yes' where name like '%wait%';
查看等待事件setup_consumers
select * from setup_consumers where name like '%wait%';
安装sysbench软件包
./sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.20.7.243 --mysql-port=3306 --mysql-user=root --mysql-password=xxxxxx --mysql-db=bytontest --test=tests/db/oltp.lua --oltp_tables_count=20 --oltp-table-size=1000000 --num-threads=16 --max-requests=0 --report-interval=1 --rand-init=on prepare
./sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.20.7.243 --mysql-port=3306 --mysql-user=root --mysql-password=xxxxxx --mysql-db=bytontest --test=tests/db/oltp.lua --oltp_tables_count=20 --oltp-table-size=1000000 --num-threads=16 --max-requests=0 --report-interval=1 --rand-init=on run
Sysbench压力测试结果
在16个并发线程的oltp压力，TPS在200-300之间，延迟时间在100ms以上，说明cpu未完全使用
OLTP test statistics:
queries performed:
read: 421372//总select数量
write: 120392//总update、insert、delete语句数量
other: 60196//commit、unlock tables以及其他mutex的数量
total: 601960
transactions: 30098 (501.40 per sec.) //通常需要关注的数字(TPS)
read/write requests: 541764 (9025.25 per sec.)//读写需求
other operations: 60196 (1002.81 per sec.)
ignored errors: 0 (0.00 per sec.) //忽略的错误数
reconnects: 0 (0.00 per sec.)//重新连接数
General statistics:
total time: 60.0276s//max-time指定的压测实际
total number of events: 30098285932 //总的事件数，一般与transactions相同
total time taken by event execution: 960.3598s
response time:
min: 4.92ms
avg: 31.91ms//95%的语句的平均响应时间
max: 146.16ms
approx. 95 percentile: 72.01ms
Threads fairness:
events (avg/stddev): 1881.1250/6.34//事件的平均数和方差
execution time (avg/stddev): 60.0225/0.00
Top命令查看cpu空闲百分比
Iostat
可以看到只有sda分区tps早2000-3000左右，iowait等待为15%左右，cpu空闲为50%，说明cpu目前还不是主要性能问题
创建系统视图
use sys;
CREATE VIEW sys.test_waits AS SELECT
sum( timer_wait ) AS timer_wait,
sum( number_of_bytes ) AS number_of_bytes,
event_name,
operation
FROM
performance_schema.events_waits_current
WHERE
event_name != 'idle'
GROUP BY
event_name,
operation;
分组按照事件类型查询
SELECT
sys.format_time ( timer_wait ),
sys.format_bytes ( number_of_bytes ),
event_name,
operation
FROM
sys.test_waits
WHERE
sys.format_time ( timer_wait ) NOT REGEXP 'ns | us'
ORDER BY
timer_wait DESC;
根据分组排序
前两个和表的IO有关等待事件也是最长的，使用sysbench测试对表的查询和修改最多
第三个是innodb数据库引擎的操作系统文件io等待事件
剩下的大部分和锁有关，主要是mutex
SELECT
thread_id,
event_name,
sys.format_time ( timer_wait ),
index_name,
nesting_event_type,
operation,
number_of_bytes
FROM
performance_schema.events_waits_current
WHERE
event_name !='idle'
ORDER BY
timer_wait DESC;
有条件可以把数据库文件，redo，binlog，等文件分开放在不同的分区下，防止数据库随机io和顺序io资源争用导致的阻塞