[GENERAL] WAL directory size calculation

Discussion:

(too old to reply)

Moreno Andreo

2016-07-28 13:25:18 UTC

Hi folks! :-)
I'm about to bring up my brand new production server and I was wondering
if it's possible to calculate (approx.) the WAL directory size.
I have to choose what's better in terms of cost vs. performance (we are
on Google Cloud Platform) between a ramdisk or a separate persistent
disk. Obviously ramdisk will be times faster disk, but having a, say,
512 GB ramdisk will be a little too expensive :-)
I've read somewhere that the formula should be 16 MB * 3 *
checkpoint_segment in size. But won't it be different depending on the
type of /wal_level/ we set? And won't it also be based on the volume of
transactions in the cluster?
And, in place of not-anymore-used-in-9.5 /checkpoint_segment/ what
should I use? /max_wal_size/?
Aside of this, I'm having 350 DBs that sum up a bit more than 1 TB, and
plan to use wal_level=archive because I plan to have a backup server
with barman.

Using the above formula I have:
16 MB * 3 * 1 GB
that leads to to ... uh .. 48000 TB?

Any ideas?
Thanks
Moreno.-

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

David G. Johnston

2016-07-28 13:33:25 UTC

Permalink

Post by Moreno Andreo
I've read somewhere that the formula should be 16 MB * 3 *
checkpoint_segment in size.

â[...]â

Post by Moreno Andreo
16 MB * 3 * 1 GB
that leads to to ... uh .. 48000 TB?

âYou seem to be mis-remembering the formula.â

âcheckpoiint_segments is a quantity (i.e., unit-less), not a size. Saying
its "1GB" makes no sense.

âI'm also doubting you multiply it by 3 - add three to it maybe...

âEach segment takes 16MB. The total space required is that times whatever
maximum count of segments you expect to have.â

I'd suggest waiting for better responses on these lists or searching out
prior conversations on this topic - of which I am sure there are many.

David J.

Andreas Kretschmer

2016-07-28 13:54:01 UTC

Permalink

Post by Moreno Andreo
Hi folks! :-)
I'm about to bring up my brand new production server and I was
wondering if it's possible to calculate (approx.) the WAL directory size.
I have to choose what's better in terms of cost vs. performance (we
are on Google Cloud Platform) between a ramdisk or a separate
persistent disk. Obviously ramdisk will be times faster disk, but
having a, say, 512 GB ramdisk will be a little too expensive :-)

Don't use a RAM-Disk for WAL! DON'T! NEVER!

You really need only some GB, if you are using Replication. Without
Replication 1 GB would be fine, even with replication. But it must be
realible!

Andreas

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

Moreno Andreo

2016-07-28 14:41:28 UTC

Permalink

Post by Andreas Kretschmer

Post by Moreno Andreo
Hi folks! :-)
I'm about to bring up my brand new production server and I was
wondering if it's possible to calculate (approx.) the WAL directory
size.
I have to choose what's better in terms of cost vs. performance (we
are on Google Cloud Platform) between a ramdisk or a separate
persistent disk. Obviously ramdisk will be times faster disk, but
having a, say, 512 GB ramdisk will be a little too expensive :-)

Don't use a RAM-Disk for WAL! DON'T! NEVER!

OK OK OK, got the point...... :-)

Post by Andreas Kretschmer
You really need only some GB, if you are using Replication. Without
Replication 1 GB would be fine, even with replication. But it must be
realible!

I'll get what seems to be the minumum persistent disk in size among GCP,
10 GB. Thanks!

Post by Andreas Kretschmer
Andreas

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

Moreno Andreo

2016-07-28 16:27:41 UTC

Permalink

<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Il 28/07/2016 15:33, David G. Johnston
ha scritto:<br>
</div>
<blockquote
cite="mid:CAKFQuwaMGb-***@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><span
style="font-family:arial,sans-serif">On Thu, Jul 28, 2016 at
9:25 AM, Moreno Andreo </span><span dir="ltr"
style="font-family:arial,sans-serif"><<a
moz-do-not-send="true"
href="mailto:***@evolu-s.it" target="_blank">***@evolu-s.it</a>></span><span
style="font-family:arial,sans-serif"> wrote:</span><br>
</div>
<div class="gmail_extra">
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">I've
read somewhere that the formula should be 16 MB * 3 *
checkpoint_segment in size.<br>
</blockquote>
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline">[...]</div>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">Using
the above formula I have:<br>
16 MB * 3 * 1 GB<br>
that leads to to ... uh .. 48000 TB?<br>
</blockquote>
<div><br>
</div>
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">You seem
to be mis-remembering the formula.</div>
</div>
</div>
</div>
</div>
</blockquote>
The result is too big to be sane... so there *must* be something
wrong<br>
<blockquote
cite="mid:CAKFQuwaMGb-***@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div><br>
</div>
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline">checkpoiint_segments
is a quantity (i.e., unit-less), not a size. Saying its
"1GB" makes no sense.</div>
</div>
</div>
</div>
</div>
</blockquote>
Yes, my "1 GB" is related to max_wal_file_size, that from 9.5 came
over checkpoint_segment.<br>
<blockquote
cite="mid:CAKFQuwaMGb-***@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline"><br>
</div>
</div>
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline">I'm
also doubting you multiply it by 3 - add three to it
maybe...</div>
</div>
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline"><br>
</div>
</div>
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">Each
segment takes 16MB. The total space required is that
times whatever maximum count of segments you expect to
have.</div>
</div>
</div>
</div>
</div>
</blockquote>
That's the variable. How many segments do I expect to have? (jumping
from 9.1 (current) to 9.5 (new) documentation and having a bit of
confusion in my head...)<br>
<br>
<blockquote
cite="mid:CAKFQuwaMGb-***@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br>
</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">I'd
suggest waiting for better responses on these lists
...[snip]...</div>
</div>
</div>
</div>
</div>
</blockquote>
Got one, in fact. The statement to *absolutely* not use ramdisk with
wal files resolved almost all of my doubts.<br>
<br>
<blockquote
cite="mid:CAKFQuwaMGb-***@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br>
</div>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">David J.</div>
<br>
</div>
</div>
</div>
</div>
</blockquote>
<p>Thanks</p>
<p>Moreno<br>
</p>
</body>
</html>

