Thoughts on Upcoming MySQL 5.6 Defaults

During Oracle Open World 2012 and the parallel MySQL Connect conference, the new 5.6 version was announced. It’s only release candidate right now, but that means the GA release is just around the corner.

With that James Day has posted changes to various of the new parameter defaults. Many of them you may not run into on day-to-day production systems. However there are a few which I do see a lot.

Welcome changes to defaults

innodb_file_per_table = 1 (formerly 0 or off)

This is a parameter which really needs to be set on most systems. It tells InnoDB not to use just one large tablespace for all tables, but rather to create an individual tablespace for each InnoDB table. If you come from the Oracle world you might wonder why this is necessary, but due to the evolution of InnoDB itself, this having lots of individual tablespaces helps a lot with concurrency. And that’s something you want for scalability of those web applications.

What made the default really troublesome before is that if you forgot to set it, all your existing tables would be created in the single large tablespace. That would leave you only one option. Set the parameter, and then REBUILD all those previously created tables. The syntax to do rebuild wasn’t difficult, but the load it would generate on your systems surely was.

innodb_log_file_size = 48m (formerly 5m)

Here’s another parameter that didn’t have a great default previously. The more transactional activity you have in your database, the quicker these files fill up. As one fills up the database must switch to the next. You want to keep this switching to a minimum just like switching redologs in Oracle. By resizing these files larger, you make your database faster.

With the previous default of 5m, one had to jump through a number of hoops to remedy the default. First you would stop the database, then delete or move the files, then change the parameter in the my.cnf file and start MySQL again. If you started just by changing the value in my.cnf, you’d get various errors and the database wouldn’t start. So all and all a larger default avoids all these problems. Bravo!

Possibly unwelcome changes to defaults

sync_master_info=10000 (formerly 0)
sync_relay_log=10000 (formerly 0)
sync_relay_log_info=10000 (formerly 0)

I first discovered these three parameters reading the most recent printing of High Performance MySQL by Baron Schwartz, Peter Zaitsev & Vadim Tkachenko. I highly recommend the book if you haven’t picked up a copy. It is an in-depth technical tomb of MySQL information, a real tour-de-force that every database administrator should own.

I mention these parameters elsewhere in my article on bulletproofing MySQL replication with integrity checking.

The and files on MySQL slaves are not by themselves crash safe. So if your slave crashes, it can fail to update these files with the servers current position. So upon restart the slave can fail.

I decided to put those parameters into use on a production slave server. I set each of them to a value of 1. This tells the server to sync after ever 1 event or 1 transaction respectively. It didn’t occur to me to do some performance testing with the parameters on. Mistake, big mistake.

Although the change made the slave crash safe, it also made the slave much slower. It began to lag behind the master significantly until it was showing old data. Since our Drupal setup used served most of the site off of the read-only slaves, we began to see old pages.

Although these new defaults set the value to 10,000 I would not recommend setting these parameters on by default. If you do use them on production boxes I would recommend doing performance tests to make sure they don’t cause unnecessary lag to your replication setup. Perhaps the new multi-threaded replication will help somewhat with this in 5.6.

Read this far? Grab our newsletter scalable startups!

  • Simon J Mudd

    innodb_file_per_table = 1 (formerly 0 or off)
    Well I think that we need proper tablespace management. This setting is just a hack around that “missing feature”. It’s there in most other RDBMSes and it would make management of a db easier in my opinion for a majority of users.
    Nevertheless, yes the setting of 1 is better than the previous default of 0.

    sync_master_info=10000 (formerly 0)
    sync_relay_log=10000 (formerly 0)
    sync_relay_log_info=10000 (formerly 0)
    I must admit to having requested something better than the previous 5.1 functionality when you couldn’t sync anything and asked for sync options to be available in 5.5, so that a slave crash would not lose it’s replication position relative to the restored Innodb recovered state. That lead to inconsistent data and often the need to reclone the slave.
    So I’m guessing that this functionality (I’ve never got around to testing it on my current production 5.5 systems) is as you say much too harsh generating 2 or 3x the number of syncs to disc. I’m assuming this is a “second best” approach so at least the difference is not to far if the server crashes.
    That said 5.6 allows you to store the replication position inside the Innodb tables in the mysql database so the database WILL recover consistently including the restored replication position. If you use InnoDB that looks like it’s the way to go as no extra syncs should be needed, well the writes to this table will go in the binlogs and iblog files just like other changes. That’s better.

    • hullsean

      Storing the replication position inside the database seems like a good move. I’ll take a look at that. Thx for the comments @simonjmudd:disqus btw, do you have a blog I can checkout?

  • Henrik

    MySQL 5.6 also allows a form of tablespace management: You can put different partitions on different disks.

    • hullsean

      Let’s keep a close eye on this feature, and how it performs in the wild. Thx for the tip, @769f0fd707751e8755eb9b5dd837ab63:disqus

  • Pingback: Atom Wire » Blog Archive » Thoughts on Upcoming MySQL 5.6 Defaults