连接到MySQL数据库的速度很慢，需要n秒钟 [英] Connecting to MySQL database slow, takes n seconds
问题描述
为什么连接到我的InnoDB数据库通常会延迟整数秒?
某些背景
我有一个很小的MySQL数据库，由一个表用户"(150条记录)和一个表连接"(增长到150 * 150条记录)组成.表和索引总共不到5 MB.
I have a very small MySQL database, consisting of a table 'users' (150 records) and a table 'connections' (growing to 150*150 records). Tables and indexes add up to less than 5 MB.
当用户处于活动状态时，连接"中的5-50条记录会更改(权重已更改)或添加(如果尚不存在).整个应用程序运行平稳，加载时间低于100毫秒.
When users are active, 5-50 records in 'connections' are changed (the weight is changed) or added (if they didn't exist yet). The whole app runs smoothly and load times are below ~100 ms.
除非不是.
详细信息
即使在很小的负载下，页面加载时间也会从60毫秒激增至1,000毫秒至10,000毫秒之间.
Under even quite small loads, page load times spike from 60 ms to somewhere between 1,000 ms and 10,000 ms.
使用Symfony中的事件探查器，我可以将'getRepository'语句的延迟降低95％以上，而每个查询仅花费约1 ms的时间.这使我相信连接数据库是缓慢的动作.我定期编写了一个帮助程序脚本，该脚本连接到数据库以测试该理论.
Using the profiler in Symfony, I could pin down the delay for 95+% on the 'getRepository' statement, while the queries took only ~1 ms per query. This led me to believe that connecting to the database was the slow action. I wrote a helper script regularly connecting to the database to test this theory.
<?php // call this script commandline using watch
$a = microtime(true);
$pdo = new PDO('mysql:host=127.0.0.1;dbname=mydb','myuser','mypass');
file_put_contents( 'performance.txt', (microtime(true)-$a).PHP_EOL, FILE_APPEND );
奥秘
连接到数据库持续花费1-3毫秒，或者1,001-1,003毫秒，或者2,001-2,003毫秒，或者3,001-3,003毫秒，等等.以秒为单位的整数加上正常时间.两者之间没有任何区别，例如400毫秒或800毫秒.没有写操作，连接几乎立即建立.通过该应用程序执行一些写入操作后，就达到了更高的数量.
Connecting to the database took consistently 1-3 ms, or 1,001-1,003 ms, or 2,001-2,003 ms, or 3,001-3,003 ms, etc. An integer amount of seconds, plus the normal time. Nothing in between, like 400 ms or 800 ms. With no writes going on, the connection was made almost instantly. As soon as some writes were performed via the app, the higher numbers were reached.
是什么原因导致这种现象?InnoDB page_cleaner似乎每1000毫秒执行一次工作，也许这就是解释的一部分吗?
What is causing this behavior? The InnoDB page_cleaner appears to do its work every 1,000 ms, maybe that's part of the explanation?
更重要的是，我该如何解决?我本来打算改用MEMORY表，但我想应该可以使用更优雅的选择.
More importantly, how can I fix this? I was thinking of switching to MEMORY tables, but I'd say more elegant options should be available.
编辑
根据要求，变量和全局状态.
其他信息:我直接连接到127.0.0.1(请参见上面的代码段)，并且测试了skip-name-resolve标志无效.顺便说一下，这是一台Debian服务器.
Additional information: I connect directly to 127.0.0.1 (see the code snippet above) and I tested the skip-name-resolve flag to no effect. It's a Debian server, by the way.
编辑2
我发现延迟是1、3、7或15秒.注意模式:1秒，+ 2s，+ 4s，+ 8s.这确实看起来像是一个超时问题...
I found the delays were either 1, 3, 7 or 15 seconds. Notice the pattern: 1 second, +2s, +4s, +8s. This really looks as some timeout issue...
推荐答案
反向dns查找通常需要很长时间.加上 host_cache 的大小，它可能会产生不稳定的行为.
It's common that reverse dns lookup takes a long time. Along with the size of the host_cache it can give a erratic behaviour.
通过将其添加到my.cnf中将其关闭
Turn it off by adding this to my.cnf
[mysqld]
skip-name-resolve
请注意，如果您更改此设置，则所有授予都必须按IP进行，而不是按名称进行.
Note that all grants must be by ip, not by name, if you change this.
这篇关于连接到MySQL数据库的速度很慢，需要n秒钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
