On Thu, Jul 28, 2016 at 9:54 AM, Andreas Kretschmer <andr...@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 http://pgblog.wi3ck.info