"SQL_NO_CACHE" for innodb buffer pool

Question

I'm guessing the answer is no, but I was wondering if there was anyway to tell innodb to not store fetched pages in the buffer pool?

The reason for looking at doing this is check summing tables. I'd like to minimize the effects of trashing the useful cached data.

Answer 1

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!

• RolandoMySQLDBA: Brilliant solution and a hearty +1 !!!
• mvorisek: In MySQL 5.7 innodb_old_blocks_time defaults to 1000