MySQL 优化实施方案

优化的哲学

优化风险

优化设计到变更，变更就有风险

优化方向

在数据库优化上有两个主要方面：即安全与性能。（CAP）
  安全 ---> 数据可持续性
  一致
  性能 ---> 数据的高性能访问

优化框架

  基础设施：硬件，操作系统，网络，文件系统
  数据库实例：内存，线程，IO等参数
  应用层：schema设计，SQL，索引，锁
  架构层面：选型，主从

优化工具

OS （提前规划好）

cpu：top，htop，glances,vmstat、sar top、nmon、mpstat
MEM，swap：top，htop，glances,free,ps -aux
IO：iotop，iostat，vmstat，glances, ss ,netstat,iptraf,iftop,lsof

MySQL

show processlist
show status
explain
slowlog
IS，PS，SYS
PT工具

优化过程

基础设施

主机

真实硬件（PC Server）：DELL R系列，华为，浪潮，HP，曙光，联想
云产品：ECS（阿里），数据库RDS（阿里），DRDS（阿里），polarDB（阿里），TDSQL（腾讯）
CPU：IO密集型（OLTP E志强），计算密集型（OLAP，I系列，P系列）
MEM：ECC校验功能
HD：Flash--》PCI-E SSD--》SAS--》SCSI--》SATA
RAID：raid 10-->raid 5-->raid 1

存储

  FC-SAM：HDS，EMC，IBM
  冷存储

网卡

  单卡单口，网卡绑定（bond）建议主备
  交换机：高端，堆叠。

OS

swap调整（必调）

[root@WANGXIANG backup]# cat /proc/sys/vm/swappiness
30
[root@WANGXIANG backup]# echo 0 > /proc/sys/vm/swappiness
[root@WANGXIANG backup]# cat /proc/sys/vm/swappiness
0
# 永久生效
[root@WANGXIANG backup]# echo 'vm.swappiness=0' >> /etc/sysctl.conf 
[root@WANGXIANG backup]# sysctl -p
vm.swappiness = 0

IO调度策略（必调）

[root@WANGXIANG backup]# cat /sys/block/sda/queue/scheduler 
noop [deadline] cfq
deadline：最后期限，我等你一会，你要是在一定期限内没有反应，去处理别的操作
echo deadline > /sys/block/sda/queue/scheduler 

IO优化建议+排查思路

raid
no lvm，不要过度条度化，IOPS
ext4或xfs
ssd
IO调度策略

通过工具，辅助定位性能问题

top
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
CPU问题：
sys：系统相关。协调，资源管理，资源调配，资源监控，系统调用，内核程序。
      MySQL：资源调配频繁，锁逻辑处理，会话处理

us：用户进程，线程
      MySQL：干正事时间，大事务，全表扫描，复杂查询
            

wait：等待
      IO wait（索引应用不合理），锁等待

idle：空闲

定问问题方法

top -Hp mysql.pid -->
os_thread_id （setect * from performance_schema.threads where THREAD_OS_ID='12118'） -->
performance_schema.thread where os_thread_id -->
MYSQL_THREAD_ID -->
select sql_text from events_statements_history where THREAD_ID='12118' -->
explain,锁排查

数据库实例

会话

'''
max_connections 最大连接数
1500-4000
show processlist;
select count(*) from information_schma.processlist where command='sleep';

最大连接数超限：
1.IE：409 --》 max_connections 上限 --》show processlist--》sleep？语句问题？
2.改（默认151+管理员的一个连接数）--》limits.conf-->nofile没调整
3.sleep线程--》有规律的同时释放。7200s，自动回收--》Keepalived（7200s超时时间），lvs（1200s)
4.show processlist; kill 85；
5.percona-toolkits -->pt-kill

wait_timeout :处理非交互会话的超时时间 建议：60 s
3306 [(none)]>select @@wait_timeout; #查看

interactive_timeout ：处理交互会话的超时时间 建议：600 s

thread_cache_size :会话重用，cpu紧缺的情况下可以酌情开启一下thread_cache_size=10

3306 [(none)]>show status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------+-------+
'''

临时表

key_buffer_size (比配置）
1.myisam表的索引缓冲区
2.临时表的缓冲区

max_allowed_packet *****
max_allowed_packet=256