How to force data to stay in memory

I have some data where it makes a huge difference if it is in memory or not. seconds for a query vs minutes. However the queries are quite rare, so the automatic caching does not work well. The data is normally already evicted from memory in favor of more frequently used data.

Is there some way in MySQL to tell the engine to keep an entire table in memory so that even the rare queries are fast?

E.g. let's say my server has 1TB of RAM in total and that table has 300GB. I would like to make sure that 300GB of memory are always reserved for that table and the remaining 700GB are handled the normal way and shared by all other data.

I have only found the storage engine memory as a solution, but I also need persistence. So I am looking for a solution with innoDB

Please no suggestions about indexes or any other optimizations so I don't need to keep the whole table in memory. I am looking specifically for an answer to my scenario mentioned above.

There is no way to force InnoDB keep a certain set of pages in the buffer pool. InnoDB exercises its least-recently-used (LRU) algorithm and evicts pages that haven't been used for a while, if it needs to assign the memory space to some other currently-used page.

You could run a "warm up" query before your important query, to try to prime the buffer pool with pages you need for the important query, but you risk having those pages evicted quickly, because InnoDB has a modified LRU such that it treats pages that have only been accessed once from being less sticky as pages that are accessed repeatedly. This is to prevent a single table-scan (e.g. mysqldump) from evicting all the pages you want to keep in your buffer pool. This is documented here: https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html.

I think your options are:

• Add more RAM to your server and increase the buffer pool until the working set of pages in memory includes the pages you want. This might be impractical, if your data is greatly larger than any physical RAM you can afford or install.
• Use the MEMORY storage engine, and store a copy of your data. Store the original, persisted data in InnoDB. You will have all the usual challenges with keeping a cache updated.
• Run a separate instance of MySQL Server with its own InnoDB buffer pool, and store in that instance only the data you need for your important query. So the pages you want in memory aren't competing against other pages in the LRU.
• Use the same MySQL Server instance, but store the data you need for your important query in a different storage engine, for example MyRocks. Each storage engine has its own memory allocation, separate from the InnoDB buffer pool.
• Use a cache for the result of your query. For example, a proxy solution like ProxySQL (https://proxysql.com/documentation/query-cache/) or Readyset (https://cloud.readyset.io/docs/cloud/cache) can cache query results transparently. Or you could cache results manually by storing them in a third-party tool like Valkey or Redis or Memcached.