Re: [HACKERS] [PATCHES] Doc update for pg_start_backup

2007-06-29 Thread Simon Riggs
On Thu, 2007-06-28 at 23:35 -0400, Tom Lane wrote:
 Heikki Linnakangas [EMAIL PROTECTED] 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



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] [PATCHES] Doc update for pg_start_backup

2007-06-29 Thread Heikki Linnakangas

Tom Lane wrote:

Heikki Linnakangas [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] [PATCHES] Doc update for pg_start_backup

2007-06-29 Thread Theo Schlossnagle


On Jun 29, 2007, at 4:25 AM, Heikki Linnakangas wrote:


Tom Lane wrote:

Heikki Linnakangas [EMAIL PROTECTED] 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
// [EMAIL PROTECTED]: http://omniti.com
// Esoteric Curio: http://www.lethargy.org/~jesus/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] [PATCHES] Doc update for pg_start_backup

2007-06-29 Thread Jim Nasby

On Jun 29, 2007, at 3:25 AM, Heikki Linnakangas wrote:

Tom Lane wrote:

Heikki Linnakangas [EMAIL PROTECTED] 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[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] [PATCHES] Doc update for pg_start_backup

2007-06-28 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings