Forums

Hi. Is it possible to use the mySQL cache? At my (free) account, query_cache_size is set to zero, and changing it would require SUPER priviliges (root user, I guess).

Thanks<br> Georg

Unfortunately it's not. What's the effect you're trying to get, specifically? Perhaps there's another way we could suggest to achieve it?

It was just a guess, to probably speed up database access. Roughly outlined, there is a lot if items organized in "containers", and when users switch rapidly between views of different containers forth and back, the same set of queries will need to be performed every time, although the latest identical query (with identical results, if there was no database update) had been performed only seconds ago. There is also a lot of hierarchy in the retrieved data (e.g. item <- batch <- type <- general type), so the same operation (like: name of the type "A" of the batch "B" of the item "C") is repeated many, many times everytime when an inventory of such a container is generated. Anyway, it is not really a "high performance" project, so I was also asking out of curiousity. Maybe I try to save some of the intermediate results during a search in Python lists to minimize repetitive database operations.

Thanks Georg

I have a similar issue. When I run a query one time, the results are pretty slow. The second time, it is nice and fast. From looking at forums, I see that mysql provides-

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

and

mysqld --innodb-buffer-pool-load-at-startup=ON;

To save and restore the buffer pool state. I imagine if I had access to set this- that might solve the issue I am seeing.

Has anyone had success in saving/restore db caching so initial queries are fast?

but what you are describing- first hit is slow, and second hit is fast. that sounds like there is a cache/buffer?

I would like to preload the cache buffer (if possible) so user queries don't see the slowdown. Is there a way to hook into the deploy event so I can automatically warm the cache??

To give you some idea of what I am dealing with: the first query is as bad as 11 seconds, and the additional queries are 0.1 seconds. I'm starting to notice that even after I warm the cache manually it looks like it expires after a couple hours if there are no requests.

Are we talking about a mysql query cache here? Or could the first query be slow due to other factors? Is this a very big query? I feel like this is not the cause of the 11s slowness.

mysql query cache, yes. Query1 and 2 are identical. I just run it twice and see a giant difference in speed.

I'm considering using Memory storage engine instead of InnoDB. The DB is relatively small (600MB) so this seems like a decent option. If there is a way to get InnoDB to stay in memory I think that would be better though.

How are you measuring the times? Is it being logged during a webapp request, or you are looking at the actual response times etc? And is this a consistent thing that happens say whenever you come back after an hour?

Looking at the response times on the site I can observe the slowdown. I also see it running pytest. The first test is slow, the same test run again is less than a second. I don't have any custom caching layer at the moment, so I am fully relying on mysql caching. When I try again after an hour I see the same effect.

The mysql query cache is has a certain amount of space and that space is shared between all the users that are on each database server. We do not privilege some users on a database server over all of the others.