mysql 参数优化压力测试-锁优化-主从优化

最新推荐文章于 2021-03-17 22:22:19 发布

tangbin0505

最新推荐文章于 2021-03-17 22:22:19 发布

阅读量239

点赞数

分类专栏：数据库和中间件文章标签： msyql优化 mysql锁主从优化

本文链接：https://blog.csdn.net/tangbin0505/article/details/102949214

版权

数据库和中间件专栏收录该内容

22 篇文章 0 订阅

订阅专栏

1 参数优化结果

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1  //从库从主库复制的数据会写入log-bin日志文件里
relay_log_purge=0 //禁止 SQL 线程在执行完一个 relay log 后自动将其删除。
max_connections=1024 //最大连接数，如果服务器的并发请求量比较大，可以调高这个值
back_log=128 //暂存的连接数量
wait_timeout=60 //指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout=7200 //指的是mysql在关闭一个交互的连接之前所需要等待的秒数
key_buffer_size=16M //指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
query_cache_size=64M //查询缓存大小
query_cache_type=1 //缓存类型，决定缓存什么样的查询，0为禁用，1为缓存所有的结果，2为只缓存在 
                    //select语句中通过SQL_CACHE指定需要缓存的查询
query_cache_limit=50M //超过此大小的查询将不缓存
max_connect_errors=20 //与安全有关的计数器值，阻止过多尝试失败的客户端以防止暴力破解密码等情况
sort_buffer_size=2M //每个需要进行排序的线程分配该大小的一个缓冲区。
max_allowed_packet=32M //mysql根据配置文件会限制，server接受的数据包大小。
join_buffer_size=2M   //该参数对应的分配内存也是每个连接独享。
thread_cache_size=200 //服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,
innodb_buffer_pool_size=1024M  //InnoDB使用该参数指定大小的内存来缓冲数据和索引。
innodb_flush_log_at_trx_commit=1 //1为最安全，0的速度比2和1快，0和2会丢数据
innodb_log_buffer_size=32M  //日志文件所用的内存大小，对于较大的事务，可以增大缓存大小
innodb_log_file_size=128M   //此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
innodb_log_files_in_group=3 //为提高性能，MySQL可以以循环方式将日志文件写到多个文件
binlog_cache_size=2M      //为每个session 分配的内存， 提高记录bin-log的效率。
max_binlog_cache_size=8M  //表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size=512M  //指定binlog日志文件的大小，，如果当前的日志大小达到max_binlog_size，还 
                      // 会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节默
                      //认值是1GB。
expire_logs_days=7    //定义了mysql清除过期日志的时间。
read_buffer_size=2M         //MySql读入缓冲区大小
read_rnd_buffer_size=2M     //MySql的随机读（查询操作）缓冲区大小
bulk_insert_buffer_size=8M   //批量插入数据缓存大小，可以有效提高插入效率，默认为8M
[client]
socket=/tmp/mysql.sock  
        
压力测试  ：
 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='tangbin' --query="select * from tangbin.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p1 -verbose

t100w文件sql下载链接https://download.csdn.net/download/tangbin0505/11963546

1.1 安全参数

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

最安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
        
一般情况下，我们更偏向于安全。 
“双一标准”
innodb_flush_log_at_trx_commit=1        ***************
sync_binlog=1                                   ***************
innodb_flush_method=O_DIRECT

1.2 binary log

log-bin=/data/mysql-bin
binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M
max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days = 7 //定义了mysql清除过期日志的时间。
二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。
log-bin=/data/mysql-bin
binlog_format=row 
sync_binlog=1
双1标准(基于安全的控制)：
sync_binlog=1   什么时候刷新binlog到磁盘，每次事务commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';

1.3 主从优化

## 5.7 从库多线程MTS
基本要求:
5.7以上的版本(忘记小版本)
必须开启GTID 
binlog必须是row模式  

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心数作为标准

CHANGE MASTER TO
  MASTER_HOST='10.0.0.128',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1;
start slave;

2 锁的监控及处理

2.1死锁监控

show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf 
innodb_print_all_deadlocks = 1

2.2优化项目:锁的监控及处理

1. 背景: 
硬件环境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

2. 项目的职责
    2.1 通过top详细排查,发现mysqld进程占比达到了700-800%
    2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
    2.3 怀疑是MySQL 锁 或者SQL语句出了问题
    2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句    
    (1) pt-query-diagest 查看慢日志  
    (2) 锁等待有没有?
    db03 [(none)]>show status like 'innodb_row_lock%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | Innodb_row_lock_current_waits | 0     |
    | Innodb_row_lock_time          | 0     |
    | Innodb_row_lock_time_avg      | 0     |
    | Innodb_row_lock_time_max      | 0     |
    | Innodb_row_lock_waits         | 0     |
    +-------------------------------+-------+
    情况一:
            有100多个current_waits,说明当前很多锁等待情况
    情况二:
            1000多个lock_waits,说明历史上发生过的锁等待很多
    2.5 查看那个事务在等待(被阻塞了)
    2.6 查看锁源事务信息(谁锁的我)
    2.7 找到锁源的thread_id 
    2.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;

2.3锁等待模拟

tx2

USE tangbin
UPDATE t_100w SET k1='av' WHERE id=10;
## tx2:
USE oldboy 
UPDATE  t_100w SET k1='az' WHERE id=10;

2.4监控锁状态

## 1. 看有没有锁等待
SHOW  STATUS LIKE 'innodb_row_lock%';

## 2. 查看哪个事务在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)

2.5查看锁源,谁锁的我

SELECT * FROM sys.innodb_lock_waits;     ## ====>被锁的和锁定它的之间关系
locked_table : 哪张表出现的等待 
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid   : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID 
blocking_pid    : 锁源的线程号

2.6找到锁源的thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41

2.7找到锁源的SQL语句

-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;
得出结果,丢给开发
表信息 
被阻塞的
锁源SQL

tangbin0505

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 参数优化压力测试-锁优化-主从优化

1 参数优化结果[mysqld]basedir=/data/mysqldatadir=/data/mysql/datasocket=/tmp/mysql.socklog-error=/var/log/mysql.loglog_bin=/data/binlog/mysql-binbinlog_format=rowskip-name-resolveserver-id=52gt...
复制链接

扫一扫