Here’s my procedure for changing innodb_log_file_size:
- SET GLOBAL innodb_fast_shutdown = false;
- SET GLOBAL innodb_max_dirty_pages_pct = 0;
- /sbin/service mysqld restart
- /sbin/service mysqld stop
- rm -f /var/lib/mysql/ib_logfile[01]
- Edit /etc/my.cnf and set new value for innodb_log_file_size
- /sbin/service mysqld start
Here’s a handy sql command to determine a good log file size:
|
1 |
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G |
then do this:
|
1 |
mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min; |
to get the MB per minute. Set the log file size to be large enough to hold one hour of logs.
See http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ for the full discussion.