Francisco Olarte

2016-07-28 18:45:57 UTC

Permalink

Obviously ramdisk will be times faster disk, but having a, say, 512 GB
ramdisk will be a little too expensive :-)

Besides defeating the purpose of WAL, if you are going to use non
persistent storage for WAL you could as well use minimal level,
fsync=off and friends.

Aside of this, I'm having 350 DBs that sum up a bit more than 1 TB, and plan
to use wal_level=archive because I plan to have a backup server with barman.

Is this why you plan using RAM for WAL ( assuming fast copies to the
archive and relying on it for recovery ) ?

Francisco Olarte.

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

Jan Wieck

2016-07-29 05:05:51 UTC

Permalink

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
--
http://www.postgresql.org/mailpref/pgsql-general

--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info

Jeff Janes

2016-08-03 15:49:49 UTC

Permalink

On Thu, Jul 28, 2016 at 6:33 AM, David G. Johnston

Post by David G. Johnston

Post by Moreno Andreo
I've read somewhere that the formula should be 16 MB * 3 *
checkpoint_segment in size.

[...]

Post by Moreno Andreo
16 MB * 3 * 1 GB
that leads to to ... uh .. 48000 TB?

You seem to be mis-remembering the formula.
checkpoiint_segments is a quantity (i.e., unit-less), not a size. Saying
its "1GB" makes no sense.

Right, that doesn't make any sense.

Post by David G. Johnston
I'm also doubting you multiply it by 3 - add three to it maybe...

No, multiplying by 3 is roughly correct. A log file can't be
recycled/removed until there have been 2 checkpoints started and
completed. And if you have checkpoint completion target set to high
value, the 2nd checkpoint will complete just before the 3rd one will
be triggered to start. So that means you have 3 * checkpoint_segments
worth of segment.

Cheers,

Jeff

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

Jeff Janes

2016-08-03 16:01:15 UTC

Permalink

Post by Moreno Andreo
Hi folks! :-)
I'm about to bring up my brand new production server and I was wondering if
it's possible to calculate (approx.) the WAL directory size.
I have to choose what's better in terms of cost vs. performance (we are on
Google Cloud Platform) between a ramdisk or a separate persistent disk.

As others have said, there is almost no point in putting WAL on a
ramdisk. It will not be there exactly at the time you need it.

Post by Moreno Andreo
Obviously ramdisk will be times faster disk, but having a, say, 512 GB
ramdisk will be a little too expensive :-)
I've read somewhere that the formula should be 16 MB * 3 *
checkpoint_segment in size. But won't it be different depending on the type
of /wal_level/ we set? And won't it also be based on the volume of
transactions in the cluster?

Not in usual cases. If you have more volume, then checkpoint_segment
will get exceeded more frequently and you will have more frequent
checkpoints. As long as your system can actually keep up with the
checkpoints, then the more frequent checkpoints will cancel the higher
volume, leaving you with the same steady-state number of segments.

Post by Moreno Andreo
And, in place of not-anymore-used-in-9.5 /checkpoint_segment/ what should I
use? /max_wal_size/?

max_wal_size doesn't just replace "checkpoint_segment" in the formula.
It replaces the entire
formula itself. That was the reason for introducing it.

Post by Moreno Andreo
Aside of this, I'm having 350 DBs that sum up a bit more than 1 TB, and plan
to use wal_level=archive because I plan to have a backup server with barman.
16 MB * 3 * 1 GB

If you are getting the "1 GB" from max_wal_size, then see above.

Note that max_wal_size is not a hard limit. It will be exceeded if
your system can't keep up with the checkpoint schedule. Or if
archive_command can't keep up.

Cheers,

Jeff

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