Re: Performance of COPY for Archive operations

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.

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

Simon Riggs wrote:
> > Bruce Momjian wrote
> > Simon Riggs wrote:
> > >
> > > I've spent a while working with PITR functionality on the Win32 port.
> > >
> > > I noticed that *it works*, which is always great, but using a
> > COPY command
> > > the archival operation was significantly slower than the writing of the
> > > xlogs themselves.
> > >
> > > At one point, I got to being more than 10 xlog files behind
> > with the list
> > > growing steadily, and took a while to clear the logjam when my test
> > > workloads completed. Not much point having archiving thats
> > actually slower
> > > than the writing of xlog....
> >
> > Why was it slow?  'cp' was slower than the WAL writes?  Seems strange to
> > me.   Do we have some sleep loop in there that is causing us to read
> > that directory too slowly?  I didn't think so.
> >
>
> (Win32 COPY, not cp.)
>
> Yes, it seemed strange, that's why I mention it... nothing like that on
> linux.
>
> When there are multiple files ready for archive, ARCHIVER loops until
> they're all done. You're right, it could conceivably be something to do with
> directory access speed, but I'm thinking that the NT COPY command itself has
> some strangeness.
>
> My test involved writing 1 million records, each > 4k to a table using an
> Insert Select. The Server had a single disk, but there's no reason to expect
> that head movement on the disk would favour one process over another. That's
> probably THE most common setup for people using the Windows version anyway,
> so it is important.
>
> I note also Mark Wong's recent large scale benchmark that showed less than a
> 1% overhead from archiving.
>
> > > IIRC the COPY command isn't the best thing to use for bulk-copying on
> > > Windows, but I can't remember what is better. Anybody?
> >
> > COPY is the fastest way to get data in and out of PostgreSQL.
>
> Agreed....but I meant copying NT files around using the NT COPY command, not
> the PostgreSQL COPY command.
>
> I had some performance issues in '98 related to this - just hoping some
> Win32 wiz will educate me...
>
> ...
>
>
> More importantly, can anybody repeat this result? I performed this twice,
> with the same results each time.
>
> Thanks,
>
> Best Regards, Simon Riggs
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

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