Why we should not have the collective size of innodb_log_files more than the size of the innodb_buffer_pool

Question

Below is my understanding about the log file group.

Whenever innodb tables were inserted/updated those will be captured in innodb_log_buffer_size. If the innodb_log_buffer_size filled up or time has come to flush it to the disc, they will be written onto innodb_log_files which are maintained in a group and committed in the table space at the same time.

Now my question is that why we should not have the collective size of innodb_log_files more than the size of the innodb_buffer_pool and why should we write to these log files though we are flushing it to the disc...?

I couldnt able to get proper explanation on this over the web at least I could not able to understand. Please give me some better explanation.

Really thanks in advance. Regards, UDAY

Answer 1

Now my question is that why we should not have the collective size of innodb_log_files more than the size of the innodb_buffer_pool and why should we write to these log files though we are flushing it to the disc...?

--> I am not sure where it is mentioned like innodb_buffer_pool_size should always be more than this value innodb_log_files_in_group * innodb_log_file_size. Ofcourse! recommended size for innodb_buffer_pool_size should be 70-80% of available memory for Innodb-only installations, should be larger than default which is 8MB.

innodb_log_buffer_size a large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

innodb_log_file_size The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash. You should watch the Innodb_os_log_written variable over a period of several minutes, and estimate how many bytes per second are written to your InnoDB transaction logs. You can multiply this value by 3600 (an hour) and divide by innodb_log_files_in_group to choose a good log file size.

innodb_log_buffer_size uses internally to log to these log files in order to save Disc I/O.