Обсуждение: Re: [PATCHES] Doc update for pg_start_backup
Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Added a note to the docs that pg_start_backup can take a long time to
> finish now that we spread out checkpoints:
I was starting to wordsmith this, and then wondered whether it's not
just a stupid idea for pg_start_backup to act that way. The reason
you're doing it is to take a base backup, right? What are you going
to take the base backup with? I do not offhand know of any backup
tools that don't suck major amounts of I/O bandwidth. That being
the case, you're simply not going to schedule the operation during
full-load periods. And that leads to the conclusion that
pg_start_backup should just use CHECKPOINT_IMMEDIATE and not slow
you down.
Thoughts?
regards, tom lane
On Thu, 2007-06-28 at 23:35 -0400, Tom Lane wrote: > Heikki Linnakangas <heikki@enterprisedb.com> writes: > > Added a note to the docs that pg_start_backup can take a long time to > > finish now that we spread out checkpoints: > > I was starting to wordsmith this, and then wondered whether it's not > just a stupid idea for pg_start_backup to act that way. The reason > you're doing it is to take a base backup, right? What are you going > to take the base backup with? I do not offhand know of any backup > tools that don't suck major amounts of I/O bandwidth. That being > the case, you're simply not going to schedule the operation during > full-load periods. Well, that assumes you can predict a time of reduced load and that time critical activities won't happen at that point. Many times you can, but I see no reason to force a checkpoint immediate. If you use snapshots you can copy the data away in your own time, so not all backup mechanisms draw extensive/high priority I/O power. > And that leads to the conclusion that > pg_start_backup should just use CHECKPOINT_IMMEDIATE and not slow > you down. I would prefer the default to be do this slowly. If there is a reason to do it fast, maybe, but we should err towards low impact. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Tom Lane wrote: > Heikki Linnakangas <heikki@enterprisedb.com> writes: >> Added a note to the docs that pg_start_backup can take a long time to >> finish now that we spread out checkpoints: > > I was starting to wordsmith this, and then wondered whether it's not > just a stupid idea for pg_start_backup to act that way. The reason > you're doing it is to take a base backup, right? What are you going > to take the base backup with? I do not offhand know of any backup > tools that don't suck major amounts of I/O bandwidth. scp over a network? It's still going to consume a fair amount of I/O, but the network could very well be the bottleneck. > That being > the case, you're simply not going to schedule the operation during > full-load periods. And that leads to the conclusion that > pg_start_backup should just use CHECKPOINT_IMMEDIATE and not slow > you down. That's probably true in most cases. But on a system that doesn't have quite periods, you're still going to have to take the backup. To be honest, I've never worked as a DBA and never had to deal with taking backups of a production system, so my gut feelings on this could be totally wrong. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Jun 29, 2007, at 4:25 AM, Heikki Linnakangas wrote: > Tom Lane wrote: >> Heikki Linnakangas <heikki@enterprisedb.com> writes: >>> Added a note to the docs that pg_start_backup can take a long >>> time to finish now that we spread out checkpoints: >> I was starting to wordsmith this, and then wondered whether it's not >> just a stupid idea for pg_start_backup to act that way. The reason >> you're doing it is to take a base backup, right? What are you going >> to take the base backup with? I do not offhand know of any backup >> tools that don't suck major amounts of I/O bandwidth. > > scp over a network? It's still going to consume a fair amount of I/ > O, but the network could very well be the bottleneck. > >> That being >> the case, you're simply not going to schedule the operation during >> full-load periods. And that leads to the conclusion that >> pg_start_backup should just use CHECKPOINT_IMMEDIATE and not slow >> you down. > > That's probably true in most cases. But on a system that doesn't > have quite periods, you're still going to have to take the backup. > To be honest, I've never worked as a DBA and never had to deal with > taking backups of a production system, so my gut feelings on this > could be totally wrong. I'll share my two cents having had to back up many terabytes of oracle, postgres and mysql every day... The comments that taking a backup causes a lot of absolutely unavoidable I/O is right on the mark. If you have a large enough database where this matters the technique usually looks as follows. (1) sanity (2) postgres_start_backup (3) snap (4) postgres_stop_backup (5) backup Now, the backup will always have to read the data, if it is full it reads every block. If it is incremental, it reads the blocks that changed. You will frequently be in the position of performing a full backup. The bandwidth for doing the read will inevitably happen in one or more of the above steps. I strongly prefer that load to happen in (5) and for steps (2,3,4) to happen as quickly as possible. Right now on our largest (slowest) production box which is postgres and over a terabyte, steps 2-4 take about 30-60 seconds. Step 5 takes *cough* about 18 hours *cough*. The snap in many of our cases is an logical software enabled snapshot (either Veritas, LVM or ZFS). However, you can use many enterprise storage to take a hard snapshot and expose that as a LUN to mount elsewhere on attached to the same SAN. Many confuse this for being "free". Regardless of how the snap is taken you have to pay for it.. either at snap time, at read time or at release time. Nothing's free. // Theo Schlossnagle // Principal@OmniTI: http://omniti.com // Esoteric Curio: http://www.lethargy.org/~jesus/
On Jun 29, 2007, at 3:25 AM, Heikki Linnakangas wrote: > Tom Lane wrote: >> Heikki Linnakangas <heikki@enterprisedb.com> writes: >>> Added a note to the docs that pg_start_backup can take a long >>> time to finish now that we spread out checkpoints: >> I was starting to wordsmith this, and then wondered whether it's not >> just a stupid idea for pg_start_backup to act that way. The reason >> you're doing it is to take a base backup, right? What are you going >> to take the base backup with? I do not offhand know of any backup >> tools that don't suck major amounts of I/O bandwidth. > > scp over a network? It's still going to consume a fair amount of I/ > O, but the network could very well be the bottleneck. You can also use rsync and have it do bandwidth limiting (AFAIK that would work locally too). >> That being >> the case, you're simply not going to schedule the operation during >> full-load periods. And that leads to the conclusion that >> pg_start_backup should just use CHECKPOINT_IMMEDIATE and not slow >> you down. > > That's probably true in most cases. But on a system that doesn't > have quite periods, you're still going to have to take the backup. Correct. If the load presented by the base backup is too high, you'll be looking at ways to slow it down; but I've yet to run across such a case in the field. I think having start_backup do a checkpoint immediate by default would be best, since it's least surprising, but I do like having it as an option for cases where it's needed (though I think those cases are probably pretty rare). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)