优化哲学

优化有风险

优化的范围

存储 主机 和操作系统

• 主机架构稳定性
• I/O规划及配置
• swap》
• OS内核参数
• 网络问题

应用（index，lock，session）

• 应用程序稳定性 和性能
• SQL语句性能
• 串行访问资源
• 性能欠佳会话管理

数据库优化（内存，数据库设计，参数）

• 内存
• 数据库结构（物理&逻辑）
• 实例配置

优化工具介绍

系统层

cpu

一般做数据处理（主要功能）和调度资源（次要功能）

top命令

%cpu  当前某个程序占用的cpu的时间百分比
800%:代表8核cpu占用时间比，为百分之百。单核cpu，做多占用100%
us： 用户程序占用系统cpu时间的百分比,平均所有cpu的资源。cpu花费在计算方面的资源
sy： 内核程序占用系统的cpu时间的百分比，平均所有cpu资源，cpu花费在调度方面的资源
sy过高的原因：
内核本身有bug
并发很高（MySQL是单进程，多线程模式），需要频繁调用资源给线程
锁的原因
id： 空闲cpu时间占比
wa： cpu要等待的时间占比
cpu要处理程序。
程序由指令+数据组成，
wa 是 cpu等待数据的过程(也是等待IO的过程)。可能是MySQL等待大的事务处理，也可能是锁的问题

top  按 1，指看MySQL对cpu使用的情况，看资源分配是否均匀

MEM

缓存和缓冲（cpu和IO的中间地带）

MySQL 不建议使用swap

IO

输入和输出

iostat 工具 yum -y install sysstat iostat 结合cpu和内存去查看
glances 工具 yum -y install glances

数据库层面

状态查看命令

show status
show variables
show index
show processlist
show slave status
show engine innodb status
sc /explain
slowlog

扩展类深度优化

pt系列
pt-query-digest,pt-osc,pt-index
mysqlslap
sysbenchD
information_schema(I_S)
performace_schema(P_S)
sys

优化思路

主机 存储 网络

主机

真实的硬件(pc_server): Dell R系列，华为，浪潮，HP,曙光，联想
云产品： ECS ，数据库RDS，DRDS,PolorDB
IBM小型机： P6 570，595， P7 720 750 780 P8

cpu

OLTP:混合型，增删改查都涉及，IO密集型
OLAP: 数据仓库，专门做查 CPU密集型
I
O密集型： 线上系统，OLTP主要是IO密集型的业务，高并发（E系列，至强，主频相对低，核心数量多）
CPU密集型： 数据分析，数据处理，OLAP，需要CPU高计算能力（i系列 ，IBM power系列）

内存

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

磁盘

SATA-III 、SAS 、FC 、SSD(SATA) 、PCI-e ssd 、Flash

存储

根据存储类型不同，选择不同不同存储设备
配置合理的RAID级别（RAID5，RAID10,热备盘）

网络

• 硬件买好的（单卡单口）
• 网卡绑定（多网卡多口）
  网卡绑定 1.主备（一个网卡坏了，另一个网卡顶替） 2.轮询（负载均衡，两个网卡同时工作）

系统层面

swap调整

echo 0 > /proc/sys/vm/swappiness
默认30，内存用到了70%，就使用swap
永久修改：

vim /etc/sysctl.conf
vm.swapiness=0
sysctl -p

IO 调整策略

centos7: 默认deadline
centos6: 修改为deadline

 cat /sys/block/sda/queue/scheduler 
noop [deadline] cfq 

deadline 是最后期限，有效降低cpu wait的指标。如果IO等待不来，就进行下一个IO

实例优化（参数）

Max_connections [重要]

最大连接数（并发），对于单机来讲最多3000个，瓶颈在IO上

• 可以先评估
• 可以先调测试值
  评估依据

mysql> show status like 'Max_used_connections';  # 最大使用的连接数
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 13    |
+----------------------+-------+
1 row in set (0.00 sec)

1 .开始数据库时，我们可以设置一个比较大的测试值
2 .观察 show status like 'Max_used_connections;' 变化
3 .如果max_used_connections 跟 Max_connections 相同，那边Max_connections设置过低，或者超过服务负载上限了

back_log

当Max_connections到达最大连接时，用户再连接进来就会放入back_log中，相当于等待连接。如果等待连接的数量超过back_log中，将不会授予该连接资源。如果期望短时间内有很多连接，可以增加它

判断依据
show full processlist
当发现大量的待连接进程时，就需要加大back_log或者Max_connections的值
修改方式

vim /etc/my.cnf
back_log=1024

wait_timeout 和 interactive_timeout

wait_timeout:只连接数据库的，非交互试连接等待的时间（只连接的数据库连接什么都不做）一般把此参数调小
interactive_timeout: 只连接数据库，交互式连接等待的时间（只连接数据库的连接做增删改查操作）一般不调此参数
wait_timeout=60
interactive_timeout=1200
对于interactive_timeout调整，我们应询问开发人员长连接最多持续多长时间

key_buffer_size [重要]

• MyISAM 表的索引缓冲区
• 临时表的缓冲区
  临时表：当两个表做join联合查询时，会生成一个临时表，临时表可以在磁盘中，也可以在缓冲区中

mysql> show status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.01 sec)

crated_tmp_tables  值在内存中建立的临时表
created_tmp_disk_tables 指在磁盘中创建的临时表
created_tmp_tables/(created_tmp_disk_tables+created_tmp_tables) 越高越好
created_tmp_disk_tables/(created_tmp_disk_tables+created_tmp_tables)  一般控制在5%-10%

max_connect_errors

MySQL的错误连接次数，是个计数器。一般设置很大

sort_buffer_size 、join_buffer_size 、 read_buffer_size 、 read_rnd_buffer_size

以上都是私有内存，即每个连接都占用内存。一般对于以上添加内存的做法，是调整索引优化。以上的参数一般设置小于2M

max_allowed_packet [重要]

控制server接受数据包的大小

有时候大的插入和更新会受max_allowed_packet参数的限制，导致写入或更新失败。必须设置成1014的倍数
max_allowed_packet=256M

thread_cache_size [重要]

线程缓存的个数，即在内存中的不会被释放的线程，如果客户端请求频繁，减少cpu的调度时间，拿内存资源换cpu资源1G=4 2G=8 3G=12 4G或更多=32

innodb_buffer_pool_size [非常重要]

innodb引擎的缓存区域，一般设置成物理内存70%，最大不超过80%

双一标准

innodb_flush_log_at_trx_commit=1
sync_binlog=1

innodb_thread_concorency

控制innodb线程的并发数量，使用cpu数量，官方最大支持64个cpu
设置标准
show processlit; 如果cpu并发处理能力差，可能此处会达到Max_connections的值
top 按 1
看cpu的繁忙程度，如果平均，则表示cpu已是使用最大限度。如果不平均，可调整此参数（从小到大）然后top 在查看cpu的使用情况，是否已经平均

innodb_log_buffer_size [重要]

控制redo日志缓冲的大小，如果内存太小，会造成脏页刷写频繁

innodb_log_file_size [重要]

控制redo日志的大小，如果太小，会造成redo日志轮询速度过快

innodb_log_files_in_group [重要]

控制redo日志轮询的组数，默认是3个

innodb_flush_method [重要]

redo日志的刷写策略