My database size is approximately equal to or a little bit above the physical ram size available(500M to 1Gb). I use InnoDB tables. Performance is a critical aspect for the application i build above this database so the idea to keep most of the db blocks in the cache is quite attractive.
Is there a possibility to quickly load most of the blocks into innodb cache(or just perform full scan?) so that the sistem obtains high performance from the start?
Are there any recommendations for the systems like that - when data persistence is important, but also enough ram available and select queries performance is critical?
How often do you actually restart your DBMS?
Normally you do it once in a blue moon and at those times you can live with a few seconds of delay for the first queries.
After the cache lives it’s own life anyway.
And even if you do a select * … to try to populate the cache do you know which data that are needed in the cache?
Normally you just leave this up to the DBMS to handle.
Guidelines for performance (most important first):
Make sure that you are writing queries so that indexes can be used.
Make sure that those indexes do exist.
RAM (need I say more ).
If your application is querying the same queries all the time and you have little changes to the data, then you can get a dramatic performance increase by using the query_cache.
