一个参数救活被hang住的数据库!

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

作者介绍

贺春旸普惠金融MySQL专家,《MySQL管理之道》第一版、第二版作者。曾任职于中国移动飞信、机锋安卓市场,拥有丰富的数据库管理经验。目前致力于MySQL、Linux等开源技术的研究。

现象

开年头一天上班,开发说程序连接不上数据库了,程序伴随着有大量的update锁超时,试着引导他们用SQLYOG客户端连接均无问题,然后查看监控图发现有大量的锁,如下图:

 

20170208094711168.jpg

 

20170208094720928.jpg

 

排查

  • 数据库版本为:10.0.28-MariaDB-enterprise - MariaDB Enterprise Certified Binary

  • DELL R730XD 128G内存(BP 70G)/14块SAS 15000转RAID10

  • Update操作/秒30-50左右 innodb_lock_wait_timeout锁等待超时设置为10秒

     

 

在MySQL中information_schema库下有三个经典的数据字典表:INNODB_LOCK_WAITS、PROCESSLIST、INNODB_TRX,三者可以结合起来,就能够查到相对比较完整的阻塞信息和事务的情况。

 

1、通过以下SQL语句查看

SELECT 
  a.trx_id,
  trx_state,
  trx_started,
  b.id AS thread_id,
  b.info,
  b.user,
  b.host,
  b.db,
  b.command,
  b.state 
FROM
  information_schema.`INNODB_TRX` a,
  information_schema.`PROCESSLIST` b 
WHERE a.trx_mysql_thread_id = b.id 
ORDER BY a.trx_started;

 

查询结果如下:

20170208094736217.jpg

 

请注意红色标识的,trx_state事务状态是RUNNING,但command那里查不到正在执行的SQL,显示的是Sleep状态。

2、通过以下SQL语句查看

SHOW ENGINE INNODB STATUS\G

 

查询结果如下:

20170208094744549.jpg

 

请注意红色标识,事务ID和线程ID的状态为ACTIVE且运行了563秒,凭着以往处理故障的经验,这是N多条未提交事务的SQL引起的。

 

分析

当时慢查询日志里并没有记录慢SQL,线上设置的为1秒,询问开发是哪个SQL被锁了,也不清楚,说是通过框架生成的SQL语句,不好排查。

然后我们开启了general_log抓包,得到了很多简单的update,每次更新为1条记录,例如update t1 set name='aa' where id=XX,通过explain查看执行计划,where后面的字段都用到了索引,正常情况下执行这种SQL只需零点几毫秒的时间,但由于会话A对该记录更改未提交,会话B又对该记录进行更改,此时就会出现锁等待,直到超过了innodb_lock_wait_timeout参数设置的阈值。

在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量的连接数资源,造成严重的性能问题,甚至拖跨数据库。最终我们断定为开发的代码里应忘加了commit提交事务的操作,导致这一惨案的发生,可参考下面的重现操作。

前端应用JAVA Mybatis连接池一直不释放,积压过多的请求无法被处理,最终呈现给开发的现象是数据库又挂了。通俗来讲相当于在银行里办理业务,一个人办理不完,就得排队等待,越排越多,最终造成银行里人流混乱。


重现

MariaDB [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update t5 set name = 'aa' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

记住!千万别COMMIT!等待一会(抽一根烟的时间),然后重复执行上述命令,即可查看到跟我这里的截图完全一致。

 

解决方案

让开发再排查代码,已经不太现实了,时间不等人,业务不等人,不能再增加用户的投诉量了,DBA首先要保证的是数据库别跑挂了,先恢复!

下面介绍给大家一个参数innodb_kill_idle_transaction,意思为当一个事务长时间未提交,那么这个连接就不能关闭,内存就不释放,并发一大,导致DB连接数增多,就会对性能产生影响。

默认是0秒,你可以根据自己的情况设定阈值。超过这个阈值,服务端自动杀死未提交的空闲事务。

下面的截图展示了这一过程:

20170208094753837.jpg

 

从图中结果上可以看出,当未提交的事务超过空闲时间30秒时,被后台进程自动KILL掉,执行COMMIT提交后,连接被强行断开。

 

设置这个参数后只针对新的连接有效,正在执行的连接无效,固我们让运维又依次重启前端应用后,数据库终于复活成功!

原文发布时间为:2017-02-08

本文来自云栖社区合作伙伴DBAplus

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7月前
|
缓存 关系型数据库 数据库
PostgreSQL技术大讲堂 - 第32讲:数据库参数调整
从零开始学PostgreSQL技术大讲堂 - 第32讲:数据库参数调整
541 2
|
8月前
|
SQL Java 数据库连接
一篇文章彻底理解数据库的各种 JDBC 超时参数 1
一篇文章彻底理解数据库的各种 JDBC 超时参数
|
3天前
|
关系型数据库 MySQL 数据库
修改数据库参数
【6月更文挑战第20天】修改数据库参数。
9 1
|
16天前
|
存储 关系型数据库 MySQL
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
26 0
|
17天前
|
存储 SQL 缓存
Java性能优化(十)-数据库调优-数据库参数设置优化
Java性能优化(十)-数据库调优-数据库参数设置优化
23 0
|
1月前
|
SQL 关系型数据库 数据库
postgresql数据库修改参数的方式
在PostgreSQL数据库中,你可以通过多种方式修改数据库参数,以更改其行为。以下是一些常见的修改数据库参数的方式: 1. **通过配置文件修改(postgresql.conf):** PostgreSQL的配置文件是 `postgresql.conf`。你可以直接编辑该文件,找到要修改的参数,修改其值,然后重新启动PostgreSQL服务以使更改生效。 通常,`postgresql.conf` 文件位于 PostgreSQL 数据目录下。修改完毕后,确保重新启动 PostgreSQL 服务。 2. **使用 ALTER SYSTEM 命令:** PostgreSQL
148 1
|
1月前
|
存储 SQL Oracle
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
52 7
|
8月前
|
网络协议 Java 关系型数据库
一篇文章彻底理解数据库的各种 JDBC 超时参数 2
一篇文章彻底理解数据库的各种 JDBC 超时参数
|
1月前
|
SQL Oracle 关系型数据库
oracle查询数据库参数sql语句
oracle查询数据库参数sql语句
|
1月前
|
SQL Java 数据库连接
Java【付诸实践 01】使用org.apache.ibatis.plugin.Interceptor拦截器实现全局mapper.xml参数注入(可用于切换数据库实例schema)源码实例分享
Java【付诸实践 01】使用org.apache.ibatis.plugin.Interceptor拦截器实现全局mapper.xml参数注入(可用于切换数据库实例schema)源码实例分享
50 0

热门文章

最新文章