13 MySQL-优化

优化哲学

为什么优化？

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

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

优化风险

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

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

谁参与优化

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

优化方向

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

优化的范围及思路

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

image.png

优化工具的使用

系统层面的

CPU

top
cpu使用情况的平均值：

CPU每个核心的分别使用的情况（按1）：

程序是如何使用CPU的？
系统给每个程序分配CPU的时候，以时间来划分表的。
CPU有效工作时间?
计算: 程序运行,数据处理
控制: 少量的关于申请资源和释放资源等
3.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

KiB Mem : 4028432 total, 3774464 free, 116240 used, 137728 buff/cache
1.2.1 名称介绍
total :总内存大小
free ：空闲的
used ：在使用的
buff/cache ：缓冲区 和 缓存
1.2.2 内存管理子系统：
slab Allocator
buddy system
程序=指令+数据
对于page cache来讲(OS buffer)

1. 内存的可用空间的计算 free +buffer cache
2. 内存回收(buffer)的方式:
   (1) 写入磁盘
   (2) swap

对于数据库来讲：需要将swap屏蔽掉
1.1.3 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
1.1.4.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了
   存储规划有问题.

数据库优化工具

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.1 硬件优化
主机

真实的硬件（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），交换机堆叠

以上问题，提前规避掉。
3.2 操作系统优化
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深入业务,或者要和开发人员\业务人员配合实现

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

MySQL参数优化测试

虚拟机vm12.5，OS centos 6.9（系统已优化），cpu4（I5 4440 3.1GHZ）,MEM4GB ,HardDisk:SSD

模拟数据库数据

drop database if exists oldboy;
create database oldboy charset utf8mb4 collate utf8mb4_bin;
use oldboy;
create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()