On 28/05/2014 13:42, Joost Roeleveld wrote: >>> Currently, I do the following: >>> > > Every year, a full backup >>> > > Then, every month, I have an incremental based on either the yearly or >>> > > previous monthly. >>> > > Ditto for the weekly (but then based on monthly or weekly) >>> > > And again for the daily. >> > >> > OK, that makes sense. >> > >> > It reminds me of an issue my wife had with the data warehouse when she >> > worked at the bank. In a nutshell, they needed backups but backups were >> > impossible to achieve because physics says so. They needed to get data >> > off the disk 4 times faster than data comes off a disk - SCSI limits >> > being rather hard limits :-) That opinion didn't go down well when I >> > offered it > Haha :) > I know the feeling. > I'd love to know the final solution they came up with.
It was clever magic with LVM snapshots, but I'm not privy to the details - it was a bank after all and there's only so much Mrs Alan and the sysadmins could tell me. But it went something like this: Take a snapshot and copy that data to the SAN. This takes days or weeks and it's only ever done once. Thereafter, snapshots only. The backup system would take that last full + incrementals and create a new full for it's own use, this process runs independent from everything else and must take as long as it takes while the db carries on doing it's thing. If two backup jobs start to overlap in time, one of them gets discarded. Quite a clever scheme actually and it relies on storage being shared on the SAN to work, plus some in-house magic to get the backup system to recognise and use LVM snapshots natively. I believe performance impact was kept to acceptable levels by cleverly setting priorities - read/writes by the db take priority over backup reads which has to take a back seat and just wait it's turn. > >> > The solution was to do it much like your plan above. >> > With the benefit that the infrequent full backups would be done on a >> > fixed schedule in a change window with X hours downtime that was known >> > well in advance. > Using snapshots, the downtime is the same couple of minutes each night. > The problem is that during the backup, the performance of the server is > impacted. For a full backup, that means weeks... -- Alan McKinnon [email protected]
