文档

MySQL性能排查解析

更新时间:
一键部署

概述

本文主要介绍使用阿里云ECS实例搭建MySQL时,性能排查相关的思路以及方法。

详细信息

说明

阿里云提醒您:

  • 如果您对实例或数据有修改、变更等风险操作,务必注意实例的容灾、容错能力,确保数据安全。

  • 如果您对实例(包括但不限于ECS、RDS)等进行配置与数据修改,建议提前创建快照或开启RDS日志备份等功能。

  • 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息,建议您及时修改。

请根据现场实际情况,参考以下对应的解决方案。

整体性能排查思路

资源监控

  • CPU

  • 内存

  • IO

  • 网络

  • 连接

  • 磁盘

日志分析

  • error log

  • slow log

  • general log

  • binlog

SQL分析

  • slow log

  • explain

  • show profile

  • optimizer_trace

锁分析

  • 表锁

  • 行锁

    • 行锁

    • 间隙锁

    • 下一键锁

  • MDL锁

  • 死锁

MySQL资源性能瓶颈

CPU

  • 查看MySQL的进程cpu消耗,查看是user消耗较多,还是sys消耗较高。

    • 可以使用top命令进行查看。

  • 查看是否活跃链接过高导致,MySQL为单进程多线程类型数据库,活跃链接越多,越消耗CPU。

    • 可以使用show processlist 进行查看。

  • 查看MySQL是否存在大量慢日志,慢日志导致cpu消耗较高。

    • 前提为开启了慢日志。

      • set global slow_query_log='ON';
      • set global slow_query_log_file='/var/lib/mysql/slow.log';
      • set global long_query_time=1;
    • 查看慢日志。

      • select * from mysql.slow_log;
      • mysqldumpslow;
  • 其他。

IO

由于数据库实例在ECS上,IO相关资源排查可直接使用ECS监控进行排查。

  • 参考此文档进行io排查。

  • 参考ECS监控进行排查。

20230925140712.jpg

内存

  • 服务器层面。

    • 通过error log查看是否存在OOM,如果服务器开启过coredump文件的保存,还可以直接通过coredump文件进行分析。

    • 使用TOP命令查看程序占用RAM的情况。

    • 使用GDB进行查看(影响MySQL实例的使用,请慎用)。

  • MySQL实例层面。

    • 开启performance_schema(重启实例才会生效,开启后会损耗10%左右的性能)。

    • update setup_instruments set ENABLED='YES' where name like 'memory/%';
    • select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes where current_count>0;

连接数

  • 通过show processlist 进行详细连接查看。

  • 通过 show status like '%connect%' ; show status like '%threads%'; 查看连接情况。

image

网络

由于数据库实例在ECS上,网络相关资源排查可直接使用ECS监控进行排查。

20230922114019.jpg

慢SQL分析

开启慢SQL

  • 开启慢日志。

    set global slow_query_log=1; 
  • 设置慢日志存储位置。

    set global slow_query_log_file='/var/lib/mysql/slow.log'; 
  • 设置慢日志阈值,执行超过1秒的sql将被记录。

    set global long_query_time=1; 

获取慢日志

  • select * from mysql.slow_log;
  • mysqldumpslow。

    • 获取慢日志中最多的10个SQL。

      mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log

查看表结构并使用explain查看执行计划

  • show create table table_name 查看表结构。

  • explain慢sql语句查看具体的慢sql的执行计划。

    • id: sql的序列号。

    • select_type:查询类型,SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。

    • table:所属table 。

    • type:访问类型。system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

    • possible_keys:可能使用的索引。

    • key:实际使用的索引。

    • key_len:索引长度。

    • ref:显示索引的哪一列被使用了,哪些列或常量被用于查找索引列上的值。

    • rows:根据表的统计信息及索引选用情况,大致估算出找到所需记录多需要读取的行数。

    • extra:额外信息。

image

  • 通过表结构和执行计划判断是否走了正确的索引。

使用show profile查看SQL执行细节

  • 查看状态。

    SHOW VARIABLES LIKE 'profiling';
  • 开启。

    set profiling=on;
  • 查看结果。

    show profiles;
  • 诊断SQL。

    show profile cpu,block io for query 上一步SQL数字号码;

使用optimizer_trace查看具体的执行计划

  • 开启optimizer_trace。

    SET SESSION optimizer_trace="enabled=on";
  • 执行慢sql。

  • 查看执行计划树。

    SELECT * FROM information_schema.OPTIMIZER_TRACE\G
  • 分析执行计划树。

常见执行计划走错的解决方案

  • analyze table table_name重新对标收集统计信息。

  • force index进行强制指定索引。

常见SQL优化方法

  • 联合索引遵循最左侧原则,将where使用最频繁的列放在最左侧。

  • 联合索引的效率一般高于单列索引。

  • 字段使用函数,将不能使用索引。

  • 无引号导致全表扫描,无法使用索引。

  • 当取出的数据量超过表中数据的20%,优化器就不会使用索引,而是全表扫描。

  • 涉及到order by group by的语句,将where条件和order group的字段作为联合索引,排序方法要一致。

锁分析(需开启performance_schema并开启对应的插件监控)

死锁

  • error log中查看报错:Deadlock found when trying to get lock... 。

  • 查看最后一次死锁:show engine innodb status\G 获取语句事务id。

  • 通过events_statements_history_long来查询死锁语句。

MDL锁

  • show processlist发现大量waiting for metadata lock 的sql。

  • 通过performance_schema.metadata_locks视图进行查看具体持有MDL锁的线程。

  • 通过information_schema.innodb_trx查看此线程是否存在未提交的事务。

  • 通过performance_schema.events_statements_current表来查询某个线程正在执行或最后一次执行完成的语句事件信息。

行级锁

  • 日志中出现Lock wait timeout exceeded。

  • show processlist查看正在执行的sql。

  • 通过information_schema.innodb_lock_waits进行查看锁情况,获取事务id。

  • 通过information_schema.innodb_trx查看事务执行情况。

  • 通过kill或commit释放持有的锁,从业务逻辑判断执行逻辑,进行优化。

主备异常

主备复制延迟

排查方法

当备实例出现延迟时,可以根据以下排查方法定位问题:

  • 检查备库的IOPS,确认备库是否存在资源瓶颈。

  • 检查主库的TPS,确认主实例TPS是否过高。

  • 检查备库的Binlog增长量,确定是否存在大事务。

  • 查看慢日志信息,确认是否存在alterrepaircreate等DDL操作。

  • 备库执行show slave status \G命令,确定是否存在元数据锁。

  • 检查备库是否存在无主键表的删除或者更新操作,可以通过在备库上执行show engine innodb status \G语句查看,或者执行show open tables语句后,查看输出结果的in_use列的值为1的表。

解决方法

本节列出以下四种常见的解决方法,您可以根据排查方法定位的问题原因选择对应的解决方法:

  • 备库规格过小建议您升级备库所在ECS的实例规格,使备库的配置大于或者等于主实例的配置,避免由于备库规格较小导致延迟。

  • 主实例的TPS(Transaction Per Second)过高确认主实例的TPS是否正常,如果TPS过高,则需要对业务进行优化或者拆分,保证主实例的TPS不会导致备库出现延迟。

  • 主实例的大事务。

  1. 在备库出现大事务导致延迟时,登录数据库,执行以下SQL语句,确认Seconds_Behind_Master不断变化,而Exec_Master_Log_Pos却保持不变,说明备库的SQL线程在执行一个大事务或者DDL操作,然后通过show processlist语句定位具体的线程。show slave status \G系统显示类似如下结果:

    20230922112203.jpg

  2. 建议您将大事务拆分为小事务分别执行。例如,在delete语句中增加where条件子句,限制每次删除的数据量,将一次删除操作拆分为多次数据量较小的删除操作进行。这样备库可以迅速地完成事务的执行,不会造成数据的延迟。

  • 主实例的DDL语句执行时间较长。

    • 对于DDL直接引起的备库延迟,建议在业务低峰期执行这些DDL。

    • 对于来自主实例的DDL语句在备库上被阻塞的情况:

      1. 在备库上执行show processlist语句,确认SQL线程的状态为“waiting for table metadata lock”。

      2. 使用kill命令终止备库上引起阻塞的会话,恢复备库和主实例的数据同步,详情请参见解决MDL锁导致无法操作数据库的问题

主备复制中断

  • 通过error log获取具体的报错。

  • 通过具体的报错进行分析解决。

  • 本页导读 (0)