Re: Performance of COPY for Archive operations

Bruce Momjian wrote:
> I can imagine WAL writing as fast as MS COPY, and I can imagine MS COPY
> lagging behind on an I/O bound system.  Remind me, how does the archvier
> know a WAL file is full?
>
> Suppose the system is 100% I/O bound.  Archiver can just keep up with
> WAL, but if WAL gets a lead, can archiver catch up?  Basically archiver
> can never get ahead of WAL but WAL can get ahead of archiver.
> Statistically does that cause a consistent lag?  I am not sure.
>

Well my thinking on this is:
You're right, the whole thing is I/O bound.
The backend writing WAL was competing with the archiver COPY process which
was both reading and then writing WAL. That means that the backend is
literally doing half the work of the COPY process - as a result, the COPY
process doesn't just lag behind, it gets progressively further and further
behind. The only point at which it can catch up is when the long running
transaction stops.

The moral of this story is: don't archive transaction logs to the same disk
that pg_xlog is on. Many will say, "we knew that anyway" - but my feeling is
that many Windows systems are configured very simply, with just one disk or
one volume of RAID disks.

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"

Is that a TODO item, or a hotfix for 8.0 beta?

Best Regards, Simon Riggs