0

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)

3

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.

  • This is interesting. Is the buffer saving/reloading guaranteed to be ACID? – Mvorisek Dec 6 '17 at 22:43
  • I am not sure. The Buffer Pool is a like a living, breathing entity (See i.stack.imgur.com/9EcRi.jpg) – RolandoMySQLDBA Dec 6 '17 at 22:44
  • If you do this copying of the Buffer Pool map on a Slave, you could stop slave;, flush tables;, SET GLOBAL innodb_buffer_pool_dump_now = 1;, and start slave;. That's just a light suggestion. – RolandoMySQLDBA Dec 6 '17 at 22:49
  • This is actually single DB system. If I would have slaves, I can run the the problematic queries on 2nd system. – Mvorisek Dec 6 '17 at 23:11
2

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.)

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.