Wednesday, January 03, 2007

Increase innodb_log_file_size: the proper way

If you modify the innodb_log_file_size, MySQL will fail to restart and InnoDB will complain about the size of the changed log file.

The proper way to increase the innodb_log_file_size:
  1. shutdown mysql server
  2. make backup of data and log files
  3. remove InnoDB log files
  4. set new value for innodb_log_file_size in my.cnf
  5. start mysqld
  6. check error logs to ensure everything went fine.
Also see:
  1. Choosing proper innodb_log_file_size
  2. innodb_log_file_size (forum post by Jay Pipes)
  3. mysqld crashes when innodb_log_file_size is set greater than 4G
  4. InnoDB startup options

2 comments:

Vishal Thakkar said...

I am facing some issue in setting innodb_log_file_size parameter in MySQL server even after following the steps mentioned above.

When i tried increasing the innodb_log_file_size to 128M (1/4th of innodb_buffer_pool_size), it throws errors like incorrect imformation in file dbname.tablenane.frm files.

The current values of innodb variables in my.cnf are shown below:

innodb_log_buffer_size = 8M
innodb_log_file_size = 5M
innodb_buffer_pool_size = 512M

I came to know from some forums that innodb_log_buffer_size should never be greater than innodb_log_file_size which is currently true in case.

Can you please help me out on this?

Should i reset the value of innodb_log_buffer_size to its default 1MB and then change the innodb_log_file_size?

Please let us know your thoughts.

Thanks.

Anonymous said...

I dont understand why MySQL documentation doesnt say this, i thought the system made the change by itself. Thanks a lot!. ubuntu 10.10 64bit mysql 5.1.49