mysql案例 ~ mysql磁盘分析与调优

 一 简介 谈谈磁盘IO的问题二 目的:如何进行IO性能问题的排查

二  linux角度
   一 机械硬盘基本定义
       寻道时间，表示磁头在不同磁道之间移动的时间(最耗时)。
       旋转延迟，表示在磁道找到时，中轴带动盘面旋转到合适的扇区开头处。
       传输时间，表示盘面继续转动，实际读取数据的时间

 二 机械盘与SSD盘的对比

    1 无论是SSD还是机械盘,随机读写IO的效率都远远低于顺序读写IO

                                     1 对于机械盘,随机读写IO会增加更多的盘片旋转和磁头寻道时间

                                     2 对于SSD硬盘,随机读写IO会造成的大量的垃圾回收(垃圾需要先擦除回收然后再进行写入)

    2 机械硬盘的基本单位是扇区(通常是512字节) SSD硬盘的基本单位是页(通常是4K 8K)

    3 SSD对比机械盘最大的优势就是没有寻道时间,对于机械盘消耗时间最大的也就是寻道时间

    4 SSD本身有写入次数限制(寿命),还有写磨损问题 这个需要注意,一般服务器情况下都是SSD+机械硬盘混合搭配

 三 linux系统的算法

    CFQ 完全公平调度器,为每个进程都创建不同的IO调度队列,并按照时间片来平均分配每个进程的IO请求,常用于多进程的场景

    DEADLINE 为读写请求分别创建不同的IO队列,并保证最先到达deadline的请求会被优先处理,常用于数据库场景,能提高机械磁盘的吞吐量

    NOOP    是一个先入先出的队列,只做一些合并IO的操作,适合SSD

 四 文件系统层
   选择xfs或者ext4文件系统,推荐xfs
  五 mysql角度
       控制脏页刷新
       1 innodb_flush_neighbors 设置为0 关闭该特性
       2 innodb_io_capacity 脏页刷新数量,建议设置为iops的60%,如果设置的过低,会限制tps的能力,如果设置的过高,会加大磁盘io的压力,因为一次性刷新的脏页数量会多

       3 调整redo log大小,建议单个redo大于1G,增加多个redo文件,防止出现写磨损问题

       4 调整 innodb_max_dirty_pages_pct 脏页占用比,进行调低. nnodb_max_dirty_pages_pct和  innodb_io_capacity 进行合理搭配调节
      参数说明
      当刷新一个脏页时，innodb存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新,这样做能合并多个IO,减少硬盘压力
      建议 机械硬盘开启 SSD硬盘关闭        

六 压测
     1 压测磁盘组的随机读写能力
        fio -filename=/data/d.txt -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=1500M -numjobs=40 -runtime=10 -group_reporting -name=mytest
       关心参数
       read and write的iops
    2 通过压力测试得出服务器的最大承受值
         请注意:util并不能真正反映磁盘组的整体性能,反过来,util值忙,代表磁盘繁忙程度,想要看磁盘压力

          1  观察iowait.

          2 观察磁盘的IOPS(w/s+r/s)

          3 观察await

三 mysql角度
  一 事务
   1 写日志文件
       1 流程 redo log+binlog 二阶段提交-> 写日志文件 顺序写
       2 控制参数
        innodb_flush_log_at_trx_commit = 1 控制redo log的磁盘刷新
        sync_binlog = 1 控制binlog 的磁盘刷新
  2 脏页刷新
      1 将内存中改变的数据页刷新到磁盘中
      2 控制参数
        innodb_flush_neighbors 控制相邻脏页的刷新
       innodb_io_capacity 控制脏页刷新的数量

       innodb_max_dirty_pages_pct 脏页占用比
 二 查询
   1 慢语句
     使用索引不当的慢sql查询会造成磁盘的繁忙,这种情况多出现在

             1 大表的慢sql查询

             2 表的主键碎片化也会造成大量的随机读,常见于uuid作为主键或者执行过大量更改的情况

             3 单个慢sql出现在慢日志,慢sql本身受到影响(1 脏页刷新 2 日志刷新 3 并发sql查询)
   2 并发语句
     大量并发语句并发查询导致的磁盘繁忙情况
四  判断分析
   磁盘没有到达瓶颈
    1 根据上文提出的,要分析mysql哪一部分出了问题,日志刷新->脏页刷新->慢日志,根据某一个环节进行优化
   磁盘到达瓶颈
   1 mysql慢日志出现了很多不该出现的慢日志语句,通常表现在扫描行数很少,单体执行很快
   2 监控图的iops经常性报警,尤其是在业务高峰期,由于IO限制导致的负载升高,iowait值很高
   解决办法:1 拆分业务 2 做读写分离 3 升级硬盘,采用ssd硬盘

五 SSD相关配置

   1  PCIE+SSD卡,直接插入支持PCIE插槽的服务器即可,安装驱动格式化后就可使用

   2  PCIE+SSD 品牌   1 英特尔  2 宝存 

   3 宝存相关特点

       1 k-v存储的优化,针对 rocksdb

       2 原子写,针对数据库的双写

       3 IO资源隔离,针对 k8s虚拟化设置

  3 本人测试 相关参数

      mysql参数

      1 innodb_io_capacity =4000

      2 innodb_flush_neighbors=0

      3  innodb_doublewrite = OFF

      4  innodb_flush_method=O_DIRECT

      linux

      1 采用centos 7.+  操作系统,SSD产品对于最新内核有很多优化措施

      总结

      1 请注意]PCID+SSD不用设置文件系统算法

      2  不同SSD品牌,对于需要占用系统内存的SSD,要尽量降低BF的占用(本人测试,ssd本身大概能占到10G) 对于不需要系统内存的SSD(SSD本身虚拟化内存)就不必过分关注内存占用

      3  SSD 对于mysql调优主要在于脏页的相关操作(刷新数量,机制,和备份),对于读取则完全依赖SSD本身的特性方式