Thread: Redesigning checkpoint_segments

checkpoint_segments is awkward. From an admin's point of view, it 
controls two things:

1. it limits the amount of disk space needed for pg_xlog. (it's a soft 
limit, but still)
2. it limits the time required to recover after a crash.

For limiting the disk space needed for pg_xlog, checkpoint_segments is 
awkward because it's defined in terms of 16MB segments between 
checkpoints. It takes a fair amount of arithmetic to calculate the disk 
space required to hold the specified number of segments. The manual 
gives the formula: (2 + checkpoint_completion_target) * 
checkpoint_segments + 1, which amounts to about 1GB per 20 segments as a 
rule of thumb. We shouldn't impose that calculation on the user. It 
should be possible to just specify "checkpoint_segments=512MB", and the 
system would initiate checkpoints so that the total size of WAL in 
pg_xlog stays below 512MB.

For limiting the time required to recover after crash, 
checkpoint_segments is awkward because it's difficult to calculate how 
long recovery will take, given checkpoint_segments=X. A bulk load can 
use up segments really fast, and recovery will be fast, while segments 
full of random deletions can need a lot of random I/O to replay, and 
take a long time. IMO checkpoint_timeout is a much better way to control 
that, although it's not perfect either.

A third point is that even if you have 10 GB of disk space reserved for 
WAL, you don't want to actually consume all that 10 GB, if it's not 
required to run the database smoothly. There are several reasons for 
that: backups based on a filesystem-level snapshot are larger than 
necessary, if there are a lot of preallocated WAL segments and in a 
virtualized or shared system, there might be other VMs or applications 
that could make use of the disk space. On the other hand, you don't want 
to run out of disk space while writing WAL - that can lead to a PANIC in 
the worst case.


In VMware's vPostgres fork, we've hacked the way that works, so that 
there is a new setting, checkpoint_segments_max that can be set by the 
user, but checkpoint_segments is adjusted automatically, on the fly. The 
system counts how many segments were consumed during the last checkpoint 
cycle, and that becomes the checkpoint_segments setting for the next 
cycle. That means that in a system with a steady load, checkpoints are 
triggered by checkpoint_timeout, and the effective checkpoint_segments 
value converges at the exact number of segments needed for that. That's 
simple but very effective. It doesn't behave too well with bursty load, 
however; during quiet times, checkpoint_segments is dialed way down, and 
when the next burst comes along, you get several checkpoints in quick 
succession, until checkpoint_segments is dialed back up again.


I propose that we do something similar, but not exactly the same. Let's 
have a setting, max_wal_size, to control the max. disk space reserved 
for WAL. Once that's reached (or you get close enough, so that there are 
still some segments left to consume while the checkpoint runs), a 
checkpoint is triggered.

In this proposal, the number of segments preallocated is controlled 
separately from max_wal_size, so that you can set max_wal_size high, 
without actually consuming that much space in normal operation. It's 
just a backstop, to avoid completely filling the disk, if there's a 
sudden burst of activity. The number of segments preallocated is 
auto-tuned, based on the number of segments used in previous checkpoint 
cycles.

I'll write up a patch to do that, but before I do, does anyone disagree 
on those tuning principles? How do you typically tune 
checkpoint_segments on your servers? If the system was to tune it 
automatically, what formula should it use?

- Heikki

On Wed, Jun 5, 2013 at 9:16 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> checkpoint_segments is awkward. From an admin's point of view, it controls
> two things:
>
> 1. it limits the amount of disk space needed for pg_xlog. (it's a soft
> limit, but still)
> 2. it limits the time required to recover after a crash.
>
> For limiting the disk space needed for pg_xlog, checkpoint_segments is
> awkward because it's defined in terms of 16MB segments between checkpoints.
> It takes a fair amount of arithmetic to calculate the disk space required to
> hold the specified number of segments. The manual gives the formula: (2 +
> checkpoint_completion_target) * checkpoint_segments + 1, which amounts to
> about 1GB per 20 segments as a rule of thumb. We shouldn't impose that
> calculation on the user. It should be possible to just specify
> "checkpoint_segments=512MB", and the system would initiate checkpoints so
> that the total size of WAL in pg_xlog stays below 512MB.
>
> For limiting the time required to recover after crash, checkpoint_segments
> is awkward because it's difficult to calculate how long recovery will take,
> given checkpoint_segments=X. A bulk load can use up segments really fast,
> and recovery will be fast, while segments full of random deletions can need
> a lot of random I/O to replay, and take a long time. IMO checkpoint_timeout
> is a much better way to control that, although it's not perfect either.
>
> A third point is that even if you have 10 GB of disk space reserved for WAL,
> you don't want to actually consume all that 10 GB, if it's not required to
> run the database smoothly. There are several reasons for that: backups based
> on a filesystem-level snapshot are larger than necessary, if there are a lot
> of preallocated WAL segments and in a virtualized or shared system, there
> might be other VMs or applications that could make use of the disk space. On
> the other hand, you don't want to run out of disk space while writing WAL -
> that can lead to a PANIC in the worst case.
>
>
> In VMware's vPostgres fork, we've hacked the way that works, so that there
> is a new setting, checkpoint_segments_max that can be set by the user, but
> checkpoint_segments is adjusted automatically, on the fly. The system counts
> how many segments were consumed during the last checkpoint cycle, and that
> becomes the checkpoint_segments setting for the next cycle. That means that
> in a system with a steady load, checkpoints are triggered by
> checkpoint_timeout, and the effective checkpoint_segments value converges at
> the exact number of segments needed for that. That's simple but very
> effective. It doesn't behave too well with bursty load, however; during
> quiet times, checkpoint_segments is dialed way down, and when the next burst
> comes along, you get several checkpoints in quick succession, until
> checkpoint_segments is dialed back up again.
>
>
> I propose that we do something similar, but not exactly the same. Let's have
> a setting, max_wal_size, to control the max. disk space reserved for WAL.
> Once that's reached (or you get close enough, so that there are still some
> segments left to consume while the checkpoint runs), a checkpoint is
> triggered.

What if max_wal_size is reached while the checkpoint is running? We should
change the checkpoint from spread mode to fast mode? Or, if max_wal_size
is hard limit, we should keep the allocation of new WAL file waiting until
the checkpoint has finished and removed some old WAL files?

> In this proposal, the number of segments preallocated is controlled
> separately from max_wal_size, so that you can set max_wal_size high, without
> actually consuming that much space in normal operation. It's just a
> backstop, to avoid completely filling the disk, if there's a sudden burst of
> activity. The number of segments preallocated is auto-tuned, based on the
> number of segments used in previous checkpoint cycles.

How is wal_keep_segments handled in your approach?

> I'll write up a patch to do that, but before I do, does anyone disagree on
> those tuning principles?

No at least from me. I like your idea.

Regards,

-- 
Fujii Masao

On 05.06.2013 21:16, Fujii Masao wrote:
> On Wed, Jun 5, 2013 at 9:16 PM, Heikki Linnakangas
> <hlinnakangas@vmware.com>  wrote:
>> I propose that we do something similar, but not exactly the same. Let's have
>> a setting, max_wal_size, to control the max. disk space reserved for WAL.
>> Once that's reached (or you get close enough, so that there are still some
>> segments left to consume while the checkpoint runs), a checkpoint is
>> triggered.
>
> What if max_wal_size is reached while the checkpoint is running? We should
> change the checkpoint from spread mode to fast mode?

The checkpoint spreading code already tracks if the checkpoint is "on 
schedule", and it takes into account both checkpoint_timeout and 
checkpoint_segments. Ie. if you consume segments faster than expected, 
the checkpoint will speed up as well. Once checkpoint_segments is 
reached, the checkpoint will complete ASAP, with no delays to spread it out.

This would still work the same with max_wal_size. A new checkpoint would 
be started well before reaching max_wal_size, so that it has enough time 
to complete. If the checkpoint "falls behind", it will hurry up until 
it's back on schedule. If max_wal_size is reached anyway, it will 
complete ASAP.

> Or, if max_wal_size
> is hard limit, we should keep the allocation of new WAL file waiting until
> the checkpoint has finished and removed some old WAL files?

I was not thinking of making it a hard limit. It would be just like 
checkpoint_segments from that point of view - if a checkpoint takes a 
long time, max_wal_size might still be exceeded.

>> In this proposal, the number of segments preallocated is controlled
>> separately from max_wal_size, so that you can set max_wal_size high, without
>> actually consuming that much space in normal operation. It's just a
>> backstop, to avoid completely filling the disk, if there's a sudden burst of
>> activity. The number of segments preallocated is auto-tuned, based on the
>> number of segments used in previous checkpoint cycles.
>
> How is wal_keep_segments handled in your approach?

Hmm, haven't thought about that. I think a better unit to set 
wal_keep_segments in would also be MB, not segments. Perhaps 
max_wal_size should include WAL retained for wal_keep_segments, leaving 
less room for checkpoints. Ie. when you you set wal_keep_segments 
higher, a xlog-based checkpoint would be triggered earlier, because the 
old segments kept for replication would leave less room for new 
segments. And setting wal_keep_segments higher than max_wal_size would 
be an error.

- Heikki

Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

> I was not thinking of making it a hard limit. It would be just
> like checkpoint_segments from that point of view - if a
> checkpoint takes a long time, max_wal_size might still be
> exceeded.

Then I suggest we not use exactly that name.  I feel quite sure we
would get complaints from people if something labeled as "max" was
exceeded -- especially if they set that to the actual size of a
filesystem dedicated to WAL files.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Jun 6, 2013 at 3:35 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 05.06.2013 21:16, Fujii Masao wrote:
>>
>> On Wed, Jun 5, 2013 at 9:16 PM, Heikki Linnakangas
>> <hlinnakangas@vmware.com>  wrote:
>>>
>>> I propose that we do something similar, but not exactly the same. Let's
>>> have
>>>
>>> a setting, max_wal_size, to control the max. disk space reserved for WAL.
>>> Once that's reached (or you get close enough, so that there are still
>>> some
>>> segments left to consume while the checkpoint runs), a checkpoint is
>>> triggered.
>>
>>
>> What if max_wal_size is reached while the checkpoint is running? We should
>> change the checkpoint from spread mode to fast mode?
>
>
> The checkpoint spreading code already tracks if the checkpoint is "on
> schedule", and it takes into account both checkpoint_timeout and
> checkpoint_segments. Ie. if you consume segments faster than expected, the
> checkpoint will speed up as well. Once checkpoint_segments is reached, the
> checkpoint will complete ASAP, with no delays to spread it out.

Yep, right. One problem is that this mechanism doesn't work in the standby.
So, are you planning to 'fix' that so that max_wal_size works well even in
the standby? Or just leave that as it is? According to the remaining part of
your email, you seem to choose the latter, though.

>
> This would still work the same with max_wal_size. A new checkpoint would be
> started well before reaching max_wal_size, so that it has enough time to
> complete. If the checkpoint "falls behind", it will hurry up until it's back
> on schedule. If max_wal_size is reached anyway, it will complete ASAP.
>
>
>> Or, if max_wal_size
>> is hard limit, we should keep the allocation of new WAL file waiting until
>> the checkpoint has finished and removed some old WAL files?
>
>
> I was not thinking of making it a hard limit. It would be just like
> checkpoint_segments from that point of view - if a checkpoint takes a long
> time, max_wal_size might still be exceeded.

So, if the archive command keeps failing or its speed is very slow
(e.g., because
of using compression tool), max_wal_size can still be extremely exceeded. Right?

I'm wondering if it's worth exposing the option specifying whether to use
max_wal_size as the hard limit or not. If it's not hard limit, the
disk space can
be filled up with WAL files and PANIC can happen. In this case, in order to
restart the database service, we need to enlarge the disk space or relocate
some WAL files to another disk space, and then we need to start up the server.
The normal crash recovery needs to be done. This would lead lots of service
down time.

OTOH, if we use max_wal_size as a hard limit, we can avoid such PANIC
error and long down time. Of course, in this case, once max_wal_size is
reached, we cannot complete any query writing WAL until the checkpoint
has completed and removed old WAL files. During that time, the database
service looks like down from a client, but its down time is shorter than the
PANIC error case. So I'm thinking that some users might want the hard
limit of pg_xlog size.

>>> In this proposal, the number of segments preallocated is controlled
>>> separately from max_wal_size, so that you can set max_wal_size high,
>>> without
>>> actually consuming that much space in normal operation. It's just a
>>> backstop, to avoid completely filling the disk, if there's a sudden burst
>>> of
>>> activity. The number of segments preallocated is auto-tuned, based on the
>>> number of segments used in previous checkpoint cycles.
>>
>>
>> How is wal_keep_segments handled in your approach?
>
>
> Hmm, haven't thought about that. I think a better unit to set
> wal_keep_segments in would also be MB, not segments.

+1

Regards,

-- 
Fujii Masao

Heikki,

> We shouldn't impose that calculation on the user. It
> should be possible to just specify "checkpoint_segments=512MB", and the
> system would initiate checkpoints so that the total size of WAL in
> pg_xlog stays below 512MB.

Agreed.

> For limiting the time required to recover after crash,
> checkpoint_segments is awkward because it's difficult to calculate how
> long recovery will take, given checkpoint_segments=X. A bulk load can
> use up segments really fast, and recovery will be fast, while segments
> full of random deletions can need a lot of random I/O to replay, and
> take a long time. IMO checkpoint_timeout is a much better way to control
> that, although it's not perfect either.

This is true, but I don't see that your proposal changes this at all
(for the better or for the worse).

> A third point is that even if you have 10 GB of disk space reserved for
> WAL, you don't want to actually consume all that 10 GB, if it's not
> required to run the database smoothly.

Agreed.

> I propose that we do something similar, but not exactly the same. Let's
> have a setting, max_wal_size, to control the max. disk space reserved
> for WAL. Once that's reached (or you get close enough, so that there are
> still some segments left to consume while the checkpoint runs), a
> checkpoint is triggered.

Refinement of the proposal:

1. max_wal_size is a hard limit
2. checkpointing targets 50% of ( max_wal_size - wal_keep_segments )  to avoid lockup if checkpoint takes longer than
expected.
3. wal_keep_segments is taken out of max_wal_size.a. it automatically defaults to 20% of max_wal_size if
max_wal_senders> 0b. for that reason, we don't allow it to be larger   than 80% of max_wal_size
 
4. preallocated WAL isn't allowed to shrink smaller than
wal_keep_segements + (max_wal_size * 0.1).

This would mean that I could set my server to:

max_wal_size = 2GB

and ...

* by default, 26 segments (416MB) would be kept for wal_keep_segments.
* checkpoint target would be 77 segments (1.2GB)
* preallocated WAL will always be at least 39 segments (624MB),
including keep_segments.

now, if I had a fairly low transaction database, but wanted to make sure
I could recover from an 8-hour break in replication, I might bump up
wal_keep_segments to 1GB.  In that case:

* 64 segments (1GB) would be kept.
* checkpoints would target 96 segments (1.5GB)
* preallocated WAL would always be at least 77 segments (1.2GB)

> Hmm, haven't thought about that. I think a better unit to set
> wal_keep_segments in would also be MB, not segments.

Well, the ideal unit from the user's point of view is *time*, not space.That is, the user wants the master to keep,
say,"8 hours of
 
transaction logs", not any amount of MB.  I don't want to complicate
this proposal by trying to deliver that, though.

> In this proposal, the number of segments preallocated is controlled
> separately from max_wal_size, so that you can set max_wal_size high,
> without actually consuming that much space in normal operation. It's
> just a backstop, to avoid completely filling the disk, if there's a
> sudden burst of activity. The number of segments preallocated is
> auto-tuned, based on the number of segments used in previous checkpoint
> cycles.

"based on"; can you give me your algorithmic thinking here?  I'm
thinking we should have some calculation of last cycle size and peak
cycle size so that bursty workloads aren't compromised.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Wed, Jun 5, 2013 at 3:24 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> OTOH, if we use max_wal_size as a hard limit, we can avoid such PANIC
> error and long down time. Of course, in this case, once max_wal_size is
> reached, we cannot complete any query writing WAL until the checkpoint
> has completed and removed old WAL files. During that time, the database
> service looks like down from a client, but its down time is shorter than the
> PANIC error case. So I'm thinking that some users might want the hard
> limit of pg_xlog size.

I wonder if we could tie this in with the recent proposal from the
Heroku guys to have a way to slow down WAL writing.  Maybe we have
several limits:

- When limit #1 is passed (or checkpoint_timeout elapses), we start a
spread checkpoint.

- If it looks like we're going to exceed limit #2 before the
checkpoint completes, we attempt to perform the checkpoint more
quickly, by reducing the delay between buffer writes.  If we actually
exceed limit #2, we try to complete the checkpoint as fast as
possible.

- If it looks like we're going to exceed limit #3 before the
checkpoint completes, we start exerting back-pressure on writers by
making them wait every time they write WAL, probably in proportion to
the number of bytes written.  We keep ratcheting up the wait until
we've slowed down writers enough that will finish within limit #3.  As
we reach limit #3, the wait goes to infinity; only read-only
operations can proceed until the checkpoint finishes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 06/05/2013 05:37 PM, Robert Haas wrote:
>
> On Wed, Jun 5, 2013 at 3:24 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> OTOH, if we use max_wal_size as a hard limit, we can avoid such PANIC
>> error and long down time. Of course, in this case, once max_wal_size is
>> reached, we cannot complete any query writing WAL until the checkpoint
>> has completed and removed old WAL files. During that time, the database
>> service looks like down from a client, but its down time is shorter than the
>> PANIC error case. So I'm thinking that some users might want the hard
>> limit of pg_xlog size.
>
> I wonder if we could tie this in with the recent proposal from the
> Heroku guys to have a way to slow down WAL writing.  Maybe we have
> several limits:

I didn't see that proposal, link? Because the idea of slowing down 
wal-writing sounds insane.

JD



-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms   a rose in the deeps of my heart. - W.B. Yeats

<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Thu, Jun 6, 2013 at 10:00 AM,
JoshuaD. Drake <span dir="ltr"><<a href="mailto:jd@commandprompt.com"
target="_blank">jd@commandprompt.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0px 0px
0px0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="im"><br /> On 06/05/2013 05:37 PM, Robert
Haaswrote:<br /><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><br/> On Wed, Jun 5, 2013 at 3:24 PM, Fujii Masao <<a
href="mailto:masao.fujii@gmail.com"target="_blank">masao.fujii@gmail.com</a>> wrote:<br /><blockquote
class="gmail_quote"style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> OTOH, if
weuse max_wal_size as a hard limit, we can avoid such PANIC<br /> error and long down time. Of course, in this case,
oncemax_wal_size is<br /> reached, we cannot complete any query writing WAL until the checkpoint<br /> has completed
andremoved old WAL files. During that time, the database<br /> service looks like down from a client, but its down time
isshorter than the<br /> PANIC error case. So I'm thinking that some users might want the hard<br /> limit of pg_xlog
size.<br/></blockquote><br /> I wonder if we could tie this in with the recent proposal from the<br /> Heroku guys to
havea way to slow down WAL writing.  Maybe we have<br /> several limits:<br /></blockquote><br /></div> I didn't see
thatproposal, link? Because the idea of slowing down wal-writing sounds insane.<span class=""><font color="#888888"><br
/></font></span></blockquote>Hereit is:<br clear="all" /><a
href="http://www.postgresql.org/message-id/CAM3SWZQcyNxvPaskr-pxm8DeqH7_qevW7uqbhPCsg1FpSxKpoQ@mail.gmail.com">http://www.postgresql.org/message-id/CAM3SWZQcyNxvPaskr-pxm8DeqH7_qevW7uqbhPCsg1FpSxKpoQ@mail.gmail.com</a><br
/></div>--<br />Michael<br /></div></div> 

On Wed, Jun 5, 2013 at 6:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> I didn't see that proposal, link? Because the idea of slowing down
> wal-writing sounds insane.

It's not as insane as introducing an archiving gap, PANICing and
crashing, or running this hunk o junk I wrote
http://github.com/fdr/ratchet

On 06/05/2013 05:37 PM, Robert Haas wrote:

> - If it looks like we're going to exceed limit #3 before the
> checkpoint completes, we start exerting back-pressure on writers by
> making them wait every time they write WAL, probably in proportion to
> the number of bytes written.  We keep ratcheting up the wait until
> we've slowed down writers enough that will finish within limit #3.  As
> we reach limit #3, the wait goes to infinity; only read-only
> operations can proceed until the checkpoint finishes.

Alright, perhaps I am dense. I have read both this thread and the other 
one on better handling of archive command 
(http://www.postgresql.org/message-id/CAM3SWZQcyNxvPaskr-pxm8DeqH7_qevW7uqbhPCsg1FpSxKpoQ@mail.gmail.com). 
I recognize there are brighter minds than mine on this thread but I just 
honestly don't get it.

1. WAL writes are already fast. They are the fastest write we have 
because it is sequential.

2. We don't want them to be slow. We want data written to disk as 
quickly as possible without adversely affecting production. That's the 
point.

3. The spread checkpoints have always confused me. If anything we want a 
checkpoint to be fast and short because:

4. Bgwriter. We should be adjusting bgwriter so that it is writing 
everything in a manner that allows any checkpoint to be in the range of 
never noticed.

Now perhaps my customers workloads are different but for us:

1. Checkpoint timeout is set as high as reasonable, usually 30 minutes 
to an hour. I wish I could set them even further out.

2. Bgwriter is set to be aggressive but not obtrusive. Usually adjusting 
based on an actual amount of IO bandwidth it may take per second based 
on their IO constraints. (Note I know that wal_writer comes into play 
here but I honestly don't remember where and am reading up on it to 
refresh my memory).

3. The biggest issue we see with checkpoint segments is not running out 
of space because really.... 10GB is how many checkpoint segments? It is 
with wal_keep_segments. If we don't want to fill up the pg_xlog 
directory, put the wal logs that are for keep_segments elsewhere.

Other oddities:

Yes checkpoint_segments is awkward. We shouldn't have to set it at all. 
It should be gone. Basically we start with X amount perhaps to be set at 
initdb time. That X amount changes dynamically based on the amount of 
data being written. In order to not suffer from recycling and creation 
penalties we always keep X+N where N is enough to keep up with new data.

Along with the above, I don't see any reason for checkpoint_timeout. 
Because of bgwriter we should be able to rather indefinitely not worry 
about checkpoints (with a few exceptions such as pg_start_backup()). 
Perhaps a setting that causes a checkpoint to happen based on some 
non-artificial threshold (timeout) such as amount of data currently in 
need of a checkpoint?

Heikki said, "I propose that we do something similar, but not exactly 
the same. Let's have a setting, max_wal_size, to control the max. disk 
space reserved for WAL. Once that's reached (or you get close enough, so 
that there are still some segments left to consume while the checkpoint 
runs), a checkpoint is triggered.

