Can we shrink InnoDB Buffer Pool?

In my previous article, I wrote how to answer the question: Is InnoDB Buffer Pool big enough? But the buffer pool can also be too big. So you may wonder if you can shrink it.

Photo: Piazza del Popolo
Piazza del Popolo seen from il Pincio, Rome.
Is it oversized?

Reasons to shrink the Buffer Pool

Why would one do such a thing? A common reason is saving resources. Saving memory can be very important. For example, with AWS you may need a more expensive EC2 instance to have more memory. As a general rule, for db class instances, “more expensive” means that both the on demand cost and the potential effective cost double (at the moment of this writing).

It’s also possible that you need more memory to allocate in other ways. Maybe you have many concurrent users, or they run many complex JOINs, and session buffers need more memory.

And don’t forget that a big buffer pool can be slow. Giving your server an impressive amount of memory to allocate a huge buffer pool could slow down all your reads and your writes.

How to check if InnoDB Buffer Pool is oversized

Here I describe how I check if InnoDB buffer pool is oversized. I’m sure there are other ways.

Whichever method we use, let’s keep in mind that the buffer pool should always have some free pages.

Let’s also take growth into account. If the amount of data you frequently read is getting bigger over time, we should be prepared to handle foreseeable loads in the near future.

Number of free pages

Innodb_buffer_pool_pages_free is the number of free pages. If it’s always a large number, the buffer pool is bigger than the amount of data we use.

It’s hard to give numbers that anyone can use, because every workload is different. Let’s say that having 10% of the buffer pool made of free pages is very prudent, keeping this percentage even bigger is probably useless.

Hits and misses ratio

Innodb_buffer_pool_read_requests is the number of reads. Innodb_buffer_pool_reads is the number of disk reads, that occur when the desired data is not found in the buffer pool. The ratio between these variables tells us if the buffer pool is efficient.

Trying to shrink the buffer pool could reduce this ratio. If we can’t afford this, and the buffer pool is not clearly oversized, let’s leave things as they are.

Counting accesses to pages

In am oversized buffer pool that is not as big as the whole dataset, all the most frequently pages stay in memory all the time. Other pages, depending on the running queries, may stay there forever, or be regularly evicted to make room for other pages that are not frequently read (and will therefore be evicted). But how to check if this is what happens?

The information_schema database contains a table called INNODB_BUFFER_PAGE. It provides a list of the pages present in the buffer pool, and each page can be uniquely identified. My basic idea is to check for how much time the pages stay in memory.

For this purpose I wrote a stored procedure. It is based on a Morgan Tocker’s idea. What my procedure does is:

  • Scan information_schema.INNODB_BUFFER_PAGE for a specified number of times, at a specified interval.
  • Write each found page id’s into a temporary table.
  • After all observations, return the number of pages by frequency. Example:
+--------------+-------------+----------+
| occurrencies | page_number | bytes    |
+--------------+-------------+----------+
| 3            | 34          | 557056   |
| 4            | 57          | 933888   |
| 5            | 562         | 9207808  |
| <null>       | 687         | 11255808 |
+--------------+-------------+----------+

In this example 34 pages were found 3 times, 4 pages were found 57 times and 5 pages were found 562 times. The total number of found pages is 687. Looking at the size in bytes helps us understand if a certain number of pages is relevant or not.

The procedure is the following:

-- Example:
-- CALL _.show_working_set_size(5, 3);
CREATE PROCEDURE show_working_set_size(
        IN in_observations_count INT UNSIGNED,
        IN in_observation_interval INT UNSIGNED
    )
    MODIFIES SQL DATA
BEGIN
    DROP TEMPORARY TABLE IF EXISTS innodb_used_pages;
    CREATE TEMPORARY TABLE innodb_used_pages (
        block_id INT UNSIGNED NOT NULL COMMENT 'Memory block id',
        pool_id INT UNSIGNED NOT NULL COMMENT 'Bupper pool instance id',
        occurrencies INT UNSIGNED NOT NULL COMMENT 'How many times the page was found in buffer pool',
        -- block_id is probably not unique across instances
        PRIMARY KEY (block_id, pool_id)
    )
        ENGINE MEMORY
        COMMENT 'Stats on pages found in the buffer pool'
    ;

    WHILE in_observations_count > 0 DO
        INSERT IGNORE INTO innodb_used_pages
            SELECT pool_id, block_id, 1 AS occurrencies
                FROM information_schema.INNODB_BUFFER_PAGE
                WHERE PAGE_STATE NOT IN ('NOT_USED', 'READY_FOR_USE')
                AND PAGE_TYPE NOT IN ('IBUF_FREE_LIST', 'TRX_SYSTEM', 'UNDO_LOG')
            ON DUPLICATE KEY UPDATE occurrencies := occurrencies + 1;
        DO SLEEP(in_observation_interval);
        SET in_observations_count = in_observations_count - 1;
    END WHILE;
   
    SELECT
            occurrencies,
            COUNT(*) AS page_number,
            COUNT(*) * @@innodb_page_size AS bytes
        FROM innodb_used_pages
        GROUP BY occurrencies WITH ROLLUP;
    DROP TEMPORARY TABLE innodb_used_pages;
END;

