概述

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

详细信息

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

整体性能排查思路

资源监控

• 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监控进行排查。

内存

• 服务器层面。

  • 通过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%'; 查看连接情况。

网络

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

慢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：额外信息。

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

使用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增长量，确定是否存在大事务。

• 查看慢日志信息，确认是否存在alter、repair和create等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系统显示类似如下结果：

   

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

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

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

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

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

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

主备复制中断

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

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