In this proposal, the number of segments preallocated is controlled 
separately from max_wal_size, so that you can set max_wal_size high, 
without actually consuming that much space in normal operation. It's 
just a backstop, to avoid completely filling the disk, if there's a 
sudden burst of activity. The number of segments preallocated is 
auto-tuned, based on the number of segments used in previous checkpoint 
cycles. "

This makes sense except I don't see a need for the parameter. Why not 
just specify how the algorithm works and adhere to that without the need 
for another GUC? Perhaps at any given point we save 10% of available 
space (within a 16MB calculation) for pg_xlog, you hit it, we checkpoint 
and LOG EXACTLY WHY.

Instead of "running out of disk space PANIC" we should just write to an 
emergency location within PGDATA and log very loudly that the SA isn't 
paying attention. Perhaps if that area starts to get to an unhappy place 
we immediately bounce into read-only mode and log even more loudly that 
the SA should be fired. I would think read-only mode is safer and more 
polite than an PANIC crash.

I do not think we should worry about filling up the hard disk except to 
protect against data loss in the event. It is not user unfriendly to 
assume that a user will pay attention to disk space. Really?

Open to people telling me I am off in left field. Sorry if it is noise.

Sincerely,

JD



-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms   a rose in the deeps of my heart. - W.B. Yeats

On 06/05/2013 06:23 PM, Daniel Farina wrote:
>
> On Wed, Jun 5, 2013 at 6:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> I didn't see that proposal, link? Because the idea of slowing down
>> wal-writing sounds insane.
>
> It's not as insane as introducing an archiving gap, PANICing and
> crashing, or running this hunk o junk I wrote
> http://github.com/fdr/ratchet
>

Well certainly we shouldn't PANIC and crash but that is a simple fix. 
You have a backup write location and start logging really loudly that 
you are using it.

Sincerely,

JD

-- 
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms   a rose in the deeps of my heart. - W.B. Yeats

On Wed, Jun 5, 2013 at 8:23 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> It's not as insane as introducing an archiving gap, PANICing and
>> crashing, or running this hunk o junk I wrote
>> http://github.com/fdr/ratchet
>>
>
> Well certainly we shouldn't PANIC and crash but that is a simple fix. You
> have a backup write location and start logging really loudly that you are
> using it.

If I told you there were some of us who would prefer to attenuate the
rate that things get written rather than cancel or delay archiving for
a long period of time, would that explain the framing of the problem?

Or, is it that you understand that's what I want, but find the notion
of such a operation hard to relate to?

Or, am I misunderstanding your confusion?

Or, none of the above?

On 6/5/2013 10:07 PM, Daniel Farina wrote:
>
> If I told you there were some of us who would prefer to attenuate the
> rate that things get written rather than cancel or delay archiving for
> a long period of time, would that explain the framing of the problem?

I understand that based on what you said above.

> Or, is it that you understand that's what I want, but find the notion
> of such a operation hard to relate to?

I think this is where I am at. To me, you don't attenuate the rate that 
things get written, you fix the problem in needing to do so. The problem 
is one of provisioning. Please note that I am not suggesting there 
aren't improvements to be made, there absolutely are. I just wonder if 
we are looking in the right place (outside of some obvious badness like 
the PANIC running out of disk space).

> Or, am I misunderstanding your confusion?
To be honest part of my confusion was just trying to parse all the bits 
that people were talking about into a cohesive, "this is the actual 
problem".

Sincerely,

JD

On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> I just wonder if we are looking in the right place (outside of some obvious
> badness like the PANIC running out of disk space).

So you don't think we should PANIC on running out of disk space? If
you don't think we should do that, and you don't think that WAL
writing should be throttled, what's the alternative?


-- 
Peter Geoghegan

On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> On 6/5/2013 10:07 PM, Daniel Farina wrote:
>>
>>
>> If I told you there were some of us who would prefer to attenuate the
>> rate that things get written rather than cancel or delay archiving for
>> a long period of time, would that explain the framing of the problem?
>
>
> I understand that based on what you said above.
>
>
>> Or, is it that you understand that's what I want, but find the notion
>> of such a operation hard to relate to?
>
>
> I think this is where I am at. To me, you don't attenuate the rate that
> things get written, you fix the problem in needing to do so. The problem is
> one of provisioning. Please note that I am not suggesting there aren't
> improvements to be made, there absolutely are. I just wonder if we are
> looking in the right place (outside of some obvious badness like the PANIC
> running out of disk space).

Okay, well, I don't see the fact that the block device is faster than
the archive command as a "problem," it's just an artifact of the
ratios of performance of stuff in the system.  If one views archives
as a must-have, there's not much other choice than to attenuate.

An alternative is to buy a slower block device.  That'd accomplish the
same effect, but it's a pretty bizarre and heavyhanded way to go about
it, and not easily adaptive to, say, if I made the archive command
faster (in my case, I well could, with some work).

So, I don't think it's all that unnatural to allow for the flexibility
of a neat attenuation technique, and it's pretty important too.
Methinks.  Disagree?

Final thought: I can't really tell users to knock off what they're
doing on a large scale.  It's better to not provide abrupt changes in
service (like crashing or turning off everything for extended periods
while the archive uploads).  So, smoothness and predictability is
desirable.

On 6/5/2013 10:54 PM, Peter Geoghegan wrote:
> On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> I just wonder if we are looking in the right place (outside of some obvious
>> badness like the PANIC running out of disk space).
> So you don't think we should PANIC on running out of disk space? If
> you don't think we should do that, and you don't think that WAL
> writing should be throttled, what's the alternative?

As I mentioned in my previous email:

Instead of "running out of disk space PANIC" we should just write to an 
emergency location within PGDATA and log very loudly that the SA isn't 
paying attention. Perhaps if that area starts to get to an unhappy place 
we immediately bounce into read-only mode and log even more loudly that 
the SA should be fired. I would think read-only mode is safer and more 
polite than an PANIC crash.

I do not think we should worry about filling up the hard disk except to 
protect against data loss in the event. It is not user unfriendly to 
assume that a user will pay attention to disk space. Really?



JD

On Wed, Jun 5, 2013 at 11:05 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> On 6/5/2013 10:54 PM, Peter Geoghegan wrote:
>>
>> On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake <jd@commandprompt.com>
>> wrote:
>>>
>>> I just wonder if we are looking in the right place (outside of some
>>> obvious
>>> badness like the PANIC running out of disk space).
>>
>> So you don't think we should PANIC on running out of disk space? If
>> you don't think we should do that, and you don't think that WAL
>> writing should be throttled, what's the alternative?
>
>
> As I mentioned in my previous email:
>
>
> Instead of "running out of disk space PANIC" we should just write to an
> emergency location within PGDATA and log very loudly that the SA isn't
> paying attention. Perhaps if that area starts to get to an unhappy place we
> immediately bounce into read-only mode and log even more loudly that the SA
> should be fired. I would think read-only mode is safer and more polite than
> an PANIC crash.
>
> I do not think we should worry about filling up the hard disk except to
> protect against data loss in the event. It is not user unfriendly to assume
> that a user will pay attention to disk space. Really?

Okay, then I will say it's user unfriendly, especially for a transient
use of space, and particularly if there's no knob for said SA to
attenuate what's going on.  You appear to assume the SA can lean on
the application to knock off whatever is going on or provision more
disk in time, or that disk is reliable enough to meet one's goals.  In
my case, none of these precepts are true or desirable.

On 6/5/2013 11:09 PM, Daniel Farina wrote:
> Instead of "running out of disk space PANIC" we should just write to an
> emergency location within PGDATA and log very loudly that the SA isn't
> paying attention. Perhaps if that area starts to get to an unhappy place we
> immediately bounce into read-only mode and log even more loudly that the SA
> should be fired. I would think read-only mode is safer and more polite than
> an PANIC crash.
>
> I do not think we should worry about filling up the hard disk except to
> protect against data loss in the event. It is not user unfriendly to assume
> that a user will pay attention to disk space. Really?
> Okay, then I will say it's user unfriendly, especially for a transient
> use of space, and particularly if there's no knob for said SA to
> attenuate what's going on.  You appear to assume the SA can lean on
> the application to knock off whatever is going on or provision more
> disk in time, or that disk is reliable enough to meet one's goals.  In
> my case, none of these precepts are true or desirable.
I have zero doubt that in your case it is true and desirable. I just 
don't know that it is a positive solution to the problem as a whole. 
Your case is rather limited to your environment, which is rather limited 
to the type of user that your environment has. Which lends itself to the 
idea that this should be a Heroku Postgres thing, not a .Org wide thing.

Sincerely,

JD

On Wed, Jun 5, 2013 at 11:28 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> I have zero doubt that in your case it is true and desirable. I just don't
> know that it is a positive solution to the problem as a whole. Your case is
> rather limited to your environment, which is rather limited to the type of
> user that your environment has. Which lends itself to the idea that this
> should be a Heroku Postgres thing, not a .Org wide thing.

If you look through the -general archives, or on stack overflow you'll
find ample evidence that it is a problem that lots of people have.


-- 
Peter Geoghegan

Instead of "running out of disk space PANIC" we should just write to an emergency location within PGDATA

This merely buys you some time, but with aggressive and sustained write throughput you are left on the same spot. Practically speaking it's the same situation as increasing the pg_xlog disk space.

I agree it is better than PANIC, but read-only mode is definitely also a form of throttling; a much more abrupt and unfriendly one if I may add.

On 6/5/2013 11:31 PM, Peter Geoghegan wrote:
> On Wed, Jun 5, 2013 at 11:28 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> I have zero doubt that in your case it is true and desirable. I just don't
>> know that it is a positive solution to the problem as a whole. Your case is
>> rather limited to your environment, which is rather limited to the type of
>> user that your environment has. Which lends itself to the idea that this
>> should be a Heroku Postgres thing, not a .Org wide thing.
> If you look through the -general archives, or on stack overflow you'll
> find ample evidence that it is a problem that lots of people have.
>

Not to be unkind but the problems of the uniformed certainly are not the 
problems of the informed. Or perhaps they are certainly the problems of 
the informed :P. I do read -general and I don't see it much honestly. I 
don't watch stackoverflow that much but I am sure it probably does come 
up here, sometimes but I bet I can point once again to a lack of 
provisioning on their part.

This reminds me of the time that someone from Heroku said at PgEast, 
with a show of hands how many people here don't backup there database to 
S3. Almost everyone in the audience raised their hands.

Again, I don't question your need but just because it is hot and now 
doesn't mean it is healthy. I honestly do no see the requirement you are 
trying to represent as a need for the wider, production community.

(in short, not a single one of my customers would benefit from it, and 
90% of them are running databases Heroku can't.)

That is not a slight, honestly. I think your service is cool. I am just 
being honest.

Sincerely,

JD

On 06.06.2013 06:20, Joshua D. Drake wrote:
> 3. The spread checkpoints have always confused me. If anything we want a
> checkpoint to be fast and short because:

(I'm sure you know this, but:) If you perform a checkpoint as fast and 
short as possible, the sudden burst of writes and fsyncs will overwhelm 
the I/O subsystem, and slow down queries. That's what we saw before 
spread checkpoints: when a checkpoint happens, the response times of 
queries jumped up.

> 4. Bgwriter. We should be adjusting bgwriter so that it is writing
> everything in a manner that allows any checkpoint to be in the range of
> never noticed.

Oh, I see where you're going. Yeah, that would be one way to do it. 
However, spread checkpoints has pretty much the same effect. Imagine 
that you tune your system like this: disable bgwriter altogether, and 
set checkpoint_completion_target=0.9. With that, there will be a 
checkpoint in progress most of the time, because by the time one 
checkpoint completes, it's almost time to begin the next one already. In 
that case, the checkpointer will be slowly performing the writes, all 
the time, in the background, without affecting queries. The effect is 
the same as what you described above, except that it's the checkpointer 
doing the writing, not bgwriter.

As it happens, that's pretty much what you get with the default settings.

> Now perhaps my customers workloads are different but for us:
>
> 1. Checkpoint timeout is set as high as reasonable, usually 30 minutes
> to an hour. I wish I could set them even further out.
>
> 2. Bgwriter is set to be aggressive but not obtrusive. Usually adjusting
> based on an actual amount of IO bandwidth it may take per second based
> on their IO constraints. (Note I know that wal_writer comes into play
> here but I honestly don't remember where and am reading up on it to
> refresh my memory).

I've heard people just turning off bgwriter because it doesn't have much 
effect anyway. You might want to try that, and if checkpoints cause I/O 
spikes, raise checkpoint_completion_target instead.

> 3. The biggest issue we see with checkpoint segments is not running out
> of space because really.... 10GB is how many checkpoint segments? It is
> with wal_keep_segments. If we don't want to fill up the pg_xlog
> directory, put the wal logs that are for keep_segments elsewhere.

Yeah, wal_keep_segments is a hack. We should replace it with something 
else, like having a registry of standbys in the master, and how far 
they've streamed. That way the master could keep around the amount of 
WAL actually needed by them, not more not less. But that's a different 
story.

> Other oddities:
>
> Yes checkpoint_segments is awkward. We shouldn't have to set it at all.
> It should be gone.

The point of having checkpoint_segments or max_wal_size is to put a 
limit (albeit a soft one) on the amount of disk space used. If you don't 
care about that, I guess we could allow max_wal_size=-1 to mean 
infinite, and checkpoints would be driven off purely based on time, not 
WAL consumption.

> Basically we start with X amount perhaps to be set at
> initdb time. That X amount changes dynamically based on the amount of
> data being written. In order to not suffer from recycling and creation
> penalties we always keep X+N where N is enough to keep up with new data.

To clarify, here you're referring to controlling the number of WAL 
segments preallocated/recycled, rather than how often checkpoints are 
triggered. Currently, both are derived from checkpoint_segments, but I 
proposed to separate them. The above is exactly what I proposed to do 
for the preallocation/recycling, it would be tuned automatically, but 
you still need something like max_wal_size for the other thing, to 
trigger a checkpoint if too much WAL is being consumed.

> Along with the above, I don't see any reason for checkpoint_timeout.
> Because of bgwriter we should be able to rather indefinitely not worry
> about checkpoints (with a few exceptions such as pg_start_backup()).
> Perhaps a setting that causes a checkpoint to happen based on some
> non-artificial threshold (timeout) such as amount of data currently in
> need of a checkpoint?

Either I'm not understanding what you said, or you're confused. The 
point of checkpoint_timeout is put a limit on the time it will take to 
recover in case of crash. The relation between the two, 
checkpoint_timeout and how long it will take to recover after a crash, 
it not straightforward, but that's the best we have.

Bgwriter does not worry about checkpoints. By "amount of data currently 
in need of a checkpoint", do you mean the number of dirty buffers in 
shared_buffers, or something else? I don't see how or why that should 
affect when you perform a checkpoint.

> Heikki said, "I propose that we do something similar, but not exactly
> the same. Let's have a setting, max_wal_size, to control the max. disk
> space reserved for WAL. Once that's reached (or you get close enough, so
> that there are still some segments left to consume while the checkpoint
> runs), a checkpoint is triggered.
>
> In this proposal, the number of segments preallocated is controlled
> separately from max_wal_size, so that you can set max_wal_size high,
> without actually consuming that much space in normal operation. It's
> just a backstop, to avoid completely filling the disk, if there's a
> sudden burst of activity. The number of segments preallocated is
> auto-tuned, based on the number of segments used in previous checkpoint
> cycles. "
>
> This makes sense except I don't see a need for the parameter. Why not
> just specify how the algorithm works and adhere to that without the need
> for another GUC?

Because you want to limit the amount of disk space used for WAL. It's a 
soft limit, but still.

> Perhaps at any given point we save 10% of available
> space (within a 16MB calculation) for pg_xlog, you hit it, we checkpoint
> and LOG EXACTLY WHY.

Ah, but we don't know how much disk space is available. Even if we did, 
there might be quotas or other constraints on the amount that we can 
actually use. Or the DBA might not want PostgreSQL to use up all the 
space, because there are other processes on the same system that need it.

- Heikki

On 05.06.2013 23:16, Josh Berkus wrote:
>> For limiting the time required to recover after crash,
>> checkpoint_segments is awkward because it's difficult to calculate how
>> long recovery will take, given checkpoint_segments=X. A bulk load can
>> use up segments really fast, and recovery will be fast, while segments
>> full of random deletions can need a lot of random I/O to replay, and
>> take a long time. IMO checkpoint_timeout is a much better way to control
>> that, although it's not perfect either.
>
> This is true, but I don't see that your proposal changes this at all
> (for the better or for the worse).

Right, it doesn't. I explained this to justify that it's OK to replace 
checkpoint_segments with max_wal_size. If someone is trying to use 
checkpoint_segments to limit the time required to recover after crash, 
he might find the current checkpoint_segments setting more intuitive 
than my proposed max_wal_size. checkpoint_segments means "perform a 
checkpoint every X segments", so you know that after a crash, you will 
have to replay at most X segments (except that 
checkpoint_completion_target complicates that already). With 
max_wal_size, the relationship is not as clear.

What I tried to argue is that I don't think that's a serious concern.

>> I propose that we do something similar, but not exactly the same. Let's
>> have a setting, max_wal_size, to control the max. disk space reserved
>> for WAL. Once that's reached (or you get close enough, so that there are
>> still some segments left to consume while the checkpoint runs), a
>> checkpoint is triggered.
>
> Refinement of the proposal:
>
> 1. max_wal_size is a hard limit

I'd like to punt on that until later. Making it a hard limit would be a 
much bigger patch, and needs a lot of discussion how it should behave 
(switch to read-only mode, progressively slow down WAL writes, or what?) 
and how to implement it.

But I think there's a clear evolution path here; with current 
checkpoint_segments, it's not sensible to treat that as a hard limit. 
Once we have something like max_wal_size, defined in MB, it's much more 
sensible. So turning it into a hard limit could be a follow-up patch, if 
someone wants to step up to the plate.

> 2. checkpointing targets 50% of ( max_wal_size - wal_keep_segments )
>     to avoid lockup if checkpoint takes longer than expected.

Will also have to factor in checkpoint_completion_target.

>> Hmm, haven't thought about that. I think a better unit to set
>> wal_keep_segments in would also be MB, not segments.
>
> Well, the ideal unit from the user's point of view is *time*, not space.
>   That is, the user wants the master to keep, say, "8 hours of
> transaction logs", not any amount of MB.  I don't want to complicate
> this proposal by trying to deliver that, though.

OTOH, if you specify it in terms of time, then you don't have any limit 
on the amount of disk space required.

>> In this proposal, the number of segments preallocated is controlled
>> separately from max_wal_size, so that you can set max_wal_size high,
>> without actually consuming that much space in normal operation. It's
>> just a backstop, to avoid completely filling the disk, if there's a
>> sudden burst of activity. The number of segments preallocated is
>> auto-tuned, based on the number of segments used in previous checkpoint
>> cycles.
>
> "based on"; can you give me your algorithmic thinking here?  I'm
> thinking we should have some calculation of last cycle size and peak
> cycle size so that bursty workloads aren't compromised.

Yeah, something like that :-). I was thinking of letting the estimate 
decrease like a moving average, but react to any increases immediately. 
Same thing we do in bgwriter to track buffer allocations:

>     /*
>      * Track a moving average of recent buffer allocations.  Here, rather than
>      * a true average we want a fast-attack, slow-decline behavior: we
>      * immediately follow any increase.
>      */
>     if (smoothed_alloc <= (float) recent_alloc)
>         smoothed_alloc = recent_alloc;
>     else
>         smoothed_alloc += ((float) recent_alloc - smoothed_alloc) /
>             smoothing_samples;
>

- Heikki

On 6/6/2013 1:11 AM, Heikki Linnakangas wrote:
>
> (I'm sure you know this, but:) If you perform a checkpoint as fast and 
> short as possible, the sudden burst of writes and fsyncs will 
> overwhelm the I/O subsystem, and slow down queries. That's what we saw 
> before spread checkpoints: when a checkpoint happens, the response 
> times of queries jumped up.

That isn't quite right. Previously we had lock issues as well and 
checkpoints would take considerable time to complete. What I am talking 
about is that the background writer (and wal writer where applicable) 
have done all the work before a checkpoint is even called. Consider that 
everyone of my clients that I am active with sets the 
checkpoint_completion_target to 0.9. With a proper bgwriter config this 
works.

>
>> 4. Bgwriter. We should be adjusting bgwriter so that it is writing
>> everything in a manner that allows any checkpoint to be in the range of
>> never noticed.
>
> Oh, I see where you're going.

O.k. good. I am not nuts :D
> Yeah, that would be one way to do it. However, spread checkpoints has 
> pretty much the same effect. Imagine that you tune your system like 
> this: disable bgwriter altogether, and set 
> checkpoint_completion_target=0.9. With that, there will be a 
> checkpoint in progress most of the time, because by the time one 
> checkpoint completes, it's almost time to begin the next one already. 
> In that case, the checkpointer will be slowly performing the writes, 
> all the time, in the background, without affecting queries. The effect 
> is the same as what you described above, except that it's the 
> checkpointer doing the writing, not bgwriter.

O.k. if that is true, then we have redundant systems and we need to 
remove one of them.
> Yeah, wal_keep_segments is a hack. We should replace it with something 
> else, like having a registry of standbys in the master, and how far 
> they've streamed. That way the master could keep around the amount of 
> WAL actually needed by them, not more not less. But that's a different 
> story.
>
>> Other oddities:
>>
>> Yes checkpoint_segments is awkward. We shouldn't have to set it at all.
>> It should be gone.
>
> The point of having checkpoint_segments or max_wal_size is to put a 
> limit (albeit a soft one) on the amount of disk space used. If you 
> don't care about that, I guess we could allow max_wal_size=-1 to mean 
> infinite, and checkpoints would be driven off purely based on time, 
> not WAL consumption.
>

I would not only agree with that, I would argue that max_wal_size 
doesn't need to be there at least as a default. Perhaps as an "advanced" 
configuration option that only those in the know see.


>> Basically we start with X amount perhaps to be set at
>> initdb time. That X amount changes dynamically based on the amount of
>> data being written. In order to not suffer from recycling and creation
>> penalties we always keep X+N where N is enough to keep up with new data.
>
> To clarify, here you're referring to controlling the number of WAL 
> segments preallocated/recycled, rather than how often checkpoints are 
> triggered. Currently, both are derived from checkpoint_segments, but I 
> proposed to separate them. The above is exactly what I proposed to do 
> for the preallocation/recycling, it would be tuned automatically, but 
> you still need something like max_wal_size for the other thing, to 
> trigger a checkpoint if too much WAL is being consumed.

You think so? I agree with 90% of this paragraph but it seems to me that 
we can find an algortihm that manages this without the idea of 
max_wal_size (at least as a user settable).

>> Along with the above, I don't see any reason for checkpoint_timeout.
>> Because of bgwriter we should be able to rather indefinitely not worry
>> about checkpoints (with a few exceptions such as pg_start_backup()).
>> Perhaps a setting that causes a checkpoint to happen based on some
>> non-artificial threshold (timeout) such as amount of data currently in
>> need of a checkpoint?
>
> Either I'm not understanding what you said, or you're confused. The 
> point of checkpoint_timeout is put a limit on the time it will take to 
> recover in case of crash. The relation between the two, 
> checkpoint_timeout and how long it will take to recover after a crash, 
> it not straightforward, but that's the best we have.

I may be confused but it is my understanding that bgwriter writes out 
the data from the shared buffer cache that is dirty based on an interval 
and a max pages written. If we are writing data continuously, we don't 
need checkpoints except for special cases (like pg_start_backup())?
>
> Bgwriter does not worry about checkpoints. By "amount of data 
> currently in need of a checkpoint", do you mean the number of dirty 
> buffers in shared_buffers, or something else? I don't see how or why 
> that should affect when you perform a checkpoint.
>
>> Heikki said, "I propose that we do something similar, but not exactly
>> the same. Let's have a setting, max_wal_size, to control the max. disk
>> space reserved for WAL. Once that's reached (or you get close enough, so
>> that there are still some segments left to consume while the checkpoint
>> runs), a checkpoint is triggered.
>>
>> In this proposal, the number of segments preallocated is controlled
>> separately from max_wal_size, so that you can set max_wal_size high,
>> without actually consuming that much space in normal operation. It's
>> just a backstop, to avoid completely filling the disk, if there's a
>> sudden burst of activity. The number of segments preallocated is
>> auto-tuned, based on the number of segments used in previous checkpoint
>> cycles. "
>>
>> This makes sense except I don't see a need for the parameter. Why not
>> just specify how the algorithm works and adhere to that without the need
>> for another GUC?
>
> Because you want to limit the amount of disk space used for WAL. It's 
> a soft limit, but still.
>

Why? This is the point that confuses me. Why do we care? We don't care 
how much disk space PGDATA takes... why do we all of a sudden care about 
pg_xlog?


>> Perhaps at any given point we save 10% of available
>> space (within a 16MB calculation) for pg_xlog, you hit it, we checkpoint
>> and LOG EXACTLY WHY.
>
> Ah, but we don't know how much disk space is available. Even if we 
> did, there might be quotas or other constraints on the amount that we 
> can actually use. Or the DBA might not want PostgreSQL to use up all 
> the space, because there are other processes on the same system that 
> need it.
>

We could however know how much disk space is available.

Sincerely,

JD

> - Heikki
>

On 06.06.2013 11:42, Joshua D. Drake wrote:
> On 6/6/2013 1:11 AM, Heikki Linnakangas wrote:
>>> Yes checkpoint_segments is awkward. We shouldn't have to set it at all.
>>> It should be gone.
>>
>> The point of having checkpoint_segments or max_wal_size is to put a
>> limit (albeit a soft one) on the amount of disk space used. If you
>> don't care about that, I guess we could allow max_wal_size=-1 to mean
>> infinite, and checkpoints would be driven off purely based on time,
>> not WAL consumption.
>
> I would not only agree with that, I would argue that max_wal_size
> doesn't need to be there at least as a default. Perhaps as an "advanced"
> configuration option that only those in the know see.

Well, we have checkpoint_segments=3 as the default currently, which in 
the proposed scheme would be about equal to max_wal_size=120MB. For 
better or worse, our defaults are generally geared towards small 
systems, and that sounds about right for that.

>>> Basically we start with X amount perhaps to be set at
>>> initdb time. That X amount changes dynamically based on the amount of
>>> data being written. In order to not suffer from recycling and creation
>>> penalties we always keep X+N where N is enough to keep up with new data.
>>
>> To clarify, here you're referring to controlling the number of WAL
>> segments preallocated/recycled, rather than how often checkpoints are
>> triggered. Currently, both are derived from checkpoint_segments, but I
>> proposed to separate them. The above is exactly what I proposed to do
>> for the preallocation/recycling, it would be tuned automatically, but
>> you still need something like max_wal_size for the other thing, to
>> trigger a checkpoint if too much WAL is being consumed.
>
> You think so? I agree with 90% of this paragraph but it seems to me that
> we can find an algortihm that manages this without the idea of
> max_wal_size (at least as a user settable).

We are in a violent agreement :-). max_wal_size would not directly 
affect the preallocation of segments. The preallocation would be driven 
off the actual number of segments used in previous checkpoint cycles, 
not on max_wal_size.

Now, max_wal_size would affect when checkpoints happen (ie. if you're 
about to reach max_wal_size, a checkpoint would be triggered), which 
would in turn affect the number of segments used between cycles. But 
there would be no direct connection between the two; the code to 
calculate how much to preallocate would not refer to max_wal_size.

Maybe max_wal_size should set an upper limit on how much to preallocate, 
though. If you want to limit the WAL size, we probably shouldn't exceed 
it on purpose by preallocating segments, even if the algorithm based on 
previous cycles suggests says we should. This situation would arise if 
the checkpoints can't keep up, so that each checkpoint cycle is longer 
than we'd want, and we'd exceed max_wal_size because of that.

>>> This makes sense except I don't see a need for the parameter. Why not
>>> just specify how the algorithm works and adhere to that without the need
>>> for another GUC?
>>
>> Because you want to limit the amount of disk space used for WAL. It's
>> a soft limit, but still.
>
> Why? This is the point that confuses me. Why do we care? We don't care
> how much disk space PGDATA takes... why do we all of a sudden care about
> pg_xlog?

Hmm, dunno. We always have had checkpoint_segments setting to limit 
that, I was just thinking of retaining that functionality.

A few reasons spring to mind: First, running out of WAL space leads to a 
PANIC, which is not nice (I know, we talked about fixing that). 
Secondly, because we can. If a user inserts 10 GB of data into a table, 
we'll have to just store it, but with WAL, we can always issue a 
checkpoint to shrink it. People have asked for quotas for user data too, 
so some people do want to limit disk usage.

Mind you, it's possible to have a tiny database with a high TPS rate, 
such that the WAL grows really big compared to the size of the user 
data. Something with a small hot table that's updated a lot. In such a 
scenario, limiting the WAL size make sense, and it won't affect 
performance much either because checkpointing a small database is very 
cheap.

- Heikki

On 05.06.2013 22:18, Kevin Grittner wrote:
> Heikki Linnakangas<hlinnakangas@vmware.com>  wrote:
>
>> I was not thinking of making it a hard limit. It would be just
>> like checkpoint_segments from that point of view - if a
>> checkpoint takes a long time, max_wal_size might still be
>> exceeded.
>
> Then I suggest we not use exactly that name.  I feel quite sure we
> would get complaints from people if something labeled as "max" was
> exceeded -- especially if they set that to the actual size of a
> filesystem dedicated to WAL files.

You're probably right. Any suggestions for a better name? 
wal_size_soft_limit?

- Heikki

On 05.06.2013 22:24, Fujii Masao wrote:
> On Thu, Jun 6, 2013 at 3:35 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com>  wrote:
>> The checkpoint spreading code already tracks if the checkpoint is "on
>> schedule", and it takes into account both checkpoint_timeout and
>> checkpoint_segments. Ie. if you consume segments faster than expected, the
>> checkpoint will speed up as well. Once checkpoint_segments is reached, the
>> checkpoint will complete ASAP, with no delays to spread it out.
>
> Yep, right. One problem is that this mechanism doesn't work in the standby.

Sure it does:

> commit 71815306e9e1ba7e95752779d2ad51d0c2b9c747
> Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
> Date:   Wed Jun 9 15:04:07 2010 +0000
>
>     In standby mode, respect checkpoint_segments in addition to
>     checkpoint_timeout to trigger restartpoints. We used to deliberately only
>     do time-based restartpoints, because if checkpoint_segments is small we
>     would spend time doing restartpoints more often than really necessary.
>     But now that restartpoints are done in bgwriter, they're not as
>     disruptive as they used to be. Secondly, because streaming replication
>     stores the streamed WAL files in pg_xlog, we want to clean it up more
>     often to avoid running out of disk space when checkpoint_timeout is large
>     and checkpoint_segments small.
>
>     Patch by Fujii Masao, with some minor changes by me.

One problam with that is that if you set checkpoint_segments (or 
max_wal_size, under the proposal) lower in the standby than in the 
master, we can't do restartpoints any more frequently than checkpoints 
have happened in the master. I wasn't planning to do anything about that.

- Heikki

Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
> On 05.06.2013 22:18, Kevin Grittner wrote:
>> Heikki Linnakangas<hlinnakangas@vmware.com>  wrote:
>>
>>> I was not thinking of making it a hard limit. It would be just
>>> like checkpoint_segments from that point of view - if a
>>> checkpoint takes a long time, max_wal_size might still be
>>> exceeded.
>>
>> Then I suggest we not use exactly that name.  I feel quite sure we
>> would get complaints from people if something labeled as "max" was
>> exceeded -- especially if they set that to the actual size of a
>> filesystem dedicated to WAL files.
>
> You're probably right. Any suggestions for a better name?
> wal_size_soft_limit?

After reading later posts on the thread, I would be inclined to
support making it a hard limit and adapting the behavior to match.
I'm pretty sure I've seen at least one case where a separate
filesystem has been allocated for WAL which has been unexpectedly
filled.  People would like some way to deal with that.

I'm also concerned about the "spin up" from idle to high activity.
Perhaps a "min" should also be present, to mitigate repeated short
checkpoint cycles for "bursty" environments?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 06.06.2013 15:31, Kevin Grittner wrote:
> Heikki Linnakangas<hlinnakangas@vmware.com>  wrote:
>> On 05.06.2013 22:18, Kevin Grittner wrote:
>>> Heikki Linnakangas<hlinnakangas@vmware.com>   wrote:
>>>
>>>> I was not thinking of making it a hard limit. It would be just
>>>> like checkpoint_segments from that point of view - if a
>>>> checkpoint takes a long time, max_wal_size might still be
>>>> exceeded.
>>>
>>> Then I suggest we not use exactly that name.  I feel quite sure we
>>> would get complaints from people if something labeled as "max" was
>>> exceeded -- especially if they set that to the actual size of a
>>> filesystem dedicated to WAL files.
>>
>> You're probably right. Any suggestions for a better name?
>> wal_size_soft_limit?
>
> After reading later posts on the thread, I would be inclined to
> support making it a hard limit and adapting the behavior to match.

Well, that's a lot more difficult to implement. And even if we have a 
hard limit, I think many people would still want to have a soft limit 
that would trigger a checkpoint, but would not stop WAL writes from 
happening. So what would we call that?

I'd love to see a hard limit too, but I see that as an orthogonal feature.

How about calling the (soft) limit "checkpoint_wal_size"? That goes well 
together with checkpoint_timeout, meaning that a checkpoint will be 
triggered if you're about to exceed the given size.

> I'm also concerned about the "spin up" from idle to high activity.
> Perhaps a "min" should also be present, to mitigate repeated short
> checkpoint cycles for "bursty" environments?

With my proposal, you wouldn't get repeated short checkpoint cycles with 
bursts. The checkpoint interval would be controlled by 
checkpoint_timeout, and checkpoint_wal_size. If there is a lot of 
activity, then checkpoints will happen more frequently, as 
checkpoint_wal_size is reached sooner. But it would not depend on the 
activity in previous checkpoint cycles, only the current one, so it 
would not make a difference if you have a continuously high load, or a 
bursty one.

The history would matter for the calculation of how many segments to 
preallocate/recycle, however. Under the proposal, that would be 
calculated separately from checkpoint_wal_size, and for that we'd use 
some kind of a moving average of how many segments were used in previous 
cycles. A min setting might be useful for that. We could also try to 
make WAL file creation cheaper, ie. by using posix_fallocate(), as was 
proposed in another thread, and doing it in bgwriter or walwriter. That 
would make it less important to get the estimate right, from a 
performance point of view, although you'd still want to get it right to 
avoid running out of disk space (having the segments preallocated 
ensures that they are available when needed).

- Heikki

Daniel,

So your suggestion is that if archiving is falling behind, we should
introduce delays on COMMIT in order to slow down the rate of WAL writing?

Just so I'm clear.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

>> Then I suggest we not use exactly that name.  I feel quite sure we
>> would get complaints from people if something labeled as "max" was
>> exceeded -- especially if they set that to the actual size of a
>> filesystem dedicated to WAL files.
> 
> You're probably right. Any suggestions for a better name?
> wal_size_soft_limit?

"checkpoint_size_limit", or something similar.  That is, what you're
defining is:

"this is the size at which we trigger a checkpoint even if
checkpoint_timeout has not been exceeded".

However, I think it's worth considering: if we're doing this "sizing
checkpoints based on prior cycles" thing, do we really need a size_limit
*at all* for most users?   I can see how a hard limit is useful, but not
how a soft limit is.

Most of our users most of the time don't care how large WAL is as long
as it doesn't exceed disk space.  And on most databases, hitting
checkpoint_timeout is more frequent than hitting checkpoint_segments --
at least in my substantial performance-tuning experience.  So I think
most users would prefer a setting which essentially says "make WAL as
big as it has to be in order to maximize throughput", and wouldn't worry
about the disk space.

>
> Yeah, something like that :-). I was thinking of letting the estimate
> decrease like a moving average, but react to any increases immediately.
> Same thing we do in bgwriter to track buffer allocations:

Seems reasonable.  Given the behavior of xlog, I'd want to adjust the
algo so that peak usage on a 24-hour basis would affect current
preallocation.  That is, if a site regularly has a peak from 2-3pm where
they're using 180 segments/cycle, then they should still be somewhat
higher at 2am than a database which doesn't have that peak.  I'm pretty
sure that the bgwriter's moving average cycles much shorter time scales
than that.

>> Well, the ideal unit from the user's point of view is *time*, not space.
>>   That is, the user wants the master to keep, say, "8 hours of
>> transaction logs", not any amount of MB.  I don't want to complicate
>> this proposal by trying to deliver that, though.
>
> OTOH, if you specify it in terms of time, then you don't have any limit
> on the amount of disk space required.

Well, the best setup from my perspective as a remote DBA for a lot of
clients would be two-factor:

wal_keep_time: ##hr
wal_keep_size_limit: ##GB

That is, we would try to keep ##hr of WAL around for the standbys,
unless that amount exceeded ##GB (at which point we'd write a warning to
the logs).  If max_wal_size was a hard limit, we wouldn't need
wal_keep_size_limit, of course.

However, to some degree Andres' work will render all this
wal_keep_segments stuff obsolete by letting the master track what
segment was last consumed by each replica, so I don't think it's worth
pursuing this line of thinking a lot further.

In any case, I'm just pointing out that we need to think of
wal_keep_segments as part of the total WAL size, and not as something
seperate, because that's confusing our users.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 06.06.2013 20:24, Josh Berkus wrote:
>> Yeah, something like that :-). I was thinking of letting the estimate
>> decrease like a moving average, but react to any increases immediately.
>> Same thing we do in bgwriter to track buffer allocations:
>
> Seems reasonable.

Here's a patch implementing that. Docs not updated yet. I did not change
the way checkpoint_segments triggers checkpoints - that'll can be a
separate patch. This only decouples the segment preallocation behavior
from checkpoint_segments. With the patch, you can set
checkpoint_segments really high, without consuming that much disk space
all the time.

> Given the behavior of xlog, I'd want to adjust the
> algo so that peak usage on a 24-hour basis would affect current
> preallocation.  That is, if a site regularly has a peak from 2-3pm where
> they're using 180 segments/cycle, then they should still be somewhat
> higher at 2am than a database which doesn't have that peak.  I'm pretty
> sure that the bgwriter's moving average cycles much shorter time scales
> than that.

Makes sense. I didn't implement that in the attached, though.

Having a separate option to specify a minimum number of segments (or
rather minimum size in MB) to keep preallocated would at least allow a
DBA to set that manually, based on the observed peak. I didn't implement
such a manual option in the attached, but that would be easy.

- Heikki

>> Given the behavior of xlog, I'd want to adjust the
>> algo so that peak usage on a 24-hour basis would affect current
>> preallocation.  That is, if a site regularly has a peak from 2-3pm where
>> they're using 180 segments/cycle, then they should still be somewhat
>> higher at 2am than a database which doesn't have that peak.  I'm pretty
>> sure that the bgwriter's moving average cycles much shorter time scales
>> than that.
> 
> Makes sense. I didn't implement that in the attached, though.

It's possible that it won't matter.  Performance testing will tell us.

> Having a separate option to specify a minimum number of segments (or
> rather minimum size in MB) to keep preallocated would at least allow a
> DBA to set that manually, based on the observed peak. I didn't implement
> such a manual option in the attached, but that would be easy.

Yeah, I'd really like to get away from adding manual options which need
to be used in non-specialty cases.  I think we'll need one at some point
-- there are DB applications which are VERY bursty -- but let's not
start there and see if we can make reasonable autotuning work.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 6/6/13 4:42 AM, Joshua D. Drake wrote:
>
> On 6/6/2013 1:11 AM, Heikki Linnakangas wrote:
>>
>> (I'm sure you know this, but:) If you perform a checkpoint as fast and
>> short as possible, the sudden burst of writes and fsyncs will
>> overwhelm the I/O subsystem, and slow down queries. That's what we saw
>> before spread checkpoints: when a checkpoint happens, the response
>> times of queries jumped up.
>
> That isn't quite right. Previously we had lock issues as well and
> checkpoints would take considerable time to complete. What I am talking
> about is that the background writer (and wal writer where applicable)
> have done all the work before a checkpoint is even called.

That is not possible, and if you look deeper at a lot of workloads
you'll eventually see why.  I'd recommend grabbing snapshots of
pg_buffercache output from a lot of different types of servers and see
what the usage count distribution looks like.  That's what did in order
to create all of the behaviors the current background writer code caters
to.  Attached is a small spreadsheet that shows the main two extremes
here, from one of my old talks.  "Effective buffer cache system" is full
of usage count 5 pages, while the "Minimally effective buffer cache" one
is all usage count 1 or 0.  We don't have redundant systems here; we
have two that aim at distinctly different workloads.  That's one reason
why splitting them apart ended up being necessary to move forward, they
really don't overlap very much on some servers.

Sampling a few servers that way was where the controversial idea of
scanning the whole buffer pool every few minutes even without activity
came from too.  I found a bursty real world workload where that was
necessary to keep buffers clean usefully, and that heuristic helped them
a lot.  I too would like to visit the exact logic used, but I could cook
up a test case where it's useful again if people really doubt it has any
value.  There's one in the 2007 archives somewhere.

The reason the checkpointer code has to do this work, and it has to
spread the writes out, is that on some systems the hot data set hits a
high usage count.  If shared_buffers is 8GB and at any moment 6GB of it
has a usage count of 5, which absolutely happens on many busy servers,
the background writer will do almost nothing useful.  It won't and
shouldn't touch buffers unless their usage count is low.  Those heavily
referenced blocks will only be written to disk once per checkpoint cycle.

Without the spreading, in this example you will drop 6GB into "Dirty
Memory" on a Linux server, call fdatasync, and the server might stop
doing any work at all for *minutes* of time.  Easiest way to see it
happen is to set checkpoint_completion_target to 0, put the filesystem
on ext3, and have a server with lots of RAM.  I have a monitoring tool
that graphs Dirty Memory over time because this problem is so nasty even
with the spreading code in place.

There is this idea that pops up sometimes that a background writer write
is better than a checkpoint one.  This is backwards.  A dirty block must
be written at least once per checkpoint.  If you only write it once per
checkpoint, inside of the checkpoint process, that is the ideal.  It's
what you want for best performance when it's possible.

At the same time, some workloads churn through a lot of low usage count
data, rather than building up a large block of high usage count stuff.
On those your best hope for low latency is to crank up the background
writer and let it try to stay ahead of backends with the writes.  The
checkpointer won't have nearly as much work to do in that situation.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

On 6/6/13 4:41 AM, Heikki Linnakangas wrote:

> I was thinking of letting the estimate
> decrease like a moving average, but react to any increases immediately.
> Same thing we do in bgwriter to track buffer allocations:

Combine what your submitted patch does and this idea, and you'll have 
something I prototyped a few years ago.  I took the logic and tested it 
out in user space by parsing the output from log_checkpoints to see how 
many segments were being used.  That approach coughed out a value about 
as good for checkpoint_segments as I picked by hand.

The main problem was it liked to over-tune the segments based on a small 
bursts of activity, leaving a value higher than you might want to use 
the rest of the time.  The background writer didn't worry about this 
very much because the cost of making a mistake for one 200ms cycle was 
pretty low.  Setting checkpoint_segments high is a more expensive issue.  When I set these by hand, I'll aim more to
covera 99th percentile of 
 
the maximum segments number rather than every worst case seen.

I don't think that improvement is worth spending very much effort on 
though.  The moving average approach is more than good enough in most 
cases.  I've wanted checkpoint_segments to go away in exactly this 
fashion for a while.

The general complaint the last time I suggested a change in this area, 
to make checkpoint_segments larger for the average user, was that some 
people had seen workloads where that was counterproductive.  Pretty sure 
Kevin Grittner said he'd seen that happen.  That's how I remember this 
general idea dying the last time, and I still don't have enough data to 
refute that doesn't happen.

As far as the UI, if it's a soft limit I'd suggest wal_size_target for 
the name.  What I would like to see is a single number here in memory 
units that replaces both checkpoint_segments and wal_keep_segments.  If 
you're willing to use a large chunk of disk space to handle either one 
of activity spikes or the class of replication issues wal_keep_segments 
targets, I don't see why you'd want to ban using that space for the 
other one too.

To put some perspective on how far we've been able to push this in the 
field with minimal gripes, the repmgr tool requires wal_keep_segments be >=5000, which works out to 78GB.  I still see
somepeople use 73GB SAS 
 
drives in production servers for their WAL files, but that's the only 
time I've seen that number become scary when deploying repmgr. 
Meanwhile, the highest value for checkpoint_segments I've set based on 
real activity levels was 1024, on a server where checkpoint_timeout is 
15 minutes (and can be no shorter without checkpoint spikes).  At no 
point during that fairly difficult but of tuning work did 
checkpoint_segments do anything but get in the way.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

On Thu, Jun 6, 2013 at 10:43 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> The general complaint the last time I suggested a change in this area, to
> make checkpoint_segments larger for the average user, was that some people
> had seen workloads where that was counterproductive.  Pretty sure Kevin
> Grittner said he'd seen that happen.  That's how I remember this general
> idea dying the last time, and I still don't have enough data to refute that
> doesn't happen.

My guess is that, with Heikki's patch, a lot of the value of keeping
checkpoint_segments low should go away - because if there wasn't much
activity, checkpoint_segments will in effect remain low, even the
configured value is not so low.  And if activity is high, well then
larger checkpoint_segments will be better anyway.

(As to why smaller checkpoint_segments can help, here's my guess: if
checkpoint_segments is relatively small, then when we recycle a
segment we're likely to find its data already in cache.  That's a lot
better than reading it back in from disk just to overwrite the data.)

> As far as the UI, if it's a soft limit I'd suggest wal_size_target for the
> name.  What I would like to see is a single number here in memory units that
> replaces both checkpoint_segments and wal_keep_segments.  If you're willing
> to use a large chunk of disk space to handle either one of activity spikes
> or the class of replication issues wal_keep_segments targets, I don't see
> why you'd want to ban using that space for the other one too.

This isn't really making sense to me.  I don't think we should assume
that someone who wants to keep WAL around for replication also wants
to wait longer between checkpoints.  Those are two quite different
things.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> wrote:

> (As to why smaller checkpoint_segments can help, here's my guess:
> if checkpoint_segments is relatively small, then when we recycle
> a segment we're likely to find its data already in cache.  That's
> a lot better than reading it back in from disk just to overwrite
> the data.)

My recollection on this topic is that before pg_upgrade Wisconsin
Courts had to upgrade all of the geographically distributed
databases to a new PostgreSQL version, and that was being done with
pg_dump piped to psql in conjunction with the rollout of new
hardware (according to the four-year replacement policy).  The
upgrade process involved a DBA staying late centrally while the
conversion ran, a field tech staying late on the client site to
haul off the old box once successful conversion was confirmed, a
business analyst staying late to confirm proper operation after the
conversion, and a web programmer staying late to confirm that all
web interfaces showed proper data flow post-conversion.  Every
minute shaved off of the upgrade process saved a lot of staff time,
so the DBA team tested the conversion process very carefully.

Some findings were unsurprising, like that a direct connection
between the servers using a cross-wired network patch cable was
faster than plugging both machines into the same switch.  But we
tested all of our assumptions, and re-tested the surprising ones.
One such surprise was that the conversion ran faster, even on a
"largish" database of around 200GB, with 3 checkpoint_segments than
with larger settings.  The difference was significant and
repeatable. My personal theory was that segments were being
recycled and overwritten while still in the battery-backed
controller cache, so writes from multiple cycles evaporated in the
cache, reducing total physical disk writes.  Greg Smith blew that
theory out of the water by finding the same behavior on his laptop,
which did not have a write-back cache.  AFAIK, this mystery remains
unsolved, although Robert's idea above sounds plausible.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jun 7, 2013 at 3:14 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Some findings were unsurprising, like that a direct connection
> between the servers using a cross-wired network patch cable was
> faster than plugging both machines into the same switch.  But we
> tested all of our assumptions, and re-tested the surprising ones.
> One such surprise was that the conversion ran faster, even on a
> "largish" database of around 200GB, with 3 checkpoint_segments than
> with larger settings.

!

I can't account for that finding, because my experience is that small
checkpoint_segments settings lead to *terrible* bulk restore
performance.

*scratches head*

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> wrote:
> Kevin Grittner <kgrittn@ymail.com> wrote:

>> One such surprise was that the conversion ran faster, even on a
>> "largish" database of around 200GB, with 3 checkpoint_segments
>> than with larger settings.
>
> !
>
> I can't account for that finding, because my experience is that
> small checkpoint_segments settings lead to *terrible* bulk
> restore performance.
>
> *scratches head*

Perhaps it was due to some of the "running with scissors" settings
we used for the upgrade process that we don't normally use, like
fsync = off and full_page_writes = off.  We also used a larger than
usual maintenance_work_mem which reduced disk sorts, possibly
helping the WAL files to remain cached on the controller.

Maybe it also helped keep data flowing to the actual disks, so that
it didn't alternate between "idle" and "glutted" states, although I
don't have any evidence to support that theory.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 6/7/13 2:43 PM, Robert Haas wrote:
>> name.  What I would like to see is a single number here in memory units that
>> replaces both checkpoint_segments and wal_keep_segments.
>
> This isn't really making sense to me.  I don't think we should assume
> that someone who wants to keep WAL around for replication also wants
> to wait longer between checkpoints.  Those are two quite different
> things.

It's been years since I saw anyone actually using checkpoint_segments as 
that sort of limit.  I see a lot of sites pushing the segments limit up 
and then using checkpoint_timeout carefully.  It's pretty natural to say 
"I don't want to go more than X minutes between checkpoints".  The case 
for wanting to say "I don't want to go more than X MB between 
checkpoints" instead, motivated by not wanting too much activity to 
queue between them, I'm just not seeing demand for that now.

The main reason I do see people paying attention to checkpoint_segments 
still is to try and put a useful bound on WAL disk space usage.  That's 
the use case I think overlaps with wal_keep_segments such that you might 
replace both of them.  I think we really only need one control that 
limits how much WAL space is expected inside of pg_xlog, and it should 
be easy and obvious how to set it.

The more I look at this checkpoint_segments patch, the more I wonder why 
it's worth even bothering with anything but a disk space control here. 
checkpoint_segments is turning into an internal implementation detail 
most sites I see wouldn't miss at all.  Rather than put work into 
autotuning it, I'd be happy to eliminate checkpoint_segments altogther, 
in favor of a WAL disk space limit.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

On 06/07/2013 01:00 AM, Josh Berkus wrote:
> Daniel,
>
> So your suggestion is that if archiving is falling behind, we should
> introduce delays on COMMIT in order to slow down the rate of WAL writing?
Delaying commit wouldn't be enough; consider a huge COPY, which can
produce a lot of WAL at a high rate without a convenient point to delay at.

I expect a delay after writing an xlog record would make a more suitable
write-rate throttle, though I'd want to be sure the extra branch didn't
hurt performance significantly. Branch prediction hints would help;
since we don't *care* if the delay branch is slow and causes pipeline
stalls, tagging the no-delay branch as likely would probably deal with
that concern for supported compilers/platforms.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 06/06/2013 03:21 PM, Joshua D. Drake wrote:
>
> Not to be unkind but the problems of the uniformed certainly are not
> the problems of the informed. Or perhaps they are certainly the
> problems of the informed :P.
I'm not convinced that's a particularly good argument not to improve
something. Sure, it might be a usability issue not a purely technical
issue, but that IMO doesn't make it much less worth fixing.

Bad usability puts people off early, before they can become productive
and helpful community members. It also puts others off trying the
software at all by reputation alone.

In any case, I don't think this is an issue of the informed vs
uninformed. It's also a matter of operational sanity at scale. "The
sysadmin" can't watch 100,000 individual servers and jump in to make
minute tweaks - nor should they have to when some auto-tuning could
obviate the need.

The same issue exists with vacuum - it's hard for basic users to
understand, so they misconfigure it and often achieve the opposite
results to what they need. It's been getting better, but some
feedback-based control would make a world of difference when running Pg.

In this I really have to agree with Hekki and Daniel - more usable and
preferably feedback-tuned defaults would be really, really nice to have,
though I'd want good visibility (logging, SHOW commands, etc) into what
they were doing and options to override for special cases.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 6/6/13 4:09 PM, Heikki Linnakangas wrote:
> On 06.06.2013 20:24, Josh Berkus wrote:
>>> Yeah, something like that :-). I was thinking of letting the estimate
>>> decrease like a moving average, but react to any increases immediately.
>>> Same thing we do in bgwriter to track buffer allocations:
>>
>> Seems reasonable.
> 
> Here's a patch implementing that. Docs not updated yet. I did not change
> the way checkpoint_segments triggers checkpoints - that'll can be a
> separate patch. This only decouples the segment preallocation behavior
> from checkpoint_segments. With the patch, you can set
> checkpoint_segments really high, without consuming that much disk space
> all the time.

I don't understand what this patch, by itself, will accomplish in terms
of the originally stated goals of making checkpoint_segments easier to
tune, and controlling disk space used.  To some degree, it makes both of
these things worse, because you can no longer use checkpoint_segments to
control the disk space.  Instead, it is replaced by magic.

What sort of behavior are you expecting to come out of this?  In testing,
I didn't see much of a difference.  Although I'd expect that this would
actually preallocate fewer segments than the old formula.

Two small issues in the code:

Code change doesn't match comment:

+        *
+        * XXX: We don't have a good estimate of how many WAL files we should keep
+        * preallocated here. Quite arbitrarily, use max_advance=5. That's good
+        * enough for current use of this function; this only gets called when
+        * there are no more preallocated WAL segments available.        */       installed_segno = logsegno;
-       max_advance = XLOGfileslop;
+       max_advance = CheckPointSegments;

KB should be kB.

On 07/03/2013 11:28 AM, Peter Eisentraut wrote:
> On 6/6/13 4:09 PM, Heikki Linnakangas wrote:
> I don't understand what this patch, by itself, will accomplish in terms
> of the originally stated goals of making checkpoint_segments easier to
> tune, and controlling disk space used.  To some degree, it makes both of
> these things worse, because you can no longer use checkpoint_segments to
> control the disk space.  Instead, it is replaced by magic.
> 
> What sort of behavior are you expecting to come out of this?  In testing,
> I didn't see much of a difference.  Although I'd expect that this would
> actually preallocate fewer segments than the old formula.

Since I haven't seen a reply to Peter's comments from Heikki, I'm
marking this patch "returned with feedback".  I know, it's a very busy
CF, and I'm sure that you just couldn't get back to this one.  We'll
address it in September?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 03.07.2013 21:28, Peter Eisentraut wrote:
> On 6/6/13 4:09 PM, Heikki Linnakangas wrote:
>> Here's a patch implementing that. Docs not updated yet. I did not change
>> the way checkpoint_segments triggers checkpoints - that'll can be a
>> separate patch. This only decouples the segment preallocation behavior
>> from checkpoint_segments. With the patch, you can set
>> checkpoint_segments really high, without consuming that much disk space
>> all the time.
>
> I don't understand what this patch, by itself, will accomplish in terms
> of the originally stated goals of making checkpoint_segments easier to
> tune, and controlling disk space used.  To some degree, it makes both of
> these things worse, because you can no longer use checkpoint_segments to
> control the disk space.  Instead, it is replaced by magic.

The patch addressed the third point in my first post:

> A third point is that even if you have 10 GB of disk space reserved
> for WAL, you don't want to actually consume all that 10 GB, if it's
> not required to run the database smoothly. There are several reasons
> for that: backups based on a filesystem-level snapshot are larger
> than necessary, if there are a lot of preallocated WAL segments and
> in a virtualized or shared system, there might be other VMs or
> applications that could make use of the disk space. On the other
> hand, you don't want to run out of disk space while writing WAL -
> that can lead to a PANIC in the worst case.

> What sort of behavior are you expecting to come out of this?  In testing,
> I didn't see much of a difference.  Although I'd expect that this would
> actually preallocate fewer segments than the old formula.

For example, if you set checkpoint_segments to 200, and you temporarily
generate 100 segments of WAL during an initial data load, but the normal
workload generates only 20 segments between checkpoints. Without the
patch, you will permanently have about 120 segments in pg_xlog, created
by the spike. With the patch, the extra segments will be gradually
removed after the data load, down to the level needed by the constant
workload. That would be about 50 segments, assuming the default
checkpoint_completion_target=0.5.


Here's a bigger patch, which does more. It is based on the ideas in the
post I started this thread with, with feedback incorporated from the
long discussion. With this patch, WAL disk space usage is controlled by
two GUCs:

min_recycle_wal_size
checkpoint_wal_size

These GUCs act as soft minimum and maximum on overall WAL size. At each
checkpoint, the checkpointer removes enough old WAL files to keep
pg_xlog usage below checkpoint_wal_size, and recycles enough new WAL
files to reach min_recycle_wal_size. Between those limits, there is a
self-tuning mechanism to recycle just enough WAL files to get to end of
the next checkpoint without running out of preallocated WAL files. To
estimate how many files are needed for that, a moving average of how
much WAL is generated between checkpoints is calculated. The moving
average is updated with "fast-rise slow-decline" behavior, to cater for
peak rather than true average use to some extent.

As today, checkpoints are triggered based on time or WAL usage,
whichever comes first. WAL-based checkpoints are triggered based on the
good old formula: CheckPointSegments = (checkpoint_max_wal_size / (2.0 +
checkpoint_completion_target)) / 16MB. CheckPointSegments controls that
like before, but it is now an internal variable derived from
checkpoint_wal_size, not visible to users.

These settings are fairly intuitive for a DBA to tune. You begin by
figuring out how much disk space you can afford to spend on WAL, and set
checkpoint_wal_size to that (with some safety margin, of course). Then
you set checkpoint_timeout based on how long you're willing to wait for
recovery to finish. Finally, if you have infrequent batch jobs that need
a lot more WAL than the system otherwise needs, you can set
min_recycle_wal_size to keep enough WAL preallocated for the spikes.

You can also set min_recycle_wal_size = checkpoint_wal_size, which gets
you the same behavior as without the patch, except that it's more
intuitive to set it in terms of "MB of WAL space required", instead of
"# of segments between checkpoints".

Does that make sense? I'd love to hear feedback on how people setting up
production databases would like to tune these things. The reason for the
auto-tuning between the min and max is to be able to set reasonable
defaults e.g for embedded systems that don't have a DBA to do tuning.
Currently, it's very difficult to come up with a reasonable default
value for checkpoint_segments which would work well for a wide range of
systems. The PostgreSQL default of 3 is way way too low for most
systems. On the other hand, if you set it to, say, 20, that's a lot of
wasted space for a small database that's not updated much. With this
patch, you can set "max_wal_size=1GB" and if the database ends up
actually only needing 100 MB of WAL, it will only use that much and not
waste 900 MB for useless preallocated WAL files.

These GUCs are still soft limits. If the system is busy enough that the
checkpointer can't reach its target, it can exceed checkpoint_wal_size.
Making it a hard limit is a much bigger task than I'm willing to tackle
right now.

- Heikki

On 08/23/2013 02:08 PM, Heikki Linnakangas wrote:

> Here's a bigger patch, which does more. It is based on the ideas in the
> post I started this thread with, with feedback incorporated from the
> long discussion. With this patch, WAL disk space usage is controlled by
> two GUCs:
> 
> min_recycle_wal_size
> checkpoint_wal_size
> 
<snip>

> These settings are fairly intuitive for a DBA to tune. You begin by
> figuring out how much disk space you can afford to spend on WAL, and set
> checkpoint_wal_size to that (with some safety margin, of course). Then
> you set checkpoint_timeout based on how long you're willing to wait for
> recovery to finish. Finally, if you have infrequent batch jobs that need
> a lot more WAL than the system otherwise needs, you can set
> min_recycle_wal_size to keep enough WAL preallocated for the spikes.

We'll want to rename them to make it even *more* intuitive.

But ... do I understand things correctly that checkpoint wouldn't "kick
in" until you hit checkpoint_wal_size?  If that's the case, isn't real
disk space usage around 2X checkpoint_wal_size if spread checkpoint is
set to 0.9?  Or does checkpoint kick in sometime earlier?

> except that it's more
> intuitive to set it in terms of "MB of WAL space required", instead of
> "# of segments between checkpoints".

Yes, it certainly is.  We'll need to caution people that fractions of
16MB will be ignored.

> Does that make sense? I'd love to hear feedback on how people setting up
> production databases would like to tune these things. The reason for the
> auto-tuning between the min and max is to be able to set reasonable
> defaults e.g for embedded systems that don't have a DBA to do tuning.
> Currently, it's very difficult to come up with a reasonable default
> value for checkpoint_segments which would work well for a wide range of
> systems. The PostgreSQL default of 3 is way way too low for most
> systems. On the other hand, if you set it to, say, 20, that's a lot of
> wasted space for a small database that's not updated much. With this
> patch, you can set "max_wal_size=1GB" and if the database ends up
> actually only needing 100 MB of WAL, it will only use that much and not
> waste 900 MB for useless preallocated WAL files.

This sounds good, aside from the potential 2X issue I mention above.

Mind you, what admins really want is a hard limit on WAL size, so that
they can create a partition and not worry about PG running out of WAL
space.  But ...

> Making it a hard limit is a much bigger task than I'm willing to tackle
> right now.

... agreed.  And this approach could be built on for a hard limit later on.

As a note, pgBench would be a terrible test for this patch; we really
need something which creates uneven traffic.  I'll see if I can devise
something.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Sat, Aug 24, 2013 at 2:38 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 03.07.2013 21:28, Peter Eisentraut wrote:
>>
>> On 6/6/13 4:09 PM, Heikki Linnakangas wrote:
>>>
>>> Here's a patch implementing that. Docs not updated yet. I did not change
>>> the way checkpoint_segments triggers checkpoints - that'll can be a
>>> separate patch. This only decouples the segment preallocation behavior
>>> from checkpoint_segments. With the patch, you can set
>>> checkpoint_segments really high, without consuming that much disk space
>>> all the time.
>>
>>
>> I don't understand what this patch, by itself, will accomplish in terms
>> of the originally stated goals of making checkpoint_segments easier to
>> tune, and controlling disk space used.  To some degree, it makes both of
>> these things worse, because you can no longer use checkpoint_segments to
>> control the disk space.  Instead, it is replaced by magic.
>
>
> The patch addressed the third point in my first post:
>
>> A third point is that even if you have 10 GB of disk space reserved
>> for WAL, you don't want to actually consume all that 10 GB, if it's
>> not required to run the database smoothly. There are several reasons
>> for that: backups based on a filesystem-level snapshot are larger
>> than necessary, if there are a lot of preallocated WAL segments and
>> in a virtualized or shared system, there might be other VMs or
>> applications that could make use of the disk space. On the other
>> hand, you don't want to run out of disk space while writing WAL -
>> that can lead to a PANIC in the worst case.
>
>
>> What sort of behavior are you expecting to come out of this?  In testing,
>> I didn't see much of a difference.  Although I'd expect that this would
>> actually preallocate fewer segments than the old formula.
>
>
> For example, if you set checkpoint_segments to 200, and you temporarily
> generate 100 segments of WAL during an initial data load, but the normal
> workload generates only 20 segments between checkpoints. Without the patch,
> you will permanently have about 120 segments in pg_xlog, created by the
> spike. With the patch, the extra segments will be gradually removed after
> the data load, down to the level needed by the constant workload. That would
> be about 50 segments, assuming the default checkpoint_completion_target=0.5.
>
>
> Here's a bigger patch, which does more. It is based on the ideas in the post
> I started this thread with, with feedback incorporated from the long
> discussion. With this patch, WAL disk space usage is controlled by two GUCs:
>
> min_recycle_wal_size
> checkpoint_wal_size

I think it will be helpful for users to configure using wal size
rather than by number of segments and
your idea to keep WAL size under control can be helpful to users.

> These GUCs act as soft minimum and maximum on overall WAL size. At each
> checkpoint, the checkpointer removes enough old WAL files to keep pg_xlog
> usage below checkpoint_wal_size, and recycles enough new WAL files to reach
> min_recycle_wal_size. Between those limits, there is a self-tuning mechanism
> to recycle just enough WAL files to get to end of the next checkpoint
> without running out of preallocated WAL files. To estimate how many files
> are needed for that, a moving average of how much WAL is generated between
> checkpoints is calculated. The moving average is updated with "fast-rise
> slow-decline" behavior, to cater for peak rather than true average use to
> some extent.
>
> As today, checkpoints are triggered based on time or WAL usage, whichever
> comes first. WAL-based checkpoints are triggered based on the good old
> formula: CheckPointSegments = (checkpoint_max_wal_size / (2.0 +
> checkpoint_completion_target)) / 16MB. CheckPointSegments controls that like
> before, but it is now an internal variable derived from checkpoint_wal_size,
> not visible to users.

