Just need a way to keep some tables from being able to be swapped out of memory once there. We have high update rates that cause the tables to be pushed from memory and by doing so, impacts performance.

I believe that the named buffer pools should merely be logical names, and at the low level there should be only a single buffer pool. At the data structure level, the buf_pool.LRU list could be partly replaced by one list per named buffer pool, so that we can easily evict all tables of a named pool, or move them to the traditional buf_pool.LRU list.

How would impossible constraints be dealt with?

• What if SET GLOBAL innodb_buffer_pool_size would shrink the buffer pool to be smaller than the size of the pinned tables?
• What if the pinned tables grow larger than the buffer pool?

It might be simplest to not completely prevent eviction of pages, but merely assign some priorities for eviction. Ideas for this would be welcome.

Certainly an 1114 type errors would not be wanted, but as with MAX_HEAP_TABLE_SIZE, there should be some way to measure how much (via show status and show variables), and a warning that goes into the MariaDB log or the SQL response to say that said pool is full and a table `ABC` had to be moved into the lower priority pool to maintain integrity would not be so bad. Or even a threshold being breached, say 80% full, generating warnings somewhere.

As an aside, one of the things that I have loved about MariaDB/MySQL was the whole concept of the storage engine. When I met the PostgreSQL guys at Linux world years ago and asked why they couldn't simply use the ENGINE tag to create a memory table, they quickly turned to creating memory file partitions and placing the tables in that storage partition. Was so sad. Anyway, a decade and a half ago now (2007).

I would hope that you could use a MariaDB'ism similar to ROW_FORMAT to bias the table towards the pinned pool or something. Just thinking out loud here... I would say just practice Keep It Simple Silly (KISS) adage, but I understand that it may not be in the end.

Right, we would probably not want any errors like ER_RECORD_FILE_FULL (1114) to be returned.

Would the following work?

• Introduce a parameter such as innodb_buffer_pool_pinned_pct for defining the share of the buffer pool that is reserved for pinned tables.
• Introduce some interface for declaring tables or individual indexes as pinned. One way would be HA_TOPTION_BOOL or HA_IOPTION_BOOL.
• All pinned tables or indexes would be equal. When pages in the buffer pool need to be replaced, pinned pages will be exempt from eviction as long as the pinned pages occupy less of the buffer pool than the specified limit.

The InnoDB implementation might redefine buf_page_t::state() so that pinned pages can be identified. I do not have a good idea for an efficient data structure or algorithm for keeping track of the number of share of pinned pages. A worst-case-inefficient algorithm could be to run a second scan of buf_pool.LRU when all eligible victims in the first scan are pinned.

Another idea: Could the desired effect be achieved by having a SET SESSION parameter that would specify an interface like nice(1) or ionice(1) for a rarely executed low-priority query that would tend to pollute the buffer pool? The impact of that would be that the “recently used” status of pages accessed would not change, and any pages that had to be loaded into the buffer pool would count as “not accessed” and not trigger any read-ahead. This could of course be implemented independently of the “pinning” feature.

Certainly status reporting would be important like total size, percent utilized and percent used by each table, maybe an info schema table for pinned table details. Then a simple way to dynamically add or remove tables from the pinned pool.

Movement to and from the pinned pool should be dynamic and in background as a result of an alter (assuming it's an alter).