From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: wal_level=archive gives better performance than minimal - why?
Date: Mon, 23 Jan 2012 00:07:01 +0100
Message-ID: <4F1C9695.4000304@fuzzy.cz> (raw)
In-Reply-To: <4F14C101.60607@fuzzy.cz>
References: <4F0F6A02.3000403@fuzzy.cz>
<4F14A649.5060501@2ndQuadrant.com>
<4F14C101.60607@fuzzy.cz>
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.htmlhttp://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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: wal_level=archive gives better performance than minimal - why?
In-Reply-To: <4F1C9695.4000304@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox