I have a server where the MySQL indexes won't fit into memory (2TB total index, 512GB RAM on machine), and am wondering how MySQL decides what parts to put into memory and what not.

The relevant tables are range partitioned on ID, and I'm told (but can't find anything proving this) that the last 3 months is available in memory.

I'm hoping there's a more substantial answer available somewhere.

  • Assuming you are using InnoDB, see The InnoDB Buffer Pool. Generally speaking, frequently accessed (parts of) data and indexes stay in the cache. Partitioning by ID will probably not help you to seperate old and new data. Also make sure your configuration fits your server specs. If you never heard about the buffer pool and set up the server, you probably didn't change the default values. The mysql default values are made for servers with 500MB ram. If you have 1000 times that much, you might be missing some potential here.
    – Solarflare
    Feb 15, 2017 at 12:57

2 Answers 2


InnoDB's "buffer_pool" contains blocks that are kept there in (roughly) a "least recently used" order. That is, when you touch a block for either reading or writing, it is pulled into the buffer_pool and marked as "most recently used". If there was not room for this block, then the "least recently used" block was first removed from the buffer_pool.

PARTITIONing has nothing to do with the caching scheme. In particular, partitioning by the PRIMARY KEY is almost always useless, and almost always redundant with letting the caching (described above) happen.

If you would like to discuss performance improvements, please provide SHOW CREATE TABLE and the slow query.

Using PARTITION BY RANGE(TO_DAYS(...)) is an excellent use of partitioning, but only because it makes dropping "old" data very efficient. Note: I did not say BY RANGE(id).

Even if the math is correct, you may or may not have "the last 3 months" cached in RAM -- if someone scanned stuff from 4 months ago, someblocks would have to be bump to allow for that scan. Then the 4-month-old bllcks would clutter the buffer_pool until they get bumped out.


Since you have a "mission critical" system that must accept writes all the time, and have no PRIMARY KEY, you need some drastic action. Here's what I would do.

I would back up into the code. If there is not already code between the "users" and the database, I would start by building such a layer and force the "users" to call an API that say "write ... to the database".

To start with, the API would simply do an INSERT like the original code. This has minimal impact.

Then I would prepare new code, wherein the API has more complex code behind it. In particular, it would be writing to the existing table and the new table (with PRIMARY KEY, new index(es), changed datatypes, PARTITIONing, etc).

Meanwhile, another script would be copying, in chunks, the existing data from the existing table to the new table. (This is part of what pt-online-schema-change does, but we will take some liberties.)

I suggest chunk size of 1000 rows, each chunk as a its own 'transaction' (assuming you are a using InnoDB). The chunking should follow some index, whichever one closes to being unique. (An yes/no column would be poor because you could not get more than 2 chunks.)

When all finished, you will have two copies of the data; you can test before dropping the old table.

Reads can proceed unchanged during the process because the old table remains intact.

Some blogs with tips:

  • Interesting. Does the buffer pool contain both data and indexes? And if so, does MySQL use the same LRU algorithm to decide what subtrees of the indexes to put into the pool? I have a rather strange (in relational database terms) table, with the id column being a bigint containing the timestamp (and some other values) bit shifted into it, to partition by. The table has no primary key (only indexes, not even unique), which in hindsight appears to be a horrible mistake, as we now have a huge GEN_CLUST_INDEX.
    – Aeveus
    Feb 16, 2017 at 7:00
  • 1
    Both data and index - yes. In fact the data's BTree and a secondary index's BTree look virtually identical. Each is a collection of 16KB blocks cached via the buffer_pool on an "equal" basis. To the buffer_pool and LRU, it's just a bunch of blocks.
    – Rick James
    Feb 16, 2017 at 16:44
  • @Aeveus - you did a good job of separating this question from this; I am only now noticing that both belong to the same Asker.
    – Rick James
    Feb 16, 2017 at 16:48
  • There appear to be quite some issues with this table, and at first I wasn't sure what was causing what problem. I'm sure many issues remain, but being able to slowly tackle them helps.
    – Aeveus
    Feb 16, 2017 at 16:51
  • Take a look at pt-online-schema-change. It is a tool for modifying a large table without a long downtime.
    – Rick James
    Feb 16, 2017 at 16:55

MySQL does not load the data to memory while storing. It will load the result set of the query to Query cache only when query issued. If the same query is issued again, it will fetch from Query cache. This is applicable for both partitioned or non-partitioned tables.

  • If a table is written to a lot, the Query cache is a burden and should be turned off.
    – Rick James
    Feb 17, 2017 at 22:22
  • Virtually all writes are of the form of read-modify-write, leaving the block in memory. That involves caches other than the Query cache.
    – Rick James
    Feb 17, 2017 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.