批量写入造成mysql访问慢问题追踪

现象

2020.03的某天下午,突然收到同事反馈,产品的某个页面出现'服务无响应'问题

跟进

  • 由于服务器是分线路访问,可以通过 ping 指令,找到具体的服务器ip,然后本地绑定域名,进行模拟请求,确认问题并查看日志。

  • 发现是mysql数据库响应慢,造成接口响应速度下降 20+ 倍

  • 进行以下操作:

    1. 通过以下指令打开、设置并查看慢查询日志

       // 查看
       show variables like 'slow_query%';
       show variables like 'long_query_time';
       // 设置
       set global [variablesName]=[Value]
       // 日志输出方式:TABLE,FILE
       show variables like '%log_output%'
    2. 查看mysql的连接数等状态

       // 如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。
       show full processlist;
       // 状态
       show status [like '%variables%'];
       // 参数
       show variables [like '%variables%']
    3. 查看mysql使用的内存、cpu、磁盘、网络状况

  • 分析

    1. 数据库连接数已满(max=1000)

    2. 磁盘、网络、cpu、内存 都不高,特别是内存低的很奇怪,只占总内存的 8% 左右,不能有效利用机器性能

    3. 慢查询日志:发现发生慢查询的语句,都是使用到了索引、并且索引有效的查询语句

  • 猜测是:服务器配置 + 用户流量激增造成

    1. 看域名流量,没有异常

    2. mysql配置有异常:key_buffer_size 设置的很小。调整后,问题没有好转

  • 查看锁状况

    1. 由于相关表使用的是MyISAM引擎,所以通过 show status like 'table%' 查看锁状态,发现:Table_locks_immediate / Table_locks_waited ~= 2

    2. 因为这个库只能通过主从同步进行修改,这时想起相关的运营组最近在进行自动入库操作。

    3. 停止后,问题缓解

      总结

  • 问题复盘

    1. 这个数据库采用主从结构(一主多从),异步同步,落盘策略为:sync_binlog=0

    2. 自动入库:最近才开始,采用 while do { 写入数据(标记无效),校验文件md5,修改标记(有效) }

      • 问题:相关人员说,前两天同样的操作没有出问题啊

      • 确认:当天加上了文件md5校验

      • 猜测:文件校验操作,造成mysql数据被分隔成更多次数刷新到磁盘并触发主从同步动作,造成更加密集的MyISAM表锁。由于,写锁优先级高,造成客户端请求近乎饿死,进而造成连接数打满。

    3. 处理

      • 考虑到这是个多年轻的项目,这次需要切换 MyISAM 到 innodb

      • 调整Mysql相关参数,尽可能的发挥机器性能

MYSQL 知识

1. 相关命令、参数

  • 落盘策略: innodb_flush_log_at_trx_commit 和 sync_binlog

  • 查看主从同步状态:

      show slave status
      show master status

    2. mysql 的 sql_mode:

  • 查看当前sql-mode

      SELECT @@GLOBAL.sql_mode;
      SELECT @@SESSION.sql_mode;
  • 设置当前sql-mode

      // 命令
      SET GLOBAL sql_mode = 'modes...';
      SET SESSION sql_mode = 'modes...';
      // my.cnf中配置sql-mode
      [mysqld]
      #set the SQL mode to strict
      #sql-mode="modes..." 
      sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    3. 查看引擎的锁定状态

  • Myisam : show status like 'table%'

    • 通过检查 table_locks_waited 和 table_locks_immediate 状态变量分析系统上表锁争夺情况

    • 表级锁是MyISAM不适合含有大量更新操作和查询操作应用的原因。

      •Table_locks_immediate 
        The number of times that a request for a table lock could be granted immediately. 
      •Table_locks_waited 
        The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
  • Innodb : show status like 'innodb_row_lock%'

    • 查看InnoDB行锁争用情况

      在学习锁的过程中,常用:set autocommit = 0; 来关闭自动提交,进而观察的状态。

      4. MyISAM & InnoDB

      引擎

      事务

      锁机制

      外键

      并发性能

      缓存

      备份

      系统表是否使用

      引入GTID后的影响

      MyISAM

      不支持

      表锁

      不支持

      索引

      为了保持数据一致性,必须对表加读锁,影响业务写

      使用

      有问题(一个事务中同时使用事务引擎和非事务引擎)

      InnoDB

      支持

      行锁

      支持

      索引和数据

      不需要锁表,不影响业务读写

      使用

      无问题

