mysql优化笔记整理

zhangkai

MySQL优化

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

2. 优化工具介绍
2.1 系统层
CPU :  计算(主)和调度(次)  
MEM :  缓存和缓冲
IO  :  输入和输出

(1) top命令

%Cpu(s):  0.0 us, 0.0 sy, 100.0 id,  0.0 wa

id   空闲的CPU时间片占比
us         用户程序工作所占用的时间片占比
sy   内核工作花费的cpu时间片占比

过高的原因:
内核本身bug
并发很高
锁
wa         cpu用来等待的时间片占比
IO
等待大的处理事件
锁
KiB Mem :  4937752 total,  3988956 free,   476100 used,   472696 buff/cache   4193924 avail Mem
KiB Swap:  1048572 total,  1048572 free,        0 used.


(2) iostat
[root@db01 ~]# iostat -dk 1

一般情况下，CPU高，IO也应该高。
如果：CPU 高   ，IO 比较低
wait 高：  有可以能IO出问题了（Raid ，过度条带化）   
SyS  高：  有可能是锁的问题，需要进一步去数据库中判断

(3) vmstat
[root@db01 ~]# vmstat   1


2.2 数据库层工具
    show status  
    show variables
    show index  
    show processlist
    show slave status
    show engine innodb status
    desc /explain
    slowlog
       
    扩展类深度优化:
    pt系列（pt-query-digest pt-osc pt-index 等）
    mysqlslap
    sysbench
    information_schema （I_S）
    performance_schema (P_S)
    sys



3. 优化思路

3.0 未优化前的压力测试
db01 [(none)]>create database oldguo charset utf8mb4;
db01 [(none)]>use oldguo;
db01 [oldguo]>set sql_log_bin=0;
db01 [oldguo]>source t100w.sql
db01 [oldguo]>grant all on *.* to root@'localhost' identified by '123';

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldguo' \
--query="select * from oldguo.t100w where k2='ABxy'" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose

3.1 主机,存储,网络
主机
真实的硬件（PC Server）: DELL  R系列 ，华为，浪潮，HP，曙光,联想
云产品：ECS、数据库RDS、DRDS、PolarDB
IBM 小型机 P6  570  595   P7 720  750  780     P8

CPU根据数据库类型
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（临时），
vim /etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p


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优化的难度。

3.3 数据库实例(参数)

3.3.1 Max_connections *****
（1）简介
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。
（2）判断依据
show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+
       
（3）修改方式举例
vim /etc/my.cnf
Max_connections=1024

补充:
    1.开启数据库时,我们可以临时设置一个比较大的测试值
    2.观察show status like 'Max_used_connections';变化
    3.如果max_used_connections跟max_connections相同,
    那么就是max_connections设置过低或者超过服务器的负载上限了

3.3.2 back_log ***
（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
（2）判断依据
show full processlist
发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例
vim /etc/my.cnf
back_log=1024


3.3.3 wait_timeout和interactive_timeout ****

（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
（3）修改方式举例
wait_timeout=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。


3.3.4 key_buffer_size *****
(1) myisam 表的索引缓冲区
(2) 临时表的缓冲区
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |

Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)   越高越好
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)

控制在5%-10%以内
mysql> show variables like "key_buffer_size%";



3.4 优化后的压力测试

max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M



3.5 应用层面优化