patternphpMinor
Execute query cache on startup - Reasonable?
Viewed 0 times
queryreasonablecachestartupexecute
Problem
I have a rather database intensive application for a server which is intended to be turned off and on throughout the day. The issue comes at first use of the application after startup; it is slower than intended due to the large volumes of queries.
Once the query cache has taken ahold it is much more efficient and user experience pleasing. Obviously for the user experience I would like this throughout and not after the queries have been cached.
Is it reasonable, in terms of performance and error handling, to run a cron script (php) on startup to execute some queries so they can be cached or is there a method to save the mysql cache and load on the service startup?
Once the query cache has taken ahold it is much more efficient and user experience pleasing. Obviously for the user experience I would like this throughout and not after the queries have been cached.
Is it reasonable, in terms of performance and error handling, to run a cron script (php) on startup to execute some queries so they can be cached or is there a method to save the mysql cache and load on the service startup?
Solution
please refer to this link? this should answer your question
Yes, 100% reasonable to make use of the MySQL caching system!
How to "warm up" a cold DB server?
Warm-up is needed because the best performance of a database is
achieved when its various caches are populated with the most relevant
data, since reading from memory is generally substantially faster than
reading from disk.
InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS
cache. Then there's also the MySQL query cache. MySQL has the open
table cache. Some or all of these structures contribute to the ideal
performance of a server. When a server is handling traffic that is
characteristic of its workload, it tends to have the most frequently
used -- and therefore presumably the most relevant data -- already
cached.
Take the load off of an active MySQL server and you will not see it
start closing tables and evicting data from its caches. If you leave
it running, then come back to it tomorrow, you should find that it's
still as warm as you left it.
Yes, 100% reasonable to make use of the MySQL caching system!
How to "warm up" a cold DB server?
Warm-up is needed because the best performance of a database is
achieved when its various caches are populated with the most relevant
data, since reading from memory is generally substantially faster than
reading from disk.
InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS
cache. Then there's also the MySQL query cache. MySQL has the open
table cache. Some or all of these structures contribute to the ideal
performance of a server. When a server is handling traffic that is
characteristic of its workload, it tends to have the most frequently
used -- and therefore presumably the most relevant data -- already
cached.
Take the load off of an active MySQL server and you will not see it
start closing tables and evicting data from its caches. If you leave
it running, then come back to it tomorrow, you should find that it's
still as warm as you left it.
Context
StackExchange Database Administrators Q#126462, answer score: 2
Revisions (0)
No revisions yet.