MySQL性能指标

发表于 | 分类于 | | 阅读
MySQL监控以及MySQL性能指标

MYSQL 性能指标值获取


性能指标监控

 指标分类 指标名称 指标说明
性能类指标 QPS 数据库每秒处理的请求数量
TPS 数据库每秒处理的事务数量
并发数 数据库实例当前并行处理的会话数量
连接数 连接到数据库会话的数量
缓存命中率 查询命中缓存的比例
高可用指标 可用性 数据库是否可以正常对外服务
阻塞 当前阻塞的会话数
慢查询 慢查询情况
主从延迟 主从延迟时间
主从状态 主从链路是否正常
死锁 查看死锁信息


QPS [数据库每秒处理的请求数量]

show global status where variable_name in ('Queries', 'uptime');


QPS = (Queries2 -Queries1) / (uptime2 - uptime1)

两次查询间隔  queries数量差       时间间隔


TPS [数据库每秒处理的事务数量]

show global status where variable_name in ('com_insert' , 'com_delete' , 'com_update', 'uptime');


事务数TC ≈ 'com_insert' , 'com_delete' , 'com_update'

TPS  ≈ (TC2 -TC1) / (uptime2 - uptime1)


并发数 [数据库实例当前并行处理的会话数量]

show global status like 'Threads_running';


连接数 [连接到数据库会话的数量]

show global status like 'Threads_connected';


最大连接数
show global status like 'max_connections';

缓存命中率 [查询命中缓存的比例]

show global status like 'innodb_buffer_pool_read_requests';   # innodb缓冲池查询总数

show global status like 'innodb_buffer_pool_reads';           # innodb从磁盘查询数

(innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests > 0.95

可用性 [数据库是否可以正常对外服务]

周期性连接数据库    并执行  select @@version;

阻塞 [当前阻塞的会话数]


# 5.7 - 
select b.trx_mysql_thread_id as '被阻塞线程',
     b.trx_query as '被阻塞SQL',
     c.trx_mysql_thread_id as '阻塞线程',
     c.trx_query as '阻塞SQL',
     (unix_timestamp()-unix_timestamp(c.trx_started)) as '阻塞时间'
from information_schema.innodb_lock_waits a
join information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id
join information_schema.innodb_trx c on a.blocking_trx_id=c.trx_id
where(unix_timestamp()-unix_timestamp(c.trx_started)) > 阻塞秒数

# 5.7+

select waiting_pid as '被阻塞线程',
    waiting_query as '被阻塞SQL',
     blocking_pid as '阻塞线程',
     blocking_query as '阻塞SQL',
     wait_age as '阻塞时间',
     sql_kill_blocking_query as '建议操作'
from sys.innodb_lock_waits
where(unix_timestamp()-unix_timestamp(wait_started)) > 阻塞秒数

慢查询 [慢查询情况]

1 开启慢查询日志    

    slow_query_log=on
    slow_query_log_file=存放目录
    long_query_time=0.1秒
    log_queries_not_using_indexes=on

2  select * from information_schema.processlist where time > 60 and command <> 'Sleep';

主从延迟 [主从延迟时间], 主从状态 [主从链路是否正常]


show slave status;


        Seconds_Behind_Master: 0    主从延迟时间
        
        
        Slave_IO_Running: Yes       主从状态
        Slave_SQL_Running: Yes
        Last_Errno: 0
        Last_Error:

死锁 [查看死锁信息]

show engine innodb status;


原文链接 Monitoring MySQL performance metrics

MySQL 关键统计数据

如果您的数据库运行缓慢，或者由于任何原因无法提供查询服务，那么依赖于该数据库的每个部分也会遇到性能问题

为了让您的数据库平稳运行，您可以主动监控涵盖性能和资源利用率四个方面的指标:

Query throughput
查询吞吐量

Query execution performance
查询执行性能

Connections
连接

Buffer pool usage
缓冲池使用

MySQL 用户可以访问数据库中的数百个指标，本文中我们将重点介绍一些关键指标，这些指标将使您能够实时了解数据库的健康状况和性能。

查询吞吐量（Query throughput）

MySQL 有一个内部计数器 server status variable

SHOW GLOBAL STATUS LIKE "Questions";

在监控任何系统时，您主要关心的是确保其工作得到有效完成。

数据库的工作是执行查询，因此您的首要监控重点应该是确保 MySQL 按预期执行查询

指标 说明 获取方式
Questions 已执行语句的计数（由客户端发送） 服务器状态变量 SHOW GLOBAL STATUS LIKE "Questions";
Com_select SELECT 语句 服务器状态变量 GLOBAL STATUS
Writes Inserts, updates, or deletes 语句 从服务器状态变量计算 GLOBAL STATUS

监控读取和写入命令的细分，以更好地了解数据库的工作负载并识别潜在的瓶颈。

读取查询通常由 Com_select 指标捕获 写入取决于 Com_insert + Com_update + Com_delete

1  Questions   

客户端应用程序发送的所有语句(包含服务器端准备语句的一部分运行的命令，如 PREPARE 和 DEALLOCATE PREPARE)

SHOW GLOBAL STATUS LIKE "Questions";
+---------------+------------+
| Variable_name |    Value   |
+---------------+------------+
| Questions     | 2948114537 |


2 Com_select	

SHOW GLOBAL STATUS LIKE "Com_select";
+---------------+------------+
| Variable_name |    Value   |
+---------------+------------+
| Questions     | 2787134496 |

3 Write 

Com_insert + Com_update + Com_delete

当前的查询率自然会上升和下降，因此它并不总是基于固定阈值的可操作指标。

但值得警惕查询量的突然变化——尤其是吞吐量的急剧下降可能表明存在严重问题。

查询执行性能 (Query performance)

指标 说明 获取方式
Query run time 每个模式的平均运行时间 Average run time, per schema 性能模式查询 performance_schema
Query errors 产生错误的 SQL 语句数 性能模式查询 performance_schema
Slow_queries 超过可配置的 long_query_time 限制的查询数 从服务器状态变量计算

性能数据之 performance schema

自 MySQL 5.6.6 起默认启用，MySQL 中的 performance_schema 数据库表存储有关服务器事件和查询执行的低级统计信息

MySQL 用户有多种监控查询延迟的选项，通过使用 MySQL 的内置指标和查询性能模式 performance schema

许多关键指标包含在performance schema的 events_statements_summary_by_digest 表中，

该表捕获有关每个规范化语句的延迟、错误和查询量的信息

events_statements_summary_by_digest   所有计时器测量值均以皮秒为单位  picoseconds
  • 微秒为单位 提取每个db数据库实例的平均运行时间

SELECT schema_name
     , SUM(count_star) count
     , ROUND((SUM(sum_timer_wait) / SUM(count_star))
              / 1000000) AS avg_microsec
  FROM performance_schema.events_statements_summary_by_digest
 WHERE schema_name IS NOT NULL
 GROUP BY schema_name
 order by avg_microsec desc ;

+--------------------+-------+--------------+
| schema_name        | count | avg_microsec |
+--------------------+-------+--------------+
| employees          |   223 |       171940 |
| performance_schema |    37 |        20761 |
| sys                |     4 |          748 |
+--------------------+-------+--------------+


# 查看具体sql的执行情况
select
	schema_name, digest_text
     ,
	count_star count
     ,
	round( sum_timer_wait / count_star / 1000000) as avg_microsec
from
	performance_schema.events_statements_summary_by_digest
where
	schema_name = "bv_crm"
	
order by avg_microsec desc limit 10 


schema_name|digest_text                                                      |count   |avg_microsec|
-----------+---------------------------------------------------------------------------------------
db     |SELECT * from table where id = ?                                     |     149|     1047723|
db     |SELECT SUM ( `system_value` ) WHERE `date` BETWEEN ? AND ? )         |23087157|         727|
  • 计算每个db数据库实例产生错误的语句总数
SELECT schema_name
     , SUM(sum_errors) err_count
  FROM performance_schema.events_statements_summary_by_digest
 WHERE schema_name IS NOT NULL
 GROUP BY schema_name;
+--------------------+-----------+
| schema_name        | err_count |
+--------------------+-----------+
| employees          |         8 |
| performance_schema |         1 |
| sys                |         3 |
+--------------------+-----------+