从MySQL5.5开始,默认存储引擎变为了InnoDB,在此之前默认引擎使用的是MyISAM.
从MySQL8.0开始,系统表也将采用InnoDB,完全放弃MyISAM。
  • MyISAM

    • MyISAM面临的主要问题

      • 问题1:主从复制中断、主从数据不一致 :由于MyISAM不支持事务,导致特别容易出现主备复制异常、主备数据不一致的情况

      • 问题2: 进行备份时,无论是采用mysqldump进行的逻辑备份还是使用extrabackup进行的物理备份,为了保证MyISAM表的数据一致性,必须对表进行加锁,导致阻塞写入。这对于重建主从是经常出现的问题。

    • MyISAM表级锁:

      • 模式:表共享读锁(Table Read Lock)、表独占写锁(Table Write Lock)

      • 默认情况下,写锁比读锁具有更高的优先级,这正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因。因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。

      • 在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。

  • InnoDB

    • 与 MyISAM 最大不同有两点:

      1. 支持事务

      2. 采用行级锁

  • 索引

    • MyISAM

      • MyISAM索引实现:MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址.

      • 在MyISAM中,主索引和辅助索引辅助索引(Secondary Index, 即非主键索引)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。

    • InnoDB

      • InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。InnoDB 不会压缩索引。

        • 聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

        • 因为所有辅助索引都引用主索引(即,辅助索引也会包含主键列),如果主键定义的比较大,其他索引也将很大,所以不建议使用过长的字段作为主键。另外,如果想在表上定义很多索引,则争取尽量把主键定义得小一些。

      • 行锁

        • InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。

          • 这一点MySQL与Oracle不同,它们是通过在数据块中,对相应数据行加锁来实现的

        • InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,innoDB才使用行级锁,否则InnoDB将使用表锁。

      • 主键

        • 用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

Mysql 内存优化

注意:以下都是在MySQL目录下的my.ini文件中改写

  • InnoDB内存优化

    • InnoDB用一块内存区域做I/O缓存池,该缓存池不仅用来缓存InnoDB的索引块,而且也用来缓存InnoDB的数据块。

    • innodb_log_buffer_size

      • 决定了InnoDB重做日志缓存的大小,可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘操作。

    • Innodb_buffer_pool_size

      • 决定了InnoDB存储引擎表数据和索引数据的最大缓存区大小。

  • MyISAM内存优化

    • MyISAM存储引擎使用key_buffer缓存索引模块,加速索引的读写速度。对于MyISAM表的数据块,mysql没有特别的缓存机制,完全依赖于操作系统的IO缓存。

    • read_rnd_buffer_size

      • 对于需要做排序的MyISAM表查询,如带有order by子句的sql,适当增加read_rnd_buffer_size的值,可以改善此类的sql性能。但需要注意的是read_rnd_buffer_size独占的,如果默认设置值太大,就会造成内存浪费。

    • key_buffer_size

      • key_buffer_size决定MyISAM索引块缓存分区的大小。直接影响到MyISAM表的存取效率。对于一般MyISAM数据库,建议1/4可用内存分配给key_buffer_size:

    • read_buffer_size

      • 如果需要经常顺序扫描MyISAM表,可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个seesion独占的,如果默认值设置太大,就会造成内存浪费。

  • 调整MySQL参数并发相关的参数

    1. 调整max_connections: 提高并发连接

    2. 调整thread_cache_size

      • 加快连接数据库的速度,MySQL会缓存一定数量的客户服务线程以备重用,通过参数thread_cache_size可控制mysql缓存客户端线程的数量。

    3. innodb_lock_wait_timeout

      • 控制InnoDB事务等待行锁的时间,对于快速处理的SQL语句,可以将行锁等待超时时间调大,以避免发生大的回滚操作。(技术文)

Last updated