33MySQL优化

优化哲学
优化有风险
优化的原因
​    主机架构稳定性，I/O规划及配置，Swap，OS内核参数，网络问题

应用 (Index lock session)
​    应用程序稳定性和性能，SQL语句性能，串行访问资源，性能欠佳会话管理

数据库优化 (内存 数据库设计 参数)
​    内存，数据库结构(物理，逻辑)，实例配置

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

top命令
%Cpu(s)
id 空闲的CPU时间片占比
us 用户程序工作所占用的时间片占比
sy 内核工作花费的cpu时间占比
​    过高的原因 内核本身bug，并发很高，锁
wa cpu用来等待的时间片占比
​    过高原因 IO，等待大的处理事件，锁

iostat命令
一般情况下CPU高IO也应该高
如果CPU高IO低
​    wait高 有可能IO出问题了(Raid 过度条带化)
​    sys高 有可能是锁的问题，需要进一步去数据库中判断

数据库层
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain

扩展类深度优化
pt系列(pt-query-digest pt-osc pt-index等)
mysqlslap
sysbench
information_schema(I_S)
performance_schema(P_S)
sys

硬件优化
主机
真实的硬件(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 安全+性能都很高，最少4块盘，浪费一半的空间(高IO要求)

网络
硬件买好的(单卡单口)
网卡绑定(bonding)，交换机堆叠

系统
Swap调整
echo 0 >/pro/sys/vm/wwappiness的内容改为0(临时)
vim /etc/sysctl.conf
vm.swappiness=0 (永久)
sysctl -p

IO调度策略
centos7 默认是deadline
cat /sys/block/sda/queue/scheduler

临时修改为deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler
vim /boot/grub/grub.conf
kernel /boot/vmlinuz-2.6.18-8.e15 ro root=LABEL=/ elevator=deadline rhgb quiet

数据库实例
Max_connections
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值
判断依据
show variables like 'max_connections';
show status like 'Max_used_connections';
修改方式举例
vim /etc/my.cnf
Max_connections=1024
补充
开启数据库时，我们可以临时设置一个较大的测试值，观察show status like 'Max_used_connections'变化，如果max_used_connections跟max_connections相同，那么就是max_connections设置过低或者超过服务器的负载上限了

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

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

key_buffer_size
myisam表的索引缓冲区，临时表的缓冲区
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值
show status like "create_tmp%"
Create_tmp_tables/(Created_tmp_disk_tables+Created_tmp_tables) 越高越好
Create_tmp_disk_tables是否过多，从而认定当前服务器运行状况的优劣
Create_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables) 控制在5%-10%以内
key_buffer_size=64M

max_connect_errors
是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息，max_connect_errors的值与性能并无太大关系
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=2000

sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区，增加这个值加速
ORDER BY GROUP BY distinct union
配置依据
Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内资源
例如：500个连接将会消耗500*sort_buffer__size(2M)=1G内存
配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下
sort_buffer_size=1M

join_buffer_size
用于表间关联缓存大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。尽量在SQL与方面进行优化，效果较为明显。
优化的方法 在on条件列加索引，至少应当是有MUL索引
read_buffer_size=1M
mysql读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，mysql会为它分配一段内存缓冲区，如果对表的顺序扫描请求非常繁忙，并且你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
read_rnd_buffer_size=1M
mysql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如，按照顺序排序)，将分配一个随机读缓存区。进行排序查询时，mysql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但mysql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据，随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的

max_allowed_packet
mysql根据配置文件会限制，server接受的数据包大小
配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数
配置方法 max_allowed_packet=256M

thread_cache_size
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限)，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能
配置依据
通过比较Connections和Threads_created状态的变化，可以看到这个变量的作用。
设置规则如下：1GB内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存，可配置更大。服务器此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
试图连接到mysql(不管是否连接成功)的连接数

show status like 'threads_%';
Threads_cached 代表当前此时此刻线程缓存中有多少空闲线程
Threads_connected 代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数
Threads_created 代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表名mysql服务器一直在创建线程，这也是比较耗cpu sys资源，可以适当增加配置文件中thread_cache_size值
Threads_running 代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态
配置方法 thread_cache_size=32
整理：
Threads_created 一般在架构设计阶段，会设置一个测试值，做压力测试，结合zabbix监控，看一段时间内此状态的变化，如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK，如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下(内存)

innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样
配置依据
InnoDB使用该参数指定大小的内存来缓冲数据和索引，对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%，一般建议不要超过物理内存的70%
innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit
主要控制了innodb将log_buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0,1,2三个。0表示当事务提交时，不做日志写入操作，而是每秒钟将log_buffer中的数据写入日志文件并flush磁盘一次，1表示每次事务的提交都会引起redo日志文件写入，flush磁盘的操作，确保了事务的ACID，2表示每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
配置依据：
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此mysql手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。根据mysql官方文档，在允许丢失最近部分事务的危险前提下，可以把该值设为0或2
配置方法
innodb_flush_log_at_trx_commit=1
双1标准中的1个1

innodb_thread_concurrency
此参数用来设置innodb线程的并发数量，默认值为0表示不限制
配置依据
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如果一个工作负载中，并发用户线程数量小于64，建议设置为0，如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置为128，并通过不断地降低这个参数，直到发现能够提供最佳性能的线程数。例如，假设系统通常有40到50个用户，但定期的数量增加至60,70甚至200，会发现性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置参数为80，以避免影响性能。
如果不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多(比如20个虚拟CPU)，建议通过设置innodb_thread_concurrency参数为这个值(也可能更低，这取决于性能体现)，如果目标是将MySQL与其他应用隔离，可以考虑绑定mysql进程到专有的虚拟CPU，但是需要注意的是，这种绑定在mysqld进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下可能会设置mysqld进程绑定的虚拟CPU，允许其他应用程序使用虚拟CPU的一部分或全部。在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小，定期检测和分析系统，负载量，用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整
设置标准：
当前系统cpu使用情况，均不均匀 top
当前的连接数有没有达到顶峰
show status like 'threads_%';
show processlist;
配置方法：
innodb_thread_concurrency=8
方法：
看top，观察每个cpu的各自的负载情况
发现不平均，先设置参数为cpu个数，然后不断增加(一倍)这个数值
一直观察top状态，直到达到比较均匀时说明已经到位了

innodb_log_buffer_size
确定日志文件所用的内存大小，以M为单位，缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小
innodb_log_buffer_size=128M
设定依据
大事务 存储过程调用CALL
多事务

innodb_log_file_size
设置ib_logfile0 ib_logfile1
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能
innodb_log_file_size=100M

innodb_log_files_in_group
为提高性能，mysql可以以循环方式将日志文件写到多个文件，推荐设置为3

锁的监控及处理
Record Lock
Next Lock
GAP Lock

监控锁的状态
查看有没有锁等待
show status like 'innodb_row_lock%';
innodb_row_lock_current_waits 当前有多少锁等待
innodb_row_lock_waits 一共发生过多少锁等待

查看哪个事务在等待(被阻塞了)
select * from information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id 事务ID号
trx_state 当前事务的状态
trx_mysql_thread_id 连接层的，连接线程ID
trx_query 当前被阻塞的操作

查看锁源
select * from sys.innodb_lock_waits 被锁和锁定它的之间关系
locked_table 哪张表出现的等待
waitting_trx_id 等待的事务
waitting_pid 等待的线程号
blocking_trx_id 锁源的事务ID
blocking_pid 锁源的线程号

查找锁源的thread_id
select * from performance_schema.threads where processlist_id=204;

根据锁源SQL线程ID，找到锁源的SQL语句
select * from performance_schema.`events_statements_current` where thread_id=1716;

优化项目 锁的监控及处理
硬件环境：DELL R720，E系列16核，48G MEM，SAS*900G*6，RAID10
在例行巡检时，发现9-11点时间段的CPU压力非常高
项目的职责
通过top详细排查，发现mysqld进程占比达到700-800%
其中有量的CPU是被用作的SYS和WAIT，us处于正常
怀疑是MySQL锁或者SQL语句出了问题
经过排查slowlog及锁等待情况，发现有大量锁等待及少量慢语句
pt-query-diagest 查看慢日志
show status like 'innodb_row_lock%'; 查看锁等待
情况一 有100多个current_waits，说明当前很多锁等待情况
情况二 1000多个lock_waits，说明历史上发生过的锁等待很多

查看哪个事务在等待
查看锁源事务信息
找到锁源的thread_id
找到锁源的SQL语句

找到语句之后，和应用开发人员进行协商
开发人员描述，此语句是事务挂起导致，提出建议是临时kill会话最终解决问题
开发人员查看后，发现是业务逻辑问题导致死锁，产生了大量锁等待。临时解决方案，将阻塞事务的会话kill掉。最终解决方案，修改代码中的业务逻辑
项目结果 经过排查处理，锁等待的个数减少80%，解决了CPU持续峰值的问题

锁监控涉及到的命令
show status like 'innodb_rows_lock%';
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;

死锁监控
show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks=1

主从优化
从库多线程MTS
5.7以上的版本 必须开启GTID，binlog必须是row模式
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON