MySQL监控&性能瓶颈排查

监控的意义&目的

业务/数据库服务是否可用

通过事务实时性能数据变化感知业务的变化

数据库性能变化趋势判断服务器资源是否足够

数据可靠性

业务数据是否可靠

服务可用，不代表数据就是正确的

有可能误操作删除数据，或者其他意外原因丢失数据

或者主从复制延迟，导致在从数据库无法读取到最新数据

通过模拟随机业务逻辑来验证数据可靠性

服务可用性

是否可对外提供服务

进程在运行，但没监听网络，或者授权不正确，或者网络出故障

因此不能只监控进程启动与否，是否监听网络

最好能模拟业务逻辑进行监控

这个业务逻辑除了能完成可用性监控外，还可以进行数据可靠性监控

服务器&MySQL实例出现高负载

服务可用，但响应很慢，其实等于不可用

响应很慢时，用户不耐烦一直刷屏，更容易引起风暴

需要及时关注整个系统响应时长、每秒处理事务数

可用性告警

快速报告线上服务宕掉及恢复情况

历史趋势

了解线上计算资源使用情况

作为计算资源扩容/收缩的参考

作为优化工作的成果展示记录

监控之关键指标

常规运行情况汇总

CPU：%user，%sys，%idle，%iowait，是否发生中断不均衡

内存：free，cached，swap，是否有内存泄漏和OOM

I/O：iops、吞吐、时延、利用率(%util)

网卡：吞吐、注意小包发送频率

系统监控

常规工具

top、free、ps、df

sysstat(sar、vmstat、mpstat、iostat)、dstat、iotop

netstat

其他工具

perf

pstack

top负载大于5要注意

free详解

used，已经使用的内存总量

free，剩余物理内存

shared，已废弃不用

buffers，写缓冲

cached，读缓冲

实际可再分配内存：total = used + free + shared + buff/cache

对比cache和used = (total - free)的差异大小

used和cache相差太大第一感觉就是内存泄漏

ps H -eo user,pid,ppid,tid,time,%cpu,cmd --sort=%cpu

cpu消耗高的原因大概有几个 1)函数的运算 2)排序的进行 3)锁等待和争用

sar详解

-u，Report CPU utilization

-d，Report activity for each block device

-r，memory utilization statistics

vmstat -S m 1 50

iostat -dmx 1

iotop -oP

看iops，iowait，%util

dstat 1

xfs要预留10%磁盘空间，用来存xfs的日中，小心，否则容易丢数据

MySQL监控

MySQL服务性能，是否有瓶颈

tps，qps，慢查询，rt，锁，等待，表缓存，线程缓存，临时表，临时文件

锁：表锁，行锁，锁等待，死锁

内存：buffer/cache命中率、等待释放

事务：事务ID增长率，unpurge历史事务，checkpoint lag，大事务，长事务

慢查询：平均耗时、平均次数，slow query重点关注distinct page特别多的

ibp监控

purge lag -- History list length

checkpoint lag

活跃时间最长的事务

select * from I_S.innodb_trx order by trx_started asc limit 1;

等待时间最长的事务

select * from sys.innodb_lock_waits order by wait_age_secs desc limit 1;

要特别关注的大事务

select * from I_S.innodb_trx where

    trx_lock_structs >= 5 or           -- 超过5把锁

    trx_rows_locked >= 100 or      -- 超过100行被锁

    trx_rows_modified >= 100 or   -- 超过100行被修改

    time_to_sec(timediff(now(),trx_started)) > 100;    -- 事务活跃超过100秒

监控大SQL、慢SQL

监控SQL注入风险

MySQL锁监控

表级锁

table_locks_immediate

table_locks_waited

行级锁

innodb_row_lock_current_wait   -- 当前等待的行锁数量

innodb_row_lock_time   -- 请求行锁总耗时(ms)

innodb_row_lock_time_avg   -- 请求行锁平均耗时(ms)

innodb_row_lock_time_max   -- 请求行锁最久耗时(ms)

innodb_row_lock_waits   -- 行锁发生次数

等待事件

innodb_buffer_pool_wait_free/innodb_log_wait等

临时表/临时文件

created_tmp_disk_tables/created_tmp_files等

打开表/文件数

open_files/open_table_definitions/open_tables等

并发连接

threads_running/threads_created/threads_cached等

查看MySQL状态

show [full] processlist; 查看不良状态

show [global] status;

show engine innodb statusG

tail -f slow.log

perf top 神器

数据缓存buffer (data page)

innodb_buffer_pool_pages_data_b = innodb_page_size * innodb_buffer_pool_pages_data

待刷新buffer

innodb_buffer_pool_pages_dirty_b = innodb_page_size * innodb_buffer_pool_pages_dirty

空闲buffer

innodb_buffer_pool_pages_free_b = innodb_page_size * innodb_buffer_pool_pages_free

缓存命中率

innodb_buffer_pool_hit_ratio = (innodb_buffer_pool_read_requests/(innodb_buffer_pool_read_requests+innodb_buffer_pool_reads)) * 100

MySQL服务可用性

不仅能连上，还能正常查询&及时返回结果

MySQL复制是否终止，延迟多大

高一致性要求的场景，复制几乎不能有延迟

如何衡量平均响应时长(average rt) 

通过模拟N次随机业务逻辑判断响应耗时，或通过MySQL的基准测试函数BENCHMARK()来判断

select benchmark(100000000,'call mysp()');

select benchmark(100000000,'select 1+1'); 

业务监控

业务状态监控

是否正常、tps、平均响应时长、自定义错误代码

接口响应延迟

在监控系统调用应用接口，探测其响应延迟，该接口实现一些基本的业务逻辑 

flush status;

selec ...;

show status like 'handler_read%';

set profiling = 1;

select ...;

show profiles;

show profile for query N;

MySQL关键选项

key_buffer_size = 8M

sort/join/read/read rnd buffer = 4M

tmp/heap table = 96M

binlog_format = row

sync_binlog = 1

long_query_time = 0.01~0.05

log_queries_not_using_indexes = 1 & log_throttle_queries_not_using_indexes = 10

interactive_timeout = wait_timeout = 600

lock_wait_timeout = 3600

log_error_verbosity = 3

time_zone = "+8:00"

thread_handling, 能支持时，若有大量短连接场景就启用

sql_safe_updates = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_buffer_pool_size，建议物理内存的50%~80%

innodb_max_dirty_pages_pct，建议不超过50%

innodb_log_buffer_size，建议32M左右

innodb_thread_concurrency = 0

innodb_lock_wait_timeout = 5~10

innodb_log_file_size = 1G

innodb_log_files_in_group = 4~8

innodb_flush_log_at_trx_commit = 1

innodb_io_capacity, innodb_io_capacity_max 根据I/O子系统性能调整

innodb_flush_sync = 0

innodb_autoinc_lock_mode = 1

推荐使用my.cnf生成工具

MySQL关键状态

aborted_*

created_tmp_*

handler_read_*

*_wait_*

open_tables, opened_tables

select_full_join

select_scan

sort_merge_passes

threads_*

*copy*, *copying*

creating sort index, sorting result

creating tmp table

closing tables, opening tables

converting HEAP to ondisk

altering table / creating index

preparing / query end

sending data / sending to client

waiting for ... lock