Take the 2-minute tour ×
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.

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes. So I moved these files to different location and restarted the MySQL server. It worked. Before shutting down the MySQL, server I also made innodb_fast_shutdown = 0.

But I have a doubt, after MySQL shutdown can we expect mysql is an consistent state and all the data that were in the ib_logfile0 and ib_logfile1 have been written to data files. So that I can delete these files or should I do something else also.

And if the answer is MySQL will be in consistent state than why these files contain some data even after graceful shutdown and start up with innodb_fast_shutdown = 0.

[EDIT details] giving the steps i have done

Steps i followed:

  1. SET GLOBAL innodb_fast_shutdown = 0
  2. /etc/init.d/mysqld stop
  3. rm -f /var/lib/mysql/ib_logfile0
  4. rm -f /var/lib/mysql/ib_logfile1
  5. /etc/init.d/mysqld start

Is the above step is fine? if yes then how come after the clean shutdown and start up the log files contains some data. Ideally it should be empty.

share|improve this question

3 Answers 3

up vote 4 down vote accepted

How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?

Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.

If you start MySQL up again, it will probably zip through the files to the latest Log Sequence Number and realise that there's no more work to be done (someone familiar with the source code that handles the innodb logs would be able to confirm/deny this).

Your steps should be absolutely fine, but I'd take Baron Schwartz's suggestion and move the ib_logfiles somewhere instead of deleting them, just in case it turns out that the server doesn't shut down cleanly for some reason.

share|improve this answer
    
when i set innodb_fast_shutdown =0 and did a shutdown and start mysql, i tried open the ib_logfile0 file and found it was not empty rather something in binary format was still present. Thats why i asked the question. please clarify. –  Trying May 6 '13 at 13:18
1  
Imagine that you have a piece of paper where you've written down a list of books that you want to read (in order), and then you go and read them all. If you decide to make a new list, it doesn't matter if you use the same paper or not... if you use the same piece of paper, you'll just look through your list and think "read that one, read that one..." until you get to one you haven't read. In the same way, the ib_logfile doesn't need to be zeroed out - the database can figure out what it's written and what it hasn't - but (if it was shut down properly) it also doesn't matter if it's not there. –  Nathan Jolly May 6 '13 at 14:09

The steps you outlined are correct.

I have written about this before

Doing innodb_fast_shutdown = 1 is incomplete because ibdata1 still have other transactional tidbits that will cleanup during the Crash Recovery phase of the next restart of mysqld. Some data changes may not make it.

Doing innodb_fast_shutdown = 0 is a complete flush of all InnoDB transactional changes in the Log Files and in ibdata1 (See Pictorial Representation).

Therefore, please do not change anything you have stated.

share|improve this answer

There is a small problem in your approach, if I understood correctly, you changed the log file size, and restarted MySQL with innodb_fast_shutdown = 0. The problem here is that you still have some transaction on log files that aren't in the ibdata. What you need to do is tell MySQL to write all changes from log files to ibdata before move the log files, see bellow

  1. Change innodb_fast_shutdown to 0
  2. Restart MySQL - it will make MySQL start with innodb_fast_shutdown = 0
  3. Stop MySQL - it will force MySQL to write all changes remaining on log files to ibdata
  4. Change the value for innodb_log_file_size and return innodb_fast_shutdown = 1
  5. Move old log files to some place else
  6. Start MySQL

Following these steps you will make sure all remaining changes will be wrote to ibdata before create the new log files.

share|improve this answer
1  
innodb_fast_shutdown is a dynamic variable, so you should be able to save yourself a restart and set it using set global innodb_fast_shutdown = 0;, then proceed with steps 3, 5, and 6. The MySQL documentation indicates that innodb_fast_shutdown = 1 should be fine, and it probably is, but it just feels like it should be set to 0. –  Nathan Jolly May 5 '13 at 15:57

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.