Re: wal_level=archive gives better performance than minimal - why?

On 17.1.2012 01:29, Tomas Vondra wrote:
> On 16.1.2012 23:35, Greg Smith wrote:
>> On 01/12/2012 06:17 PM, Tomas Vondra wrote:
>>> I've run a series fo pgbench benchmarks with the aim to see the effect
>>> of moving the WAL logs to a separate drive, and one thing that really
>>> surprised me is that the archive log level seems to give much better
>>> performance than minimal log level.
>>
>> How repeatable is this? If you always run minimal first and then
>> archive, that might be the actual cause of the difference. In this
>> situation I would normally run this 12 times, with this sort of pattern:
>>
>> minimal
>> minimal
>> minimal
>> archive
>> archive
>> archive
>> minimal
>> minimal
>> minimal
>> archive
>> archive
>> archive
>>
>> To make sure the difference wasn't some variation on "gets slower after
>> each run". pgbench suffers a lot from problems in that class.

So, I've rerun the whole benchmark (varying fsync method and wal level),
and the results are exactly the same as before ...

See this:

http://www.fuzzy.cz/tmp/fsync/tps.html
http://www.fuzzy.cz/tmp/fsync/latency.html

Each row represents one of the fsync methods, first column is archive
level, second column is minimal level. Notice that the performance with
archive level continuously increases and is noticeably better than the
minimal wal level. In some cases (e.g. fdatasync) the difference is up
to 15%. That's a lot.

This is a 20-minute pgbench read-write run that is executed after a
20-minute read-only pgbench run (to warm up the caches etc.)

The latencies seem generaly the same, except that with minimal WAL level
there's a 4-minute interval of significantly higher latencies at the
beginning.

That's suspiciously similar to the checkpoint timeout (which was set to
4 minutes), but why should this matter for minimal WAL level and not for
archive?

Tomas