MySQL InnoDB Isolated Patches for 5.0.37, 5.0.67 and Percona’s 5.0.68 Branch.

We’re migrating from MySQL 4.1.x to 5.0.x at work and one of the key features we need is the ability to freeze InnoDB and prevent it from writing to disk.

We do this to aid in syncing masters and slaves and performing backups.

Basically we freeze a master, copy the data to a new slave, unfreeze the master, bring up the new slave, and then setup replication to start from right after we froze the master.

It’s MUCH faster than performing a mysqldump (20x faster). A mysqldump tends to both do a ton of random seeks on disk as well as burn up a single core.

This type of ‘innodb hot copy’ is only bottlenecked on disk and gigabit ethernet IO.

In theory you can sync at 125MB/s…

David ended up spending the time to isolate, test, and retarget the patch for various MySQL versions.

We tested this and one of the interesting properties is that it can actually continue to execute UPDATES as long as you’re using innodb_flush_log_at_trx_commit=0.

Anyway, I’d really like to see this land in Drizzle, and MySQL +5.0.69.

It only modifies eight lines of code so seems like a pretty no brainer.

Up until this point we were using xfs_freeze but ran into a nasty bug where read() would block during a file copy. Apparently, xfs_freeze was designed to block for writes but not for reads.

My theory is that there’s a bug or a race condition between sync and xfs_freeze which is leading to a partially dirty page cache preventing us from reading while the filesystem is frozen.

  1. The hold up on patches for Innodb right now is figuring out how to get them in the tree without creating a nightmare for ourselves. We have been working on getting PBXT to flow correctly into the tree. If that works for us, we will do the same with Innodb (which will make our lives a lot easier…).


  2. PBXT is the new InnoDB and GPL is the new CLA/SCA. Drizzle is leading the way.

  3. Nice to hear that PBXT is making some headway….. Hopefully we’ll see some new SSD benchmarks soon.

    I was actually thinking that a log structured storage ending similar to the partition engine might be interesting.

    We’ve written our own table partitioning engine above MySQL… very happy with the results so far.


%d bloggers like this: