记一次 MySQL 数据库问题排查

标签: node.js mysql | 发表时间:2016-01-06 08:49 | 作者:KohPoll
出处:http://segmentfault.com/blogs

最近遇到应用频繁的响应缓慢,无法正常访问。帮忙一起定位原因,最后定位到的问题说起来真的是很小的细节问题,但是就是这些小细节导致了服务不稳定,真是细节决定成败。这里尝试着来分享下,希望对大家有所帮助。

问题 1:占着茅坑不拉屎

遇到问题首先要看的还是服务器错误日志。

错误日志中看到频繁有这样的一个异常报错: Error: ER_CON_COUNT_ERROR: Too many connections。这个报错是因为数据库的所有连接被客户端都占有了,没有空闲的连接可以使用。MySQL 默认的最大并发连接数是 100,然而我们的应用这边最多可能的并发也就 30~40 个任务,怎么也不太可能报这样的错误,推测很有可能是代码里面建立连接后没有及时的进行关闭。于是我们重点看了下执行 SQL 部分的代码,大概是下面这样(使用了node-mysql库):

  var mysql = require('mysql');
// 建立连接池
var pool = mysql.createPool({
    host: 'host',
    user: 'user',
    password: 'password',
    database: 'db'
});

exports.query = function(sql, cb) {
    // 从池子里面取一个可用连接
    pool.getConnection(function(err, connection) {
        if (err) throw err;
        // 执行sql
        connection.query(sql, function(err, rows, fields) {
            if (err) {
                return cosole.error(err);
            }
            cb(rows);
        });
        // 释放此连接
        connection.release();
    });

};

刚开始我还真没看出来有什么问题,后来仔细读了 node-mysql 的文档及这个 issue,终于发现了我们的写法是有问题的。

再次看看上面的代码, pool.getConnection 后我们执行 connection.query,然后没等 SQL 执行完,直接调用了 connection.release,由于 JavaScript 的异步特性(虽然 SQL 可能很快就执行完,但是我们也必须在 connection.query 的 callback 里面才明确的知道 SQL 执行完了),这个时候此次连接是不会被释放的!代码里面所有的 SQL 执行都调用到这个函数,这意味着我们占着一堆数据库连接不释放,这时不断的有其他数据库连接过来,直接导致其他连接被阻塞,抛出连接太多的异常。这真是典型的“拉完不及时让坑,占着茅坑不拉屎”的行为。所以,我们一定要在 SQL 执行完成后就将连接及时进行释放。因为 SQL 执行一般是非常快的(零点几秒),如果我们执行完后不释放,在同一时间产生很多数据库连接时很有可能导致连接被阻塞,产生连接过多的异常。于是我们对代码进行了如下修改:

  exports.query = function(sql, cb) {
    // 从池子里面取一个可用连接
    pool.getConnection(function(err, connection) {
        if (err) throw err;
        // 执行sql
        connection.query(sql, function(err, rows, fields) {
            // 释放连接(一定要在错误处理前,不然出错的时候也会导致该连接得不到释放)
           connection.release();

            if (err) {
                return cosole.error(err);
            }
            cb(rows);
        });
    });

};

也可以用更简单的写法 pool.query,这个方法内部会在合适的时机来释放连接,不用我们手动操作。

完成此次修改后,这个异常没有再复现,但是响应缓慢的情况依然没有得到缓解。

问题 2:一条 UPDATE 引发的血案

我们再次查看了错误日志,发现了另一个异常报错: Error: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction。这个报错就非常令人费解了,原因是锁等待超时,当前事务在等待其它事务释放锁资源造成的。

我们先大概说下什么是事务(transaction)。事务应该具有 4 个属性:

  • 原子性(事务作为整体执行,操作要么全部执行、要么全部不执行)

  • 一致性(事务应该确保数据库状态从一个一致状态转变为另一个一致状态)

  • 隔离性(多个事务并发执行时,一个事务执行不影响其他事务执行)

  • 持久性(事务提交后,对数据库修改应该永久保存在数据库中)

对于隔离性,还会分出多个隔离级别:

隔离级别 脏读 不可重复读 幻读
未提交读 可能 可能 可能
已提交读 不可能 可能 可能
可重复读 不可能 不可能 可能
未串行化 不可能 不可能 不可能
  • 脏读(Dirty Read):A 事务读到 B 事务未提交的修改。

  • 不可重复读(NonRepeatable Read):A 事务还没有结束时,B 事务也访问同一数据。在 A 事务的两次读取之间,由于 B 事务的修改,A 事务两次读到的数据可能是不一样的。

  • 幻读(Phantom Read):A 事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,B 事务也修改这个表中的数据,这种修改是向表中插入一行新数据。操作 A 事务的用户发现表中出现了 B 事务插入的行,就好象发生了幻觉一样。

MySQL 默认的级别是 REPEATABLE READ(可重复读),这表示在 MySQL 的默认情况下,“脏读”、“不可重复读”是不会发生的。这就需要在更新的时候进行必要的锁定(InnoDB 是采用行级锁的方式),从而保证一致性。需要注意的是 InnoDB 的行锁是通过给索引上的索引项加锁来实现的,这个特点意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!

我们数据库表是 InnoDB 引擎的表,而 MySQL 的 InnoDB 引擎是一个支持事务的引擎,其默认操作模式是 autocommit 自动提交模式。什么意思呢?除非我们显式地开始一个事务,否则每个查询都被当做一个单独的事务自动执行。

