0

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

Uday
  • 1,480
  • 4
  • 27
  • 44

1 Answers1

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.

Mahesh Patil
  • 1,529
  • 1
  • 11
  • 22
  • 1
    You can refer to this link for how InnoDB flush method works: 1) http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_method 2) http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance-adaptive_flushing.html read inline InnoDB must flush to disk all dirty buffer pool pages whose redo entries are contained in that portion of the log file. When required, InnoDB performs a so-called “sharp checkpoint” by flushing the appropriate dirty pages to make space available in the log file. – Mahesh Patil Mar 30 '12 at 11:51