21

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.

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.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Dan Littlejohn
  • 1,329
  • 4
  • 16
  • 30

6 Answers6

15

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.

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).

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.

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)

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • 1
    the answer turned out to be changing the mysql config file and tune innodb. With increased sizes on innodb variables the database now does warm up after a few queries. I guess what was happening is the cache was too small to have all the table indexes in memory so it was constantly swapping from disk causing a slow first query. Now it has enough to keep them all in memory so the problem is gone. – Dan Littlejohn Dec 03 '09 at 18:22
  • 3
    My question is, is it possible to make the first query faster? Or it is what is, and upping the buffer pool is the correct way to go? – Kalle H. Väravas Jun 18 '17 at 17:44
11

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.

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.

In summary: change the Output pane to History Output, right click on a Date and select 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.

WOUNDEDStevenJones
  • 5,150
  • 6
  • 41
  • 53
  • 7
    Or simply delete the contents of `C:\Users[WinUser]\AppData\Roaming\MySQL\Workbench\sql_history` and `C:\Users[WinUser]\AppData\Roaming\MySQL\Workbench\log` does the trick. – Tasos K. Feb 07 '19 at 13:47
  • I have the same problem and this didn't help. For me, it seems like MySQL Workbench just struggles significantly when interacting with a large database when you first connect. The UI literally locks up for several seconds during this phase and clicking on anything when this happens tends to either freeze the app indefinitely or make it crash. – georaldc Feb 07 '21 at 08:25
  • I tried clearing those Roaming directory logs, but it didn't solve the problem. My workaround in the end was to complete delete (rename) `C:\Users[WinUser]\AppData\Roaming\MySQL\Workbench` and start Workbench up again to start from scratch with a brand new connection configured for the slow host. _PS.: I backed up and restored my other connections_ – Spastika Dec 14 '22 at 13:27
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.

How much memory do you have any what else is running?

Brendan Long
  • 53,280
  • 21
  • 146
  • 188
  • mysql is the only thing running on the server. It has 16Gb of memory. I agree with it being de-cached from memory, but I am not able to figure out where it is caching so I can troubleshoot the issue. I am not sure if it is a mysql config problem, linux config problem, sql problem, etc. I have googled others having the same problem, but there was no insight as to what the problem is. – Dan Littlejohn Nov 24 '09 at 00:05
  • Based on this: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html, maybe try making the query_cache_size bigger. It's strange though, because Linux should automatically keep this stuff cached whether or not MySQL tells it to. You could also try general-purpose methods to speed up your queries, like adding an index and not having huge tables, but I don't know if those would be helpful for you. – Brendan Long Nov 24 '09 at 00:11
  • the query_cache_size is set to zero so this is not the issue. I want to know what is caching it. – Dan Littlejohn Nov 24 '09 at 00:14
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.

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.

Jim
  • 185
  • 1
  • 5
  • So you're saying you can execute a small query plus your original big query in less time than the big query alone? You'd think MySQL would figure this out and move whatever it needs to into memory before even attempting the big query. – mpen Jan 28 '13 at 22:53
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).

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).

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.

innodb_buffer_pool_size may also be set high (it should be...), which would cache the blocks even before the OS can return them.

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.

Try check the mysql performance blog site for lots of useful info.

oskarpearson
  • 284
  • 2
  • 8
  • This is what is going on. The server is reading the innodb table from disk on the first query and then using the cache when running similar queries. Now I have to figure out how to reduce the size of the ibdata1. Considering moving to innodb_file_per_table – Dan Littlejohn Dec 02 '09 at 18:33
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.

Adding skip-host-cache, skip-name-resolve to my.ini file solved this problem.

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.

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

Timur Lyapin
  • 1
  • 1