Simon Riggs wrote:
> On Sun, 2009-06-21 at 10:28 +0200, Stefan Kaltenbrunner wrote:
>> I did some limited testing on that but I was unable to measure any
>> significant effect - especially since the difference between
>> wal-logged and not is rather small for a non-parallel COPY (ie in the
>> above example you get around 6m20s runtime for wal-logged and ~5m40s
>> in the other case).
>
> This is a common confusion for small tests.
>
> Non-WAL logged case causes all buffers to be written to disk at end of
> COPY. This is roughly the same size as the volume of WAL written. In
> logged case we do not write data blocks, they get written at next
> checkpoint. So the reduction in I/O is not apparent, since during the
> period of the test the I/O is about the same in both cases and less I/O
> in the non-WAL logged case. On longer tests the difference shows more
> clearly because the data blocks start to migrate out of shared buffers
> while the COPY is still running, effecting the test results.
I was actually testing with and without explicit CHECKPOINTing
before/after the load(and also with longer runs) too - the difference is
negligible especially with only one process involved.
I think the difference is simply not that large because we are still
mostly CPU bound within COPY on reasonably fast IO-subsystems.
Stefan