select
	schema_name,
	digest_text,
	sum_errors ,
	count_star,
	SUM_NO_INDEX_USED
from
	performance_schema.events_statements_summary_by_digest
where
	schema_name = "db"
order by sum_errors desc

性能参数之 sys schema mysql5.7.7+

sys schema 以更易读的格式提供了一组有组织的指标，使相应的查询更加简单。

例如，

1 要找到最慢的语句（运行时占95%的语句）

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;


2 或者查看哪些规范化语句产生了错误

SELECT * FROM sys.statements_with_errors_or_warnings;

sys schema 文档中详细介绍了许多其他有用的示例

性能参数之 慢查询 slow queries 计数器

MySQL 有一个 Slow_queries 计数器，每次查询的执行时间超过 long_query_time 参数指定的秒数时，计数器就会增加

阈值默认设置为 10 秒：

SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

long_query_time 参数可以通过一个命令进行调整

SET GLOBAL long_query_time = 5;


注意： 您可能必须关闭会话并重新连接到数据库才能在会话级别应用更改

排查query性能问题

如果您的查询执行速度比预期慢

  • 通常情况下最近更改的查询是罪魁祸首。

  • 如果确定没有查询过慢，接下来要评估的是系统级指标，以查找核心资源（CPU、磁盘 I/O、内存和网络）中的约束。 CPU 饱和和 I/O 瓶颈是常见的原因

  • 检查 Innodb_row_lock_waits 指标

该指标计算 InnoDB 存储引擎必须等待获取特定行上的锁的频率。

InnoDB 自 MySQL 5.5+版以来一直是默认的存储引擎， MySQL 对 ` InnoDB 表使用行级锁定`

  • 为了提高读写操作的速度，许多用户会想要调整 InnoDB 用来缓存表和索引数据的缓冲池的大小

查询运行时间 query runtime

管理关键数据库的延迟至关重要

如果生产数据库中查询的平均运行时间开始攀升

请查找数据库实例上的资源限制、行或表锁的争用以及客户端查询模式的更改


查询错误 query errors

查询错误的突然增加可能表明您的客户端应用程序或数据库本身存在问题

您可以使用 sys schema 探索哪些查询可能导致问题。

例如，要列出返回最多错误的 10 个规范化语句

SELECT * FROM sys.statements_with_errors_or_warnings
ORDER BY errors DESC
LIMIT 10;

慢查询 slow queries

如何定义慢查询（以及因此如何配置 long_query_time 参数）取决于具体的使用

要识别执行缓慢的实际查询，您可以查询 sys schema 或深入查看 MySQL 的慢查询日志

默认情况下该日志处于禁用状态

连接 (connections)

指标 说明 获取方式
Threads_connected 当前打开的连接 服务器状态变量 GLOBAL STATUS
Threads_running 当前正在运行的连接 GLOBAL STATUS
Connection_errors_internal 由于服务器错误而拒绝的连接数 GLOBAL STATUS
Aborted_connects 与服务器的失败连接尝试次数 GLOBAL STATUS
Connection_errors_ max_connections 由于 max_connections 限制而拒绝的连接计数资源 GLOBAL STATUS

检查和设置连接限制

监控您的客户端连接至关重要，因为一旦您用尽了可用连接，新的客户端连接将被

 MySQL 连接限制默认为 151
 
 SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

1 修改 连接限制

SET GLOBAL max_connections = 200;
 
     但是，当服务器重新启动时，该设置将恢复为默认值。
     
永久设置连接限制，请在 my.cnf 配置中修改添加

max_connections = 200

监控连接利用率

  • MySQL的Threads_connected指标来计算连接线程

    每个连接一个线程

    通过监控此指标，您可以确保您有足够的容量来处理新连接

如果客户端在所有可用连接都在使用时尝试连接到 MySQL MySQL 将返回“连接过多”错误并增加 Connection_errors_max_connections

为了防止出现这种情况，您应该监控 打开的连接数(Threads_connected) 并确保它安全地保持在配置的 max_connections 限制之下
  • MySQL的 Threads_running 指标来计算active 正在积极的线程

