I often work with large datasets (over 100GB), and so caching is of course extremely important.
My question is about how MySQL (or other similar DBMSs) handle large data sets. I assume most DBMSs use some caching mechanism such as LRU to reduce swapping pages from disk. But I want to know if MySQL has any "pre-fetching" or "pre-caching" option for when a database file is initially opened.
A caching mechanism like LRU is effective when a lot of pages are already cached in memory. But when you first open a file that isn't the case. Only after a significant number of accesses would a lot of pages be cached in memory, so I would imagine that when you initially open a large database, each record lookup will almost always require a disk access (i.e. there will be cache-misses almost 100% of the time because the file was just opened so there are no pages in the cache.)
I was wondering how MySQL (or other similar DBMSs) handles this, if at all. Is there an option to prefetch an even distribution of pages or something when the database is opened?