MySQL-优化

优化风险

优化不总是对一个单纯的环境进行！还很可能是一个复杂的已投产的系统。
优化手段本来就有很大的风险，只不过你没能力意识到和预见到！
任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败！

稳定性和业务可持续性通常比性能更重要！
优化不可避免涉及到变更，变更就有风险！
优化使性能变好，维持和变差是等概率事件！
优化不能只是数据库管理员担当风险，但会所有的人分享优化成果！
所以优化工作是由业务需要驱使的！

谁参与优化

数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员

优化方向

安全优化（业务持续性）
性能优化（业务高效性）

优化的范围及思路

优化范围：
存储、主机和操作系统:
    主机架构稳定性
    I/O规划及配置
    Swap
    OS内核参数
        网络问题
应用程序:（Index，lock，session）
        应用程序稳定性和性能
        SQL语句性能
    串行访问资源
    性能欠佳会话管理
数据库优化:（内存、数据库设计、参数）
    内存
    数据库结构(物理&逻辑)
    实例配置

优化效果和成本的评估：

image.png

优化工具的使用

系统层面的

CPU

top    
cpu使用情况的平均值：
CPU每个核心的分别使用的情况（按1）

程序是如何使用CPU的

     系统给每个程序分配CPU的时候，以时间来划分表的

CPU有效工作时间

      计算: 程序运行,数据处理
      控制: 少量的关于申请资源和释放资源等

CPU无效工作时间

      等待 IO

CPU各项指标说明：

0.0 us
用户程序，在运行过程中，使用的CPU时间的占比。
我们希望的是越高越好，尽量控制在90%
0.0 sy
控制: 资源管理，内核的工作(系统调用)
sys高的原因: 
      1.  bug ,中病毒了
      2.  锁的问题
99.9 id 
CPU空间的时间占比      

0.0 wa
CPU花在等待上的时间

wa高的原因：
          1. 锁
          2. IO （raid，过度条带化）
          3. 索引
单核cpu使用情况监控：
主要判断我们cpu多核心有没有被充分利用。
现象：单颗很忙，其他很闲，对于MySQL来讲，有可能是并发参数设定不合理导致的

MEM

free
名称介绍
total :总内存大小
free  ：空闲的
used  ：在使用的
buff/cache ：缓冲区 和 缓存

内存管理子系统：

slab Allocator
buddy system 
程序=指令+数据
对于page cache来讲(OS buffer)
1. 内存的可用空间的计算   free +buffer cache 
2. 内存回收(buffer)的方式:
        (1) 写入磁盘
        (2) swap  
对于数据库来讲：需要将swap屏蔽掉

swap

KiB Swap:  2097148 total,  2097148 free,        0 used.  3701464 avail Mem 
Linux 6操作系统，默认回收策略（buffer cache），不立即回收策略
内存使用达到100%-60%时候，40% 会使用swap
Linux 7操作系统
内存使用达到100%-30%（70%）时候，才会时候swap
cat /proc/sys/vm/swappiness 
30  
echo 0 >/proc/sys/vm/swappiness    的内容改成0（临时）
vim /etc/sysctl.conf
添加:
vm.swappiness=0
sysctl -p

iostat 命令

dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
现象说明
1. IO 高 cpu us 也高,属于正常现象
 2. CPU  us高  IO很低   ,MySQL 不在做增删改查,有可能是存储过程,函数,排序,分组,多表连接
3. Wait 高  , IO低:IO出问题了,锁等待的几率比较大. 
IOPS：每秒磁盘最多能够发生的IO次数，这是个定值 
频繁小事务,IOPS很高,达到阈值,可能IO吞吐量没超过IO最大吞吐量.无法新的IO了
存储规划有问题

iostat 命令

dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
现象说明
1. IO 高 cpu us 也高,属于正常现象
2. CPU  us高  IO很低   ,MySQL 不在做增删改查,有可能是存储过程,函数,排序,分组,多表连接
3. Wait,SYS 高  , IO低:IO出问题了,锁等待过多的几率比较大. 
IOPS：每秒磁盘最多能够发生的IO次数，这是个定值 
频繁小事务,IOPS很高,达到阈值,可能IO吞吐量没超过IO最大吞吐量.无法新的IO了
存储规划有问题

数据库优化工具

    show status  
    show variables 
    show index  
    show processlist 
    show slave status
    show engine innodb status 
    desc /explain 
        slowlog
    扩展类深度优化:
    pt系列
    mysqlslap 
    sysbench 
    information_schema 
    performance_schema
    sys

优化思路分解

硬件优化

主机

真实的硬件（PC Server）: DELL  R系列 ，华为，浪潮，HP，联想
云产品：ECS、数据库RDS、DRDS

IBM 小型机 P6  570  595   P7 720  750 780     P8 

根据数据库类型

OLTP 
OLAP  
IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发
CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列）
CPU密集型： I系列的，主频很高，核心少 
IO密集型：  E系列（至强），主频相对低，核心数量多

内存容量选择

     建议2-3倍cpu核心数量 （ECC）

磁盘选择

     SATA-III   SAS    Fc    SSD（sata） pci-e  ssd  Flash
     主机 RAID卡的BBU(Battery Backup Unit)关闭

存储

根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid5、raid10、热备盘)   
r0 :条带化 ,性能高
r1 :镜像，安全
r5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）
r10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）

网络

1、硬件买好的（单卡单口）
2、网卡绑定（bonding），交换机堆叠
以上问题，提前规避掉

操作系统优化

Swap调整
echo 0 >/proc/sys/vm/swappiness的内容改成0（临时），
/etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p

这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式
这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多

IO调度策略

centos 7 默认是deadline
cat   /sys/block/sda/queue/scheduler

临时修改为deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler 
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

IO ：
    raid
    no lvm
    ext4或xfs
    ssd
    IO调度策略
提前规划好以上所有问题，减轻MySQL优化的难度

应用端

1. 开发过程规范,标准
2. 减少烂SQL:不走索引,复杂逻辑,切割大事务
3. 避免业务逻辑错误,避免锁征用
这个阶段,需要我们DBA深入业务,或者要和开发人员\业务人员配合实现