技术分享 | 大量 Opening tables 案例分析

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《技术分享 | 大量 Opening tables 案例分析》，文章讲解的知识点主要包括MySQL，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

作者：xuty
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

一、现象

某项目反馈系统非常卡，登陆 MySQL 后发现大量 SQL 处于

dict_stats_save
 -> dict_stats_exec_sql
  -> que_eval_sql
   -> que_run_threads
    -> que_run_threads_low
     -> log_free_check /* Check that there is enough space in the log to accommodate possible log entries by this query step. */
      -> log_check_margins /* Checks that there is enough free space in the log to start a new query step.Flushes the log buffer or makes a new checkpoint if necessary. */
       -> log_checkpoint_margin /* Tries to establish a big enough margin of free space in the log groups, such that a new log entry can be catenated without an immediate need for a checkpoint. */
        -> log_preflush_pool_modified_pages /* Advances the smallest lsn for which there are unflushed dirty blocks in the buffer pool. */
         -> buf_flush_wait_flushed /* Waits until a flush batch of the given lsn ends */
          -> os_thread_sleep

这段逻辑主要是先判断 log buffer 可用空间是否达到临界值（

log->buf_free > log->max_buf_free

），达到的话需要将 log buffer 中的日志先刷到 redo log 中。

然后判断 innodb buffer pool 中脏页占比是否达到临界值（

(log->lsn - oldest_lsn) > log->max_modified_age_sync

），达到的话需要进行

同步刷脏页

，这个过程可能会比较久，且阻塞当前其他写入进程。

最后判断 checkpoint 是否达到临界值（对比

log->lsn - log->last_checkpoint_lsn 

、

max_checkpoint_age

、

max_checkpoint_age_async

3 个值的大小），分别选择进行同步 checkpoint、异步 checkpoint、或是不做 checkpoint。

这里最后进入的是

log_preflush_pool_modified_pages

，就是

同步刷脏页

的过程，刷脏页的过程就是唤醒

page clean thread

进行写入，而自身则处于

buf_flush_wait_flushed

函数中，一直等待刷脏页完成，就如我们之前堆栈中看到的一样，insert、pugre_thread、dict_stats_thread 等线程均在等待 page clean thread 刷新脏页。

这块可能我理解的不是很透彻~，大家有兴趣可以去看下 MySQL · 引擎特性 · InnoDB redo log漫游 里面介绍的非常详细。

四、总结

根据上述堆栈分析，总结下来，还是由于

磁盘写入性能太差

，导致了 innodb buffer pool 中

脏页 LSN

落后

当前 LSN

太多，达到临界值后，触发了 MySQL 的同步刷脏机制，期间会卡住所有正在进行写入的线程，即诸多 insert 语句、purge 线程、还有

dict_stats_thread(统计信息更新线程)

均被阻塞。

我们一开始看到的许多会话处于 Opening tables 的现象，可以说是一个比较巧合的事。当某张表的数据量变化达到一定量级（默认 10%），就触发了后台的 dict_stats_thread，它会拿着

dict_sys->mutex(数据字典锁)

去更新对应表上面的统计信息，但是更新过程中卡在

同步刷脏页

步骤，无法及时释放 dict_sys->mutex，因此很多 SQL 会因为无法拿到 dict_sys->mutex 而处于 Opening tables 状态。

附录：

http://mysql.taobao.org/month...
http://blog.itpub.net/7728585...
https://my.oschina.net/yejr/b...
https://developer.aliyun.com/...
http://mysql.taobao.org/month...

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。