MariaDB 10.4随机性能退化
我有一个具有这样参数的服务器：
- 操作系统:Ubuntu18.04.4LTS x86_64
- 宿主: X11DPi-N(T)
- 内核:4.15.0-112-泛型
- CPU: Intel Xeon Silver4214 (48) @ 2.201GHz
- GPU: ASPEED技术公司ASPEED图形系列
- 内存: 18552MiB / 96336MiB
- SSD三星MZQLB960HAJR-00007 894.3G x2
安装了5.5.5-10.4.12-MariaDB-1:10.4.12+maria~bionic。在这个屏幕截图中显示了一个标准的DB加载：
所以我有大约400到500个每秒的选择(大部分来自不太大的有500 K记录的表)，每秒100到190次更新，以及大约50-150个同时连接。
我的问题是:有时，由于没有明显的原因，服务器有2000-3000个打开的连接/进程。根据SHOW FULL PROCESSLIST，它们是标准的SQL请求，但具有“发送数据”状态，运行时间为400-500秒。当然，此时服务器冻结，无法正常工作。我说“没有明显的理由”，因为在这个时候，我没有看到任何增加的用户数量或在网站上的活动增加。此外，重新启动MariaDB服务或完全重新启动服务器有助于摆脱这种情况，但并不总是如此:有时，即使在重新启动之后，我也几乎立即获得了相同的2000-3000个冻结进程。
是否有人遇到过类似的数据库行为？如有任何意见，我将不胜感激。
UPD:
- 我所有的选择都只调用一个表(~500 k记录，没有
JOIN和/或子查询)，其中大部分都有LIMIT 1，所以数据量不大。 - 错误日志显示了很多这样的记录：
2020-08-26 22:12:35 787380 [Warning] Aborted connection 787380 to db: ... (Got timeout reading communication packets) innodb_lock_wait_timeout为50 (默认)- 缓慢的查询日志并没有显示一些不寻常的东西。
- 我的
optimizer_switch设置：index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
回答 4
Stack Overflow用户
发布于 2020-09-08 02:11:03
解决方案非常简单:在研究了MariaDB文档(特别是本文https://mariadb.com/kb/en/thread-pool-in-mariadb/)之后，我在my.cnf中添加了以下内容，问题就消失了
thread_handling=pool-of-threads
thread_pool_size=48
#48 is a number of CPUsStack Overflow用户
发布于 2020-08-25 14:31:56
这听起来像是查询优化器随机死掉的典型例子。这是一只由来已久的黑臭虫。
当您看到查询堆在一起时，运行SHOW EXPLAIN FOR thread_id以获取其中一个已堆积的ID。看看这个查询计划是否没有意义。如果是的话，编辑查询应用程序端以包含索引提示，以防止查询优化器出错。如果您无法更改查询，您将不得不修改optimizer_switch设置，直到您识别并删除使优化器疯狂的特定选项。
Stack Overflow用户
发布于 2020-08-25 21:39:03
错误日志里有什么吗？
如果数据库冻结，很可能是磁盘问题:可能是磁盘已满，mariadb冻结1分钟(如果它不能写入任何东西)，如果临时表填满磁盘，磁盘可能已满，或者使用复制算法对一个表进行修改；您是否监视磁盘的使用(而不是在映像中，您应该这样做)？可能是磁盘I/O都是由一个查询使用的:那么所有的查询仍然会运行，但是非常慢，所以被卡住了，还是真的很慢？可能是锁问题？
由于查询运行了很长时间(400-500 s)，所以它很可能不是锁:除非您已经更改了它，否则锁等待超时时间会更短(至少它是在无害的:50年代)。
如果您知道没有运行ALTER TABLE，并且没有磁盘问题(你可能也想检查一下)，那么它仍然可能是要检查的锁：SHOW ENGINE INNODB STATUS\G。
您已经说过，执行SHOW FULL PROCESSLIST只有标准的ALTER TABLE请求，所以很可能没有ALTER TABLE。
如果查询写得不好，临时表可能会填充磁盘，因此需要对执行EXPLAIN时显示的查询进行SHOW FULL PROCESSLIST分析，并重写/优化/限制此类查询的结果集的大小，查找using temporary (有时还可以在磁盘上进行排序：using filesort)。慢速查询日志将告诉您是否存在使用磁盘的查询(如果它们在重新启动服务器时未被终止)。
如果您没有时间对查询进行优化，如果查询是大的SELECT，会减缓整个数据库的运行速度，并向用户显示信息(报告)，那么您就可以用脚本来结束需要太长时间的查询:这应该是最后的手段(脚本杀死查询所用的时间太长可以编写它们，以便以后能够分析它们)。
填充磁盘或使用所有I/O的临时表是我看到数据库冻结并在重新启动后重新启动的唯一情况。对于数据库再次冻结的情况，用户可能会再次执行相同的查询。
编辑
可能不是您的数据库出了问题，而是您的web应用程序:错误日志消息表明数据库正在扼杀某些连接。
查询的组合发送数据和中止的连接对我来说是不寻常的。通常，如果web应用程序没有关闭连接，并且它们处于睡眠状态，则会发生中止连接。您可以查看这篇文章里的每件事：
- 检查网络问题(防火墙)
- 检查web应用程序日志中的错误
- 检查
max_allowed_packet是否足够大(如果您的SELECT返回一行，则不会出现问题)
如果存在休眠查询，则无法正确关闭连接，然后达到max_connection限制，并且不会出现新的连接。有些事情还不清楚:数据库是很慢，还是什么都没有发生？web服务器端发生了什么？
也可能是驱动程序(mariadb客户端)将连接和查询保持在发送数据状态，而不是获取数据的末尾。如果它正在缓冲输出，并且在实际可以之前就被终止了(而且它也没有关闭连接)，这可能会发生。它不符合限制1，但这可以解释为什么在发送数据状态时会出现中止连接和SELECT查询。你的web应用程序使用什么语言？我可以考虑使用php进程崩溃的php非缓冲查询来重新创建这种情况，但这可能是另一个特定于语言的问题。不管怎么说，这将是非常罕见的。
https://stackoverflow.com/questions/63579091
复制相似问题
腾讯云开发者