如果您的服务器确实达到了 max_connections 限制，它将开始拒绝连接

  • Connection_errors_max_connections 将增加，Aborted_connects失败的连接尝试也会增加

  • MySQL的 Connection_errors_internal

    仅在错误来自服务器本身时增加

    内部错误可能反映内存不足或服务器无法启动新线程

  • Aborted_connects

如果此计数器增加，则您的客户端正在尝试连接到数据库但失败

使用细粒度的连接指标（例如 Connection_errors_max_connections 和 Connection_errors_internal）调查问题的根源

缓冲池使用 (Buffer pool usage)

指标 说明 获取方式
Innodb_buffer_pool_pages_total 缓冲池中的总页数 服务器状态变量 GLOBAL STATUS
Buffer pool utilization 缓冲池中已用页面与总页面的比率 根据GLOBAL STATUS计算
Innodb_buffer_pool_read_requests 对缓冲池的请求 GLOBAL STATUS
Innodb_buffer_pool_reads 缓冲池无法满足的请求(必须请求磁盘) GLOBAL STATUS

MySQL的默认存储引擎 InnoDB 使用称为缓冲池的内存区域来缓存表和索引的数据

缓冲池指标是与工作指标相对的资源指标，因此主要用于调查（而不是检测）性能问题

如果在磁盘 I/O 增加时数据库性能开始下滑，扩展缓冲池通常可以带来好处

调整缓冲池的大小 Sizing the buffer pool

缓冲池默认为相对较小的 128 M字节，但 MySQL 建议您可以将其增加到专用数据库服务器上物理内存的 80%

注意：InnoDB 的内存开销会使内存占用增加约 10%，超出分配的缓冲池大小

    如果您的物理内存用完，您的系统将求助于分页，性能将受到严重影响

缓冲池也可以划分为单独的区域，称为实例, 使用多个实例可以提高多 GiB 范围内缓冲池的并发性

缓冲池大小调整操作是分块进行的，缓冲池的大小必须设置为块大小乘以实例数的倍数

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size
                            * innodb_buffer_pool_instances

块大小默认为 128 MiB，但从 MySQL 5.7.5 开始可配置。

可以按如下方式检查这两个参数的值：

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";



如果 innodb_buffer_pool_chunk_size 查询未返回任何结果
则该参数在您的 MySQL 版本中不可调整 可以假定为 128 MiB

在服务器启动时设置缓冲池大小和实例数

mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

从 MySQL 5.7.5 开始，您还可以通过以字节为单位指定调整缓冲池的大小

例如，对于两个缓冲池实例，您可以通过将总大小设置为 8 GiB 来将每个缓冲池的大小设置为 4 GiB：
SET GLOBAL innodb_buffer_pool_size=8589934592;

InnoDB 缓冲池关键指标

Innodb_buffer_pool_read_requests 和 Innodb_buffer_pool_reads 指标是了解缓冲池利用率的关键

  • Innodb_buffer_pool_read_requests 跟踪逻辑读取请求的数量

  • Innodb_buffer_pool_reads 跟踪缓冲池无法满足的请求数量，因此必须从磁盘读取

    鉴于从内存读取通常比从磁盘读取快几个数量级，如果 Innodb_buffer_pool_reads 开始攀升，性能将受到影响

计算缓冲池利用率

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) /
 Innodb_buffer_pool_pages_total
 
 
    SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_pages_total";
    SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_pages_free";

如果您的数据库正在为大量磁盘读取提供服务，但缓冲池远未满，则可能是您的缓存最近已被清除并且仍在预热

如果您的缓冲池没有填满但可以有效地为读取提供服务，那么您的工作数据集适合内存

如果缓冲池不能有效地为您的读取工作负载提供服务，则可能是时候扩展缓存了

    SHOW GLOBAL STATUS LIKE "Innodb_buffer_pool_pages_total";
    # 262136
    
    SHOW VARIABLES LIKE "innodb_page_size"; # 默认为 16 KiB，或 16,384 字节
    #  16 KiB
    
    
    缓冲池的总大小 
    Innodb_buffer_pool_pages_total * innodb_page_size


Buy me a 肥仔水！