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

This would also make it possible for proxies like MaxScale to provide a QoS type of feature where some queries are prioritized lower than others by prepending something like SET STATEMENT innodb_skip_lru=ON to the SQL string. If MDEV-33724 was to be implemeted that doesn't add too much overhead, I'd imagine that queries which "pollute" the buffer pool could be automatically detected and then later on prepended with the SQL that skips putting them at the head of the LRU list.

One more idea that I just touched in a discussion with monty is that when executing a low-priority table scan, we might skip any buffer pool allocation and simply read the data pages into a dedicated buffer that would be reused over and over, without involving any buf_pool.mutex. I think that we must still consult buf_pool.page_hash to see if the page of interest resides in the buffer pool already. If not, we will be able to find the most recent data in the file system. This is also somewhat related to MDEV-11378 and MDEV-32067.

I think that anything that involves modifying data needs to keep using the normal buffer pool and buf_flush_page_cleaner() for the write-back.

Once again: prodiving a way to distinguish low-priority or bulk queries is an alternative way to improve the chances that high-priority smaller tables will remain in the buffer pool.