Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 22 down vote favorite
13

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?

share|improve this question
Just comment the innodb_log_file_size in my.ini ..... – user3988 Oct 20 '11 at 13:24
1  
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? – Derek Downey Oct 20 '11 at 13:32

3 Answers

up vote 19 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. mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
  2. service mysql stop
  3. rm -f /var/lib/mysql/ib_logfile[01]
  4. 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 about 1 hour before shutdown:

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

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

share|improve this answer
4  
Thanks. Deleting files randomly makes me nervous! – Derek Downey Feb 17 '11 at 2:15
thanks above solution perfectly worked for me – user11107 Aug 25 '12 at 4:28
Nice answer and the update is great as well. My only suggestion would be to COPY the ib_logfiles to another location in case something goes wrong. This will help you get an idea for how to size the files : mysqlperformanceblog.com/2011/07/09/… – Justin Nov 21 '12 at 19:00
2  
Worked for me as well, BUT: linux console UI can be misleading - mysqld startup takes a lot of time if you set a big log file size (several hundred MB or more). Console UI is showing you dots and then shows "failed!", but in fact MySQL is still starting up. Wait and keep reading the log file (or monitor the log file with "tail -f [log-file]") until you see "mysqld: ready for connections." and both log files allocated on disk. – f055 Dec 6 '12 at 13:55
up vote 8 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; "innodb_fast_shutdown" says to deal with that stuff after restarting. So removing the files may lose info?

share|improve this answer
+1 your concern seems to be supported by the docs – Jack Douglas♦ Oct 20 '11 at 14:13
I took a look at this answer and I like the first line. I usually had clients bring mysql down and up with --skip-networking as a precaution to get those last-minute changes out the way. Your first line (set innodb_fast_shutdown = OFF) eliminates that. +1 !!! – RolandoMySQLDBA Nov 30 '12 at 22:27
up vote -2 down vote

chown mysql:mysql -R /etc/mysql /var/lib/mysql && cd /var/lib/mysql && rm -f ib_logfile* && service mysql restart || service mysql restart

Try it, guaranteed to be working [tested on Debian 6]

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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