How to resize InnoDB logs?

If for any reason you need to change the size of InnoDB log files (also known as transaction logs), but not sure how to do it, this post will guide you through the steps.

Step 1: Preflight checks

Something to keep in mind

Database restart is needed as part of this process.

Locate your MySQL configuration file

If you don’t know where the configuration file is, you can follow one of my previous posts on “How to find MySQL configuration file?”.

Find the existing logs and check their size

If database is running, you can simply use a tool called lsof:

db01 ~ # lsof -c mysqld | grep ib_logfile
mysqld    15153 mysql    9uW     REG                8,3 5242880 19350809 /var/lib/mysql/ib_logfile0
mysqld    15153 mysql   10uW     REG                8,3 5242880 19350810 /var/lib/mysql/ib_logfile1

lsof not only shows paths, but also the file sizes (marked in red).

Check InnoDB shutdown mode

Check the value of SHOW GLOBAL VARIABLES LIKE 'innodb_fast_shutdown'. The setting determines how InnoDB performs shutdown. If you are running on MySQL 5.0 or newer, it is a very important step, so do not forget about it.

innodb_fast_shutdown can be configured one of three different values:

  • 0 – InnoDB will clean up old and redundant data and perform insert buffer merge before shutting down.
  • 1 – A fast shutdown which skips the above tasks. It’s also the default one.
  • 2 – Performs a controlled “crash”.

If innodb_fast_shutdown is set either to 0 or 1, you can proceed to the next step. Otherwise change it:

mysql> SET GLOBAL innodb_fast_shutdown=1;
Query OK, 0 rows affected (0.00 sec)

Remember! You must not proceed with innodb_fast_shutdown set to 2!

Now you are ready to go.

Step 2: InnoDB log files resize procedure

Shut down MySQL
db01 ~ # /etc/init.d/mysql stop
 * Stopping mysql ...
 * Stopping mysqld (0)              [ ok ]

Check database error log to ensure that there were no problems with shut down. Specifically you are interested seeing the following sequence:

120403 13:47:04  InnoDB: Starting shutdown...
120403 13:47:06  InnoDB: Shutdown completed; log sequence number 1091449
120403 13:47:06 [Note] /usr/sbin/mysqld: Shutdown complete
Rename the existing transaction logs

For safety reasons you don’t want to remove the existing files at this point. If anything goes wrong, restoring them may be the only way to resurrect your database. So instead you should just rename them:

db01 ~ # find /var/lib/mysql -type f -name "ib_logfile?" -exec mv {} {}_OLD \;

In find specify the path where lsof showed the logs were. Verify that they were indeed renamed:

db01 ~ # ls -la /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5242880 Apr  3 01:24 /var/lib/mysql/ib_logfile0_OLD
-rw-rw---- 1 mysql mysql 5242880 Jan 31  2010 /var/lib/mysql/ib_logfile1_OLD
Reconfigure MySQL

Use your favorite editor to update the MySQL configuration file. Either add or set innodb_log_file_size parameter to the desired value. If you do not know what value to use, 64M is often a good default. Here is how it looks in my configuration file:

db01 ~ # grep innodb_log_file_size /etc/my.cnf
innodb_log_file_size = 64M
Restart MySQL instance

During start InnoDB will create new set of logs.

db01 ~ # /etc/init.d/mysql start
 * Starting mysql ...
 * Starting mysql (/etc/mysql/my.cnf)             [ ok ]

As usual, please monitor database error log. You should see output similar to this one:

[..]
120403  1:34:18  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
120403  1:34:19  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
[..]

Step 3: Done!

Your database should now be running on a new set of InnoDB logs.

[MySQL Health Check]
About Thomas

LAMP consultant with 12+ years of experience in online media industry. Enthusiast of modern web technologies, networking and databases.

Comments

  1. davet says:

    Thanks for this very precise procedure Thomas. I did exactly this on a Centos install with Mysql 5.5.22
    I took your warnings about shutdown mode seriously
    mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_fast_shutdown’;
    +———————-+——-+
    | Variable_name | Value |
    +———————-+——-+
    | innodb_fast_shutdown | 0 |
    +———————-+——-+
    1 row in set (0.00 sec)

    But after increasing the log file sizes successfully and restarting mysql I still get an error in the logs stating that mysql was not shut down properly and that a crash recovery had started – huhh? I can reproduce this behaviour every time!

    120623 11:01:35 [Note] Event Scheduler: Purging the queue. 0 events
    120623 11:01:35 InnoDB: Starting shutdown…
    120623 11:01:37 InnoDB: Shutdown completed; log sequence number 8031797432
    120623 11:01:37 [Note] /usr/sbin/mysqld: Shutdown complete

    120623 11:01:37 mysqld_safe mysqld from pid file /var/lib/mysql/wikijira.maptek.com.au.pid ended
    120623 11:03:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    120623 11:03:29 [Note] Plugin ‘FEDERATED’ is disabled.
    120623 11:03:29 InnoDB: The InnoDB memory heap is disabled
    120623 11:03:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    120623 11:03:29 InnoDB: Compressed tables use zlib 1.2.3
    120623 11:03:29 InnoDB: Using Linux native AIO
    120623 11:03:29 InnoDB: Initializing buffer pool, size = 384.0M
    120623 11:03:29 InnoDB: Completed initialization of buffer pool
    120623 11:03:29 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile0 size to 96 MB
    InnoDB: Database physically writes the file full: wait…
    120623 11:03:29 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile1 size to 96 MB
    InnoDB: Database physically writes the file full: wait…
    120623 11:03:29 InnoDB: highest supported file format is Barracuda.
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    120623 11:03:29 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    120623 11:03:29 InnoDB: Waiting for the background threads to start
    120623 11:03:30 InnoDB: 1.1.8 started; log sequence number 8031797772
    120623 11:03:30 [Note] Event Scheduler: Loaded 0 events
    120623 11:03:30 [Note] /usr/sbin/mysqld: ready for connections.
    Version: ’5.5.22-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)

    • Davet,

      InnoDB notices that something happened to the log files and that their contents is different than what some information inside data files suggest (the logs are clean after all), so it initiates a standard recovery procedure to try to fix these problems. There’s nothing to worry about. The log that you pasted is an example of how the MySQL restart after resizing the logs should look like.

  2. James Day says:

    For those using MySQL server 5.6 it’s now even easier: just change the size in my.cnf/my.ini and restart. If crash recovery is needed, that will be done, then the files will be changed to the new size.

    James Day, MySQL Senior Principal Support Engineer, Oracle

Speak Your Mind

*