a.
In XLogFileInit(),
/*
!      * XXX: What should we use as max_segno? We used to use XLOGfileslop when
!      * that was a constant, but that was always a bit dubious: normally, at a
!      * checkpoint, XLOGfileslop was the offset from the checkpoint record,
!      * but here, it was the offset from the insert location. We can't do the
!      * normal XLOGfileslop calculation here because we don't have access to
!      * the prior checkpoint's redo location. So somewhat arbitrarily, just
!      * use CheckPointSegments.
!      */
!     max_segno = logsegno + CheckPointSegments;     if (!InstallXLogFileSegment(&installed_segno, tmppath,
!                                 *use_existent, max_segno,                                 use_lock))

Earlier max_advance is same when InstallXLogFileSegment is called from
RemoveOldXlogFiles() and XLogFileInit(),
but now they will be different (and it seems there is no direct
relation between these 2 numbers), so will it be okay for scenario
when someone else has created the file while this function was
filling, because it needs to restore as future segment which will be
decided based on max_segno?

b. Do createrestartpoint need to update the
CheckPointDistanceEstimate, as when it will try to remove old xlog
files, it needs recycleSegNo which is calculated using
CheckPointDistanceEstimate?

c. New variables are not present in postgresql.conf after initdb.

> These settings are fairly intuitive for a DBA to tune. You begin by figuring
> out how much disk space you can afford to spend on WAL, and set
> checkpoint_wal_size to that (with some safety margin, of course). Then you
> set checkpoint_timeout based on how long you're willing to wait for recovery
> to finish. Finally, if you have infrequent batch jobs that need a lot more
> WAL than the system otherwise needs, you can set min_recycle_wal_size to
> keep enough WAL preallocated for the spikes.
>
> You can also set min_recycle_wal_size = checkpoint_wal_size, which gets you
> the same behavior as without the patch, except that it's more intuitive to
> set it in terms of "MB of WAL space required", instead of "# of segments
> between checkpoints".
>
> Does that make sense? I'd love to hear feedback on how people setting up
> production databases would like to tune these things. The reason for the
> auto-tuning between the min and max is to be able to set reasonable defaults
> e.g for embedded systems that don't have a DBA to do tuning. Currently, it's
> very difficult to come up with a reasonable default value for
> checkpoint_segments which would work well for a wide range of systems. The
> PostgreSQL default of 3 is way way too low for most systems. On the other
> hand, if you set it to, say, 20, that's a lot of wasted space for a small
> database that's not updated much. With this patch, you can set
> "max_wal_size=1GB" and if the database ends up actually only needing 100 MB
> of WAL, it will only use that much and not waste 900 MB for useless
> preallocated WAL files.

As a developer, I would love to have configuration knob such as
min_recycle_wal_size, but not sure how many users will be comfortable
setting this value, actually few users I had talked about this earlier
are interested in setting max WAL size which can allow them to set an
upper limit on space required by WAL.
Can't we think of doing the calculation of files to recycle only based
on CheckPointDistanceEstimate.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sat, Aug 24, 2013 at 12:08:30AM +0300, Heikki Linnakangas wrote:
> You can also set min_recycle_wal_size = checkpoint_wal_size, which
> gets you the same behavior as without the patch, except that it's
> more intuitive to set it in terms of "MB of WAL space required",
> instead of "# of segments between checkpoints".
> 
> Does that make sense? I'd love to hear feedback on how people
> setting up production databases would like to tune these things. The
> reason for the auto-tuning between the min and max is to be able to
> set reasonable defaults e.g for embedded systems that don't have a
> DBA to do tuning. Currently, it's very difficult to come up with a
> reasonable default value for checkpoint_segments which would work
> well for a wide range of systems. The PostgreSQL default of 3 is way
> way too low for most systems. On the other hand, if you set it to,
> say, 20, that's a lot of wasted space for a small database that's
> not updated much. With this patch, you can set "max_wal_size=1GB"
> and if the database ends up actually only needing 100 MB of WAL, it
> will only use that much and not waste 900 MB for useless
> preallocated WAL files.

Where are we on this?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

(reviving an old thread)

On 08/24/2013 12:53 AM, Josh Berkus wrote:
> On 08/23/2013 02:08 PM, Heikki Linnakangas wrote:
>
>> Here's a bigger patch, which does more. It is based on the ideas in the
>> post I started this thread with, with feedback incorporated from the
>> long discussion. With this patch, WAL disk space usage is controlled by
>> two GUCs:
>>
>> min_recycle_wal_size
>> checkpoint_wal_size
>>
> <snip>
>
>> These settings are fairly intuitive for a DBA to tune. You begin by
>> figuring out how much disk space you can afford to spend on WAL, and set
>> checkpoint_wal_size to that (with some safety margin, of course). Then
>> you set checkpoint_timeout based on how long you're willing to wait for
>> recovery to finish. Finally, if you have infrequent batch jobs that need
>> a lot more WAL than the system otherwise needs, you can set
>> min_recycle_wal_size to keep enough WAL preallocated for the spikes.
>
> We'll want to rename them to make it even *more* intuitive.

Sure, I'm all ears.

> But ... do I understand things correctly that checkpoint wouldn't "kick
> in" until you hit checkpoint_wal_size?  If that's the case, isn't real
> disk space usage around 2X checkpoint_wal_size if spread checkpoint is
> set to 0.9?  Or does checkpoint kick in sometime earlier?

It kicks in earlier, so that the checkpoint *completes* just when
checkpoint_wal_size of WAL is used up. So the real disk usage is
checkpoint_wal_size.

There is still an internal variable called CheckPointSegments that
triggers the checkpoint, but it is now derived from checkpoint_wal_size
(see CalculateCheckpointSegments function):

CheckPointSegments = (checkpoint_wal_size / 16 MB) / (2 +
checkpoint_completion_target)

This is the same formula we've always had in the manual for calculating
the amount of WAL space used, but in reverse. I.e. we calculate
CheckPointSegments based on the desired disk space usage, not the other
way round.

> As a note, pgBench would be a terrible test for this patch; we really
> need something which creates uneven traffic.  I'll see if I can devise
> something.

Attached is a rebased version of this patch. Everyone, please try this
out on whatever workloads you have, and let me know:

a) How does the auto-tuning of the number of recycled segments work?
Does pg_xlog reach a steady-state size, or does it fluctuate a lot?

b) Are the two GUCs, checkpoint_wal_size, and min_recycle_wal_size,
intuitive to set?

- Heikki

On 09/01/2013 10:37 AM, Amit Kapila wrote:
> On Sat, Aug 24, 2013 at 2:38 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>
> a.
> In XLogFileInit(),
> /*
> !      * XXX: What should we use as max_segno? We used to use XLOGfileslop when
> !      * that was a constant, but that was always a bit dubious: normally, at a
> !      * checkpoint, XLOGfileslop was the offset from the checkpoint record,
> !      * but here, it was the offset from the insert location. We can't do the
> !      * normal XLOGfileslop calculation here because we don't have access to
> !      * the prior checkpoint's redo location. So somewhat arbitrarily, just
> !      * use CheckPointSegments.
> !      */
> !     max_segno = logsegno + CheckPointSegments;
>        if (!InstallXLogFileSegment(&installed_segno, tmppath,
> !                                 *use_existent, max_segno,
>                                    use_lock))
>
> Earlier max_advance is same when InstallXLogFileSegment is called from
> RemoveOldXlogFiles() and XLogFileInit(),
> but now they will be different (and it seems there is no direct
> relation between these 2 numbers), so will it be okay for scenario
> when someone else has created the file while this function was
> filling, because it needs to restore as future segment which will be
> decided based on max_segno?

I haven't really thought hard about the above. As the comment says, 
passing the same max_advance value here and in RemoveOldXlogFiles() was 
a bit dubious too, because the reference point was different.

I believe it's quite rare that two processes create a new WAL segment 
concurrently, so it isn't terribly important what we do here.

> b. Do createrestartpoint need to update the
> CheckPointDistanceEstimate, as when it will try to remove old xlog
> files, it needs recycleSegNo which is calculated using
> CheckPointDistanceEstimate?

Yeah, you're right, it should. I haven't tested this with archive 
recovery or replication at all yet.

> As a developer, I would love to have configuration knob such as
> min_recycle_wal_size, but not sure how many users will be comfortable
> setting this value, actually few users I had talked about this earlier
> are interested in setting max WAL size which can allow them to set an
> upper limit on space required by WAL.
> Can't we think of doing the calculation of files to recycle only based
> on CheckPointDistanceEstimate.

You can always just leave min_recycle_wal_size to the default. It sets a 
minimum for the number of preallocated segments, which can help if you 
have spikes that consume a lot of WAL, like nightly batch jobs. But if 
you don't have such spikes, or the overhead of creating new segments 
when such a spike happens isn't too large, you don't need to set it.

One idea is to try to make the creation of new WAL segments faster. Then 
it wouldn't hurt so much if you run out of preallocated/recycled 
segments and need to suddenly create a lot of new ones. Then we might 
not need a minimum setting at all.

- Heikki

Heikki,

Thanks for getting back to this!  I really look forward to simplifying
WAL tuning for users.

>>> min_recycle_wal_size
>>> checkpoint_wal_size
>>>
>> <snip>
>>
>>> These settings are fairly intuitive for a DBA to tune. You begin by
>>> figuring out how much disk space you can afford to spend on WAL, and set
>>> checkpoint_wal_size to that (with some safety margin, of course). Then
>>> you set checkpoint_timeout based on how long you're willing to wait for
>>> recovery to finish. Finally, if you have infrequent batch jobs that need
>>> a lot more WAL than the system otherwise needs, you can set
>>> min_recycle_wal_size to keep enough WAL preallocated for the spikes.
>>
>> We'll want to rename them to make it even *more* intuitive.
> 
> Sure, I'm all ears.

My suggestion:

max_wal_size
min_wal_size

... these would be very easy to read & understand for users:  "Set
max_wal_size based on the amount of space you have available for the
transaction log, or about 10% of the space available for your database
if you don't have a specific allocation for the log.  If your database
involves large batch imports, you may want to increase min_wal_size to
be at least the size of your largest batch."

Suggested defaults:

max_wal_size: 256MB
min_wal_size: 64MB

Please remind me because I'm having trouble finding this in the
archives: how does wal_keep_segments interact with the new settings?

>> But ... do I understand things correctly that checkpoint wouldn't "kick
>> in" until you hit checkpoint_wal_size?  If that's the case, isn't real
>> disk space usage around 2X checkpoint_wal_size if spread checkpoint is
>> set to 0.9?  Or does checkpoint kick in sometime earlier?
> 
> It kicks in earlier, so that the checkpoint *completes* just when
> checkpoint_wal_size of WAL is used up. So the real disk usage is
> checkpoint_wal_size.

Awesome.  This makes me very happy.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/01/2015 03:24 AM, Josh Berkus wrote:
> Please remind me because I'm having trouble finding this in the
> archives: how does wal_keep_segments interact with the new settings?

It's not very straightforward. First of all, min_recycle_wal_size has a 
different effect than wal_keep_segments. Raising min_recycle_wal_size 
causes more segments to be recycled rather than deleted, while 
wal_keep_segments causes old segments to be retained as old segments, so 
that they can be used for streaming replication. If you raise 
min_recycle_wal_size, it will not do any good for streaming replication.

wal_keep_segments does not affect the calculation of CheckPointSegments. 
If you set wal_keep_segments high enough, checkpoint_wal_size will be 
exceeded. The other alternative would be to force a checkpoint earlier, 
i.e. lower CheckPointSegments, so that checkpoint_wal_size would be 
honored. However, if you set wal_keep_segments high enough, higher than 
checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no 
matter how frequently you checkpoint.

It's not totally straightforward to calculate what maximum size of WAL a 
given wal_keep_segments settings will force. wal_keep_segments is taken 
into account at a checkpoint, when we recycle old WAL segments. For 
example, imagine that prior checkpoint started at segment 95, a new 
checkpoint finishes at segment 100, and wal_keep_segments=10. Because of 
wal_keep_segments, we have to retain segments 90-95, which could 
otherwise be recycled. So that forces a WAL size of 10 segments, while 
otherwise 5 would be enough. However, before we reach the next 
checkpoint, let's assume it will complete at segment 105, we will 
consume five more segments, so the actual max WAL size is 15 segments. 
However, we could start recycling the segments 90-95 before we reach the 
next checkpoint, because wal_keep_segments stipulates how many segments 
from the current *insert* location needs to be retained, with not regard 
to checkpoints. But we only attempt to recycle segments at checkpoints.

So that could be made more straightforward if we recycled old segments 
in the background, between checkpoints. That might allow merging 
wal_keep_segments and min_recycle_wal_size settings, too: instead of 
renaming all old recycleable segments at a checkpoint, you could keep 
them around as old segments until they're actually needed for reuse, so 
they could be used for streaming replication up to that point.

- Heikki

On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
> wal_keep_segments does not affect the calculation of CheckPointSegments.
> If you set wal_keep_segments high enough, checkpoint_wal_size will be
> exceeded. The other alternative would be to force a checkpoint earlier,
> i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
> honored. However, if you set wal_keep_segments high enough, higher than
> checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no
> matter how frequently you checkpoint.

So you're saying that wal_keep_segments is part of the max_wal_size
total, NOT in addition to it?

Just asking for clarification, here.  I think that's a fine idea, I just
want to make sure I understood you.  The importance of wal_keep_segments
will be fading as more people use replication slots.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/03/2015 12:28 AM, Josh Berkus wrote:
> On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
>> wal_keep_segments does not affect the calculation of CheckPointSegments.
>> If you set wal_keep_segments high enough, checkpoint_wal_size will be
>> exceeded. The other alternative would be to force a checkpoint earlier,
>> i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
>> honored. However, if you set wal_keep_segments high enough, higher than
>> checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no
>> matter how frequently you checkpoint.
>
> So you're saying that wal_keep_segments is part of the max_wal_size
> total, NOT in addition to it?

Not sure what you mean. wal_keep_segments is an extra control that can 
prevent WAL segments from being recycled. It has the same effect as 
archive_command failing for N most recent segments, if that helps.

> Just asking for clarification, here.  I think that's a fine idea, I just
> want to make sure I understood you.  The importance of wal_keep_segments
> will be fading as more people use replication slots.

Yeah.

- Heikki

On 01/03/2015 12:56 AM, Heikki Linnakangas wrote:
> On 01/03/2015 12:28 AM, Josh Berkus wrote:
>> On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
>>> wal_keep_segments does not affect the calculation of CheckPointSegments.
>>> If you set wal_keep_segments high enough, checkpoint_wal_size will be
>>> exceeded. The other alternative would be to force a checkpoint earlier,
>>> i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
>>> honored. However, if you set wal_keep_segments high enough, higher than
>>> checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no
>>> matter how frequently you checkpoint.
>>
>> So you're saying that wal_keep_segments is part of the max_wal_size
>> total, NOT in addition to it?
> 
> Not sure what you mean. wal_keep_segments is an extra control that can
> prevent WAL segments from being recycled. It has the same effect as
> archive_command failing for N most recent segments, if that helps.

I mean, if I have these settings:

max_wal_size* = 256MB
wal_keep_segments = 8

... then my max wal size is *still* 256MB, NOT 384MB?

If that's the case (and I think it's a good plan), then as a follow-on,
we should prevent users from setting wal_keep_segments to more than 50%
of max_wal_size, no?

(* max_wal_size == checkpoint_wal_size, per prior email)

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/04/2015 11:44 PM, Josh Berkus wrote:
> On 01/03/2015 12:56 AM, Heikki Linnakangas wrote:
>> On 01/03/2015 12:28 AM, Josh Berkus wrote:
>>> On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
>>>> wal_keep_segments does not affect the calculation of CheckPointSegments.
>>>> If you set wal_keep_segments high enough, checkpoint_wal_size will be
>>>> exceeded. The other alternative would be to force a checkpoint earlier,
>>>> i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
>>>> honored. However, if you set wal_keep_segments high enough, higher than
>>>> checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no
>>>> matter how frequently you checkpoint.
>>>
>>> So you're saying that wal_keep_segments is part of the max_wal_size
>>> total, NOT in addition to it?
>>
>> Not sure what you mean. wal_keep_segments is an extra control that can
>> prevent WAL segments from being recycled. It has the same effect as
>> archive_command failing for N most recent segments, if that helps.
>
> I mean, if I have these settings:
>
> max_wal_size* = 256MB
> wal_keep_segments = 8
>
> ... then my max wal size is *still* 256MB, NOT 384MB?

Right.

> If that's the case (and I think it's a good plan), then as a follow-on,
> we should prevent users from setting wal_keep_segments to more than 50%
> of max_wal_size, no?

Not sure if the 50% figure is correct, but I see what you mean: don't 
allow setting wal_keep_segments so high that we would exceed 
max_wal_size because of it.

- Heikki

On 2015-01-05 11:34:54 +0200, Heikki Linnakangas wrote:
> On 01/04/2015 11:44 PM, Josh Berkus wrote:
> >On 01/03/2015 12:56 AM, Heikki Linnakangas wrote:
> >>On 01/03/2015 12:28 AM, Josh Berkus wrote:
> >>>On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
> >>>>wal_keep_segments does not affect the calculation of CheckPointSegments.
> >>>>If you set wal_keep_segments high enough, checkpoint_wal_size will be
> >>>>exceeded. The other alternative would be to force a checkpoint earlier,
> >>>>i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
> >>>>honored. However, if you set wal_keep_segments high enough, higher than
> >>>>checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no
> >>>>matter how frequently you checkpoint.
> >>>
> >>>So you're saying that wal_keep_segments is part of the max_wal_size
> >>>total, NOT in addition to it?
> >>
> >>Not sure what you mean. wal_keep_segments is an extra control that can
> >>prevent WAL segments from being recycled. It has the same effect as
> >>archive_command failing for N most recent segments, if that helps.
> >
> >I mean, if I have these settings:
> >
> >max_wal_size* = 256MB
> >wal_keep_segments = 8
> >
> >... then my max wal size is *still* 256MB, NOT 384MB?
> 
> Right.

With that you mean that wal_keep_segments has *no* influence over
checkpoint pacing or the contrary? Because upthread you imply that it
doesn't, but later comments may mean the contrary.

I think that influencing the pacing would be pretty insane - the user
certainly doesn't expect drastic performance changes when changing
wal_keep_segments. It's confusing enough that it can cause slight
peformance variations due to recycling, but we shouldn't make it have a
larger influence.

> >If that's the case (and I think it's a good plan), then as a follow-on,
> >we should prevent users from setting wal_keep_segments to more than 50%
> >of max_wal_size, no?
> 
> Not sure if the 50% figure is correct, but I see what you mean: don't allow
> setting wal_keep_segments so high that we would exceed max_wal_size because
> of it.

That seems a unrealistic goal. I've seen setups that have set
checkpoint_segments intentionally, and with good reasoning, north of
50k.

Neither wal_keep_segments, nor failing archive_commands nor replication
slot should have an influence on checkpoint pacing.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 01/05/2015 12:06 PM, Andres Freund wrote:
> On 2015-01-05 11:34:54 +0200, Heikki Linnakangas wrote:
>> On 01/04/2015 11:44 PM, Josh Berkus wrote:
>>> On 01/03/2015 12:56 AM, Heikki Linnakangas wrote:
>>>> On 01/03/2015 12:28 AM, Josh Berkus wrote:
>>>>> On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
>>>>>> wal_keep_segments does not affect the calculation of CheckPointSegments.
>>>>>> If you set wal_keep_segments high enough, checkpoint_wal_size will be
>>>>>> exceeded. The other alternative would be to force a checkpoint earlier,
>>>>>> i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
>>>>>> honored. However, if you set wal_keep_segments high enough, higher than
>>>>>> checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no
>>>>>> matter how frequently you checkpoint.
>>>>>
>>>>> So you're saying that wal_keep_segments is part of the max_wal_size
>>>>> total, NOT in addition to it?
>>>>
>>>> Not sure what you mean. wal_keep_segments is an extra control that can
>>>> prevent WAL segments from being recycled. It has the same effect as
>>>> archive_command failing for N most recent segments, if that helps.
>>>
>>> I mean, if I have these settings:
>>>
>>> max_wal_size* = 256MB
>>> wal_keep_segments = 8
>>>
>>> ... then my max wal size is *still* 256MB, NOT 384MB?
>>
>> Right.
>
> With that you mean that wal_keep_segments has *no* influence over
> checkpoint pacing or the contrary? Because upthread you imply that it
> doesn't, but later comments may mean the contrary.

wal_keep_segments does not influence checkpoint pacing.

>>> If that's the case (and I think it's a good plan), then as a follow-on,
>>> we should prevent users from setting wal_keep_segments to more than 50%
>>> of max_wal_size, no?
>>
>> Not sure if the 50% figure is correct, but I see what you mean: don't allow
>> setting wal_keep_segments so high that we would exceed max_wal_size because
>> of it.

I wasn't clear on my opinion here. I think I understood what Josh meant, 
but I don't think we should do it. Seems like unnecessary nannying of 
the DBA. Let's just mention in the manual that if you set 
wal_keep_segments higher than [insert formula here], you will routinely 
have more WAL in pg_xlog than what checkpoint_wal_size is set to.

> That seems a unrealistic goal. I've seen setups that have set
> checkpoint_segments intentionally, and with good reasoning, north of
> 50k.

So? I don't see how that's relevant.

> Neither wal_keep_segments, nor failing archive_commands nor replication
> slot should have an influence on checkpoint pacing.

Agreed.

- Heikki

On 01/05/2015 09:06 AM, Heikki Linnakangas wrote:
> I wasn't clear on my opinion here. I think I understood what Josh meant,
> but I don't think we should do it. Seems like unnecessary nannying of
> the DBA. Let's just mention in the manual that if you set
> wal_keep_segments higher than [insert formula here], you will routinely
> have more WAL in pg_xlog than what checkpoint_wal_size is set to.
> 
>> That seems a unrealistic goal. I've seen setups that have set
>> checkpoint_segments intentionally, and with good reasoning, north of
>> 50k.
> 
> So? I don't see how that's relevant.
> 
>> Neither wal_keep_segments, nor failing archive_commands nor replication
>> slot should have an influence on checkpoint pacing.
> 
> Agreed.

