19.MySQL优化

MySQL优化

QQ截图20200426145440.jpg

优化有风险

存储,主机和操作系统优化

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

应用(Index,lock,session)

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

数据库优化:(内存、数据库设计、参数)

内存
数据库结构(物理&逻辑)
实例配置

优化工具介绍

系统层

CPU : 计算(主)和调度(次)
MEM : 缓存和缓冲
IO : 输入和输出

`top`命令

#主要查看
top - 15:15:41 up 64 days, 20:52,  3 users,  load average: 16.43, 20.23, 9.06
Tasks: 231 total,   1 running, 230 sleeping,   0 stopped,   0 zombie
%Cpu(s):  3.2 us,  2.8 sy,  0.0 ni, 93.3 id,  0.7 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  3997420 total,   110588 free,  3024180 used,   862652 buff/cache
KiB Swap:  2097148 total,  1541884 free,   555264 used.   405120 avail Mem

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

sy过高的原因

内核本身bug
数据库并发高
数据库表锁

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

其他查看工具

yum install -y sysstat
iostat -dk 1

yum install -y iotop
iotop

glances
yum install -y glances

htop
yum install -y htop

vmstat

数据库层工具

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

优化思路

准备压测使用数据

create database test  charset utf8mb4 collate utf8mb4_bin;
use test;
create table t100w (
id int,
num int,
k1 char(2),
k2 char(4),
dt timestamp
)charset utf8mb4 collate utf8mb4_bin;

delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;

call rand_data(1000000);
commit;

准备压测使用命令

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='wxrs'" engine=innodb --number-of-queries=200 -uroot -p123456 -verbose

主机,存储,网络

主机

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

CPU

根据数据库类型,OLTP(联机事务处理)和OLAP(联机分析处理)
IO密集型:intel E系列(至强),主频相对低,核心多,线上系统,OLTP主要是IO密集型的业务,高并发
CPU密集型:intel I系列(酷睿),主频很高,核心少,数据分析数据处理,OLAP,cpu密集型的,需要CPU高计算能力(i系列,IBM power系列)

内存

建议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要求)

网络

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

系统

Swap调整,一般情况下会关闭swap

#临时设置关闭swap
echo 0 >/proc/sys/vm/swappiness

#永久设置关闭swap
vim /etc/sysctl.conf

#添加以下内容
vm.swappiness=0

#使用命令立即生效
sysctl -p

设置IO调度策略,可以有效降低io等待时间

#centos7默认是deadline
cat /sys/block/sda/queue/scheduler

#centos6临时修改为deadline
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
不要使用lvm
文件系统可以使用xfs或ext4
硬盘可以使用ssd,也使用ssd来存放relaylog和binlog,可以有效降低主从延迟
提前规划好以上所有问题,减轻MySQL优化的难度

数据库实例参数优化

Max_connections*****

Mysql的最大连接数,如果服务器的并发请求量比较大,可以调高这个值,当然这是要建立在机器能够支撑的情况下,因为如果连接数越来越多,mysql会为每个连接提供缓冲区,就会开销的越多的内存,所以需要适当的调整该值,不能随便去提高设值,因为io的限制有些可以调到3000,实际生产中需要根据实际情况和以下命令作为参考来进行调整

#判断依据
#当前支持最大连接数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

#数据库运行以来最大连接数
mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 103   |
+----------------------+-------+
1 row in set (0.00 sec)

如果连接数量超过max_connections的值,会出现如下错误

[root@home ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=153 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='wxrs'" engine=innodb --number-of-queries=200 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections

修改方式

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

vim /etc/my.cnf

max_connections=1024

back_log ***

mysql能暂存的连接数量,当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用,如果mysql的连接数据达到max_connections时候,新来的请求将会被存在堆栈中,等待某一连接释放资源,该推栈的数量及back_log,如果等待连接的数量超过back_log,将不被授予连接资源

back_log值只出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中,只有如果期望在一个短时间内有很多连接的时候需要增加它

#判断依据
#该命令可以查看当前正在发生的并发连接,发现大量的待连接进程时,就需要加大back_log或者加大max_connections的值
mysql> show full processlist;
+-------+------+-----------+------+---------+------+----------+-----------------------+
| Id    | User | Host      | db   | Command | Time | State    | Info                  |
+-------+------+-----------+------+---------+------+----------+-----------------------+
| 10100 | root | localhost | NULL | Query   |    0 | starting | show full processlist |
+-------+------+-----------+------+---------+------+----------+-----------------------+
1 row in set (0.00 sec)

修改方式

开启数据库时,我们可以临时设置一个比较大的测试值
观察show status like 'Max_used_connections';变化
如果max_used_connections跟max_connections相同,那么就是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如果设置太小,那么连接关闭的就很快,从而使一些持久的连接不起作用

#查看当前设置的参数
mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)

