How does MySQL manage its memory with regards to indexes?

To start off, the reason I'm asking this, is because I feel I have a database that - according to my own estimates - should have been killing the disks with massive I/O, because of indexes not fitting in memory, but in actuality it is still performing fine.

Let's start with the relevant table:

CREATE TABLE `search` (
  `a` bigint(20) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  `f` varchar(255) DEFAULT NULL,
  `g` varchar(255) DEFAULT NULL,
  `h` varchar(255) DEFAULT NULL,
  `i` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The a column is a 8 byte digit that has the timestamp (in seconds) encoded into it. The table has a PARTITION BY RANGE (a), that separates the table into monthly partitions. This is because we only keep 24 months in the database, and the rest is purged.

The table grows by approximately 200 million rows per month; the full table contains about 5 billion rows.

The server it runs on has about 360GB of memory, and 300GB of that is reserved for MySQL. What I find interesting is that some time ago, disk utilization started going up a bit. Now, I believe this is because of certain indexes no longer fitting into memory, causing MySQL to load them from the disk, but this is just a guess; I'm unfamiliar with the internals of MySQL.

Is there a way to see what pages/blocks are loaded into memory at a given time, or for a specific query?

These are the three tables being actually used:

``
CREATE TABLE search (
a bigint(20) unsigned NOT NULL,
b int(10) unsigned NOT NULL,
c int(10) unsigned DEFAULT NULL,
d int(10) unsigned DEFAULT NULL,
e varchar(255) DEFAULT NULL,
f varchar(255) DEFAULT NULL,
g varchar(255) DEFAULT NULL,
h varchar(255) DEFAULT NULL,
i varchar(255) DEFAULT NULL,
KEY a_idx (a),
KEY b_idx (b),
KEY c_idx (c, a),
KEY d_idx (d, a`),

What indexes? You have no indexes! So any query will scan the entire table -- all partitions. Once the entire table is bigger than innodb_buffer_pool_size, a table scan will not finish without having to hit the disk. And the next table scan will have reread everything from disk.

An index does not need to be kept in memory. It acts just like a table -- it is composed of 16KB blocks that are cached into the buffer pool as needed, then bumped out when 'old' (think "least-recently-used" caching schemes).

Again, if you do a full index scan, and the index won't fit in the buffer pool, then the cache will become useless and you will hit the disk all the time.

But... The proper definition, and use, of indexes does not have to end up with that fate. I have seen terabyte-sized tables work fine in 32GB of RAM. In particular a "point query" (... WHERE primary_key = constant ...) will take less than 1 second, regardless of how big the table is or how small the buffer_pool is. At worst (cold cache), a billion-row table might need to fetch 5 blocks in the BTree to find the single row you ask for.

PARTITION BY RANGE(id) is almost always useless. Instead, PRIMARY KEY(id), without partitioning, does a better job of locating a row by id.

There are tools for looking at what is in the buffer_pool, but I would hate to deal with 20 million block numbers to deal with what you are asking for!

Instead, let's see your actual SHOW CREATE TABLE (so we can see indexes/partitions) and a few SELECTs. From those we can discuss what is going on under the covers. This may be much faster and more informative.

See also my cookbook on creating optimal indexes.
See my partition blog for the limited utility of PARTITIONing.

StackExchange Database Administrators Q#179883, answer score: 5