Skip site navigation (1) Skip section navigation (2)

Re: Performance of COPY for Archive operations

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers-win32(at)postgresql(dot)org
Subject: Re: Performance of COPY for Archive operations
Date: 2004-09-20 22:35:48
Message-ID: 200409202235.i8KMZmA02435@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers-win32
We already have a warning that prints when checkpoints happen too
frequently.   I wonder if we should print a warning if the number of WAL
records doubles from its maximum which is checkpoint_segments*2+1 I
think.

---------------------------------------------------------------------------

Simon Riggs wrote:
> >Tom Lane wrote
> > "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > > Nothing that surprising there, though I think I would like to
> > put a WARNING
> > > message into the Archiver that triggers if more than
> > CHECKPOINT_SEGMENTS WAL
> > > files are ready to archive at any one time. Though maybe that
> > would cause
> > > more problems than it would solve: "Archiving of transaction logs cannot
> > > keep up with system activity. If this occurs regularly, you should
> > > reconsider your database-disk layout"
> >
> > Can't see the value of this.  The problem will be readily apparent from
> > growth of the pg_xlog directory --- anyone who doesn't notice that
> > probably isn't perusing the postmaster log either.
> 
> Hmmm, message levels were a point we differed on previously, IIRC.
> 
> Certainly, if the growth happened over a long period, then I'd agree - the
> admin should have spotted it.
> 
> If the behaviour were more volatile, then the admin might not spot it - the
> effects are only shown when the system becomes I/O bound, which might be
> regularly at peak loading, but never long enough to notice. I had considered
> just such volatility in the design, though with regard to operator induced
> behaviour like tape changes or deliberate batching of log files.
> 
> The issue is that by falling behind the archiver is increasing the
> transaction loss window, possibly undermining somewhat the purpose of PITR.
> 
> The message shows in the log long after the situation occurred and the space
> increase has disipated. The admin may never look at the logs, agreed, but if
> the message isn't there they certainly will never notice. You and I will
> know, because when the crash occurs, we'll get a pattern of error messages
> we'll recognise, but thats not much help to the admin.
> 
> Do we wait for such a crash before we add the hint?
> 
> > Also, once it starts
> > to bleat, what's going to make it stop?  Filling the disk with warning
> > messages won't be a constructive improvement on the situation :-(
> 
> Filling the disk with log messages would be pointless, agreed.
> 
> If the message appeared as part of the normal archiver cycle, then the
> message would only appear once per 2*CHECKPOINT_SEGMENTS "transaction log
> archived" and "transaction log recycled" messages. Thus no more likely to
> fill up the disk.
> 
> Of course, the archiver could always report less frequently, since it keeps
> state between cycles.
> 
> I'm not in a rush to add this, just think its needed, based upon my
> observations on Windows.
> 
> Best Regards, Simon Riggs
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers-win32 by date

Next:From: Barry LindDate: 2004-09-20 23:45:52
Subject: pg_autovacuum log paths can't contain spaces
Previous:From: Simon RiggsDate: 2004-09-20 20:59:20
Subject: Re: Performance of COPY for Archive operations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group