大事务造成线上insert插入缓慢
问题描述
最近有用户反馈,线上很多订单下单特别慢,每天集中在早上9-10点钟,我跟踪生产tomcat日志,发现一段insert sql执行时间大概要60s的样子。怀疑应该是生产有大事务执行导致表被锁了。
解决方法
使用监控脚本轮询查找LOCK WAIT状态的sql,并记录这个时候processlist中运行过久的sql,来定位到底是哪条sql造成的。
#!/bin/sh
while(true)
do
echo "Begin" >> lock.sql
#查询LOCK WAIT状态sql
mysql -uroot -p'密码' -P3306 -h192.168.1.100 -e"select trx_started, concat(trx_query,'\n') from information_schema.innodb_trx where trx_state='LOCK WAIT' and now()-trx_started>=10" | grep -v "trx_query" > /app/lockwait/lockwaitsql`date +%Y%m%d`.txt
if [ -s /app/lockwait/lockwaitsql`date +%Y%m%d`.txt ]; then
cat /app/lockwait/lockwaitsql`date +%Y%m%d`.txt >> lock.sql
fi
#查看LOCK WAIT状态时,线上正在执行的代码
mysql -uroot -p'密码' -P3306 -h192.168.1.100 -e"select concat(USER,';',info,'\n') from information_schema.processlist where info is not null and time>=10" > /app/lockwait/slowquery`date +%Y%m%d`.txt
if [ -s /app/lockwait/slowquery`date +%Y%m%d`.txt ]; then
cat /app/lockwait/slowquery`date +%Y%m%d`.txt >> lock.sql
fi
echo "End" >> lock.sql
sleep 10
done
通过上面这个方法,我们已经快速的找到了造成线上insert慢的sql,然后通过执行计划分析如下,发现这条sql用了trstatus=1的S锁,插入trastatus=1的数据时候,就会造成X锁等待。
解决方案:调整select的执行计划,不走trastatus字段索引