Notes:

  • The procedure works on both MySQL and MariaDB. I expect Morgan’s procedure to run on them both too.
  • Queries against information_schema.INNODB_BUFFER_PAGE cause contention. Don’t use this procedure on busy servers.
  • Morgan’s solution identifies the pages by (block_id). I believe this was correct at the time, but not now we have multiple buffer pool instances, so I identify them by (block_id, pool_id). I hope I’m correct; if not, I don’t think that the impact on the results is relevant.
  • I try to ignore irrelevant pages, but I don’t understand all values of PAGE_TYPE and PAGE_STATE.

Reference

MySQL:

MariaDB:

See also

Related articles:

Related courses:

Conclusions

An oversized InnoDB buffer pool is waste of resources, and possibly a performance problem. Not only it can be slower than necessary, but it takes memory that could be needed by other buffers.

We discussed the methods I use to check if a buffer pool is oversized. I recommend not to use the third by yourself if you are not a MySQL professional, because as mentioned it can cause contention, and probably you don’t know how to check that.

Checking the size of the free list and the ratio between memory reads and disk reads is part of my MySQL Health Checks. Consider using a health check if you have a doubt that you are wasting your memory. Also, if you find out that your buffer pool is oversized, in my experience this means that there are many other aspects to tune in your MySQL servers.

If you know more way to check if the buffer pool is oversized, please comment!

Toodle pip,
Federico

Comments (5)

  1. Did you consider doing some sampling, instead of checking the whole innodb_buffer_page? If, for a certain block_id the page_number has changed, then this page has been replaced. If we sample 1 out of 1000 blocks, we will reduce contention on the buffer_pool and we can still get a good approach.

    Should your procedure use page_number instead of block_id? You are checking if a page is in the pool. The block_id’s are constant unless you resize the buffer pool. And pool_id should also be considered (if you have multiple pools).

    1. Hi Pep, thanks a lot for your contribution!
      The page number is a good idea. I will test it.
      About sampling… The table doesn’t have indexes, so a WHERE will still read all rows. LIMIT would only read X rows, but 1) if they aren’t returned in consistent order, this won’t work 2) if they are, LIMIT X will only read the first blocks from the first buffer pool.
      Since you’re here, do you know if my filters on PAGE_STATE and PAGE_TYPE can be improved?
      Cheers.

      1. I made some tests this morning. I wrongly assumed that the implementation of innodb_buffer_page had some “intelligence” and thus searching for a block_id was not performing a full scan. Although this is not a normal heap table (just look at the results of explain format=json), searching for a block_id or page_number is not significantly faster than searching for any other field (I can be missing something because table implementation it is not documented)

        Things I found so far:
        – There are duplicated block_id’s for each pool_id. This means that the combination of pool_id and block_id is not a valid primary key to identify a page in the buffer pool.
        – Sampling (not retrieving all the innodb_buffer_page rows) can be faster, for example, block_id%10=1 was faster than processing the whole table, but I think this is related to heap table engine performance rather than information_schema performance.
        – Space identifies the tablespace, so to identify a disk page you need page_number and space.
        – Not sure what page types are relevant… I guess index and undo… but I would say that index is the relevant one.

        Now I’m wondering if the relation between old and not old pages can give also information regarding the size of the buffer pool for the current workload…

        1. Hey, thanks for sharing your further investigation.
          For now I will only comment about query optimisation.
          In MySQL 8, most information_schema tables are views on tables in the mysql schema. We cannot see the structure of that schema (a questionable design choice), but for EXPLAIN we can easily see that they have indexes. BUT, this is not true for INNODB_* tables.
          Regular MEMORY tables don’t have any optimisation for that kind of queries. information_schema could. The information we ask are retrieved in real time when it needs them, so I suppose that sampling reduces the amount of work to do. Sampling by modulus could be a good idea.
          How faster was it in your case? Did you try on a busy server?

  2. I did not test on a busy server. I made some irrelevant tests to check the impact of accessing the buffer pool while retrieving information from innodb_buffer_page. What I’ve found so far is that the sampling 1 out of 1000 reduces the time by half… but this is not a benchmark, just the test I run.

    I think that on a busy system with a large pool and lots of tables, some contention can happen due to:
    – The use of several buffer_pool related mutexes.
    – The use of a dictionary mutex for each page just to retrieve the table/index name.

    How this could be improved? I’m not a developer but my ideas/suggestions to database developers would be:
    – Do not re-generate the innodb_buffer_page each time it is queried, cache it for some time. This could be configurable.
    – Index it.
    – Create an innodb_buffer_page_raw table that doesn’t include the table_name/index_name to avoid the dict mutex.
    – Reduce the usage of buffer pool related mutexes. This could bring some inconsistencies to the contents of that table. But must of the information would be good enough.

    Things to check/alternatives:
    SET GLOBAL innodb_buffer_pool_dump_now=ON; and analyze the file generated. Probably this is not using any dictionary mutex. Need to check buffer_pool mutexes… I guess it holds mutexes to generate an in-memory structure, then releases them and after that dump the structure into a file.

    (UPDATE: Forget about that last idea, the documentation says: “This information is derived from the INNODB_BUFFER_PAGE_LRU INFORMATION_SCHEMA table.” so the same contention will happen.)

Leave a Reply

Your email address will not be published. Required fields are marked *