Search code examples
mysqlcachinginnodb

How to direct MySQL not to cache a table in memory?

Lets say I have several InnoDB tables:

1. table_a 20Gb
2. table_b 10Gb
3. table_c 1Gb
4. table_d 0.5Gb

And a server with limited memory (8Gb)

I want fast access to table_c and table_d, and can allow slower access to table_a and table_b.

Is there a way to direct MySQL to cache c,d in memory, and NOT a,b?

(I'd move a,b to a different servers, but sometimes I require a join on a,c)

Solution

  • InnoDB doesn't have any option to direct certain tables to stay in memory and other tables to stay out of memory. But it's kind of unnecessary.

    InnoDB reads tables by loading them page-by-page into the buffer pool. Your usage of the tables guides InnoDB to keep pages in memory.

    Reading a page once in a while is unlikely to kick out pages that you need to stay in memory. InnoDB keeps an area of the buffer pool reserved for recently-accessed pages. There's an algorithm for "promoting" pages into this reserved area, and pages that aren't promoted tend to get kicked out first.

    Read details here: https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

    If you really need to ensure that certain tables are not cached in the InnoDB buffer pool, the only certain way is to alter the storage engine for those tables. Non-InnoDB tables (e.g. MyISAM) are never cached in the InnoDB buffer pool. But this is probably not a good enough reason to switch storage engine.