mysql第一次查询慢，然后对相关查询快 [英] mysql slow on first query, then fast for related queries

问题描述

我一直在努力解决一个问题，该问题仅在数据库闲置了一段时间以查询数据时才发生.第一个查询将非常慢，大约为30秒，然后相关查询将很快为0.1秒.我以为这与缓存有关，但是我一直找不到原因.

I have been struggling with a problem that only happens when the database has been idle for a period of time for the data queried. The first query will be extremely slow, on the order of 30 seconds and then related queries will be fast like 0.1 seconds. I am assuming this is related to caching, but I have been unable to find the cause of it.

将mysql变量tmp_table_size，max_heap_table_size更改为较大的大小除了在内存中创建临时表外没有任何作用.

Changing the mysql variables tmp_table_size, max_heap_table_size to a larger size had no effect except to create the temp tables in memory.

我认为这与查询本身无关，因为它的索引很好，并且在第一个慢速查询之后，同一查询的变体不会显示在慢速查询日志中.我最想尝试确定这种情况的原因，或者是一种方法来重置有问题的缓存，以便对问题进行故障排除.

I do not think this is related to the query itself as it is well indexed and after the first slow query, variants of the same query do not show up in the slow query log. I am most interested in trying to determine the cause of this or a way to reset the offending cache so I can troubleshoot the issue.

推荐答案

innodb数据文件的页面被缓存在innodb缓冲池中.这就是您所期望的.即使在良好的硬盘驱动器上，文件的读取速度也很慢，尤其是随机读取，这通常是数据库看到的.

Pages of the innodb data files get cached in the innodb buffer pool. This is what you'd expect. Reading files is slow, even on good hard drives, especially random reads which is mostly what databases see.

可能是您的第一个查询正在执行某种表扫描，该表扫描将许多页面拉入缓冲池，然后快速访问它们.或类似的东西.

It may be that your first query is doing some kind of table scan which pulls a lot of pages into the buffer pool, then accessing them is fast. Or something similar.

这是我所期望的.

理想情况下，对所有表使用相同的引擎(例外:系统表，临时表(可能)以及非常小的表或短命的表).如果您不这样做，那么他们必须为ram而战.

Ideally, use the same engine for all tables (exceptions: system tables, temporary tables (perhaps) and very small tables or short-lived ones). If you don't do this then they have to fight for ram.

假设您所有的表都是innodb，请使缓冲池使用最多服务器物理内存的75％(假设您在计算机上没有执行太多其他任务).

Assuming all your tables are innodb, make the buffer pool use up to 75% of the server's physical ram (assuming you don't run too many other tasks on the machine).

然后，您将能够将大约12G的数据库装入ram，因此，一旦对其进行预热"，数据库的最常使用"的12G将位于ram中，可以方便，快捷地进行访问.

Then you will be able to fit around 12G of your database into ram, so once it's "warmed up", the "most used" 12G of your database will be in ram, where accessing it is nice and fast.

某些mysql用户倾向于在重新启动后热身"生产服务器，方法是向它们发送从另一台机器复制的查询一段时间(这些将是复制从属服务器)，直到将它们添加到生产池中为止.这样可以避免在缓存处于冷状态时看到的极慢的情况.例如，Youtube就是这样做的(或者至少以前是这样做的； Google购买了它们，现在他们可能会使用Google-fu)

Some users of mysql tend to "warm up" production servers following a restart by sending them queries copied from another machine for a while (these will be replication slaves) until they add them into their production pool. This avoids the extreme slowness seen while the cache is cold. For example, Youtube does this (or at least it used to; Google bought them and they may now use Google-fu)

这篇关于mysql第一次查询慢，然后对相关查询快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！