MySQL全面优化

1.优化哲学

1.1 为什么优化？

为了获得成就感?
为了证实比系统设计者更懂数据库?
为了从优化成果来证实优化者更有价值?

但通常事实证实的结果往往会和您期待相反！
优化有风险，涉足需谨慎！

1.2 优化风险

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

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

1.3 谁参与优化

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

1.4 优化方向

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

1.5 优化的范围及思路

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

2. 优化工具的使用

2.1 系统层面的

2.1.1 CPU

top  
top -H
top -H -p pid
top -H -p 9618

CPU使用情况:
%Cpu(s):  
0.1 us    : 用户进程占用CPU时间占比
我们觉得越高越好,不要满负荷

0.2 sy    : 系统本身和内核工作CPU时间占比
资源管理,维护,分配,回收等等.

并发连接过高 .
锁争用
99.8 id   : 空闲

0.0 wa    : 等待时CPU时间占比
IO吞吐 有问题   : 硬件,链路,RAID 
IO/PS  峰值     :   大量随机IO ,索引设计不合理,多表连接优化不到位,大量子查询,大事务.  
锁争用

2.1.2 MEM

KiB Mem :  4028432 total,  3774464 free,   116240 used,   137728 buff/cache

1.2.1 名称介绍

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

2.1.3 内存管理子系统：

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

2.1.4 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 

2.2. 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了
存储规划有问题.

2.3 数据库优化工具

    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

3. 优化思路分解

3.1 硬件优化

3.1.1 主机

(1) 真实的硬件（PC Server）: 
    DELL  R系列 ，华为，浪潮，HP，联想 ,曙光
        
(2)云产品：
    ECS、
    数据库RDS、DRDS\Polardb  , TDSQL

(3) IBM 小型机: 
 P6  570  595  
 P7 720  750 780     
 P8 

3.1.2 CPU根据数据库类型

OLTP  在线事务处理系统.
OLAP  数据分析,处理 

IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发
CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列）

CPU密集型： I 系列的，主频很高，核心少 
IO密集型：  E系列（至强），主频相对低，核心数量多

3.1.3 内存

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

3.1.4 磁盘选择

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

3.1.3 存储设备

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

3.1.4 网络

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

3.2 操作系统优化

3.2.1 Swap调整

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

扩展:内存管理

1. 三个区域 :
    常驻内存集: 程序运行服务的
    页缓存page cache: 
        FREE list 
        LRU  list
    匿名页:

2. Slab Allocator
    将 page cache 划分成了好多条链状结构.
类型一: 
    Free list(空闲的)
    ==================================================
     |    |
     o    o
    
类型二 :
    LRU list (正在被使用的)
    
    冷================================================热
      i    i 
      0    0
    
3. buddy system (内存伙伴系统)
    内存回收和重利用
    
cat /proc/sys/vm/swappiness   ====> 0

free list 上为 0;

buddy system 进行内存回收和重利用 
1. 优先释放 Cache(负责查询类的内存结构),从冷到热进行释放. 
2. Cache没法释放时,会根据buffer从冷导热,进行回收和重用内存
3. 所有可被释放 buffer 或cache,已经全部被回收重用了,还是内存紧缺的话
此时,swap还是会被使用. 


内存泄露问题:     
8G   使用率达到了 95%以上
innodb_buffer_pool_size=2G 
redo_buffer_size=256M 
其他内存总共: 1G 左右
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

3.2.2 IO调度策略

centos 7 默认是 deadline
cat   /sys/block/sda/queue/scheduler
cat   /sys/block/vda/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优化的难度。

3.3 应用层

1. 开发过程规范,标准
2. 减少慢SQL:不走索引,复杂逻辑,切割大事务.
    like '%aa%'
    != not in 
    limit >500w 
    DDL  ---> show processlist ;  ---> kill  ---> Online DDL ,pt-osc
    delete 大量数据. ----> pt-archive
    update : 索引  ,   锁.

3. 避免业务逻辑错误,避免锁争用.
    a :   update  id=10 ; update  id=100;
    b :  update   id=100; update id=10;


这个阶段,需要我们DBA深入业务,或者要和开发人员\业务人员配合实现

优化,最根本的是"优化"人.
                    ----oldguo