Performance Zone is brought to you in partnership with:

I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most. Peter is a DZone MVB and is not an employee of DZone and has posted 266 posts at DZone. You can read more from them at their website. View Full User Profile

My Favorite Secret MySQL 5.6 Feature

09.06.2013
| 3278 views |
  • submit to reddit
This post comes from  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.



Published at DZone with permission of Peter Zaitsev, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)