On Tue, 22 May 2012 17:17:44 +1000, Russell Coker <russell@coker.com.au> wrote:
On Tue, 22 May 2012, James Harper <james.harper@bendigoit.com.au> wrote:
vg's 0, 1, and 2 are on different sets of spindles to give higher IOPS. The problem is that the 4 snapshots need to be created atomically or else I'm going to get my database and transaction logs out of sync as there could be a few ms between the snapshot of lv2 and lv3.
If a database is designed correctly shouldn't it be possible to be consistent even if the snapshots are at different times?
No. Typically, the log tail is trimmed as things are written back as a checkpoint is performed (writing back things from log to data file). If you have copied 1/2 the data file and checkpoint gets done that is in the first half of the data file and the log wraps... you're pretty hosed no matter what.
The purpose of the log is that logged transactions can be replayed if they didn't get to the main data store. So if you snapshot the database filesystem and then snapshot the log a bit later then the DB server should just replay the newer transactions and everything should be fine - according to my knowledge of databases.
This would be incredibly DBMS specific, and there would be caveats and possibilities where you end up with either missing, corrupted or completely useless data.
FWIW I have my MySQL VMs setup with a /mysql filesystem that contains database files under /mysql/db and logs under /mysql/log.
it depends what logs. If it's the binary logs used for replication, then these have a looser connection than the logs for innodb. Luckily (spruking the open source product my employer develops) XtraBackup is there to take consistent non-blocking backups of InnoDB.
It's generally claimed that the benefits of having a separate filesystem for the database log at the low end involve different RAID levels. For high end databases I've seen an Oracle document about how to tune different ZFS filesystems for different parts of an Oracle database. But both those cases probably aren't great options for a VM.
I've never seen any benchmark that says ZFS should even be considered for databases (nor btrfs for that matter). Basically, it's still; step 1 for database performance: use XFS. -- Stewart Smith