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)
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 !!
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.
stop slave;, flush tables;, SET GLOBAL innodb_buffer_pool_dump_now = 1;, and start slave;. That's just a light suggestion.
Commented
Dec 6, 2017 at 22:49
As of December 2017, unfortunately the answer to all three questions a), b) and c) is no.
One thing you can do is try to avoid that unnecessary data get loaded into the buffer pool. One way this could happen is when you backup a database with logical backup tools such as mysqldump and mydumper. So instead you could use Percona's xtrabackup or other physical backup tools, and/or you could tweak innodb_old_blocks_time and innodb_old_blocks_pct so as to minimize the impact of logical backups as well as full table scans. (See e.g. this for more details.)
It is almost always folly to try to do better than a simple LRU scheme that is in use in the buffer pool.
One thing you can do is to avoid or mitigate queries that tend to blow out the cache. Let's see your collection of queries, plus SHOW CREATE TABLE. The solution may be as simple as adding a composite index on a query that is unnecessarily doing a table scan. Or shrinking the datatype sizes so that a big table is not so big.
