Re: WAL directory size calculation

Поиск
Список
Период
Сортировка
От Jan Wieck
Тема Re: WAL directory size calculation
Дата
Msg-id CAGBW59efOLMXMkjHmioCeovg_XEQiJrpHC+29xXpq3fpoBKbWg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: WAL directory size calculation  (Andreas Kretschmer <andreas@a-kretschmer.de>)
Список pgsql-general
Дерево обсуждения


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 по дате отправления:

Предыдущее
От: Vitaly Burovoy
Дата:
Сообщение: Re: Using timestamp(tz) in C functions
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: Uber migrated from Postgres to MySQL