Oh, right, slots can also cause the log to increase in size.  And we've
already had the discussion about hard limits, which is maybe a future
feature and not part of this patch.

Can we figure out a reasonable formula?  My thinking is 50% for
wal_keep_segments, because we need at least 50% of the wals to do a
reasonable spread checkpoint.   If max_wal_size is 1GB, and
wal_keep_segments is 1.5GB, what would happen?  What if
wal_keep_segments is 0.9GB?

I need to create a fake benchmark for this ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 01/30/2015 04:48 AM, Venkata Balaji N wrote:
> I performed series of tests for this patch and would like to share the
> results. My comments are in-line.

Thanks for the testing!

> *Test 1 :*
>
> In this test, i see removed+recycled segments = 3 (except for the first 3
> checkpoint cycles) and has been steady through out until the INSERT
> operation completed.
>
> Actual calculation of CheckPointSegments = 3.2 ( is getting rounded up to 3
> )
>
> pg_xlog size is 128M and has increased to 160M max during the INSERT
> operation.
>
> shared_buffers = 128M
> checkpoint_wal_size = 128M
> min_recycle_wal_size = 80M
> checkpoint_timeout = 5min

Hmm, did I understand correctly that pg_xlog peaked at 160MB, but most 
of the stayed at 128 MB? That sounds like it's working as designed; 
checkpoint_wal_size is not a hard limit after all.

>> b) Are the two GUCs, checkpoint_wal_size, and min_recycle_wal_size,
>> intuitive to set?
>
> During my tests, I did not observe the significance of min_recycle_wal_size
> parameter yet. Ofcourse, i had sufficient disk space for pg_xlog.
>
> I would like to understand more about "min_recycle_wal_size" parameter. In
> theory, i only understand from the note in the patch that if the disk space
> usage falls below certain threshold, min_recycle_wal_size number of WALs
> will be removed to accommodate future pg_xlog segments. I will try to test
> this out. Please let me know if there is any specific test to understand
> min_recycle_wal_size behaviour.

min_recycle_wal_size comes into play when you have only light load, so 
that checkpoints are triggered by checkpoint_timeout rather than 
checkpoint_wal_size. In that scenario, the WAL usage will shrink down to 
min_recycle_wal_size, but not below that. Did that explanation help? Can 
you suggest changes to the docs to make it more clear?

- Heikki

On Fri, Jan 30, 2015 at 3:58 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
>> During my tests, I did not observe the significance of
>> min_recycle_wal_size
>> parameter yet. Ofcourse, i had sufficient disk space for pg_xlog.
>>
>> I would like to understand more about "min_recycle_wal_size" parameter. In
>> theory, i only understand from the note in the patch that if the disk
>> space
>> usage falls below certain threshold, min_recycle_wal_size number of WALs
>> will be removed to accommodate future pg_xlog segments. I will try to test
>> this out. Please let me know if there is any specific test to understand
>> min_recycle_wal_size behaviour.
>
> min_recycle_wal_size comes into play when you have only light load, so that
> checkpoints are triggered by checkpoint_timeout rather than
> checkpoint_wal_size. In that scenario, the WAL usage will shrink down to
> min_recycle_wal_size, but not below that. Did that explanation help? Can you
> suggest changes to the docs to make it more clear?

First, as a general comment, I think we could do little that would
improve the experience of tuning PostgreSQL as much as getting this
patch committed with some reasonable default values for the settings
in question.  Shipping with checkpoint_segments=3 is a huge obstacle
to good performance.  It might be a reasonable value for
min_recycle_wal_size, but it's not a remotely reasonable upper bound
on WAL generated between checkpoints.  We haven't increased that limit
even once in the 14 years we've had it (cf.
4d14fe0048cf80052a3ba2053560f8aab1bb1b22) and typical disk sizes have
grown by an order of magnitude since then.

Second, I *think* that these settings are symmetric and, if that's
right, then I suggest that they ought to be named symmetrically.
Basically, I think you've got min_checkpoint_segments (the number of
recycled segments we keep around always) and max_checkpoint_segments
(the maximum number of segments we can have between checkpoints),
essentially splitting the current role of checkpoint_segments in half.
I'd go so far as to suggest we use exactly that naming.  It would be
reasonable to allow the value to be specified in MB rather than in
16MB units, and to specify it that way by default, but maybe a
unit-less value should have the old interpretation since everybody's
used to it.  That would require adding GUC_UNIT_XSEG or similar, but
that seems OK.

Also, I'd like to propose that we set the default value of
max_checkpoint_segments/checkpoint_wal_size to something at least an
order of magnitude larger than the current default setting.  I'll open
the bidding at 1600MB (aka 100).  I expect some pushback here, but I
don't think this is unreasonable; some people will need to raise it
further.  If you're generating 1600MB of WAL in 5 minutes, you're
either making the database bigger very quickly (in which case the
extra disk space that is consumed by the WAL will quickly blend into
the background) or you are updating the data already in the database
at a tremendous rate (in which case you are probably willing to burn
some disk space to have that go fast).  Right now, it's impractical to
ship something like checkpoint_segments=100 because we'd eat all that
space even on tiny databases with no activity.  But this patch fixes
that, so we might as well try to ship a default that's large enough to
use the database as something other than a toy.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

On 2015-02-02 08:36:41 -0500, Robert Haas wrote:
> Also, I'd like to propose that we set the default value of
> max_checkpoint_segments/checkpoint_wal_size to something at least an
> order of magnitude larger than the current default setting.

+1

I think we need to increase checkpoint_timeout too - that's actually
just as important for the default experience from my pov. 5 minutes
often just unnecessarily generates FPWs en masse.

> I'll open the bidding at 1600MB (aka 100).

Fine with me.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 02/02/2015 04:21 PM, Andres Freund wrote:
> Hi,
>
> On 2015-02-02 08:36:41 -0500, Robert Haas wrote:
>> Also, I'd like to propose that we set the default value of
>> max_checkpoint_segments/checkpoint_wal_size to something at least an
>> order of magnitude larger than the current default setting.
>
> +1

I don't agree with that principle. I wouldn't mind increasing it a 
little bit, but not by an order of magnitude. For better or worse, *all* 
our defaults are tuned toward small systems, and so that PostgreSQL 
doesn't hog all the resources. We shouldn't make an exception for this.

> I think we need to increase checkpoint_timeout too - that's actually
> just as important for the default experience from my pov. 5 minutes
> often just unnecessarily generates FPWs en masse.
>
>> I'll open the bidding at 1600MB (aka 100).
>
> Fine with me.

I wouldn't object to raising it a little bit, but that's way too high. 
It's entirely possible to have a small database that generates a lot of 
WAL. A table that has only a few rows, but is updated very very 
frequently, for example. And checkpointing such a database is quick too, 
so frequent checkpoints are not a problem. You don't want to end up with 
1.5 GB of WAL on a 100 MB database.

- Heikki

On 02/02/2015 03:36 PM, Robert Haas wrote:
> Second, I*think*  that these settings are symmetric and, if that's
> right, then I suggest that they ought to be named symmetrically.
> Basically, I think you've got min_checkpoint_segments (the number of
> recycled segments we keep around always) and max_checkpoint_segments
> (the maximum number of segments we can have between checkpoints),
> essentially splitting the current role of checkpoint_segments in half.
> I'd go so far as to suggest we use exactly that naming.  It would be
> reasonable to allow the value to be specified in MB rather than in
> 16MB units, and to specify it that way by default, but maybe a
> unit-less value should have the old interpretation since everybody's
> used to it.  That would require adding GUC_UNIT_XSEG or similar, but
> that seems OK.

Works for me. However, note that "max_checkpoint_segments = 10" doesn't 
mean the same as current "checkpoint_segments = 10". With 
checkpoint_segments = 10 you end up with about 2x-3x as much WAL as with 
max_checkpoint_segments = 10. So the "everybody's used to it" argument 
doesn't hold much water.

- Heikki

Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
> On 02/02/2015 04:21 PM, Andres Freund wrote:
>> On 2015-02-02 08:36:41 -0500, Robert Haas wrote:
>>> Also, I'd like to propose that we set the default value of
>>> max_checkpoint_segments/checkpoint_wal_size to something at
>>> least an order of magnitude larger than the current default
>>> setting.
>>
>> +1
>
> I don't agree with that principle. I wouldn't mind increasing it
> a little bit, but not by an order of magnitude.

Especially without either confirming that this effect is no longer
present, or having an explanation for it:

http://www.postgresql.org/message-id/4A44E58C0200002500027FCF@gw.
wicourts.gov

Note that Greg Smith found the same effect on a machine without any
write caching, which shoots down my theory, at least on his
machine:

http://www.postgresql.org/message-id/flat/4BCCDAD5.3040101@2ndquadrant.com#4BCCDAD5.3040101@2ndquadrant.com

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Feb 3, 2015 at 7:31 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 02/02/2015 03:36 PM, Robert Haas wrote:
>> Second, I*think*  that these settings are symmetric and, if that's
>> right, then I suggest that they ought to be named symmetrically.
>> Basically, I think you've got min_checkpoint_segments (the number of
>> recycled segments we keep around always) and max_checkpoint_segments
>> (the maximum number of segments we can have between checkpoints),
>> essentially splitting the current role of checkpoint_segments in half.
>> I'd go so far as to suggest we use exactly that naming.  It would be
>> reasonable to allow the value to be specified in MB rather than in
>> 16MB units, and to specify it that way by default, but maybe a
>> unit-less value should have the old interpretation since everybody's
>> used to it.  That would require adding GUC_UNIT_XSEG or similar, but
>> that seems OK.
>
> Works for me. However, note that "max_checkpoint_segments = 10" doesn't mean
> the same as current "checkpoint_segments = 10". With checkpoint_segments =
> 10 you end up with about 2x-3x as much WAL as with max_checkpoint_segments =
> 10. So the "everybody's used to it" argument doesn't hold much water.

Hmm, that's surprising.  Why does that happen?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/03/2015 05:19 PM, Robert Haas wrote:
> On Tue, Feb 3, 2015 at 7:31 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 02/02/2015 03:36 PM, Robert Haas wrote:
>>> Second, I*think*  that these settings are symmetric and, if that's
>>> right, then I suggest that they ought to be named symmetrically.
>>> Basically, I think you've got min_checkpoint_segments (the number of
>>> recycled segments we keep around always) and max_checkpoint_segments
>>> (the maximum number of segments we can have between checkpoints),
>>> essentially splitting the current role of checkpoint_segments in half.
>>> I'd go so far as to suggest we use exactly that naming.  It would be
>>> reasonable to allow the value to be specified in MB rather than in
>>> 16MB units, and to specify it that way by default, but maybe a
>>> unit-less value should have the old interpretation since everybody's
>>> used to it.  That would require adding GUC_UNIT_XSEG or similar, but
>>> that seems OK.
>>
>> Works for me. However, note that "max_checkpoint_segments = 10" doesn't mean
>> the same as current "checkpoint_segments = 10". With checkpoint_segments =
>> 10 you end up with about 2x-3x as much WAL as with max_checkpoint_segments =
>> 10. So the "everybody's used to it" argument doesn't hold much water.
>
> Hmm, that's surprising.  Why does that happen?

That's the whole point of this patch. "max_checkpoint_segments = 10", or 
"max_checkpoint_segments = 160 MB", means that the system will begin a 
checkpoint so that when the checkpoint completes, and it truncates away 
or recycles old WAL, the total size of pg_xlog is 160 MB.

That's different from our current checkpoint_segments setting. With 
checkpoint_segments, the documented formula for calculating the disk 
usage is (2 + checkpoint_completion_target) * checkpoint_segments * 16 
MB. That's a lot less intuitive to set.

- Heikki

On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
>>> Works for me. However, note that "max_checkpoint_segments = 10" doesn't
>>> mean
>>> the same as current "checkpoint_segments = 10". With checkpoint_segments
>>> =
>>> 10 you end up with about 2x-3x as much WAL as with
>>> max_checkpoint_segments =
>>> 10. So the "everybody's used to it" argument doesn't hold much water.
>>
>>
>> Hmm, that's surprising.  Why does that happen?
>
> That's the whole point of this patch. "max_checkpoint_segments = 10", or
> "max_checkpoint_segments = 160 MB", means that the system will begin a
> checkpoint so that when the checkpoint completes, and it truncates away or
> recycles old WAL, the total size of pg_xlog is 160 MB.
>
> That's different from our current checkpoint_segments setting. With
> checkpoint_segments, the documented formula for calculating the disk usage
> is (2 + checkpoint_completion_target) * checkpoint_segments * 16 MB. That's
> a lot less intuitive to set.

Hmm, that's different from what I was thinking.  We probably shouldn't
call that max_checkpoint_segments, then.  I got confused and thought
you were just trying to decouple the number of segments that it takes
to trigger a checkpoint from the number we keep preallocated.

But I'm confused: how can we know how much new WAL will be written
before the checkpoint completes?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 03/02/15 16:50, Robert Haas wrote:
> On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>>
>> That's the whole point of this patch. "max_checkpoint_segments = 10", or
>> "max_checkpoint_segments = 160 MB", means that the system will begin a
>> checkpoint so that when the checkpoint completes, and it truncates away or
>> recycles old WAL, the total size of pg_xlog is 160 MB.
>>
>> That's different from our current checkpoint_segments setting. With
>> checkpoint_segments, the documented formula for calculating the disk usage
>> is (2 + checkpoint_completion_target) * checkpoint_segments * 16 MB. That's
>> a lot less intuitive to set.
>
> Hmm, that's different from what I was thinking.  We probably shouldn't
> call that max_checkpoint_segments, then.  I got confused and thought
> you were just trying to decouple the number of segments that it takes
> to trigger a checkpoint from the number we keep preallocated.
>
> But I'm confused: how can we know how much new WAL will be written
> before the checkpoint completes?
>

The preallocation is based on estimated size of next checkpoint which is 
basically running average of the previous checkpoints with some 
additional adjustments for unsteady behavior (last checkpoint has higher 
weight in the formula).

(we also still internally have the CheckPointSegments which is 
calculated the way Heikki described above)

In any case, I don't like the max_checkpoint_segments naming too much, 
and I don't even like the number of segments as limit too much, I think 
the ability to set this in actual size is quite nice property of this 
patch and as Heikki says the numbers don't map that well to the old ones 
in practice.

I did some code reading and I do like the patch. Basically only negative 
thing I can say is that I am not big fan of _logSegNo variable name but 
that's not new in this patch, we use it all over the place in xlog.

I would vote for bigger default of the checkpoint_wal_size (or whatever 
it will be named) though, since the current one is not much bigger in 
practice than what we have now and that one is way too conservative.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

On 02/03/2015 07:50 AM, Robert Haas wrote:
> On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> That's the whole point of this patch. "max_checkpoint_segments = 10", or
>> "max_checkpoint_segments = 160 MB", means that the system will begin a
>> checkpoint so that when the checkpoint completes, and it truncates away or
>> recycles old WAL, the total size of pg_xlog is 160 MB.
>>
>> That's different from our current checkpoint_segments setting. With
>> checkpoint_segments, the documented formula for calculating the disk usage
>> is (2 + checkpoint_completion_target) * checkpoint_segments * 16 MB. That's
>> a lot less intuitive to set.
> 
> Hmm, that's different from what I was thinking.  We probably shouldn't
> call that max_checkpoint_segments, then.  I got confused and thought
> you were just trying to decouple the number of segments that it takes
> to trigger a checkpoint from the number we keep preallocated.

Wait, what?  Because the new setting is an actual soft maximum, we
*shouldn't* call it a maximum?  Or are you saying something else?

On 02/03/2015 04:25 AM, Heikki Linnakangas wrote:
> On 02/02/2015 04:21 PM, Andres Freund wrote:
>> I think we need to increase checkpoint_timeout too - that's actually
>> just as important for the default experience from my pov. 5 minutes
>> often just unnecessarily generates FPWs en masse.

I have yet to see any serious benchmarking on checkpoint_timeout.  It
does seem that for some workloads on some machines a longer timeout is
better, but I've also seen workloads where a longer timeout decreases
throughput or raises IO.  So absent some hard numbers, I'd be opposed to
changing the default.

>>
>>> I'll open the bidding at 1600MB (aka 100).
>>
>> Fine with me.
>
> I wouldn't object to raising it a little bit, but that's way too high.
> It's entirely possible to have a small database that generates a lot of
> WAL. A table that has only a few rows, but is updated very very
> frequently, for example. And checkpointing such a database is quick too,
> so frequent checkpoints are not a problem. You don't want to end up with
> 1.5 GB of WAL on a 100 MB database.

I suggest 192MB instead (12 segments).  That almost doubles our current
real default, without requiring huge disk space which might surprise
some users.

In practice, checkpoint_segments is impossible to automatically tune
correctly.  So let's be conservative.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Tue, Feb 3, 2015 at 4:18 PM, Josh Berkus <josh@agliodbs.com> wrote:
>>> That's different from our current checkpoint_segments setting. With
>>> checkpoint_segments, the documented formula for calculating the disk usage
>>> is (2 + checkpoint_completion_target) * checkpoint_segments * 16 MB. That's
>>> a lot less intuitive to set.
>>
>> Hmm, that's different from what I was thinking.  We probably shouldn't
>> call that max_checkpoint_segments, then.  I got confused and thought
>> you were just trying to decouple the number of segments that it takes
>> to trigger a checkpoint from the number we keep preallocated.
>
> Wait, what?  Because the new setting is an actual soft maximum, we
> *shouldn't* call it a maximum?  Or are you saying something else?

I am saying that I proposed calling it max_checkpoint_segments because
I thought it was the maximum number of segments between checkpoints.
But it's not.

>> I wouldn't object to raising it a little bit, but that's way too high.
>> It's entirely possible to have a small database that generates a lot of
>> WAL. A table that has only a few rows, but is updated very very
>> frequently, for example. And checkpointing such a database is quick too,
>> so frequent checkpoints are not a problem. You don't want to end up with
>> 1.5 GB of WAL on a 100 MB database.
>
> I suggest 192MB instead (12 segments).  That almost doubles our current
> real default, without requiring huge disk space which might surprise
> some users.
>
> In practice, checkpoint_segments is impossible to automatically tune
> correctly.  So let's be conservative.

We are too often far too conservative about these things.  If we make
the default 192MB, it will only ever get tuned in one direction: up.
It is not a bad thing for us to set the settings high enough that once
in a great while someone might find them to be too high rather than
too low.

I find it amazing that anyone here thinks that a user would be OK with
using 192MB of space for WAL, but 384MB would break the bank.  The
hard drive in my laptop is 456GB.  The point is, with Heikki's work
here, you're only going to use the maximum amount of space if you have
massive write activity.  And if you have massive write activity, it's
extremely likely that you will be OK with using a very modest amount
of disk space to have that be fast.  Right now, we have to be really
conservative because we're going to use the full allocation all the
time, but this fixes that.  I think.

If somebody were to propose limiting the size of the database to
192MB, and requiring a configuration setting to make it larger,
everybody would say that's a terrible idea.  Heck, if I were to
propose limiting the database to 19.2GB, and require a configuration
setting to make it larger, everybody would say that's a terrible idea.
But what we actually have is not far off from that.  Sure, you can
create a 20GB database with an out-of-the-box configuration, but you'd
better get out your pillow before starting the data load, because with
checkpoint_segments=3 that's going to be fantastically slow.  And
you'd better hope that the update rate is pretty low, too, because if
it's anything even slightly interesting you're going to be spewing
checkpoint warnings into the log.  So our settings need to *support*
creating a 20GB database out of the box, but it's OK if it performs
absolutely terribly.

I really have a hard time believing that there are many people who are
going to complain about WAL utilization peaking at 1.6GB (my initial
proposal).  Your database is probably rapidly expanding, and the WAL
utilization will drop when it stops.  And if it isn't rapidly
expanding, because you're doing a ton of updates in place, you'll
probably still be happier to spend a little extra disk space than to
have it be cripplingly slow.  And if you're not, then, first, what is
wrong with you, and second, well then you can turn down the setting.
That's why we have settings.  I enjoy getting paid to tell people to
increase checkpoint_segments by two orders of magnitude as much as the
next PostgreSQL consultant, but I don't enjoy the fact that people
benchmark the default configuration and get terrible results because
we haven't updated the default value for this parameter since it was
added in 2001.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/04/2015 09:28 AM, Robert Haas wrote:
> On Tue, Feb 3, 2015 at 4:18 PM, Josh Berkus <josh@agliodbs.com> wrote:
>>>> That's different from our current checkpoint_segments setting. With
>>>> checkpoint_segments, the documented formula for calculating the disk usage
>>>> is (2 + checkpoint_completion_target) * checkpoint_segments * 16 MB. That's
>>>> a lot less intuitive to set.
>>>
>>> Hmm, that's different from what I was thinking.  We probably shouldn't
>>> call that max_checkpoint_segments, then.  I got confused and thought
>>> you were just trying to decouple the number of segments that it takes
>>> to trigger a checkpoint from the number we keep preallocated.
>>
>> Wait, what?  Because the new setting is an actual soft maximum, we
>> *shouldn't* call it a maximum?  Or are you saying something else?
> 
> I am saying that I proposed calling it max_checkpoint_segments because
> I thought it was the maximum number of segments between checkpoints.
> But it's not.

That's good, though, isn't it? Knowing the number of segments between
checkpoints is useful only to postgres experts with experience.  What
the patch defines is what most users actually want to know: how much
disk space, total, do I need to allocate?

Let me push "max_wal_size" and "min_wal_size" again as our new parameter
names, because:

* does what it says on the tin
* new user friendly
* encourages people to express it in MB, not segments
* very different from the old name, so people will know it works differently

