How to manually build mysql cache

Question

I have a table of over 150,000 rows of which most would be updated daily. I have mysql caching turned on so the pages load faster however everytime the database is updated the pages load slow again, which I assume is the cache building itself again.

So at the moment I have resorted to doing a wget -m --delete-after http://localhost/ on the server however this takes about 4 hours to complete and moves something like 13 gig.

Is there a better way to do this?

Answer 1

150,000 rows is really small in terms of table size.

Is your table properly indexed?

How are you doing the update, are you scheduling and/or throttling the updates or just letting it go all at once?

The slowdown is most likely happening because you are trying to update too many rows at once w/o letting the server catch a breath. Try throttling the updates.

Answer 2

MySQL cache works differently depending on whether you use the MyISAM storage engine or the InnoDB storage engine.

MyISAM caches indexes only, not data. You can use LOAD INDEX INTO CACHE to preload MyISAM indexes into the key buffer cache. But there is no equivalent statement if you use InnoDB.

InnoDB caches both data and index pages. There's no specialized command to warm up the cache buffers, but you can execute a few SQL statements that do full table-scans and full index-scans to load them into the buffers. You should be able to do this using a script on the server, without resorting to wget!

I agree with the answer from @code_burgar: 150k rows is small enough that you shouldn't notice much performance penalty while the cache is warming up.

If you're talking about warming up the Query Cache, that's a different issue. You'll have to warm up the Query Cache using specific SQL queries, since that cache keeps result sets associated with those SQL queries verbatim. Your wget solution is inefficient and probably duplicates a lot of work. You should be able to prime the Query Cache by running a script on the server that executes each query that you want to cache once.

But you may need to do a code review to figure out what those queries are, and periodically update your cache preload script if your code changes.