Mysql – Control InnoDB buffer pool allocation in MySQL (5.7+)

indexinnodbMySQL

I have some large tables which causes to fill the InnoDB buffer pool and removing other data.

Is there any way how can I control the InnoDB buffer allocation in one shared database?

Like:
a) Limit the buffer size for one table
b) Limit the buffer size for data (so indexes will remain cached)
c) Allocate a buffer exclusively for one particular table or even for one particular table index (or for all indexes in one table)

Best Answer

I am sorry to bring this up but only MyISAM does what you asking for when it comes to caching indexes for a specific table. I have written about this before. At this time, using MyISAM is like MySQL sacrilege, but if you are that desparate to cache indexes independently and your tables are read only, you can try converting them to MyISAM and setting up dedicated keycaches. CAVEAT : YOU DIDN'T HEAR THIS FROM ME !!

BACK TO REALITY...

Perhaps you should consider taking snapshots of the Buffer Pool.

Whenever you get the InnoDB Buffer Pool into the state you want, run this

SET GLOBAL innodb_buffer_pool_dump_now = 1;

This will generate a file called ib_buffer_pool (usually located in datadir), containing a map of all tablespace_ids that are in the Buffer Pool at that moment. You could copy this file to some folder and retrieve it later.

You could reload ib_buffer_pool by running

SET GLOBAL innodb_buffer_pool_load_now = 1;

This will copy all .ibd pages (data and indexes) back into the InnoDB Buffer Pool.

You should consider taking a snapshot of the buffer pool before running a backup and reloading the buffer pool afterwards.

For more information, please read the MySQL Documentation on innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.

Related Solutions

Mysql – “SQL_NO_CACHE” for innodb buffer pool

Months later I stumbled across the effective solution to what I was seeking in my question.

From the docs itself there are two variables to help prevent the trashing of the buffer pool for large table scans such that would occur during a checksum or mysqldump.

The first is innodb_old_blocks_pct (default 37%). This the sublist of pages that are eligible for eviction when a new page is inserted. When a new page is first read it is added to the head of this list.

innodb_old_blocks_time (default 0) defines the number of milliseconds after being added must elapse before an access that cause it to move out of the old list to the head of the new list. Since the default is 0 the first access puts it to the head of the new list.

So in short set innodb_old_blocks_time to a nonzero value, say 500, and then a table scan will basically just cause the old sublist to continually get cycled through. Pages getting read for other purposes will remain in the new list allowing your DB to remain warmed with real application relevant data!

Mysql – How much memory do I need for innodb buffer pool

If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

This will produce exactly what you need to set innodb_buffer_pool_size in /etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBB

Recently, I answered another question like this in the DBA StackExchange.

Best Answer

BACK TO REALITY...

Related Solutions

Mysql – “SQL_NO_CACHE” for innodb buffer pool

Mysql – How much memory do I need for innodb buffer pool

Related Question