patternsqlMinor
Mysql: Tell InnoDB to keep a specific table or database out of pool/cache?
Viewed 0 times
innodbkeeptelldatabasemysqlcachespecificpoolouttable
Problem
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.
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?
Solution
Any SELECT, INSERT, UPDATE, or DELETE performed against InnoDB tables will do all the following:
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.
- 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:
- innodb_old_blocks_pct
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).
- innodb_old_blocks_time
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.
Context
StackExchange Database Administrators Q#21701, answer score: 2
Revisions (0)
No revisions yet.