# 批量写入造成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语句，可以将行锁等待超时时间调大，以避免发生大的回滚操作。（技术文）


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://joyous-x.gitbook.io/mbook/part-iii-database/problem_with_using_myisam.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.