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.
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
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,
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: