Postgres Pro
Downloads
Home   >   mailing lists

Re: WAL directory size calculation - Mailing list pgsql-general

From Jan Wieck
Subject Re: WAL directory size calculation
Date
Msg-id CAGBW59efOLMXMkjHmioCeovg_XEQiJrpHC+29xXpq3fpoBKbWg@mail.gmail.com
Whole thread Raw
In response to Re: WAL directory size calculation  (Andreas Kretschmer <andreas@a-kretschmer.de>)
List pgsql-general
Tree view


On Thu, Jul 28, 2016 at 9:54 AM, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:
Without Replication 1 GB would be fine, even with replication. But it must be realible!


The required size of WAL depends on what your intended checkpoint_timeout vs. the amount
of WAL generated from data turnover is. A rather small 40GB database, churning TPC-C style
transactions at a rate of 1,000 TPS can easily generate 60MB of WAL per second (if configured
wrong). To keep the WAL size at or below 1GB would require a checkpoint to complete every
17 seconds. In this case, max_wal_size=1GB is a very wrong config option.

One problem here is that the more frequent checkpoints occur, the more full page writes will be
required. Which drives up the amount of WAL, requiring checkpoints even more frequently
when max_wal_size is the limiting factor. This is a classic "down spiral" scenario.

At 1,000 TPS, the above benchmark levels out (after about 1-2 hours) around 60-64GB of
WAL space used (with max_wal_size = 96GB and checkpoint_timeout=20min). The total
amount of WAL actually produced goes down significantly (due to reduced full page writes)
and the transaction response time improves in average as well as in stddev. The whole DB
looks more like it is cruising, than fighting.

This example isn't a big database (40-80GB) or anything exotic. Just a write heavy OLTP
load. 


Regards, Jan





 
Andreas



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Jan Wieck
Senior Postgres Architect

pgsql-general by date:

Previous
From: Vitaly Burovoy
Date:
Subject: Re: Using timestamp(tz) in C functions
Next
From: Achilleas Mantzios
Date:
Subject: Re: Uber migrated from Postgres to MySQL