How does MySQL manage its memory with regards to indexes?

Question

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`),
  KEY `e_idx` (`e`, `a`),
  KEY `f_idx` (`f`, `a`),
  KEY `g_idx` (`g`, `a`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `channels` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `clients` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_hash` varchar(4095) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `hash_idx` (`client_hash`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

These are the queries that are currently running:

SELECT      S.a,
            S.b,
            S.e,
            S.f,
            S.g,
            S.h,
            S.i,
            C1.client_hash,
            C2.name
FROM        search S
LEFT JOIN   clients C1
ON          S.c = C1.id
LEFT JOIN   channels C2 
ON          S.d = C2.id
WHERE       S.e = "foo"
AND         S.a >= 6409642363135721472
AND         S.a <= 6443039964404908032
AND         S.b >= 1492361157
AND         S.b <= 1500137142
ORDER BY    S.a DESC
LIMIT       50

SELECT      S.a,
            S.b,
            S.e,
            S.f,
            S.g,
            S.h,
            S.i,
            C1.client_hash,
            C2.name
FROM        search S
LEFT JOIN   clients C1
ON          S.c = C1.id
LEFT JOIN   channels C2 
ON          S.d = C2.id
WHERE       S.f = "bar"
AND         S.a >= 6409642363135721472
AND         S.b >= 1492361157
ORDER BY    S.a DESC
LIMIT       50

SELECT      S.a,
            S.b,
            S.e,
            S.f,
            S.g,
            S.h,
            S.i,
            C1.client_hash,
            C2.name
FROM        search S
LEFT JOIN   clients C1
ON          S.c = C1.id
LEFT JOIN   channels C2 
ON          S.d = C2.id
WHERE       S.g = "baz"
AND         S.a >= 6409642363135721472
AND         S.b >= 1492361157
ORDER BY    S.a DESC
LIMIT       50

SELECT      S.a,
            S.b,
            S.e,
            S.f,
            S.g,
            S.h,
            S.i,
            C1.client_hash,
            C2.name
FROM        search S
LEFT JOIN   clients C1
ON          S.c = C1.id
LEFT JOIN   channels C2 
ON          S.d = C2.id
WHERE       S.g LIKE "baz%"
AND         S.a >= 6409642363135721472
AND         S.b >= 1492361157
ORDER BY    S.a DESC
LIMIT       50

Answer 1

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.

Answer 2

(My previous answer still applies, but it was written before the INDEXes and SELECTs were available.)

Optimal indexes

All 4 queries look like this, correct?

SELECT  S.a, S.b, S.e, S.f, S.g, S.h, S.i, C1.client_hash, C2.name
    FROM  search S
    LEFT JOIN  clients  C1  ON S.c = C1.id
    LEFT JOIN  channels C2  ON S.d = C2.id
    WHERE  S.<some-column> = "..."   -- or LIKE
      AND  S.a >= 6409642363135721472
      AND  S.b ... (some range)
    ORDER BY  S.a DESC
    LIMIT  50 

where is (at least) e,f,g.

I see these as being the only useful indexes for S:

INDEX(e, a)
INDEX(f, a)
INDEX(g, a)

When comparing e/f/g against a constant, all of these are handled by INDEX(g,a):

WHERE S.g = "baz"
  AND S.a >= constant
ORDER BY S.a
LIMIT 50

The test S.b >= constant will cause it to extend beyond 50 rows, but hopefully not the entire table? At least the filesort is avoided.

LIKE won't work as well

For S.g LIKE "baz%", any of the following 3 indexes may be useful. The Optimizer might pick the best based on estimates of how few rows each AND clause needs.

INDEX(g, a) -- already asked for this; it will use only the `g` part
INDEX(a) -- hoping to get `S.a >= constant ORDER BY S.a LIMIT`
INDEX(b) -- in case it filters well (but not if partitioned by b)

So, I recommend 5 indexes.

Cut back to 50

Because of the LIMIT 50, I would make the following change. The rationale is that the ramp up to doing the ORDER BY .. LIMIT might have to gather a lot more than 50 rows. In doing so, it would be doing a lot more than 50 JOINs to clients and channels. So this reformulation limits those lookups to 50:

SELECT  S.a, S.b, S.e, S.f, S.g, S.h, S.i,
        ( SELECT client_hash FROM clients WHERE id = S.c ) AS client_hash,
        ( SELECT name       FROM channels WHERE id = S.d ) AS channel_name
    FROM  search S
    WHERE  S.<some-column> =/LIKE ...
      AND  S.a .. some range
      AND  S.b .. some range
    ORDER BY  S.a DESC
    LIMIT  50 

Note that the LEFT JOINs turned into subqueries. The results should be identical.

PARTITION

You have a 2- or 3-dimensional problem (ranges on a and b and possibly g (when LIKE)). 2D is one of the rare use cases for PARTITIONing. Now for the question of whether it applies for your queries.

Here's my best guess, based on very little knowledge of your data set:

PARTITION BY RANGE(b)

and have 20-50 partitions. The hope is that the range test on b would limit the desired data to one (or very few) partitions, thereby leading to less work.

You asked about PARTITION BY RANGE(id), yet I still see no id in the table. Do you have any unique column (or combination of columns)? Do you have a PRIMARY KEY? Please answer these; I may have a helpful tip on how to make use of the PK for clustering of the data.

(I may revise my index recommendations if partitioning we go with partitioning.)

Since a or b is redundant

Assuming you keep a but remove b,

WHERE  S.<some-column> =/LIKE ...
  AND  S.a .. some range
  AND  S.b .. some range
ORDER BY  S.a DESC

should become

WHERE  S.<some-column> =/LIKE ...
  AND  S.a .. some range
ORDER BY  S.a DESC

and INDEX(b) goes away. That leaves 4 indexes needed for the queries provided.

I recommend making those changes, then reassess whether the LIKE query performs well enough, and whether any other queries need to be brought into the discussion. That is, no PARTITIONing until we see if it is worth adding.

More questions relevant to partitioning: Are new rows being continually added? Are old timestamps being DELETEd?

Which is more selective? S.g LIKE "baz%"? Or S.a >= 6409642363135721472?