Tag Archives: mysql56

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 master.info and relay-log.info 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!