I have a Scenario to set inndb_log_file_size as some cron jobs and services are running on Master and Slaves(Select relevant Data From Slave and update it on Master) So in That Scenario If We Calculate
Innodb_log_file_size through the Following formula SHOW ENGINE INNODB STATUS\G; SELECT SLEEP(60); SHOW ENGINE INNODB STATUS\G; then it usually exceeds 9 GB on contrary its 2GB in my. cnf. On the Other hand, The inndb_buffer_pool_size is almost 80 GB. Some fox says that inndb_log_file_size should be 25 percent of inndb_buffer_pool_size.
What Does it impact on the slave? In slaves inndb_log_file_size is almost 268 Mb does it impact slave performance? What should be the slave size ? is it the same like master Does SQL?
Hello @tamoor_ali,
inndb_log_file_size should be large enough to handle approximately 1 hour’s worth of transactions. If this is too small, then InnoDB will experience micro-stalls while it aggressively flushes dirty pages to disk. Having it too large really only impacts crash recovery, if that should happen.
1 hour has been deemed “plenty of time” for InnoDB to flush everything, even on the busiest of systems.
That is the correct formula and it should be executed during the busiest time of your DB.
Update your my.cnf to match formula. Shut down mysql, remove the log files, then restart mysql. InnoDB will rebuild new ones with larger size.
Replica typically should match source. (slave and master are old terms that are no longer used) Run the same formula on replica and use that in replica my.cnf