Skip site navigation (1) Skip section navigation (2)

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

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: wal_level=archive gives better performance than minimal - why?
Date: 2012-01-17 00:29:53
Message-ID: 4F14C101.60607@fuzzy.cz (view raw or flat)
Thread:
Lists: pgsql-performance
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.

AFAIK it's well repeatable - the primary goal of the benchmark was to
see the benefir of moving the WAL to a separate device (with various WAL
levels and device types - SSD and HDD).

I plan to rerun the whole thing this week with a bit more details logged
to rule out basic configuration mistakes etc.

Each run is completely separate (rebuilt from scratch) and takes about 1
hour to complete. Each pgbench run consists of these steps

  1) rebuild the data from scratch
  2) 10-minute warmup (read-only run)
  3) 20-minute read-only run
  4) checkpoint
  5) 20-minute read-write run

and the results are very stable.

Tomas

In response to

Responses

pgsql-performance by date

Next:From: Willy-Bas LoosDate: 2012-01-20 15:36:35
Subject: when benchmarking insert , can there be caching effects?
Previous:From: Greg SmithDate: 2012-01-16 22:35:53
Subject: Re: wal_level=archive gives better performance than minimal - why?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group