show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
slowlog
--扩展类深度优化:
---pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys
create database hhh charset utf8mb4 collate utf8mb4_bin;
use hhh;
2.创建表
create table t1(id int(11),num int(11),k1 char(2),k2 char(4),dt timestamp not null);
3.插入100万条数据
delimiter //
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
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 test values(i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter;
mysql> call rand_data(1000000);
4.进行压力测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='opt' \
--query="select * from opt.test where num='505037'" engine=innodb \
--number-of-queries=20000 -uroot -p123456 -verbose
五、数据库参数优化
1.Max_connections 参数
--简介
Mysql的最大连接数,如果服务器的并发请求量比较大,可以调高这个值,当然这是要建立在机器能够支撑的情况下,因为如果连接数越来越多,mysql会为每个连接提供缓冲区,就会开销的越多的内存,所以需要适当的调整该值,不能随便去提高设值。
--查看方式
show variables like 'max_connections';
mysql> select @@max_connections;--查看已经使用的
mysql> show status like 'Max_used_connections';--一般配置
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 每秒支持的IO
connections 连接数
TPS 每秒最多允许的事务
QPS 每秒最多的查询量
2.back_log参数
--简介
mysql能暂存的连接数量,当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用,如果mysql的连接数据达到max_connections时候,新来的请求将会被存在堆栈中,等待某一连接释放资源,该推栈的数量及back_log,如果等待连接的数量超过back_log,将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中,只有如果期望在一个短时间内有很多连接的时候需要增加它
--查看方式
mysql> show variables like '%back_log%';
mysql> select @@back_log;
--查看是否有等待的,发现大量的待连接进程时,就需要加大back_log或者加大max_connections的值
mysql> show full processlist
--配置方式
vim /etc/my.cnf
back_log=1024
--简介
key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度
此参数与myisam表的索引有关
临时表的创建有关(多表链接、子查询中、union)
在有以上查询语句出现的时候,需要创建临时表,用完之后会被丢弃
临时表有两种创建方式:
1)内存中------->key_buffer_size
2)磁盘上------->ibdata1(5.6)
ibtmp1 (5.7)
--查看方式
通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。
mysql> show variables like "key_buffer_size%";
--查看有多少索引,是走磁盘的
mysql> show status like "key_read%";
一共有10个索引读取请求,有2个请求在内存中没有找到直接从硬盘中读取索引
控制在 5%以内 。
--注意:key_buffer_size只对myisam表起作用,即使不使用myisam表,但是内部的临时磁盘表是myisam表,也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知:
--查看临时表创建
mysql> show status like "created_tmp%";
以各自的一个时段内的差额计算,来判断基于内存的临时表利用率。所以,我们会比较关注 Created_tmp_disk_tables 是否过多,从而认定当前服务器运行状况的优劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
控制在5%-10%以内
--配置方式
key_buffer_size=64M
5.query_cache_size参数
--简介:
查询缓存简称QC,使用查询缓冲,mysql将查询结果存放在缓冲区中,今后对于同样的select语句(区分大小写),将直接从缓冲区中读取结果。
SQL层:
select * from t1 where name=:NAME;select * from t1 where name=:NAME;
1)查询完结果之后,会对SQL语句进行hash运算,得出hash值,我们把他称之为SQL_ID
2)会将存储引擎返回的结果+SQL_ID存储到缓存中。
--存储方式:
例子:select * from t1 where id=10; 100次
1)将select * from t1 where id=10; 进行hash运算计算出一串hash值,我们把它称之为“SQL_ID"
2)将存储引擎返回上来的表的内容+SQLID存储到查询缓存中
--使用方式:
1)一条SQL执行时,进行hash运算,得出SQLID,去找query cache
2)如果cache中有,则直接返回数据行,如果没有,就走原有的SQL执行流程
一个sql查询如果以select开头,那么mysql服务器将尝试对其使用查询缓存。
--注意:两个sql语句,只要想差哪怕是一个字符(列如大小写不一样;多一个空格等),那么这两个sql将使用不同的一个cache。
--查看方式
mysql> show variables like "%query_cache_size%";
#查看是否开启
mysql> show variables like "query_cache%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 | #超过此大小的查询将不缓存
| query_cache_min_res_unit | 4096 | #缓存块的最小大小,太小的话会生成很多内存碎片
| query_cache_size | 1048576 | #查询缓存大小
| query_cache_type | OFF | #缓存类型,是否开启
| query_cache_wlock_invalidate | OFF | #查询的表被锁,也可以走缓存查询数据
+------------------------------+---------+
--查看配置多大根据谁呢
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |#缓存中相邻内存块的个数,内存碎片| Qcache_free_memory | 1031360 |#Query Cache 中目前剩余的内存大小| Qcache_hits | 0 |#表示有多少次命中缓存,数字越大,缓存效果越理想| Qcache_inserts | 0 |#没有命中,新插入的数据| Qcache_lowmem_prunes | 0 |#多少条Query因为内存不足而被清除出QueryCache| Qcache_not_cached | 2002 |#不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数| Qcache_queries_in_cache | 0 |#当前Query Cache 中cache 的Query 数量| Qcache_total_blocks | 1 |#当前Query Cache 中的block 数量
+-------------------------+---------+
# 求命中率:
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
如果出现hits比例过低,其实就可以关闭查询缓存了。使用redis专门缓存数据
# 判断内存够不够
Qcache_free_memory + Qcache_lowmem_prunes
--配置方式
query_cache_size=128M
query_cache_type=1
6.max_connect_errors 参数
max_connect_errors是一个mysql中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况,当超过指定次数,mysql服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
--查看方式
mysql> show variables like "%connect_error%";
--修改/etc/my.cnf配置文件
vim /etc.my.cnf
[mysqld]
max_connect_errors=2000
7.sort_buffer_size参数
--简介:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速
ORDER BY
GROUP BY
distinct
union--配置依据
Sort_Buffer_Size并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。
列如:500个连接将会消耗500*sort_buffer_size(2M)=1G内存
--查看方式
mysql> show variables like "%sort_buffer_size%";--配置方法
修改/etc/my.cnf配置文件
vim /etc/my.cnf
[mysqld]
sort_buffer_size=1M
8.max_allowed_packet 参数
--简介:
mysql根据配置文件会限制,server接受的数据包大小。
--配置依据:
有时候大的插入和更新会受max_allowed_packet参数限制,导致写入或者更新失败,更大值是1GB,必须设置1024的倍数
--查看方式
mysql> show variables like '%max_allowed_packet%';
--配置方法:
max_allowed_packet=32M
9.join_buffer_size参数
--简介
select a.name,b.name from a join b on a.id=b.id where xxxx
用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。
尽量在SQL与方面进行优化,效果较为明显。
优化的方法:在on条件列加索引,至少应当是有MUL索引
--查看方式
mysql> show variables like "%join_buffer_size%";--配置
join_buffer_size=2M
10.thread_cache_size 参数
--简介
服务器线程缓存,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.--配置依据
通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。
设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)--查看方式
mysql> show variables like "%thread_cache_size%";--查看实际情况,试图连接到MySQL(不管是否连接成功)的连接数
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。
如果一直陡峭的增长,或者出现大量峰值,那么继续增加此值的大小,在系统资源够用的情况下(内存)
11.innodb_buffer_pool_size 参数
--简介
对于InnoDB表来说,innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
--配置依据:
InnoDB使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。
--查看
mysql> show variables like "%innodb_buffer_pool_size%";
# 注意:这里默认是128M
--配置方法
innodb_buffer_pool_size=2048M
--简介
此参数用来设置innodb线程的并发数量,默认值为0表示不限制。
--配置依据
在官方doc上,对于innodb_thread_concurrency的使用,也给出了一些建议,如下:
如果一个工作负载中,并发用户线程的数量小于64,建议设置innodb_thread_concurrency=0;
如果工作负载一直较为严重甚至偶尔达到顶峰,建议先设置innodb_thread_concurrency=128,
并通过不断的降低这个参数,96, 80, 64等等,直到发现能够提供最佳性能的线程数,
例如,假设系统通常有40到50个用户,但定期的数量增加至60,70,甚至200。你会发现,
性能在80个并发用户设置时表现稳定,如果高于这个数,性能反而下降。在这种情况下,
建议设置innodb_thread_concurrency参数为80,以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多(比如20个虚拟CPU),
建议通过设置innodb_thread_concurrency 参数为这个值(也可能更低,这取决于性能体现),
如果你的目标是将MySQL与其他应用隔离,你可以l考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是,这种绑定,在myslqd进程一直不是很忙的情况下,可能会导致非最优的硬件使用率。在这种情况下,
你可能会设置mysqld进程绑定的虚拟 CPU,允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下,最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统,负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
128 -----> top cpu
--设置标准:
1)当前系统cpu使用情况,均不均匀
top
2)当前的连接数,有没有达到顶峰
show status like 'threads_%';
show processlist;
--查看
mysql> show variables like "%innodb_thread_concurrency%";
--配置方法:
innodb_thread_concurrency=8
方法:
1. 看top ,观察每个cpu的各自的负载情况
2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值
3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.
14.innodb_log_buffer_size参数
此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,对于较大的事务,可以增大缓存大小。
innodb_log_buffer_size=128M
--设定依据:
1、大事务: 存储过程调用 CALL
2、多事务
--查看
mysql> show variables like "%innodb_log_buffer_size%";--配置
innodb_log_buffer_size=128M
15.innodb_log_file_size = 100M 参数
设置 ib_logfile0 ib_logfile1
此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能.
--查看
mysql> show variables like '%innodb_log_file_size%';
--配置
innodb_log_file_size = 100M
16.innodb_log_files_in_group = 3 参数
--简介
为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
--查看
mysql> show variables like '%innodb_log_files_in_group%';
--配置
innodb_log_files_in_group=3
17.read_buffer_size = 1M 参数
--简介
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样,该参数对应的分配内存也是每个连接独享
--查看
mysql> show variables like '%read_buffer_size%';
--配置
read_buffer_size=1M
18.read_rnd_buffer_size = 1M 参数
--简介
MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。
注:顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据,而辅助索引和主键所在的数据段不同,因此访问方式是随机的。
--查看
mysql> show variables like '%read_rnd_buffer_size%';
--配置
read_rnd_buffer_size=1M
19.bulk_insert_buffer_size = 8M参数
批量插入数据缓存大小,可以有效提高插入效率,默认为8M
tokuDB percona
myrocks
RocksDB
TiDB
MongoDB
--查看
mysql> show variables like '%bulk_insert_buffer_size%';
--配置
bulk_insert_buffer_size=8M
SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41
5.找到锁源的SQL语句
-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;
得出结果,丢给开发
表信息
被阻塞的
锁源SQL
6.优化项目:锁的监控及处理
1. 背景:
硬件环境:DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)2. 项目的职责
1)通过top详细排查,发现mysqld进程占比达到了700-800%2)其中有量的CPU是被用作的SYS和WAIT,us处于正常
3)怀疑是MySQL 锁 或者SQL语句出了问题
4)经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句
pt-query-diagest 查看慢日志
锁等待有没有?
db01 [(none)]>show status like 'innodb_row_lock%';--情况一:
有100多个current_waits,说明当前很多锁等待情况
--情况二:1000多个lock_waits,说明历史上发生过的锁等待很多
5)查看那个事务在等待(被阻塞了)6)查看锁源事务信息(谁锁的我)7)找到锁源的thread_id
8)找到锁源的SQL语句
3. 找到语句之后,和应用开发人员进行协商
1)
开发人员描述,此语句是事务挂起导致
我们提出建议是临时kill 会话,最终解决问题
2)
开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
临时解决方案,将阻塞事务的会话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;
7.死锁监控
show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1