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 have some tables that are used primarily to keep historical data, or that are only used once a day.

I do not want any operations on these tables to disrupt the more important tables present in the InnoDB buffer pool that get queried multiple times per second.

  • Should I just let the engine just do its thing and trust that it decides which tables are most important?
  • Is there a way to "pin" certain tables to the InnoDB cache?
  • Am I wasting my time?
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Any SELECT, INSERT, UPDATE, or DELETE performed against InnoDB tables will do all the following:

  • Add new pages to the InnoDB Buffer Pool
  • Generate dirty pages (via UPDATE) within the InnoDB Buffer Pool
  • Remove old pages from the InnoDB Buffer Pool that you may still need

You cannot dictate what tables will and will not affect the InnoDB Buffer Pool. However, you can stagnate the rotation of Buffer Pool pages at MySQL startup with these two options:

Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool).

Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

For further information, please see The InnoDB Buffer Pool section of MySQL Documentation

If you are not sure of the effects, it is best to leave it as is. Perhaps more server memory and a bigger InnoDB Buffer Pool may be the answer. Tweaking these settings may be overkill if you could just upgrade RAM.

share|improve this answer
add comment

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.