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.
我一直在努力解决只有在数据库空闲一段时间才能查询数据时才会出现的问题。第一个查询将非常慢，大约30秒，然后相关查询将快速像0.1秒。我假设这与缓存有关，但我一直无法找到它的原因。
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.
将mysql变量tmp_table_size，max_heap_table_size更改为更大的大小除了在内存中创建临时表之外没有任何效果。
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.
我不认为这与查询本身有关，因为它被很好地编入索引，并且在第一次慢查询之后，同一查询的变体不会出现在慢查询日志中。我最感兴趣的是尝试确定此原因或重置违规缓存的方法，以便我可以解决问题。
6 个解决方案
#1
8
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.
innodb数据文件的页面缓存在innodb缓冲池中。这是你所期望的。即使在良好的硬盘驱动器上，读取文件也很慢，特别是随机读取，这主要是数据库看到的。
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.
可能是您的第一个查询正在执行某种表扫描，这会将大量页面拉入缓冲池，然后快速访问它们。或类似的东西。
This is what I'd expect.
这是我所期待的。
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.
理想情况下，对所有表使用相同的引擎（例外：系统表，临时表（可能）和非常小的表或短期表）。如果你不这样做，那么他们必须为公羊而战。
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).
假设您的所有表都是innodb，请使缓冲池最多使用服务器物理RAM的75％（假设您没有在计算机上运行太多其他任务）。
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.
然后你就可以将你的数据库的12G左右放入ram中，所以一旦它“热身”，你的数据库中“最常用的”12G就会处于ram状态，访问它的速度非常快。
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用户倾向于在重新启动后“预热”生产服务器，通过向其他计算机复制查询一段时间（这些将是复制从属），直到将它们添加到生产池中。这避免了缓存冷却时看到的极端缓慢。例如，Youtube就是这样做的（至少它已经习惯了;谷歌买了它们，现在他们可能会使用Google-fu）
#2
3
Is anything else running on your mysql server? My thought is that maybe after the first query, your table is still cached in memory. Once it's idle, another process is causing it to be de-cached. Just a guess though.
你的mysql服务器上还有其他东西在运行吗？我的想法是，可能在第一次查询之后，您的表仍然缓存在内存中。一旦它处于空闲状态，另一个进程就会导致它被缓存。只是一个猜测。
How much memory do you have any what else is running?
你还有什么内存正在运行？
#3
1
I had an SSIS package that was timing out. The query was very simple, from a single MySQL table, but it sometimes returned a lot of records and would sometimes take a few minutes initially to execute, then only a few milliseconds afterwards if I wanted to query it again. We were stuck with the ADO connection, which meant it would time out after 30 seconds, so about half the databases we were trying to load were failing.
我有一个超时的SSIS包。查询非常简单，来自单个MySQL表，但它有时会返回大量记录，有时最初需要几分钟才能执行，如果我想再查询一次，则只需几毫秒。我们坚持使用ADO连接，这意味着它会在30秒后超时，因此我们尝试加载的数据库中有一半是失败的。
After beating my head against the wall I tried performing an initial query first; very simple and only returning a few rows. Since it was so simple it executed fast and set the table in the cache for faster querying. In the next step of the package I would do the more complex query which returned the large data set that kept timing out. Problem solved - all tables loaded. I may start doing this on a regular basis, the complex queries execute much faster by doing a simple query first.
在我的头撞到墙后，我尝试先执行初始查询;非常简单，只返回几行。由于它非常简单，因此可以快速执行并将表设置在缓存中以便更快地进行查询。在包的下一步中，我将执行更复杂的查询，该查询返回保持超时的大数据集。问题解决了 - 所有表格都已加载我可以定期开始这样做，通过先进行简单的查询，复杂的查询执行得更快。
#4
0
Ttry and compare the output of "vmstat 1" on the linux command line when running the query after a period of time, vs when you re-run it and get results fast. Specifically check the "bi" column (that's the kb read from disk per second).
在一段时间后运行查询时，测试并比较linux命令行上“vmstat 1”的输出，与重新运行查询并快速获得结果时相比较。具体检查“bi”列（即每秒从磁盘读取的kb）。
You may find the operating system is caching the disk blocks in the fast case (and thus a low "bi" figure), but not in the slow case (and hence a large "bi" figure).
你可能会发现操作系统在快速情况下缓存磁盘块（因此是一个低“bi”数字），但不是在慢速情况下（因此是一个大的“bi”数字）。
You might also find that vmstat shows high/low cpu usage in either case. If it's low when fast, and disk throughput is also low, then your system may still be returning a cached query, even though you've indicated the relevant config value is set to zero. Perhaps check the output of show engine innodb status and SHOW VARIABLES and confirm.
您可能还会发现vmstat在任何一种情况下都显示高/低CPU使用率。如果它在快速时很低，并且磁盘吞吐量也很低，那么即使您已指示相关配置值设置为零，您的系统仍可能返回缓存查询。也许检查show engine innodb status和SHOW VARIABLES的输出并确认。
innodb_buffer_pool_size may also be set high (it should be...), which would cache the blocks even before the OS can return them.
innodb_buffer_pool_size也可以设置为高（应该是......），即使在OS返回之前也会缓存块。
You might also find that "key_buffer" is set high - this would cache the keys in the indexes, which could make your select blindingly fast.
您可能还会发现“key_buffer”设置为高 - 这会将索引缓存在索引中，这可能会使您的选择速度惊人。
Try check the mysql performance blog site for lots of useful info.
尝试检查mysql性能博客站点以获取大量有用信息。
#5
0
I had issue when MySQL 5.6 was slow on first query after idle period. This was a connection problem, not MySQL instance problem, e.g. if you run MYSQL Query Browser execute "select * from some_queue", leave it alone for a couple of hours, then execute any query, it runs slow, while at the same time processes on server or new instance of Browser will select from same tables instantly.
当闲置期后第一次查询MySQL 5.6很慢时，我遇到了问题。这是一个连接问题，而不是MySQL实例问题，例如如果你运行MYSQL查询浏览器执行“select * from some_queue”，单独放置几个小时，然后执行任何查询，它运行缓慢，同时服务器上的进程或浏览器的新实例将从相同的表中选择即刻。
Adding skip-host-cache, skip-name-resolve to my.ini file solved this problem.
添加skip-host-cache，skip-name-resolve到my.ini文件解决了这个问题。
I don't know why is that. Why I tried this: MySQL 5.1 without those options was slowly establishing connections from other networks (e.g. server is 192.168.1.100, 192.168.1.101 connects fast, 192.168.2.100 connects slow), MySQL 5.6 didn't have such problem to start with so we didn't add these to my.ini initially.
我不知道为什么会这样。为什么我试过这个：没有那些选项的MySQL 5.1正在慢慢建立来自其他网络的连接（例如服务器是192.168.1.100,192.168.1.101快速连接，192.168.2.100连接速度慢），MySQL 5.6没有这样的问题开始我们最初没有将这些添加到my.ini中。
UPD: Solved half the cases, actually. Setting wait_timeout to maximum integer fixed the other half. Maybe I even now can remove skip-host-cache, skip-name-resolve and it won't slow down in 100% of the cases
UPD：实际解决了一半的案件。将wait_timeout设置为最大整数固定另一半。也许我现在甚至可以删除skip-host-cache，skip-name-resolve，它不会在100％的情况下减速
#6
0
MySQL Workbench:
MySQL Workbench：
The below isn't 100% related to this SO question, but the symptoms are very related and this is the first SO result when searching for "mysql workbench slow" or related terms, so hopefully it's useful for others.
以下不是100％与此SO问题相关，但症状非常相关，这是搜索“mysql workbench slow”或相关术语时的第一个SO结果，所以希望它对其他人有用。
Clear the query history! - following the process at MySql workbench query history ( last executed query / queries ) i.e. create / alter table, select, insert update queries to clear MySQL Workbench's query history really sped up the program for me.
清除查询历史记录！ - 遵循MySql工作台查询历史记录（最后执行的查询/查询）的过程，即创建/更改表，选择，插入更新查询以清除MySQL Workbench的查询历史记录真的为我加速了程序。
In summary: change the Output pane to History Output, right click on a Date and select Delete All Logs.
总结：将Output窗格更改为History Output，右键单击Date并选择Delete All Logs。
The issue I was experiencing was "slow first query" in that it would take a few seconds to load the results even though the duration/fetch were well under 1 second. After clearing my query history, the duration/fetch times stayed the same (well under 1 second, so no DB behavior actually changed), but now the results loaded instantly rather than after a few second delay.
我遇到的问题是“第一次查询缓慢”，即使持续时间/获取时间远低于1秒，加载结果也需要几秒钟。清除查询历史记录后，持续时间/获取时间保持不变（远低于1秒，因此实际上没有更改DB行为），但现在结果立即加载而不是在几秒钟后延迟。
#1
8
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.
innodb数据文件的页面缓存在innodb缓冲池中。这是你所期望的。即使在良好的硬盘驱动器上，读取文件也很慢，特别是随机读取，这主要是数据库看到的。
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.
可能是您的第一个查询正在执行某种表扫描，这会将大量页面拉入缓冲池，然后快速访问它们。或类似的东西。
This is what I'd expect.
这是我所期待的。
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.
理想情况下，对所有表使用相同的引擎（例外：系统表，临时表（可能）和非常小的表或短期表）。如果你不这样做，那么他们必须为公羊而战。
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).
假设您的所有表都是innodb，请使缓冲池最多使用服务器物理RAM的75％（假设您没有在计算机上运行太多其他任务）。
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.
然后你就可以将你的数据库的12G左右放入ram中，所以一旦它“热身”，你的数据库中“最常用的”12G就会处于ram状态，访问它的速度非常快。
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用户倾向于在重新启动后“预热”生产服务器，通过向其他计算机复制查询一段时间（这些将是复制从属），直到将它们添加到生产池中。这避免了缓存冷却时看到的极端缓慢。例如，Youtube就是这样做的（至少它已经习惯了;谷歌买了它们，现在他们可能会使用Google-fu）
#2
3
Is anything else running on your mysql server? My thought is that maybe after the first query, your table is still cached in memory. Once it's idle, another process is causing it to be de-cached. Just a guess though.
你的mysql服务器上还有其他东西在运行吗？我的想法是，可能在第一次查询之后，您的表仍然缓存在内存中。一旦它处于空闲状态，另一个进程就会导致它被缓存。只是一个猜测。
How much memory do you have any what else is running?
你还有什么内存正在运行？
#3
1
I had an SSIS package that was timing out. The query was very simple, from a single MySQL table, but it sometimes returned a lot of records and would sometimes take a few minutes initially to execute, then only a few milliseconds afterwards if I wanted to query it again. We were stuck with the ADO connection, which meant it would time out after 30 seconds, so about half the databases we were trying to load were failing.
我有一个超时的SSIS包。查询非常简单，来自单个MySQL表，但它有时会返回大量记录，有时最初需要几分钟才能执行，如果我想再查询一次，则只需几毫秒。我们坚持使用ADO连接，这意味着它会在30秒后超时，因此我们尝试加载的数据库中有一半是失败的。
After beating my head against the wall I tried performing an initial query first; very simple and only returning a few rows. Since it was so simple it executed fast and set the table in the cache for faster querying. In the next step of the package I would do the more complex query which returned the large data set that kept timing out. Problem solved - all tables loaded. I may start doing this on a regular basis, the complex queries execute much faster by doing a simple query first.
在我的头撞到墙后，我尝试先执行初始查询;非常简单，只返回几行。由于它非常简单，因此可以快速执行并将表设置在缓存中以便更快地进行查询。在包的下一步中，我将执行更复杂的查询，该查询返回保持超时的大数据集。问题解决了 - 所有表格都已加载我可以定期开始这样做，通过先进行简单的查询，复杂的查询执行得更快。
#4
0
Ttry and compare the output of "vmstat 1" on the linux command line when running the query after a period of time, vs when you re-run it and get results fast. Specifically check the "bi" column (that's the kb read from disk per second).
在一段时间后运行查询时，测试并比较linux命令行上“vmstat 1”的输出，与重新运行查询并快速获得结果时相比较。具体检查“bi”列（即每秒从磁盘读取的kb）。
You may find the operating system is caching the disk blocks in the fast case (and thus a low "bi" figure), but not in the slow case (and hence a large "bi" figure).
你可能会发现操作系统在快速情况下缓存磁盘块（因此是一个低“bi”数字），但不是在慢速情况下（因此是一个大的“bi”数字）。
You might also find that vmstat shows high/low cpu usage in either case. If it's low when fast, and disk throughput is also low, then your system may still be returning a cached query, even though you've indicated the relevant config value is set to zero. Perhaps check the output of show engine innodb status and SHOW VARIABLES and confirm.
您可能还会发现vmstat在任何一种情况下都显示高/低CPU使用率。如果它在快速时很低，并且磁盘吞吐量也很低，那么即使您已指示相关配置值设置为零，您的系统仍可能返回缓存查询。也许检查show engine innodb status和SHOW VARIABLES的输出并确认。
innodb_buffer_pool_size may also be set high (it should be...), which would cache the blocks even before the OS can return them.
innodb_buffer_pool_size也可以设置为高（应该是......），即使在OS返回之前也会缓存块。
You might also find that "key_buffer" is set high - this would cache the keys in the indexes, which could make your select blindingly fast.
您可能还会发现“key_buffer”设置为高 - 这会将索引缓存在索引中，这可能会使您的选择速度惊人。
Try check the mysql performance blog site for lots of useful info.
尝试检查mysql性能博客站点以获取大量有用信息。
#5
0
I had issue when MySQL 5.6 was slow on first query after idle period. This was a connection problem, not MySQL instance problem, e.g. if you run MYSQL Query Browser execute "select * from some_queue", leave it alone for a couple of hours, then execute any query, it runs slow, while at the same time processes on server or new instance of Browser will select from same tables instantly.
当闲置期后第一次查询MySQL 5.6很慢时，我遇到了问题。这是一个连接问题，而不是MySQL实例问题，例如如果你运行MYSQL查询浏览器执行“select * from some_queue”，单独放置几个小时，然后执行任何查询，它运行缓慢，同时服务器上的进程或浏览器的新实例将从相同的表中选择即刻。
Adding skip-host-cache, skip-name-resolve to my.ini file solved this problem.
添加skip-host-cache，skip-name-resolve到my.ini文件解决了这个问题。
I don't know why is that. Why I tried this: MySQL 5.1 without those options was slowly establishing connections from other networks (e.g. server is 192.168.1.100, 192.168.1.101 connects fast, 192.168.2.100 connects slow), MySQL 5.6 didn't have such problem to start with so we didn't add these to my.ini initially.
我不知道为什么会这样。为什么我试过这个：没有那些选项的MySQL 5.1正在慢慢建立来自其他网络的连接（例如服务器是192.168.1.100,192.168.1.101快速连接，192.168.2.100连接速度慢），MySQL 5.6没有这样的问题开始我们最初没有将这些添加到my.ini中。
UPD: Solved half the cases, actually. Setting wait_timeout to maximum integer fixed the other half. Maybe I even now can remove skip-host-cache, skip-name-resolve and it won't slow down in 100% of the cases
UPD：实际解决了一半的案件。将wait_timeout设置为最大整数固定另一半。也许我现在甚至可以删除skip-host-cache，skip-name-resolve，它不会在100％的情况下减速
#6
0
MySQL Workbench:
MySQL Workbench：
The below isn't 100% related to this SO question, but the symptoms are very related and this is the first SO result when searching for "mysql workbench slow" or related terms, so hopefully it's useful for others.
以下不是100％与此SO问题相关，但症状非常相关，这是搜索“mysql workbench slow”或相关术语时的第一个SO结果，所以希望它对其他人有用。
Clear the query history! - following the process at MySql workbench query history ( last executed query / queries ) i.e. create / alter table, select, insert update queries to clear MySQL Workbench's query history really sped up the program for me.
清除查询历史记录！ - 遵循MySql工作台查询历史记录（最后执行的查询/查询）的过程，即创建/更改表，选择，插入更新查询以清除MySQL Workbench的查询历史记录真的为我加速了程序。
In summary: change the Output pane to History Output, right click on a Date and select Delete All Logs.
总结：将Output窗格更改为History Output，右键单击Date并选择Delete All Logs。
The issue I was experiencing was "slow first query" in that it would take a few seconds to load the results even though the duration/fetch were well under 1 second. After clearing my query history, the duration/fetch times stayed the same (well under 1 second, so no DB behavior actually changed), but now the results loaded instantly rather than after a few second delay.
我遇到的问题是“第一次查询缓慢”，即使持续时间/获取时间远低于1秒，加载结果也需要几秒钟。清除查询历史记录后，持续时间/获取时间保持不变（远低于1秒，因此实际上没有更改DB行为），但现在结果立即加载而不是在几秒钟后延迟。