Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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!

share|improve this answer
    
Brilliant solution and a hearty +1 !!! –  RolandoMySQLDBA Apr 3 '12 at 19:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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