up vote 4 down vote favorite
1
share [fb]

So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:

mysql> show variables like 'innodb_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

When I went to change the innodb_log_file_size value (example my.cnf on mysql's innodb configuration page comments to change the log file size to 25% of the buffer size. So now my my.cnf looks like this:

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

When I restart the server, I get this error:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

So my question: Is it safe to delete the old log_files, or is there another method to change the innodb_log_file_size variable?

link|improve this question
80% accept rate
Just comment the innodb_log_file_size in my.ini ..... – Sobin Oct 20 at 13:24
hmm, why would I want to comment it out to use the default value when I'm trying to change it from the default value? – DTest Oct 20 at 13:32
feedback

2 Answers

up vote 4 down vote accepted

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

  1. service mysql stop
  2. rm -f /var/lib/mysql/ib_logfile[01]
  3. service mysql start

Step 3 will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5.) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0
link|improve this answer
2  
Thanks. Deleting files randomly makes me nervous! – DTest Feb 17 at 2:15
feedback
up vote 2 down vote

innodb_buffer_pool_size -- simply change my.cnf (my.ini) and restart mysqld.

innodb_log_file_size is less critical. Don't change it unless there is a reason to. Roland provided the steps, but one aspect worries me... I do not know if the first two steps are important; it seems like they could be:

  1. set innodb_fast_shutdown = OFF
  2. restart mysql
  3. stop mysql
  4. remove the logfiles
  5. start mysql

The log files keep track of unfinished business; "fast_shutdown" say to deal with that stuff after restarting. So removing the files may lose info?

link|improve this answer
+1 your concern seems to be supported by the docs – Jack Douglas♦ Oct 20 at 14:13
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.