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