mysql> show variables like '%interactive_timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

修改方式

如果设置太大,容易造成连接打开时间过长,在show full processlist;时候,能看到很多的连接 ,一般希望wait_timeout尽可能低
长连接的应用,为了不去反复的回收和分配资源,降低额外的开销.一般我们会将wait_timeout设定比较小,interactive_timeout要和应用开发人员沟通长链接的应用是否很多.如果他需要长链接,那么这个值可以不需要调整

vim /etc/my.cnf

wait_timeout=60
interactive_timeout=1200

key_buffer_size *****

myisam表的索引缓冲区

临时表的缓冲区

#查看当前临时表情况
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 8     |
| Created_tmp_tables      | 10    |
+-------------------------+-------+
3 rows in set (0.00 sec)

#查看key_buffer_size大小
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.01 sec)

Created_tmp_disk_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%,该参数最大不会超过512M
mysqldump备份数据的情况下,会大量使用临时表,如果正在进行备份操作,临时表生成在磁盘的比例会提高

vim /etc/my.cnf

key_buffer_size=8M

max_connect_errors ***

max_connect_errors是一个mysql中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况,当超过指定次数,mysql服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系

修改方式

vim /etc/my.cnf

max_connect_errors=2000

sort_buffer_size ***

每个需要进行排序的线程分配该大小的一个缓冲区.增加这些关键字查询的速度ORDER BY,GROUP BY,distinct,union

与改参数类似的参数还有join_buffer_size,read_buffer_size,read_rnd_buffer_size,如果索引优化理想,则该参数提升性能不是很明显,并且会增加内存消耗

Sort_Buffer_Size并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源

列如:500个连接将会消耗500*sort_buffer_size(2M)=1G内存

修改方式

vim /etc/my.cnf

sort_buffer_size=1M

max_allowed_packet*****

mysql根据配置文件会限制,server接受的数据包大小

有时候大的插入和更新会受max_allowed_packet参数限制,导致写入或者更新失败,更大值是1GB,必须设置1024的倍数

修改方式

vim /etc/my.cnf

max_allowed_packet=256M

thread_cache_size *****

服务器线程缓存,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能

通过比较Connections和Threads_created状态的变量,可以看到这个变量的作用
设置规则如下:1GB内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大.
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

#查看相关参数信息
mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 9     |
| Threads_connected | 1     |
| Threads_created   | 942   |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程
Threads_connected : 代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数
Threads_created : 代表从最近一次服务启动,已创建线程的数量,如果发现Threads_created值过大的话,表明MySQL服务器一直在创建线程,这也是比较耗cpu SYS资源,可以适当增加配置文件中thread_cache_size值
Threads_running : 代表当前激活的(非睡眠状态)线程数.并不是代表正在使用的线程数,有时候连接已建立,但是连接处于sleep状态

修改方式

一般在架构设计阶段,Threads_created会设置一个测试值,做压力测试
结合zabbix监控,看一段时间内此状态的变化
如果在一段时间内,Threads_created趋于平稳,说明对应参数设定是OK
如果一直陡峭的增长,或者出现大量峰值,那么继续增加此值的大小,在系统资源够用的情况下(内存)

vim /etc/my.cnf

thread_cache_size=32

innodb_buffer_pool_size ******

对于InnoDB表来说,innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样

修改方式

InnoDB使用该参数指定大小的内存来缓冲数据和索引,对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%
show engine innodb status\G可以通过该命令的Buffer pool size,Free buffers,Database pages这几个值的情况判断,如果Free buffers太小,需要适当调整innodb_buffer_pool_size

vim /etc/my.cnf

innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit ******

双1标准其中之一,主要控制了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

vim /etc/my.cnf

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size

此参数确定些日志文件所用的内存大小,以M为单位.缓冲区更大能提高性能,对于较大的事务,可以增大缓存大小

修改方式

大事务,存储过程调用 CALL
多事务

vim /etc/my.cnf

innodb_log_buffer_size=128M

innodb_log_file_size *****

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

修改方式

vim /etc/my.cnf

innodb_log_file_size = 100M

innodb_log_files_in_group *****

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

vim /etc/my.cnf

