Re: Performance of COPY for Archive operations

I guess we will wait to see what problem reports we get from users.

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

Bruce Momjian wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
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