We have a database of about 100GB. However, most of this 100GB is "archived" data that we rarely access -- about 25GB is active data. We have innodb_buffer_pool_size set to 48G, which is typically plenty. However, everyday cPanel runs a backup that includes mysqldump of all our data -- and this fills up the buffer pool with data that is wasting RAM that could be used for something else.

I've read about using innodb_old_blocks_pct and innodb_old_blocks_time at https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-midpoint_insertion.html to help address this problem. My questions are:

(a) What innodb_old_blocks_pct and innodb_old_blocks_time settings are recommended to deal with this?

(b) Any other strategies to address this problem?


If physical memory usage is more important than performance during and afew minutes after backup, then you can decrease innodb_buffer_pool_size to something around 25GB.

If the performance is affected by backup and this is the problem you are trying to solve, you could use another instance of mysql with smaller memory footprint as a MySQL slave and run the backup on the slave.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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