July 29, 2014

Here’s my favorite secret MySQL 5.6 feature. What’s yours?

MySQL 5.6 has a great many new features, including, but certainly not limited to a number of performance improvements. However, besides the widely talked-about features such as InnoDB support for full text search, optimizer, performance schema improvements and GTID, there are also a few tiny improvements that nobody cared to mention.

One such feature is… …well, let me show you.

In the past almost-7-years that I’ve been with Percona, roughly 97.35% of my reports would include the following or similar instructions in them:

… in order to change the size of InnoDB transaction log files, you have to follow this procedure. Beware that if you don’t follow these instructions step-by-step, your server may not restart or rather it will start with InnoDB storage engine disabled and an error in the logs. So, here’s what you want to do:
1. change (or add) the following variable in my.cnf: innodb_log_file_size = XXXM
2. stop MySQL server
3. make sure you see a successful shutdown of InnoDB in the MySQL error log
4. move away old log files (at this point I have to double check where they are) by running the following command:
mv /var/lib/mysql/ib_logfile* /tmp/
5. start MySQL server – it should take a bit longer to start because it is going to be creating new transaction log files
6. When you’re positive that MySQL server has started successfully, you can remove the old log files:
rm /tmp/ib_logfile*

Occasionally, if I’m not careful enough picking the warning words, that would backfire with extra questions such as how safe this is, do they really need to do it etc. and yet in most cases increasing the size of transaction log is the best thing you can do for your InnoDB-backed MySQL server.

The Secret Feature

From now on, systems running MySQL 5.6 will make my life (and yours too) so much easier as the instructions can now be reduced to:

Change the innodb_log_file_size (which I will simply include together with other recommended changes) and restart MySQL server.

Automatic InnoDB transaction log file size change is how I would call it. You want to know how MySQL responds to this? Here’s how:

And it works both ways! Gotta love this simplicity. Tell me, what is your favorite MySQL 5.6 feature? Don’t worry, it doesn’t have to be secret. Unless you dare, of course.

About Aurimas Mikalauskas

Aurimas joined Percona in 2006, a few months after Peter and Vadim founded the company. His primary focus is on high performance, but he also specializes in full text search, high availability, content caching techniques and MySQL data recovery.

Comments

  1. Kenny Gryp says:

    I’m a Fan!

    I accidentally ran into this by accident this morning. I already did a clean shutdown and forgot to move the ib_logfile*s … but it still worked. :-)

  2. Fernando Mattera says:

    And how are you did it?

  3. Fernando Mattera says:

    Sorry, it was a typo. The real question is why still need to restart to change log configuration. This is prehistoric.

  4. You are welcome for the feature. In fact, this was one of the peculiarities of InnoDB that catched my eye shortly after I started working on it approximately 10 years ago. And I was not the only one. See for example http://bugs.mysql.com/bug.php?id=13494 from 2005.

    We made the log file size resizable in MySQL 5.6, because the old default redo log size was getting simply too small, causing unnecessarily frequent flushing of the buffer pool. There is a reason why the redo log file used to be small. The crash recovery speed has been improved a lot in 5.5 and 5.6. With older versions, applying the redo log would take much longer.

    The reason why we require a server restart for resizing the redo log is a bit tricky. In fact, we basically do the equivalent of a ‘clean shutdown’ at server startup, and delete the old redo log files, and finally create new files. This has to work even if the system is repeatedly killed and restarted during the operation.

    The log could theoretically be resized without restarting the server, but I believe that it would require a full log checkpoint (full flush of all dirty pages in the buffer pool) and quiescing all writing activity to quiesce (blocking any write operations). For a write-heavy system, this could mean a similar amount of downtime as a server restart.

  5. Indeed this is a wonderful new feature!

    One way it could go awry, however: if a site normally relies on a large log file size, but then the innodb_log_file_size line in the my.cnf is deleted or commented out. Then restart mysqld. The log file will resize to the default size, which may be inappropriately too small for the site.

    Lines do sometimes mysteriously disappear from my.cnf, due to unaccounted user errors.

  6. Stephane Combaudon says:

    @Bill: you’re right, but before 5.6, MySQL would even refuse to start in such a scenario, which is worse in my opinion.

  7. Baron says:

    Yes, I also appreciate this feature a lot.

  8. nate says:

    I was looking forward to trying out the read only mode in mysql 5.6 to read a database that was on a read only snapshot, didn’t get very far though, mysql kept crashing.. so had to resort to pulling the full data set to a writable volume which took some time.
    [..]
    2013-08-21 00:02:18 25286 [Note] InnoDB: Started in read only mode
    [..]
    InnoDB: Failing assertion: DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_TEMPORARY)
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
    InnoDB: If you get repeated assertion failures or crashes, even
    InnoDB: immediately after the mysqld startup, there may be
    InnoDB: corruption in the InnoDB tablespace. Please refer to
    InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
    InnoDB: about forcing recovery.
    00:02:22 UTC – mysqld got signal 6 ;

    It may of been caused by me trying to load a mysql 5.5 db with a 5.6 binary(was trying 5.6 specifically for the read only option), I didn’t have time to investigate further.

  9. @Stephane I disagree somewhat, refusing to restart would mean that you could catch that issue up front rather then having another layer of troubleshooting poor performance after the restart had implicitly resized the logs. Of course after restart (planned or unplanned) you should review the state of the error log for signs of issue.

  10. @Bill & @Andrew – when you think of it, this can happen to pretty much any variable. innodb_buffer_pool_size disappears – no good, innodb_file_per_table – no good, innodb_flush_log_at_trx_commit – no good, innodb_thread_concurrency – no good, well you get the point. We’re just so used to innodb_log_file_size requiring extra steps now that we can’t accept the bright future :))

  11. Stephane Combaudon says:

    @Andrew: I should have clarified a bit more.

    Before 5.5, MySQL would start with InnoDB disabled, which can lead to anything from weird application behavior to data corruption. And this may not be easy to diagnose. I’ve seen cases where restoring from a backup was the only solution to restart the server.

    I thought 5.5 behaved the same way but by experimenting, I’ve found that it is much safer: the whole server refuses to start, which as you said makes troubleshooting quite easy.

  12. Nate, if you upgrade to MySQL 5.6 without doing a slow shutdown in an earlier version first, you can get that kind of an an error during startup. I guess that the error has something to do with the crash recovery of “fast index creation” (introduced in the InnoDB Plugin) and “online ALTER TABLE” (introduced in MySQL 5.6).

Speak Your Mind

*