> We are too often far too conservative about these things.  If we make
> the default 192MB, it will only ever get tuned in one direction: up.
> It is not a bad thing for us to set the settings high enough that once
> in a great while someone might find them to be too high rather than
> too low.
> 
> I find it amazing that anyone here thinks that a user would be OK with
> using 192MB of space for WAL, but 384MB would break the bank.  The
> hard drive in my laptop is 456GB.  The point is, with Heikki's work
> here, you're only going to use the maximum amount of space if you have
> massive write activity.  And if you have massive write activity, it's
> extremely likely that you will be OK with using a very modest amount
> of disk space to have that be fast.  Right now, we have to be really
> conservative because we're going to use the full allocation all the
> time, but this fixes that.  I think.

Hmmm, I see your point.  I spend a lot of time on AWS and in
container-world, where disk space is a lot more constrained.  However,
it probably makes more sense to recommend non-default settings for that
environment, since it requires non-default settings anyway.

So, 384MB?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Wed, Feb 4, 2015 at 1:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Let me push "max_wal_size" and "min_wal_size" again as our new parameter
> names, because:
>
> * does what it says on the tin
> * new user friendly
> * encourages people to express it in MB, not segments
> * very different from the old name, so people will know it works differently

That's not bad.  If we added a hard WAL limit in a future release, how
would that fit into this naming scheme?

>> We are too often far too conservative about these things.  If we make
>> the default 192MB, it will only ever get tuned in one direction: up.
>> It is not a bad thing for us to set the settings high enough that once
>> in a great while someone might find them to be too high rather than
>> too low.
>>
>> I find it amazing that anyone here thinks that a user would be OK with
>> using 192MB of space for WAL, but 384MB would break the bank.  The
>> hard drive in my laptop is 456GB.  The point is, with Heikki's work
>> here, you're only going to use the maximum amount of space if you have
>> massive write activity.  And if you have massive write activity, it's
>> extremely likely that you will be OK with using a very modest amount
>> of disk space to have that be fast.  Right now, we have to be really
>> conservative because we're going to use the full allocation all the
>> time, but this fixes that.  I think.
>
> Hmmm, I see your point.  I spend a lot of time on AWS and in
> container-world, where disk space is a lot more constrained.  However,
> it probably makes more sense to recommend non-default settings for that
> environment, since it requires non-default settings anyway.
>
> So, 384MB?

That's certainly better, but I think we should go further.  Again,
you're not committed to using this space all the time, and if you are
using it you must have a lot of write activity, which means you are
not running on a tin can and a string.  If you have a little tiny
database, say 100MB, running on a little-tiny Amazon instance,
handling a small number of transactions, you're going to stay close to
wal_min_size anyway.  Right?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/04/2015 12:06 PM, Robert Haas wrote:
> On Wed, Feb 4, 2015 at 1:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Let me push "max_wal_size" and "min_wal_size" again as our new parameter
>> names, because:
>>
>> * does what it says on the tin
>> * new user friendly
>> * encourages people to express it in MB, not segments
>> * very different from the old name, so people will know it works differently
> 
> That's not bad.  If we added a hard WAL limit in a future release, how
> would that fit into this naming scheme?

Well, first, nobody's at present proposing a patch to add a hard limit,
so I'm reluctant to choose non-obvious names to avoid conflict with a
feature nobody may ever write.  There's a number of reasons a hard limit
would be difficult and/or undesirable.

If we did add one, I'd suggest calling it "wal_size_limit" or something
similar.  However, we're most likely to only implement the limit for
archives, which means that it might acually be called
"archive_buffer_limit" or something more to the point.

> That's certainly better, but I think we should go further.  Again,
> you're not committed to using this space all the time, and if you are
> using it you must have a lot of write activity, which means you are
> not running on a tin can and a string.  If you have a little tiny
> database, say 100MB, running on a little-tiny Amazon instance,
> handling a small number of transactions, you're going to stay close to
> wal_min_size anyway.  Right?

Well, we can test that.

So what's your proposed size?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 2/4/15 3:06 PM, Robert Haas wrote:
>> Hmmm, I see your point.  I spend a lot of time on AWS and in
>> container-world, where disk space is a lot more constrained.  However,
>> it probably makes more sense to recommend non-default settings for that
>> environment, since it requires non-default settings anyway.
>>
>> So, 384MB?
> That's certainly better, but I think we should go further.  Again,
> you're not committed to using this space all the time, and if you are
> using it you must have a lot of write activity, which means you are
> not running on a tin can and a string.  If you have a little tiny
> database, say 100MB, running on a little-tiny Amazon instance,
> handling a small number of transactions, you're going to stay close to
> wal_min_size anyway.  Right?

The main exception I can think of is when using dump/restore to upgrade
instead of pg_upgrade.  This would generate a lot of WAL for what could
otherwise be a low-traffic database.

--
- David Steele
david@pgmasters.net

On 2/4/15 6:16 PM, David Steele wrote:
> On 2/4/15 3:06 PM, Robert Haas wrote:
>>> Hmmm, I see your point.  I spend a lot of time on AWS and in
>>> container-world, where disk space is a lot more constrained.  However,
>>> it probably makes more sense to recommend non-default settings for that
>>> environment, since it requires non-default settings anyway.
>>>
>>> So, 384MB?
>> That's certainly better, but I think we should go further.  Again,
>> you're not committed to using this space all the time, and if you are
>> using it you must have a lot of write activity, which means you are
>> not running on a tin can and a string.  If you have a little tiny
>> database, say 100MB, running on a little-tiny Amazon instance,
>> handling a small number of transactions, you're going to stay close to
>> wal_min_size anyway.  Right?
>
> The main exception I can think of is when using dump/restore to upgrade
> instead of pg_upgrade.  This would generate a lot of WAL for what could
> otherwise be a low-traffic database.

But you'd still want to use that extra WAL space so you're not 
checkpointing every 3 seconds. Really I can't see this becoming an issue 
unless you're about to run out of disk space.

Is there a defined way to find out how much space we have left on the 
disk that's hosting WAL? If so we could curtail WAL size if we're close 
to running out of room. (But, honestly, I think we should just set this 
to 1-2GB and be done with it).
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Wed, Feb 4, 2015 at 4:41 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> That's certainly better, but I think we should go further.  Again,
>> you're not committed to using this space all the time, and if you are
>> using it you must have a lot of write activity, which means you are
>> not running on a tin can and a string.  If you have a little tiny
>> database, say 100MB, running on a little-tiny Amazon instance,
>> handling a small number of transactions, you're going to stay close to
>> wal_min_size anyway.  Right?
>
> Well, we can test that.
>
> So what's your proposed size?

I previously proposed 100 segments, or 1.6GB.  If that seems too
large, how about 64 segments, or 1.024GB?  I think there will be few
people who can't tolerate a gigabyte of xlog under peak load, and an
awful lot who will benefit from it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2015-02-05 09:42:37 -0500, Robert Haas wrote:
> I previously proposed 100 segments, or 1.6GB.  If that seems too
> large, how about 64 segments, or 1.024GB?  I think there will be few
> people who can't tolerate a gigabyte of xlog under peak load, and an
> awful lot who will benefit from it.

It'd be quite easier to go there if we'd shrink back to the min_size
after a while, after having peaked above it. IIUC the patch doesn't do
that?

Admittedly it's not easy to come up with an algorithm that doesn't cause
superflous file removals. Initiating wal files isn't cheap.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 02/05/2015 04:47 PM, Andres Freund wrote:
> On 2015-02-05 09:42:37 -0500, Robert Haas wrote:
>> I previously proposed 100 segments, or 1.6GB.  If that seems too
>> large, how about 64 segments, or 1.024GB?  I think there will be few
>> people who can't tolerate a gigabyte of xlog under peak load, and an
>> awful lot who will benefit from it.
>
> It'd be quite easier to go there if we'd shrink back to the min_size
> after a while, after having peaked above it. IIUC the patch doesn't do
> that?

It doesn't actively go and remove files once they've already been 
recycled, but if the system stays relatively idle for several 
checkpoints, the WAL usage will shrink down again. That's the core idea 
of the patch.

If the system stays completely or almost completely idle, that won't 
happen though, because then it will never switch to a new segment so 
none of the segments become old so that they could be removed.

- Heikki

On 02/04/2015 04:16 PM, David Steele wrote:
> On 2/4/15 3:06 PM, Robert Haas wrote:
>>> Hmmm, I see your point.  I spend a lot of time on AWS and in
>>> container-world, where disk space is a lot more constrained.  However,
>>> it probably makes more sense to recommend non-default settings for that
>>> environment, since it requires non-default settings anyway.
>>>
>>> So, 384MB?
>> That's certainly better, but I think we should go further.  Again,
>> you're not committed to using this space all the time, and if you are
>> using it you must have a lot of write activity, which means you are
>> not running on a tin can and a string.  If you have a little tiny
>> database, say 100MB, running on a little-tiny Amazon instance,
>> handling a small number of transactions, you're going to stay close to
>> wal_min_size anyway.  Right?
> 
> The main exception I can think of is when using dump/restore to upgrade
> instead of pg_upgrade.  This would generate a lot of WAL for what could
> otherwise be a low-traffic database.

Except that, when setting up servers for customers, one thing I pretty
much always do for them is temporarily increase checkpoint_segments for
the initial data load.  So having Postgres do this automatically would
be a feature, not a bug.

I say we go with ~~ 1GB.  That's an 8X increase over current default
size for the maximum

Default of 4 for min_wal_size?

On 02/04/2015 07:37 PM, Amit Kapila wrote:> On Thu, Feb 5, 2015 at 3:11
AM, Josh Berkus <josh@agliodbs.com
>> If we did add one, I'd suggest calling it "wal_size_limit" or something
>> similar.
>
> I think both the names (max_wal_size and wal_size_limit) seems to
> indicate the same same thing.  Few more suggestions:
> typical_wal_size, wal_size_soft_limit?

Again, you're suggesting more complicated (and difficult to translate,
and for that matter misleading) names in order to work around a future
feature which nobody is currently working on, and we may never have.
Let's keep clear and simple parameter names which most people can
understand, instead of making things complicated for the sake of complexity.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Feb 5, 2015 at 2:11 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Except that, when setting up servers for customers, one thing I pretty
> much always do for them is temporarily increase checkpoint_segments for
> the initial data load.  So having Postgres do this automatically would
> be a feature, not a bug.

Right!

> I say we go with ~~ 1GB.  That's an 8X increase over current default
> size for the maximum

Sounds great.

> Default of 4 for min_wal_size?

I assume you mean 4 segments; why not 3 as currently?  As long as the
system has the latitude to ratchet it up when needed, there seems to
be little advantage to raising the minimum.  Of course I guess there
must be some advantage or Heikki wouldn't have made it configurable,
but I'd err on the side of keeping this one small.  Hopefully the
system that automatically adjusts this is really smart, and a large
min_wal_size is superfluous for most people.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/05/2015 01:28 PM, Robert Haas wrote:
> On Thu, Feb 5, 2015 at 2:11 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Except that, when setting up servers for customers, one thing I pretty
>> much always do for them is temporarily increase checkpoint_segments for
>> the initial data load.  So having Postgres do this automatically would
>> be a feature, not a bug.
> 
> Right!
> 
>> I say we go with ~~ 1GB.  That's an 8X increase over current default
>> size for the maximum
> 
> Sounds great.
> 
>> Default of 4 for min_wal_size?
> 
> I assume you mean 4 segments; why not 3 as currently?  As long as the
> system has the latitude to ratchet it up when needed, there seems to
> be little advantage to raising the minimum.  Of course I guess there
> must be some advantage or Heikki wouldn't have made it configurable,
> but I'd err on the side of keeping this one small.  Hopefully the
> system that automatically adjusts this is really smart, and a large
> min_wal_size is superfluous for most people.

Keep in mind that the current is actually 7, not three (3*2+1).  So 3
would be a siginficant decrease.  However, I don't feel strongly about
it either way.  I think that there is probably a minimum reasonable
value > 1, but I'm not sure what it is.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 02/05/2015 11:28 PM, Robert Haas wrote:
> On Thu, Feb 5, 2015 at 2:11 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Default of 4 for min_wal_size?
>
> I assume you mean 4 segments; why not 3 as currently?  As long as the
> system has the latitude to ratchet it up when needed, there seems to
> be little advantage to raising the minimum.  Of course I guess there
> must be some advantage or Heikki wouldn't have made it configurable,
> but I'd err on the side of keeping this one small.  Hopefully the
> system that automatically adjusts this is really smart, and a large
> min_wal_size is superfluous for most people.

There are a few reasons for making the minimum configurable:

1. Creating new segments when you need them is not free, so if you have 
a workload with occasional very large spikes, you might want to prepare 
for them. The auto-tuning will accommodate for the peak usage, but it's 
a moving average so if the peaks are infrequent enough, it will shrink 
the size down between the spikes.

2. To avoid running out of disk space on write to WAL (which leads to a 
PANIC). In particular, if you have the WAL on the same filesystem as the 
data, pre-reserving all the space required for WAL makes it much more 
likely that you when you run out of disk space, you run out when writing 
regular data, not WAL.

3. Unforeseen issues with the auto-tuning. It might not suite everyone, 
so it's nice that you can still get the old behaviour by setting min=max.

Actually, perhaps we should have a boolean setting that just implies 
min=max, instead of having a configurable minimum?. That would cover all 
of those reasons pretty well. So we would have a "max_wal_size" setting, 
and a boolean "preallocate_all_wal = on | off". Would anyone care for 
the flexibility of setting a minimum that's different from the maximum?

- Heikki

On Thu, Feb 5, 2015 at 4:42 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> Actually, perhaps we should have a boolean setting that just implies
> min=max, instead of having a configurable minimum?. That would cover all of
> those reasons pretty well. So we would have a "max_wal_size" setting, and a
> boolean "preallocate_all_wal = on | off". Would anyone care for the
> flexibility of setting a minimum that's different from the maximum?

I like the way you have it now better.  If we knew for certain that
there were no advantage in configuring a value between 0 and the
maximum, that would be one thing, but we don't and can't know that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>>> Default of 4 for min_wal_size?
>>
>> I assume you mean 4 segments; why not 3 as currently?  As long as the
>> system has the latitude to ratchet it up when needed, there seems to
>> be little advantage to raising the minimum.  Of course I guess there
>> must be some advantage or Heikki wouldn't have made it configurable,
>> but I'd err on the side of keeping this one small.  Hopefully the
>> system that automatically adjusts this is really smart, and a large
>> min_wal_size is superfluous for most people.
>
> Keep in mind that the current is actually 7, not three (3*2+1).  So 3
> would be a siginficant decrease.  However, I don't feel strongly about
> it either way.  I think that there is probably a minimum reasonable
> value > 1, but I'm not sure what it is.

Good point.  OK, 4 works for me.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/05/2015 01:42 PM, Heikki Linnakangas wrote:
> There are a few reasons for making the minimum configurable:

Any thoughts on what the default minimum should be, if the default max
is 1.1GB/64?

> 1. Creating new segments when you need them is not free, so if you have
> a workload with occasional very large spikes, you might want to prepare
> for them. The auto-tuning will accommodate for the peak usage, but it's
> a moving average so if the peaks are infrequent enough, it will shrink
> the size down between the spikes.
> 
> 2. To avoid running out of disk space on write to WAL (which leads to a
> PANIC). In particular, if you have the WAL on the same filesystem as the
> data, pre-reserving all the space required for WAL makes it much more
> likely that you when you run out of disk space, you run out when writing
> regular data, not WAL.
> 
> 3. Unforeseen issues with the auto-tuning. It might not suite everyone,
> so it's nice that you can still get the old behaviour by setting min=max.
> 
> Actually, perhaps we should have a boolean setting that just implies
> min=max, instead of having a configurable minimum?. That would cover all
> of those reasons pretty well. So we would have a "max_wal_size" setting,
> and a boolean "preallocate_all_wal = on | off". Would anyone care for
> the flexibility of setting a minimum that's different from the maximum?

I do, actually.  Here's the case I want it for:

I have a web application which gets all of its new data as uncoordinated
batch updates from customers.  Since it's possible for me to receive
several batch updates at once, I set max_wal_size to 16GB, roughtly the
side of 8 batch updates.  But I don't want the WAL that big all the time
because it slows down backup snapshots.  So I set min_wal_size to 2GB,
roughly the size of one batch update.

That's an idiosyncratic case, but I can imagine more of them out there.

I wouldn't be opposed to min_wal_size = -1 meaning "same as
max_wal_size" though.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 2/5/15 4:53 PM, Josh Berkus wrote:
>> Actually, perhaps we should have a boolean setting that just implies
>> min=max, instead of having a configurable minimum?. That would cover all
>> of those reasons pretty well. So we would have a "max_wal_size" setting,
>> and a boolean "preallocate_all_wal = on | off". Would anyone care for
>> the flexibility of setting a minimum that's different from the maximum?
> I do, actually.  Here's the case I want it for:
>
> I have a web application which gets all of its new data as uncoordinated
> batch updates from customers.  Since it's possible for me to receive
> several batch updates at once, I set max_wal_size to 16GB, roughtly the
> side of 8 batch updates.  But I don't want the WAL that big all the time
> because it slows down backup snapshots.  So I set min_wal_size to 2GB,
> roughly the size of one batch update.
>
> That's an idiosyncratic case, but I can imagine more of them out there.
>
> I wouldn't be opposed to min_wal_size = -1 meaning "same as
> max_wal_size" though.

+1 for min_wal_size.  Like Josh, I can think of instances where this
would be good.

--
- David Steele
david@pgmasters.net

On 02/04/2015 11:41 PM, Josh Berkus wrote:
> On 02/04/2015 12:06 PM, Robert Haas wrote:
>> On Wed, Feb 4, 2015 at 1:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
>>> Let me push "max_wal_size" and "min_wal_size" again as our new parameter
>>> names, because:
>>>
>>> * does what it says on the tin
>>> * new user friendly
>>> * encourages people to express it in MB, not segments
>>> * very different from the old name, so people will know it works differently
>>
>> That's not bad.  If we added a hard WAL limit in a future release, how
>> would that fit into this naming scheme?
>
> Well, first, nobody's at present proposing a patch to add a hard limit,
> so I'm reluctant to choose non-obvious names to avoid conflict with a
> feature nobody may ever write.  There's a number of reasons a hard limit
> would be difficult and/or undesirable.
>
> If we did add one, I'd suggest calling it "wal_size_limit" or something
> similar.  However, we're most likely to only implement the limit for
> archives, which means that it might acually be called
> "archive_buffer_limit" or something more to the point.

Ok, I don't hear any loud objections to min_wal_size and max_wal_size,
so let's go with that then.

Attached is a new version of this. It now comes in four patches. The
first three are just GUC-related preliminary work, the first of which I
posted on a separate thread today.

- Heikki

On 13/02/15 18:43, Heikki Linnakangas wrote:
>
> Ok, I don't hear any loud objections to min_wal_size and max_wal_size,
> so let's go with that then.
>
> Attached is a new version of this. It now comes in four patches. The
> first three are just GUC-related preliminary work, the first of which I
> posted on a separate thread today.
>


The 0001 patch is very nice, I would go ahead and commit it.

Not really sure I see the need for 0002 but it should not harm anything 
so why not.

The 0003 should be part of 0004 IMHO as it does not really do anything 
by itself.

I am wondering a bit about interaction with wal_keep_segments.
One thing is that wal_keep_segments is still specified in number of 
segments and not size units, maybe it would be worth to change it also?
And the other thing is that, if set, the wal_keep_segments is the real 
max_wal_size from the user perspective (not from perspective of the 
algorithm in this patch, but user does not really care about that) which 
is somewhat weird given the naming.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

On Sat, Feb 21, 2015 at 11:29 PM, Petr Jelinek <petr@2ndquadrant.com> wrote:
> I am wondering a bit about interaction with wal_keep_segments.
> One thing is that wal_keep_segments is still specified in number of segments
> and not size units, maybe it would be worth to change it also?
> And the other thing is that, if set, the wal_keep_segments is the real
> max_wal_size from the user perspective (not from perspective of the
> algorithm in this patch, but user does not really care about that) which is
> somewhat weird given the naming.

It seems like wal_keep_segments is more closely related to
wal_*min*_size.  The idea of both settings is that each is a minimum
amount of WAL we want to keep around for some purpose.  But they're
not quite the same, I guess, because wal_min_size just forces us to
keep that many files around - they can be overwritten whenever.
wal_keep_segments is an amount of actual WAL data we want to keep
around.

Would it make sense to require that wal_keep_segments <= wal_min_size?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 23/02/15 03:24, Robert Haas wrote:
> On Sat, Feb 21, 2015 at 11:29 PM, Petr Jelinek <petr@2ndquadrant.com> wrote:
>> I am wondering a bit about interaction with wal_keep_segments.
>> One thing is that wal_keep_segments is still specified in number of segments
>> and not size units, maybe it would be worth to change it also?
>> And the other thing is that, if set, the wal_keep_segments is the real
>> max_wal_size from the user perspective (not from perspective of the
>> algorithm in this patch, but user does not really care about that) which is
>> somewhat weird given the naming.
>
> It seems like wal_keep_segments is more closely related to
> wal_*min*_size.  The idea of both settings is that each is a minimum
> amount of WAL we want to keep around for some purpose.  But they're
> not quite the same, I guess, because wal_min_size just forces us to
> keep that many files around - they can be overwritten whenever.
> wal_keep_segments is an amount of actual WAL data we want to keep
> around.

Err yes of course, min not max :)

>
> Would it make sense to require that wal_keep_segments <= wal_min_size?
>

It would to me, the patch as it stands is confusing in a sense that you 
can set min and max but then wal_keep_segments somewhat overrides those.

And BTW this brings another point, I actually don't see check for 
min_wal_size <= max_wal_size anywhere in the patch.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services

