1.优化哲学
1.1 为什么优化?
为了获得成就感?
为了证实比系统设计者更懂数据库?
为了从优化成果来证实优化者更有价值?
但通常事实证实的结果往往会和您期待相反!
优化有风险,涉足需谨慎!
1.2 优化风险
优化不总是对一个单纯的环境进行!还很可能是一个复杂的已投产的系统。
优化手段本来就有很大的风险,只不过你没能力意识到和预见到!
任何的技术可以解决一个问题,但必然存在带来一个问题的风险!
对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败!
稳定性和业务可持续性通常比性能更重要!
优化不可避免涉及到变更,变更就有风险!
优化使性能变好,维持和变差是等概率事件!
优化不能只是数据库管理员担当风险,但会所有的人分享优化成果!
所以优化工作是由业务需要驱使的!!!
1.3 谁参与优化
数据库管理员
业务部门代表
应用程序架构师
应用程序设计人员
应用程序开发人员
硬件及系统管理员
存储管理员
1.4 优化方向
安全优化(业务持续性)
性能优化(业务高效性)
1.5 优化的范围及思路
优化范围:
存储、主机和操作系统:
主机架构稳定性
I/O规划及配置
Swap
OS内核参数
网络问题
应用程序:(Index,lock,session)
应用程序稳定性和性能
SQL语句性能
串行访问资源
性能欠佳会话管理
数据库优化:(内存、数据库设计、参数)
内存
数据库结构(物理&逻辑)
实例配置
优化效果和成本的评估:
1. 优化工具的使用
1.1 系统层面的
1.1.1 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来讲,有可能是并发参数设定不合理导致的。
1.1.2 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 高 IO很低 MySQL 不在做增删改查,有可能是存储过程,函数,排序,分组,多表连接
3. Wait 高 , IO低:IO出问题了,锁等待的几率比较大.
4. IOPS:每秒磁盘最多能够发生的IO次数,这是个定值 频繁小事务,IOPS很高,达到阈值,可能IO吞吐量没超过IO最大吞吐量.无法新的IO了
存储规划有问题.
1.2 数据库基础优化工具
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
扩展类深度优化:
pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys
==========================================
3. 优化思路分解
3.1 硬件优化:
主机(真实的硬件+云产品ECS,数据库(RDS、DRDS))
3.1.1根据数据库类型:
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),交换机堆叠
以上问题,提前规避掉。
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. 减少烂SQL:不走索引,复杂逻辑,切割大事务
2. 避免业务逻辑错误,避免锁征用
这个阶段,需要我们DBA深入业务,或者要和开发人员\业务人员配合实现
优化,最根本的是"优化"人.
----oldguo
=====================================================
4. MySQL参数优化测试建议
4.1 参数优化前压力测试
4.4.0 优化测试前提
虚拟机vm12.5,OS centos 6.9(系统已优化),cpu*4(I5 4440 3.1GHZ),MEM*4GB ,HardDisk:SSD
1、模拟数据库数据
为了测试我们创建一个test1的库创建一个tb1的表,然后导入20万行数据,脚本如下:
vim slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="oldboy123"
DBNAME="oldboy"
TABLENAME="lufei"
#create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 200000 ]
do
insert_sql="insert into ${TABLENAME} values($i,'guojialei_$i','1','110011198809163418','1988-09-16','2017-09-13','oldboyedu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
执行脚本:
sh slap.sh
2、检查数据可用性
mysql -uroot -p123
select count(*) from oldboy.lufei;
3、在没有优化之前我们使用mysqlslap来进行压力测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t_100w where num='505037'" engine=innodb \
--number-of-queries=20000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 31.463 seconds
Minimum number of seconds to run all queries: 31.463 seconds
Maximum number of seconds to run all queries: 31.463 seconds
Number of clients running queries: 100
Average number of queries per client: 20
--------------------------------mysqlslap使用说明----------------------------
mysqlslap工具介绍
mysqlslap来自于mariadb包,测试的过程默认生成一个mysqlslap的schema,生成测试表t1,查询和插入测试数据,mysqlslap库自动生成,如果已经存在则先删除。用--only-print来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹。
常用选项:
--auto-generate-sql, -a 自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read,key,write,update和mixed(默认)
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列,默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列,默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试,例如可以调用自定义的存储过程或者sql语句来执行测试
--create-schema 代表自定义的测试库名称,测试的schema,MySQL中schema也就是database
--commint=N 多少条DML后提交一次
--compress, -C 如服务器和客户端都支持压缩,则压缩信息
--concurrency=N, -c N 表示并发量,即模拟多少个客户端同时执行select;可指定多个值,以逗号或者--delimiter参数指定值做为分隔符
--engine=engine_name, -e engine_name 代表要测试的引擎,可以有多个,用分隔符隔开
--iterations=N, -i N 测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次
--only-print 只打印测试语句而不实际执行
--detach=N 执行N条语句后断开重连
--debug-info, -T 打印内存和CPU的相关信息
测试示例:
1)单线程测试
[root@centos7 ~]# mysqlslap -a -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.004 seconds
Minimum number of seconds to run all queries: 0.004 seconds
Maximum number of seconds to run all queries: 0.004 seconds
Number of clients running queries: 1
Average number of queries per client: 0
2)多线程测试,使用--concurrency来模拟并发连接
[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter password:
Benchmark
Average number of seconds to run all queries: 3.384 seconds
Minimum number of seconds to run all queries: 3.384 seconds
Maximum number of seconds to run all queries: 3.384 seconds
Number of clients running queries: 500
Average number of queries per client: 0
3)同时测试不同的存储引擎的性能进行对比
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.187 seconds
Maximum number of seconds to run all queries: 0.202 seconds
Number of clients running queries: 500
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.355 seconds
Minimum number of seconds to run all queries: 0.350 seconds
Maximum number of seconds to run all queries: 0.364 seconds
Number of clients running queries: 500
Average number of queries per client: 2
User time 0.33, System time 0.58
Maximum resident set size 22892, Integral resident set size 0
Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31896, Involuntary context switches 0
4)执行一次测试,分别500和1000个并发,执行5000次总查询
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.378 seconds
Minimum number of seconds to run all queries: 3.378 seconds
Maximum number of seconds to run all queries: 3.378 seconds
Number of clients running queries: 500
Average number of queries per client: 10
Benchmark
Average number of seconds to run all queries: 3.101 seconds
Minimum number of seconds to run all queries: 3.101 seconds
Maximum number of seconds to run all queries: 3.101 seconds
Number of clients running queries: 1000
Average number of queries per client: 5
User time 0.84, System time 0.64
Maximum resident set size 83068, Integral resident set size 0
Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31524, Involuntary context switches 3
5)迭代测试
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.307 seconds
Minimum number of seconds to run all queries: 3.184 seconds
Maximum number of seconds to run all queries: 3.421 seconds
Number of clients running queries: 500
Average number of queries per client: 10
User time 2.18, System time 1.58
Maximum resident set size 74872, Integral resident set size 0
Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 73904, Involuntary context switches 3
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -poldboy123 -verbose
=========================================================================
二、优化细节:
1、参数优化
1.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设置过低或者超过服务器的负载上限了,
低于10%则设置过大.
IOPS
connections
TPS
QPS
1.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
1.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要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接,那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。
1.4 key_buffer_size *****
(1)简介
key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度
《1》此参数与myisam表的索引有关
《2》临时表的创建有关(多表链接、子查询中、union)
在有以上查询语句出现的时候,需要创建临时表,用完之后会被丢弃
临时表有两种创建方式:
内存中------->key_buffer_size
磁盘上------->ibdata1(5.6)
ibtmp1 (5.7)
(2)设置依据
通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)
mysql>
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 10 |
| Key_reads | 2 |
+-------------------+-------+
2 rows in set (0.00 sec)
mysql>
一共有10个索引读取请求,有2个请求在内存中没有找到直接从硬盘中读取索引
控制在 5%以内 。
注:key_buffer_size只对myisam表起作用,即使不使用myisam表,但是内部的临时磁盘表是myisam表,也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知:
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.00 sec)
mysql>
通常地,我们习惯以 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_disk_tables + Created_tmp_tables)
控制在5%-10%以内