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.