On 2015-02-22 21:24:56 -0500, Robert Haas wrote:
> On Sat, Feb 21, 2015 at 11:29 PM, Petr Jelinek <petr@2ndquadrant.com> wrote:
> > I am wondering a bit about interaction with wal_keep_segments.
> > One thing is that wal_keep_segments is still specified in number of segments
> > and not size units, maybe it would be worth to change it also?
> > And the other thing is that, if set, the wal_keep_segments is the real
> > max_wal_size from the user perspective (not from perspective of the
> > algorithm in this patch, but user does not really care about that) which is
> > somewhat weird given the naming.
> 
> It seems like wal_keep_segments is more closely related to
> wal_*min*_size.  The idea of both settings is that each is a minimum
> amount of WAL we want to keep around for some purpose.  But they're
> not quite the same, I guess, because wal_min_size just forces us to
> keep that many files around - they can be overwritten whenever.
> wal_keep_segments is an amount of actual WAL data we want to keep
> around.
> 
> Would it make sense to require that wal_keep_segments <= wal_min_size?

I don't think so. Right now checkpoint_segments is a useful tool to
relatively effectively control the amount of WAL that needs to be
replayed in the event of a crash. wal_keep_segments in contrast doesn't
have much to do with the normal working of the system, except that it
delays recycling of WAL segments a bit.

With a condition like above, how would you set up things that you have
50k segments around for replication (say a good days worth), but that
your will never have to replay more than ~800 segments (i.e. something
like checkpoint_segments = 800)?

Am I missing something?

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 02/23/2015 01:01 PM, Andres Freund wrote:
> On 2015-02-22 21:24:56 -0500, Robert Haas wrote:
>> On Sat, Feb 21, 2015 at 11:29 PM, Petr Jelinek <petr@2ndquadrant.com> wrote:
>>> I am wondering a bit about interaction with wal_keep_segments.
>>> One thing is that wal_keep_segments is still specified in number of segments
>>> and not size units, maybe it would be worth to change it also?
>>> And the other thing is that, if set, the wal_keep_segments is the real
>>> max_wal_size from the user perspective (not from perspective of the
>>> algorithm in this patch, but user does not really care about that) which is
>>> somewhat weird given the naming.
>>
>> It seems like wal_keep_segments is more closely related to
>> wal_*min*_size.  The idea of both settings is that each is a minimum
>> amount of WAL we want to keep around for some purpose.  But they're
>> not quite the same, I guess, because wal_min_size just forces us to
>> keep that many files around - they can be overwritten whenever.
>> wal_keep_segments is an amount of actual WAL data we want to keep
>> around.
>>
>> Would it make sense to require that wal_keep_segments <= wal_min_size?
>
> I don't think so. Right now checkpoint_segments is a useful tool to
> relatively effectively control the amount of WAL that needs to be
> replayed in the event of a crash. wal_keep_segments in contrast doesn't
> have much to do with the normal working of the system, except that it
> delays recycling of WAL segments a bit.
>
> With a condition like above, how would you set up things that you have
> 50k segments around for replication (say a good days worth), but that
> your will never have to replay more than ~800 segments (i.e. something
> like checkpoint_segments = 800)?

Right. While wal_keep_segments and wal_min_size both set a kind of a 
minimum on the amount of WAL that's kept in pg_xlog, they are different 
things, and a rule that one must be less than or greater than the other 
doesn't make sense.

Everyone seems to be happy with the names and behaviour of the GUCs, so 
committed.

- Heikki

On 02/23/2015 08:56 AM, Heikki Linnakangas wrote:
> Everyone seems to be happy with the names and behaviour of the GUCs, so
> committed.

Yay!

But ... I thought we were going to raise the default for max_wal_size to
something much higher, like 1GB?  That's what was discussed on this thread.

When I build, I get this:

#max_wal_size = 128MB                   # in logfile segments
#min_wal_size = 80MB

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 02/26/2015 01:32 AM, Josh Berkus wrote:
> But ... I thought we were going to raise the default for max_wal_size to
> something much higher, like 1GB?  That's what was discussed on this thread.

No conclusion was reached on that. Me and some others were against 
raising the default, while others were for it.

- Heikki

On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> On 02/26/2015 01:32 AM, Josh Berkus wrote:
>> But ... I thought we were going to raise the default for max_wal_size to
>> something much higher, like 1GB?  That's what was discussed on this
>> thread.
>
> No conclusion was reached on that. Me and some others were against raising
> the default, while others were for it.

I guess that's a fair summary of the discussion, but I still think
it's the wrong conclusion.  Right now, you can't get reasonable write
performance with PostgreSQL even on tiny databases (a few GB) without
increasing that setting by an order of magnitude.  It seems an awful
shame to go to all the work to mitigate the downsides of setting a
large checkpoint_segments and then still ship a tiny default setting.
I've got to believe that the number of people who think 128MB of WAL
is tolerable but 512MB or 1GB is excessive is almost nobody.  Disk
sizes these days are measured in TB.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> > On 02/26/2015 01:32 AM, Josh Berkus wrote:
> >> But ... I thought we were going to raise the default for max_wal_size to
> >> something much higher, like 1GB?  That's what was discussed on this
> >> thread.
> >
> > No conclusion was reached on that. Me and some others were against raising
> > the default, while others were for it.
>
> I guess that's a fair summary of the discussion, but I still think
> it's the wrong conclusion.  Right now, you can't get reasonable write
> performance with PostgreSQL even on tiny databases (a few GB) without
> increasing that setting by an order of magnitude.  It seems an awful
> shame to go to all the work to mitigate the downsides of setting a
> large checkpoint_segments and then still ship a tiny default setting.
> I've got to believe that the number of people who think 128MB of WAL
> is tolerable but 512MB or 1GB is excessive is almost nobody.  Disk
> sizes these days are measured in TB.

+1.  I thought the conclusion had actually been in favor of the change,
though there had been voices for and against.
Thanks,
    Stephen

On 03/02/2015 05:38 AM, Stephen Frost wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>>> On 02/26/2015 01:32 AM, Josh Berkus wrote:
>>>> But ... I thought we were going to raise the default for max_wal_size to
>>>> something much higher, like 1GB?  That's what was discussed on this
>>>> thread.
>>>
>>> No conclusion was reached on that. Me and some others were against raising
>>> the default, while others were for it.
>>
>> I guess that's a fair summary of the discussion, but I still think
>> it's the wrong conclusion.  Right now, you can't get reasonable write
>> performance with PostgreSQL even on tiny databases (a few GB) without
>> increasing that setting by an order of magnitude.  It seems an awful
>> shame to go to all the work to mitigate the downsides of setting a
>> large checkpoint_segments and then still ship a tiny default setting.
>> I've got to believe that the number of people who think 128MB of WAL
>> is tolerable but 512MB or 1GB is excessive is almost nobody.  Disk
>> sizes these days are measured in TB.
> 
> +1.  I thought the conclusion had actually been in favor of the change,
> though there had been voices for and against.

That was the impression I had too, which was why I was surprised.  The
last post on the topic was one by Robert Haas, agreeing with me on a
value of 1GB, and there were zero objections after that.


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 03/02/2015 08:05 PM, Josh Berkus wrote:
> On 03/02/2015 05:38 AM, Stephen Frost wrote:
>> * Robert Haas (robertmhaas@gmail.com) wrote:
>>> On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>>>> On 02/26/2015 01:32 AM, Josh Berkus wrote:
>>>>> But ... I thought we were going to raise the default for max_wal_size to
>>>>> something much higher, like 1GB?  That's what was discussed on this
>>>>> thread.
>>>>
>>>> No conclusion was reached on that. Me and some others were against raising
>>>> the default, while others were for it.
>>>
>>> I guess that's a fair summary of the discussion, but I still think
>>> it's the wrong conclusion.  Right now, you can't get reasonable write
>>> performance with PostgreSQL even on tiny databases (a few GB) without
>>> increasing that setting by an order of magnitude.  It seems an awful
>>> shame to go to all the work to mitigate the downsides of setting a
>>> large checkpoint_segments and then still ship a tiny default setting.
>>> I've got to believe that the number of people who think 128MB of WAL
>>> is tolerable but 512MB or 1GB is excessive is almost nobody.  Disk
>>> sizes these days are measured in TB.
>>
>> +1.  I thought the conclusion had actually been in favor of the change,
>> though there had been voices for and against.
>
> That was the impression I had too, which was why I was surprised.  The
> last post on the topic was one by Robert Haas, agreeing with me on a
> value of 1GB, and there were zero objections after that.

I didn't make any further posts to that thread because I had already 
objected earlier and didn't have anything to add.

Now, if someone's going to go and raise the default, I'm not going to 
make a fuss about it, but the fact remains that *all* the defaults in 
postgresql.conf.sample are geared towards small systems, and not hogging 
all resources. The default max_wal_size of 128 MB is well in line with 
e.g. shared_buffers=128MB.

- Heikki

On 03/02/2015 08:05 PM, Josh Berkus wrote:

On 03/02/2015 05:38 AM, Stephen Frost wrote:

* Robert Haas (robertmhaas@gmail.com) wrote:

On Mon, Mar 2, 2015 at 6:43 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:

On 02/26/2015 01:32 AM, Josh Berkus wrote:

But ... I thought we were going to raise the default for max_wal_size to
something much higher, like 1GB?  That's what was discussed on this
thread.

No conclusion was reached on that. Me and some others were against raising
the default, while others were for it.

I guess that's a fair summary of the discussion, but I still think
it's the wrong conclusion.  Right now, you can't get reasonable write
performance with PostgreSQL even on tiny databases (a few GB) without
increasing that setting by an order of magnitude.  It seems an awful
shame to go to all the work to mitigate the downsides of setting a
large checkpoint_segments and then still ship a tiny default setting.
I've got to believe that the number of people who think 128MB of WAL
is tolerable but 512MB or 1GB is excessive is almost nobody.  Disk
sizes these days are measured in TB.

+1.  I thought the conclusion had actually been in favor of the change,
though there had been voices for and against.

That was the impression I had too, which was why I was surprised.  The
last post on the topic was one by Robert Haas, agreeing with me on a
value of 1GB, and there were zero objections after that.

I didn't make any further posts to that thread because I had already objected earlier and didn't have anything to add.

Now, if someone's going to go and raise the default, I'm not going to make a fuss about it, but the fact remains that *all* the defaults in postgresql.conf.sample are geared towards small systems, and not hogging all resources. The default max_wal_size of 128 MB is well in line with e.g. shared_buffers=128MB.

On 03/02/2015 12:23 PM, Heikki Linnakangas wrote:
> On 03/02/2015 08:05 PM, Josh Berkus wrote:
>> That was the impression I had too, which was why I was surprised.  The
>> last post on the topic was one by Robert Haas, agreeing with me on a
>> value of 1GB, and there were zero objections after that.
> 
> I didn't make any further posts to that thread because I had already
> objected earlier and didn't have anything to add.
> 
> Now, if someone's going to go and raise the default, I'm not going to
> make a fuss about it, but the fact remains that *all* the defaults in
> postgresql.conf.sample are geared towards small systems, and not hogging
> all resources. The default max_wal_size of 128 MB is well in line with
> e.g. shared_buffers=128MB.

OK, I don't think Robert or I realized that you were still not agreeing.I originally thought we should keep it small,
butRobert pointed out
 
that under your code, WAL only grows if you have high traffic.

Patch attached in a new thread.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, May 21, 2015 at 3:53 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Mar 16, 2015 at 11:05 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>
>> On Mon, Feb 23, 2015 at 8:56 AM, Heikki Linnakangas
>> <hlinnakangas@vmware.com> wrote:
>>>
>>>
>>> Everyone seems to be happy with the names and behaviour of the GUCs, so
>>> committed.
>>
>>
>>
>> The docs suggest that max_wal_size will be respected during archive
>> recovery (causing restartpoints and recycling), but I'm not seeing that
>> happening.  Is this a doc bug or an implementation bug?
>
>
> I think the old behavior, where restartpoints were driven only by time and
> not by volume, was a misfeature.  But not a bug, because it was documented.
>
> One of the points of max_wal_size and its predecessor is to limit how big
> pg_xlog can grow.  But running out of disk space on pg_xlog is no more fun
> during archive recovery than it is during normal operations.  So why
> shouldn't max_wal_size be active during recovery?

The following message of commit 7181530 explains why.
   In standby mode, respect checkpoint_segments in addition to   checkpoint_timeout to trigger restartpoints. We used
todeliberately only   do time-based restartpoints, because if checkpoint_segments is small we   would spend time doing
restartpointsmore often than really necessary.   But now that restartpoints are done in bgwriter, they're not as
disruptiveas they used to be. Secondly, because streaming replication   stores the streamed WAL files in pg_xlog, we
wantto clean it up more   often to avoid running out of disk space when checkpoint_timeout is large   and
checkpoint_segmentssmall.
 

Previously users were more likely to fall into this trouble (i.e., too frequent
occurrence of restartpoints) because the default value of checkpoint_segments
was very small, I guess. But we increased the default of max_wal_size, so now
the risk of that trouble seems to be smaller than before, and maybe we can
allow max_wal_size to trigger restartpoints.

Regards,

-- 
Fujii Masao

On 05/27/2015 12:26 AM, Jeff Janes wrote:
> On Thu, May 21, 2015 at 8:40 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>
>> On Thu, May 21, 2015 at 3:53 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>> One of the points of max_wal_size and its predecessor is to limit how big
>>> pg_xlog can grow.  But running out of disk space on pg_xlog is no more
>> fun
>>> during archive recovery than it is during normal operations.  So why
>>> shouldn't max_wal_size be active during recovery?
>>
>> The following message of commit 7181530 explains why.
>>
>>      In standby mode, respect checkpoint_segments in addition to
>>      checkpoint_timeout to trigger restartpoints. We used to deliberately
>> only
>>      do time-based restartpoints, because if checkpoint_segments is small we
>>      would spend time doing restartpoints more often than really necessary.
>>      But now that restartpoints are done in bgwriter, they're not as
>>      disruptive as they used to be. Secondly, because streaming replication
>>      stores the streamed WAL files in pg_xlog, we want to clean it up more
>>      often to avoid running out of disk space when checkpoint_timeout is
>> large
>>      and checkpoint_segments small.
>>
>> Previously users were more likely to fall into this trouble (i.e., too
>> frequent
>> occurrence of restartpoints) because the default value of
>> checkpoint_segments
>> was very small, I guess. But we increased the default of max_wal_size, so
>> now
>> the risk of that trouble seems to be smaller than before, and maybe we can
>> allow max_wal_size to trigger restartpoints.
>
> I see.  The old behavior was present for the same reason we decided to split
> checkpoint_segments into max_wal_size and min_wal_size.
>
> That is, the default checkpoint_segments was small, and it had to be small
> because increasing it would cause more space to be used even when that
> extra space was not helpful.
>
> So perhaps we can consider this change a completion of the max_wal_size
> work, rather than a new feature?

Yeah, I'm inclined to change the behaviour. Ignoring checkpoint_segments
made sense when we initially did that, but it has gradually become less
and less sensible after that, as we got streaming replication, and as we
started to keep all restored segments in pg_xlog even in archive recovery.

> It seems to be a trivial change to implement that, although I might be
> overlooking something subtle (pasted below, also attached)
>
> --- a/src/backend/access/transam/xlog.c
> +++ b/src/backend/access/transam/xlog.c
> @@ -10946,7 +10946,7 @@ XLogPageRead(XLogReaderState *xlogreader,
> XLogRecPtr targetPagePtr, int reqLen,
>                  * Request a restartpoint if we've replayed too much xlog
> since the
>                  * last one.
>                  */
> -               if (StandbyModeRequested && bgwriterLaunched)
> +               if (bgwriterLaunched)
>                 {
>                         if (XLogCheckpointNeeded(readSegNo))
>                         {
>
> This keeps pg_xlog at about 67% of max_wal_size during archive recovery
> (because checkpoint_completion_target is accounted for but goes unused)

Hmm. checkpoint_completion_target is used when determining progress
against checkpoint_timeout just fine, but the problem is that if you do
just the above, IsCheckpointOnSchedule() still won't consider consumed
WAL when it determines whether the restartpoint is "on time". So the
error is in the other direction: if you set max_wal_size to a small
value, and checkpoint_timeout to a large value, the restartpoint would
think that it has plenty of time to complete, and exceed max_wal_size.
We need to fix IsCheckpointOnSchedule() to also track progress against
max_wal_size during recovery.

I came up with the attached patch as a first attempt. It enables the
same logic to calculate if the checkpoint is on schedule to be used in
recovery. But there's a little problem (also explained in a comment in
the patch):

There is a large gap between a checkpoint's redo-pointer, and the
checkpoint record itself (determined by checkpoint_completion_target).
When we're not in recovery, we set the redo-pointer for the current
checkpoint first, then start flushing data, and finally write the
checkpoint record. The logic in IsCheckpointOnSchedule() calculates a)
how much WAL has been generated since the beginning of the checkpoint,
i.e its redo-pointer, and b) what fraction of shared_buffers has been
flushed to disk. But in recovery, we only start the restartpoint after
replaying the checkpoint record, so at the beginning of a restartpoint,
we're actually already behind schedule by the amount of WAL between the
redo-pointer and the record itself.

I'm not sure what to do about this. With the attached patch, you get the
same leisurely pacing with restartpoints as you get with checkpoints,
but you exceed max_wal_size during recovery, by the amount determined by
checkpoint_completion_target. Alternatively, we could try to perform
restartpoints faster then checkpoints, but then you'll get nasty
checkpoint I/O storms in recovery.

A bigger change would be to write a WAL record at the beginning of a
checkpoint. It wouldn't do anything else, but it would be a hint to
recovery that there's going to be a checkpoint record later whose
redo-pointer will point to that record. We could then start the
restartpoint at that record already, before seeing the checkpoint record
itself.

I think the attached is better than nothing, but I'll take a look at
that beginning-of-checkpoint idea. It might be too big a change to do at
this point, but I'd really like to fix this properly for 9.5, since
we've changed with the way checkpoints are scheduled anyway.

- Heikki

On Fri, Jun 26, 2015 at 7:08 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> I'm not sure what to do about this. With the attached patch, you get the
> same leisurely pacing with restartpoints as you get with checkpoints, but
> you exceed max_wal_size during recovery, by the amount determined by
> checkpoint_completion_target. Alternatively, we could try to perform
> restartpoints faster then checkpoints, but then you'll get nasty checkpoint
> I/O storms in recovery.
>
> A bigger change would be to write a WAL record at the beginning of a
> checkpoint. It wouldn't do anything else, but it would be a hint to recovery
> that there's going to be a checkpoint record later whose redo-pointer will
> point to that record. We could then start the restartpoint at that record
> already, before seeing the checkpoint record itself.
>
> I think the attached is better than nothing, but I'll take a look at that
> beginning-of-checkpoint idea. It might be too big a change to do at this
> point, but I'd really like to fix this properly for 9.5, since we've changed
> with the way checkpoints are scheduled anyway.

I agree.  Actually, I've seen a number of presentations indicating
that the pacing of checkpoints is already too aggressive near the
beginning, because as soon as we initiate the checkpoint we have a
storm of full page writes.  I'm sure we can come up with arbitrarily
complicated systems to compensate for this, but something simple might
be to calculate progress done+adjust/total+adjust rather than
done/total.  If you let adjust=total/9, for example, then you
essentially start the progress meter at 10% instead of 0%.  Even
something that simple might be an improvement.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 06/26/2015 03:40 PM, Robert Haas wrote:
> Actually, I've seen a number of presentations indicating
> that the pacing of checkpoints is already too aggressive near the
> beginning, because as soon as we initiate the checkpoint we have a
> storm of full page writes.  I'm sure we can come up with arbitrarily
> complicated systems to compensate for this, but something simple might
> be to calculate progress done+adjust/total+adjust rather than
> done/total.  If you let adjust=total/9, for example, then you
> essentially start the progress meter at 10% instead of 0%.  Even
> something that simple might be an improvement.

Yeah, but that's an unrelated issue. This was most recently discussed at 
http://www.postgresql.org/message-id/CAKHd5Ce-bnD=gEEdtXiT2_AY7shquTKd0yHXXk5F4zVEKRPX-w@mail.gmail.com. 
I posted a simple patch there - review and testing is welcome ;-).

- Heikki

On Fri, Jun 26, 2015 at 9:47 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> On 06/26/2015 03:40 PM, Robert Haas wrote:
>> Actually, I've seen a number of presentations indicating
>> that the pacing of checkpoints is already too aggressive near the
>> beginning, because as soon as we initiate the checkpoint we have a
>> storm of full page writes.  I'm sure we can come up with arbitrarily
>> complicated systems to compensate for this, but something simple might
>> be to calculate progress done+adjust/total+adjust rather than
>> done/total.  If you let adjust=total/9, for example, then you
>> essentially start the progress meter at 10% instead of 0%.  Even
>> something that simple might be an improvement.
>
> Yeah, but that's an unrelated issue. This was most recently discussed at
> http://www.postgresql.org/message-id/CAKHd5Ce-bnD=gEEdtXiT2_AY7shquTKd0yHXXk5F4zVEKRPX-w@mail.gmail.com.
> I posted a simple patch there - review and testing is welcome ;-).

Ah, thanks for the pointer - I had forgotten about that thread.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 06/26/2015 02:08 PM, Heikki Linnakangas wrote:
> I'm not sure what to do about this. With the attached patch, you get the
> same leisurely pacing with restartpoints as you get with checkpoints,
> but you exceed max_wal_size during recovery, by the amount determined by
> checkpoint_completion_target. Alternatively, we could try to perform
> restartpoints faster then checkpoints, but then you'll get nasty
> checkpoint I/O storms in recovery.

Ok, committed this patch. IMHO it's definitely better than the old 
behaviour.

> A bigger change would be to write a WAL record at the beginning of a
> checkpoint. It wouldn't do anything else, but it would be a hint to
> recovery that there's going to be a checkpoint record later whose
> redo-pointer will point to that record. We could then start the
> restartpoint at that record already, before seeing the checkpoint record
> itself.
>
> I think the attached is better than nothing, but I'll take a look at
> that beginning-of-checkpoint idea. It might be too big a change to do at
> this point, but I'd really like to fix this properly for 9.5, since
> we've changed with the way checkpoints are scheduled anyway.

This would've been a much more complicated patch, so I dropped that 
idea, for 9.5 anyway. Maybe later, but it's not urgent.

- Heikki