mysql锁优化_mysql锁以及配置优化

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 : 当前被阻塞的操作(一般是要丢给开发的)

查看锁源,谁锁的我!

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 : 锁源的线程号

找到锁源的thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=15;

====> 41

找到锁源的SQL语句

-- 当前在执行的语句

SELECT * FROM performance_schema.events_statements_current WHERE thread_id=41;

-- 执行语句的历史

SELECT * FROM performance_schema.events_statements_history WHERE thread_id=41;

得出结果,丢给开发

表信息

被阻塞的

锁源SQL

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

背景:

硬件环境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10

在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

项目的职责

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语句

找到语句之后,和应用开发人员进行协商

(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;

死锁监控

show engine innodb status\G

show variables like '%deadlock%';

vim /etc/my.cnf

innodb_print_all_deadlocks = 1

主从优化(GTID>>多线程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;

常规参数优化

[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

relay_log_purge=0

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

[client]

socket=/tmp/mysql.sock

最后再次压力测试 ：

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose