Over a million developers have joined DZone.

My Favorite Secret MySQL 5.6 Feature

DZone's Guide to

My Favorite Secret MySQL 5.6 Feature

· Performance Zone ·
Free Resource

Sensu is an open source monitoring event pipeline. Try it today.

This post comes from Aurimas Mikalauskas at the MySQL Performance Blog.

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 fulltextsearch, 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 seven 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 the 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, since 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:

2013-08-20 13:57:37 5048 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*32768 pages, LSN=1626007
2013-08-20 13:57:37 5048 [Warning] InnoDB: Starting to delete and rewrite log files.
2013-08-20 13:57:37 5048 [Note] InnoDB: Setting log file ./ib_logfile101 size to 512 MB
InnoDB: Progress in MB: 100 200 300 400 500
2013-08-20 13:57:39 5048 [Note] InnoDB: Setting log file ./ib_logfile1 size to 512 MB
InnoDB: Progress in MB: 100 200 300 400 500
2013-08-20 13:57:41 5048 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2013-08-20 13:57:41 5048 [Warning] InnoDB: New log files created, LSN=1626007

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.

Sensu: workflow automation for monitoring. Learn more—download the whitepaper.


Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}