手机怎么制作钓鱼网站,专门做2手手机的网站,怎么添加网站白名单,wordpress投票模板文章目录 前言1. 连接相关1.1 连接线程1.2 连接异常1.3 最大连接数 2. Com 相关3. 临时表相关4. Table Cache 相关5. 缓冲池相关6. Redo log 相关7. 行锁相关8. 排序相关9. 查询相关10. 流量相关11. Binlog 相关 前言
本篇文章介绍一些 MySQL 中常用的监控指标#xff0c;常见… 文章目录 前言1. 连接相关1.1 连接线程1.2 连接异常1.3 最大连接数 2. Com 相关3. 临时表相关4. Table Cache 相关5. 缓冲池相关6. Redo log 相关7. 行锁相关8. 排序相关9. 查询相关10. 流量相关11. Binlog 相关 前言
本篇文章介绍一些 MySQL 中常用的监控指标常见的监控工具都是采集 MySQL 中的状态变量status variables理解这些状态变量可以更好的帮助我们理解 MySQL 监控的含义及配置有效完备的监控从而游刃有余的定位数据库的性能问题。
1. 连接相关
MySQL 默认的调度方式是每一个连接一个线程既 one-thread-per-connection 所以本节涉及到的变量基本可以视为连接。 Tipsone-thread-per-connection 适合于低并发长连接的环境而在高并发或大量短连接环境下大量创建和销毁线程以及线程上下文切换会严重影响性能如果遇到此类瓶颈可以使用线程池pool-of-threads来优化。 1.1 连接线程
以下是连接线程涉及到的变量
Variable NameVariable ScopeVariable MeaningThreads_connectedGlobal当前连接线程数该值等于 SHOW PROCESSLIST 的总数。Threads_runningGlobal当前处于活跃状态的连接线程数如果该值过大会导致系统频繁地切换上下文CPU 使用率也会比较高。Threads_cachedGlobalThreads cache 缓存的线程数。在创建新的连接时会首先检查 Threads cache 中是否有缓存的线程。如果有则复用如果没有则创建新的线程。在线程池的场景中会禁用 Threads cache 此时该值为 0。Threads_createdGlobal已创建的线程数。反应的是累加值如果该值过大说明 Threads cache 过小可考虑适当增大 thread_cache_size 的值。 Tips建议配置连接数使用率和活跃连接数使用率告警连接数被占满会导致业务报错Threads_connected / max_connections 推荐阈值 85% 活跃连接数使用率过高通常 CPU 使用率也会高意味着系统很繁忙 Threads_running / max_connections 推荐阈值 50%。 show status where Variable_name in (Threads_connected, Threads_running, Threads_cached, Threads_created);1.2 连接异常
以下是连接异常相关的状态变量
Aborted_clients客户端已成功建立但中途异常断开连接的次数。常见原因有以下几种。
客户端程序断开连接前没有调用 mysql_close() 方法。客户端连接的休眠时间超过 wait_timeout 的会话值被服务器主动断开。客户端程序在数据传输时突然断开。数据包的大小超过 max_allowed_packet 的限制。
对于中途断开的连接错误日志log_error_verbosity 3中通常会有如下信息 [Note] Aborted connection 184618 to db: ‘xxx’ user: ‘xxx’ host: ‘xxxx’ (Got an error reading communication packets) Aborted_connects连接 MySQL 服务端失败的次数。常见的原因有以下几种。
客户端账号密码不准确。没有指定库的访问权限。连接包中没有包含正确的信息。超过 connect_timeout 服务端没有收到客户端的连接包。
show status where Variable_name in (Aborted_clients, Aborted_connects);1.3 最大连接数
以下是连接数相关的状态变量
Variable NameVariable ScopeVariable MeaningMax_used_connectionsGlobal数据库历史最大的连接数。Max_used_connections_timeGlobal连接数达到历史最大的时间。Connection_errors_max_connectionsGlobal连接数占满后应用有新的连接后返回 Too many connections 错误该值也会随之增大。 TipsMySQL 中的最大连接数由参数 max_connections 控制默认是 151。当连接数达到 max_connections 的限制业务会返回报错 Too many connections 状态变量 Connection_errors_max_connections 也会随之增大。建议基于 Threads_connected / max_connections 做好连接数使用率监控如果大于 85% 则触发告警。 show status where Variable_name in (Max_used_connections, Max_used_connections_time, Connection_errors_max_connections);2. Com 相关
统计操作执行的次数。以下状态变量在监控中使用较多可以反应数据库的繁忙程度。
Variable NameVariable ScopeVariable MeaningCom_insertBothinsert 语句执行次数。Com_selectBothselect 语句执行次数。Com_updateBothupdate 语句执行次数。Com_deleteBothdelete 语句执行次数。Com_commitBothcommit 语句执行次数。Com_rollbackBothrollback 语句执行次数。Com_replaceBothreplace 语句执行次数。 Tips此类变量可以使用 flush status 命令归零重新累加统计。每秒执行事务的次数 TPS 可通过 Com_commit Com_rollback 每秒增量来计算。 这里只列出了部分常见操作完整的可以使用下方 SQL 查看。
show status like Com%;3. 临时表相关
MySQL 在执行 order by、group by 查询时通常会建立一个或两个临时表当临时表较小时可以放到内存中较大时则会存在于磁盘上。可以通过以下 3 个变量监控临时文件使用情况。
Variable NameVariable ScopeVariable MeaningCreated_tmp_disk_tablesBothMySQL 内部临时表转化为磁盘表的数量。Created_tmp_filesGlobalMySQL 创建临时文件的数量。Created_tmp_tablesBothMySQL 创建在内存临时表的数量。 Tips理论上来讲使用临时表无法避免但是肯定是越少越好并且磁盘临时表需要保持在一个很小的值经验值 Created_tmp_disk_tables / Created_tmp_tables 小于 20%。 show status like Created%;4. Table Cache 相关
为了提升表的访问效率表在使用完毕后不会立即关闭而是会缓存在 Table Cache 中可通过以下 6 个变量监控 Table Cache 使用情况。
Variable NameVariable ScopeVariable MeaningOpen_tablesBoth当前打开表的数量。Open_table_definitionsGlobal当前缓存的 frm 文件的数量。Opened_tablesBoth打开过的表的数量。Open_table_definitionsGlobal缓存过的 frm 文件的数量。Table_open_cache_hitsBothTable Cache 的命中次数。Table_open_cache_missesBothTable Cache 没有命中的次数。Table_open_cache_overflowsBoth表缓存被删除的次数。
当 MySQL 要访问一张表的时候首先会检查该表的文件描述符是否在 Table Cache 中如果存在则直接使用并增大 Table_open_cache_hits 的值如果不存在则打开表并增大 Opened_tables 和 Table_open_cache_misses 的值。然后将表缓存在 Table Cache 中。
当 Table Cache 达到了 table_open_cache 的限制此时分两种场景
1. 缓存中存在未使用的表 会使用 LRU 算法淘汰掉未使用的表并在 Table Cache 中删除同时会增大 Table_open_cache_overflows 的值。
2. 缓存中的表都在使用 会临时扩容 Table Cache 一旦检测出未使用的表则触发清理从而保持在 table_open_cache 之下。 Tips如果观测 Opened_tables 大于 table_open_cache 且在持续增大意味着 table_open_cache 相对较小此时可适当调大参数。 show status where Variable_name in (Open_tables, Open_table_definitions, Opened_tables, Open_table_definitions, Table_open_cache_hits, Table_open_cache_misses, Table_open_cache_overflows);5. 缓冲池相关
对于 innodb 表引擎来说用户数据和索引及系统元数据都是以页的形式存储在表空间中表空间是 innodb 对文件系统上一个或多个物理文件的抽象也就是说数据到底还是存储在磁盘中的。但是磁盘的速度要比内存慢太多速度跟不上 CPU 的计算速度所以 innodb 引擎需要访问某个页的数据时就会把完整的页全部加载的内存中页大小默认 16 k即使访问一个页的一行数据也需要先把完整的页加载的内存中Innodb 所有读写操作都是在内存中完成的完成读写后 innodb 并不会立刻释放掉而是先缓存起来后面如果有请求需要用到这张页的话就可以直接从内存读取可以省去磁盘 IO 的开销。
MySQL 缓冲池也使用 LRU 算法进行调度本质是让热数据页在缓存中长时间保留提高查询访问效率但是缓存是有限的LRU 的作用就是减少重复数据页加载频率。 推荐阅读快速掌握 Innodb 以下是缓冲池中数据页面的相关变量
Variable NameVariable ScopeVariable Meaninginnodb_buffer_pool_pages_dataGlobal缓冲池中数据页的数量包括干净页和脏页。innodb_buffer_pool_bytes_dataGlobal数据页的大小单位是字节。innodb_buffer_pool_pages_dirtyGlobal脏页的数量。innodb_buffer_pool_bytes_dirtyGlobal脏页的大小单位是字节。innodb_buffer_pool_pages_freeGlobal空闲页的数量。innodb_buffer_pool_pages_miscGlobal用于管理开销而分配的页的数量比如行锁、自适应哈希索引等。innodb_buffer_pool_pages_totalGlobal页的总数量。innodb_buffer_pool_pages_flushedGlobal脏页被刷盘的次数。innodb_buffer_pool_wait_freeGlobal等待空闲页的次数。
show status
where Variable_name in (innodb_buffer_pool_pages_data, innodb_buffer_pool_bytes_data, innodb_buffer_pool_pages_dirty, innodb_buffer_pool_bytes_dirty, innodb_buffer_pool_pages_free, innodb_buffer_pool_pages_misc, innodb_buffer_pool_pages_total, innodb_buffer_pool_pages_flushed, innodb_buffer_pool_wait_free);如果有大表全表扫描的 SQL 执行的时候需要将整张表都加载到 buffer pool 中导致 buffer pool 中的热点数据被置换出去这种情况叫做缓存污染可以通过缓存命中率来监控此类情况。
Innodb_buffer_pool_read_requests逻辑读的数量既缓存读。Innodb_buffer_pool_reads物理读的数量既磁盘读。 Innodb 缓存命中率 (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests OLTP 型业务缓存命中率应大于 95%如果命中率低则需要调大 innodb_buffer_pool_size 及排查是否有全表扫描 SQL。
另外通过下方 SQL 可以观测 Innodb 删除、插入、读取、更改的行数。
show status like innodb_rows%;6. Redo log 相关
为了取得更好的读写性能InnoDB 会将数据缓存在内存中InnoDB Buffer Pool对磁盘数据的修改也会落后于内存这时如果进程或机器崩溃会导致内存数据丢失为了保证数据库本身的一致性和持久性InnoDB 维护了 REDO LOG。
修改 Page 之前需要先将修改的内容记录到 REDO 中并保证 REDO LOG 早于对应的 Page 落盘也就是常说的 WALWrite Ahead Log日志优先写Redo Log 的写入是顺序 IO可以获得更高的 IOPS 从而提升数据库的写入性能。
当故障发生导致内存数据丢失后InnoDB 会在重启时通过重放 REDO将 Page 恢复到崩溃前的状态。 推荐阅读8.0 新特性 - innodb_redo_log_capacity 以下是 Redo log 相关的状态变量
Variable NameVariable ScopeVariable MeaningInnodb_log_waitsGlobal因 redo buffer 过小导致 redo log buffer 刷盘的次数。Innodb_log_write_requestsGlobal写 redo log buffer 的次数。Innodb_log_writesGlobal写 redo log 次数。Innodb_os_log_fsyncsGlobal对 redo log 调用 fsync 操作的次数。Innodb_os_log_pending_fsyncsGlobalfsync 操作等待的次数。Innodb_os_log_pending_writesGlobal写 redo log 等待次数。Innodb_os_log_writtenGlobalredo log 的写入量单位是字节。
通过以上状态变量可以看出数据库的写入情况如果 Innodb_log_waits 持续增大需要确认 redo log 文件和 buffer 相关配置是否合适。另外不能通过 Innodb_os_log_written 来反映 redo 的写入量因为 redo log 基本存储单位是 block 512 bytes 小于基本存储单位的写入也会以基本单位来计算。
要评估 Redo log 写入量可参考下方文档。 推荐阅读How to calculate a good InnoDB log file size 7. 行锁相关
数据库的核心方向就是高并发整体业务场景大多是 读-读、读-写、写-写三类并发场景看似容易融合到业务场景后也比较复杂。通过锁机制主要可以帮助我们解决 写-写 和 读-读 场景下的并发安全问题所以锁争用和锁等待也是经常遇到的情况
可通过下方状态变量了解数据库中的行锁信息
Innodb_row_lock_current_waits当前正在等待行锁的操作数。Innodb_row_lock_time获取行锁花费的总时间单位毫秒。Innodb_row_lock_time_avg获取行锁花费的平均时间单位毫秒。Innodb_row_lock_time_max获取行锁花费的最大时间单位毫秒。
下面我们来做一个实验
rootmysql 14:38: [(none)]show status like %Innodb_row_lock%;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 33165 |
| Innodb_row_lock_time_avg | 16582 |
| Innodb_row_lock_time_max | 28845 |
| Innodb_row_lock_waits | 2 |
--------------------------------------Session 1Session 2Begin;delete from score where id 5;update score set number 66 where id 5; – 等待行锁
rootmysql 14:41: [test]show status like %Innodb_row_lock%;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 33165 |
| Innodb_row_lock_time_avg | 11055 |
| Innodb_row_lock_time_max | 28845 |
| Innodb_row_lock_waits | 3 |
--------------------------------------此时可以发现 Innodb_row_lock_waits 和 Innodb_row_lock_current_waits 都增长了time 相关的变量需要等事务结束后才会进行计算。 TipsInnodb_row_lock_current_waits 可以反映当前数据库行锁的情况可根据该状态变量配置行锁告警。 8. 排序相关
MySQL 中如果有涉及到排序的操作ORDER BY、GROUP BY、DISTINCT操作时如果无法使用索引则会使用文件排序。执行计划中的 Extra 列会显示 Using filesort。
MySQL 会为需要 filesort 的会话分配单独排序的缓存区sort buffer排序缓存区是需要时才分配且按需分配最大限制由 sort_buffer_size 控制默认是 256KB。如果需要排序的记录较少既 sort buffer 够用那么在内存中排序也是非常快的。如果需要排序的记录非常多MySQL 会分批处理每一批首先会在排序缓存区中排序排序后的结果会存储在临时文件中。每个排序缓存区对应一个临时文件中的一个 block。处理完毕后最后再对临时文件中的 block 进行归并排序相比直接在内存中排序需要消耗额外的 IO 和 CPU 计算资源。
以下是排序相关的状态变量
Sort_merge_passes反映的是 sort buffer 不够用使用临时文件归并排序的次数。Sort_range对索引范围扫描的结果进行排序的次数。Sort_rows排序的记录数。Sort_scan对全表扫描的结果进行排序的次数。
show status like %Sort%;Tips需要关注 Sort_merge_passes 的值如果持续增大说明有行数较大的排序操作需要定位 SQL 判断是否调大 sort buffer。 9. 查询相关
以下是查询相关的状态变量
Variable NameVariable ScopeVariable MeaningSelect_scanBoth全表扫描的次数如果是关联查询指的是驱动表执行了全表扫描。Select_full_joinBoth同样是全表扫描不过只包含关联场景驱动表全表扫描的次数。Select_rangeBoth范围查询次数。如果是关联查询指的是驱动表执行了范围查询。Select_full_range_joinBoth同样是范围查询不过只包含关联场景驱动表全表扫描的次数。Select_range_checkBoth常用于非等值的关联查询中。Slow_queriesBoth慢查询的数量无论是否开启了慢查询只要 SQL 执行耗时大于 long_query_time 该值就会增加。
show status
where Variable_name in (Select_scan, Select_full_join, Select_range, Select_full_range_join, Select_range_check, Select_range_check, Slow_queries);TipsSelect_scan 可以反映数据库是否存在全表扫描的 SQL从 Slow_queries 可以看出存储中慢 SQL 的数量建议为这两个状态变量配置监控。 10. 流量相关
以下是流量吞吐相关的状态变量
bytes_received从客户端接收的流量大小单位是字节。bytes_sent发送给客户端端流量大小单位是字节。
show status
where Variable_name in (bytes_received, bytes_sent);Tips数据库的流量吞吐可以帮助我们了解数据库的负载状况和并发处理能力。建议为其每秒增量配置监控。 11. Binlog 相关
MySQL 因为是多引擎架构所以在集群数据同步中使用的是逻辑日志 Binlog 来同步数据。当事务执行过程中未提交Binlog 是写在 binlog_cahce 中的这块内存大小由 binlog_cache_size 参数控制默认 32KB 如果超出该参数则会将多余部分存储到临时文件中。临时文件也不是无限大小的由参数 max_binlog_cache_size 控制。
Binlog_cache_use使用 Binlog Cahce 缓存的事务数量。Binlog_cache_disk_use超过 Binlog Cache 大小使用临时文件的事务数量。 Tips如果发现 Binlog_cache_disk_use 状态变量大于 0 且持续增长则需要关注 binlog_cache_size 参数是否过小或者业务有使用到大事务需要定位处理掉。