回到上面的报错,错误日志里抛出异常时执行的 SQL 语句,都是类似这样的一条 UPDATE 语句: update testScore set status=1,executionId='946012' where token='f7900c40-8f4b-11e5-b2f1-6feca76a1bf5'

问题产生的原因可以这样来描述了:我们在执行 UPDATE 语句时,MySQL 会将其当成一个事务,对表的行进行锁定,这时又有其他连接进来要 UPDATE 同样的表或者 SELECT 这张表时就必须等待锁资源,而这个等待时间太久,导致超时了。

什么?一个 UPDATE 语句居然会这么慢?这我简直不能接受啊!那我只能看看为啥这个语句如此慢了。

查看慢查询(slow_queries.log)日志里面对应的查询信息:

  # Query_time: 56.855324  Lock_time: 48.054343 Rows_sent: 0  Rows_examined: 29400
update testScore set uiTaskId=81041 where token='e7d7d8f0-8f4b-11e5-99be-9dfbb419755e';

这样一条 UPDATE 语句花了 56 秒,扫了 29400 条表记录。看到这样的执行日志,也大概猜到原因了,没有为查询字段 token 加索引!这样 MySQL 在进行 update 操作时不会走行锁,直接锁定了整张表,而这个 update 语句本身也够慢(扫了全表),那并发多个 update 更新时导致了等待锁超时。

给 testScore 表的 token 字段增加了索引,终于,这个异常不再复现,响应时间开始回归正常。

参考资料

相关 [mysql 数据库 问题] 推荐:

使用Hibernate + MYSQL数据库开发,链接超时问题:

- - CSDN博客Web前端推荐文章
使用Hibernate + MYSQL数据库开发,链接超时问题:. 查了一下,原来是mysql超时设置的问题. 如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat. . 如果不用hibernate的话, 则在 connection url中加参数: autoReconnect=true.

使用tcpdump排查mysql数据库tps飙升的问题

- - OurMySQL
   上线后习惯性的观察数据库的变化. 发现数据库的tps有很大的飙升. 不过幸好在双十一的时候在数据库方面做了一些完善,虽然主库的tps有飙升,但是总体load还不是很高. 但是问题既然出现了,还是要解决的. 确定是insert update 还是 delete操作导致tps高.    既然是tps高,那就说明数据库修改的操作多了.

MySQL数据库如何解决大数据量存储问题

- - 数据库 - ITeye博客
利用MySQL数据库如何解决大数据量存储问题. 各位高手您们好,我最近接手公司里一个比较棘手的问题,关于如何利用MySQL存储大数据量的问题,主要是数据库中的两张历史数据表,一张模拟量历史数据和一张开关量历史数据表,这两张表字段设计的很简单(OrderNo,Value,DataTime). 基本上每张表每天可以增加几千万条数据,我想问如何存储数据才能不影响检索速度呢.

记一次 MySQL 数据库问题排查

- - SegmentFault 最新的文章
最近遇到应用频繁的响应缓慢,无法正常访问. 帮忙一起定位原因,最后定位到的问题说起来真的是很小的细节问题,但是就是这些小细节导致了服务不稳定,真是细节决定成败. 这里尝试着来分享下,希望对大家有所帮助. 遇到问题首先要看的还是服务器错误日志. 错误日志中看到频繁有这样的一个异常报错: Error: ER_CON_COUNT_ERROR: Too many connections.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

关于MySQL数据库的数据类型发生隐形转换的问题咨询

- - mysqlops
1.MySQL对于int类型索引使用问题,如:tb中有主键id,普通索引tid,在执行SQL:select * from tb where id = 2 order by id 时,. extra为空,这里的id值没带引号,如果带上引号extra也为空. 2.当 select * from tb where tid = 2 order by tid,tid为普通索引,这时tid的值带引号和不带引号就会有区别,带引号时extra会提示额外的排序,不带引号时extra为空,这是怎么一回事.

Google数据库产品LevelDB对决MySQL

- - HTML5研究小组
去年一月份,Google发布了LevelDB. LevelDB是Key-Value嵌入式数据库管理系统编程库,目前的版本能够支持Billion级别的数据量. LevelDB是一个C++库,可按照字符串键值顺序映射. 源于其本身的良好设计,特别是LSM算法,LevelDB性能非常之高. 在一台4个Q6600的CPU机器上,每秒钟写数据超过40w,而随机读的性能每秒钟超过10w.

MySQL数据库的IO操作

- - haohtml's blog
         淘宝丁奇分享的PPT:MySQL数据库的IO操作,详细分享了四块的内容,并且告诉大家如何调整MySQL数据库IO操作相关的参数,给出了详细的选择策略,现替其整理成文章分享与此. 4.影响io行为的一些参数和选择策略. 一个简单的查询 select * from t where id>=(  select id from t where k1=100 limit 100000,1) limit 2;.

MySQL数据库优化总结

- - CSDN博客推荐文章
        对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要. 一般来说,要保证数据库的效率,要做好以下四个方面的工作:数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统,这个顺序也表现了这四个工作对性能影响的大小.        一、数据库设计   适度的反范式,注意是适度的.

理解MySQL数据库覆盖索引

- - haohtml's blog
看AUTO_INCREMENT就知道数据并不多,75万条. 很简单对不对?怪异的地方在于:. 如果换成MyISAM做存储引擎的时候,查询耗时只需要0.01s,用InnoDB却会是0.15s左右. 如果只是就这么点差距其实不是什么大不了的事,但是真实的业务需求比这个复杂,造成的差距也很大:MyISAM只需要0.12s,InnoDB则需要2.2s.,最终定位到问题症结是在这条SQL.