innodb_log_files_in_group = 3

bulk_insert_buffer_size**

批量插入数据缓存大小,可以有效提高插入效率,默认为8M

修改方式

vim /etc/my.cnf

max_binlog_cache_size=8M

binary log相关参数*****

参考以下链接

配置参数

vim /etc/my.cnf

log-bin=/data/mysql-bin
#为每个session分配的内存,在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率.没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点.前者建议是--1M,后者建议是:即 2--4M
binlog_cache_size = 2M
#表示的是binlog能够使用的最大cache内存大小
max_binlog_cache_size = 8M
#指定binlog日志文件的大小,如果当前的日志大小达到max_binlog_size,还会自动创建新的二进制日志.你不能将该变量设置为大于1GB或小于4096字节.默认值是1GB.在导入大容量的sql文件时,建议关闭sql_log_bin,否则硬盘扛不住,而且建议定期做删除.
max_binlog_size= 512M
#定义了mysql清除过期日志的时间
expire_logs_days = 7
#DML记录格式(statement,row,mixed),通过binlog_format参数控制,5.7版本默认配置是row,可以省略
binlog_format=row
#sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log
#当sync_binlog =N (N>0),MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去
#双1参数其中之一
sync_binlog=1

安全参数*****

参考以下链接

10.存储引擎

Innodb_flush_method=(O_DIRECT, fsync)

fsync

在数据页需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘
在redo buffuer需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘
如果innodb_flush_log_at_trx_commit=1的话,日志还是直接每次commit直接写入磁盘

O_DIRECT

在数据页需要持久化时,直接写入磁盘
在redo buffuer需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘
如果innodb_flush_log_at_trx_commit=1的话,日志还是直接每次commit直接写入磁盘

配置参数

vim /etc/my.cnf

#最安全模式
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

#最高性能模式
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

#一般情况下,我们更偏向于安全
#双1标准**********
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_flush_method=O_DIRECT

参数优化结果

vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306

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

[mysql]
socket=/tmp/mysql.sock

重启后再次进行压力测试

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='wxrs'" engine=innodb --number-of-queries=200 -uroot -p123456 -verbose

优化前结果

[root@home ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='wxrs'" engine=innodb --number-of-queries=200 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 100.288 seconds
        Minimum number of seconds to run all queries: 100.288 seconds
        Maximum number of seconds to run all queries: 100.288 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2

优化后结果

[root@home ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='wxrs'" engine=innodb --number-of-queries=200 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 54.874 seconds
        Minimum number of seconds to run all queries: 54.874 seconds
        Maximum number of seconds to run all queries: 54.874 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2

针对查询的字段添加索引后结果

[root@home ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='wxrs'" engine=innodb --number-of-queries=200 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine rbose
        Average number of seconds to run all queries: 0.088 seconds
        Minimum number of seconds to run all queries: 0.088 seconds
        Maximum number of seconds to run all queries: 0.088 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2

手机上阅读

最后一次更新于2020-07-13 14:07

神出鬼没的Enderman

long long ago,I was born.

MySQL优化

优化工具介绍

`top`命令

其他查看工具

数据库层工具

优化思路

主机,存储,网络

系统

数据库实例参数优化

Max_connections*****

back_log ***

wait_timeout和interactive_timeout ****

key_buffer_size *****

max_connect_errors ***

sort_buffer_size ***

max_allowed_packet*****

thread_cache_size *****

innodb_buffer_pool_size ******

innodb_flush_log_at_trx_commit ******

innodb_log_buffer_size

innodb_log_file_size *****

innodb_log_files_in_group *****

bulk_insert_buffer_size**

binary log相关参数*****

安全参数*****

参数优化结果

18.MySQL分布式解决方案

20.MYSQL锁的监控及处理

19.MySQL优化

MySQL优化

优化工具介绍

top命令

其他查看工具

数据库层工具

优化思路

主机,存储,网络

系统

数据库实例参数优化

Max_connections*****

back_log ***

wait_timeout和interactive_timeout ****

key_buffer_size *****

max_connect_errors ***

sort_buffer_size ***

max_allowed_packet*****

thread_cache_size *****

innodb_buffer_pool_size ******

innodb_flush_log_at_trx_commit ******

innodb_log_buffer_size

innodb_log_file_size *****

innodb_log_files_in_group *****

bulk_insert_buffer_size**

binary log相关参数*****

安全参数*****

参数优化结果

18.MySQL分布式解决方案

20.MYSQL锁的监控及处理

`top`命令