subject:"\[HACKERS\] Redesigning checkpoint

On Thu, Feb 5, 2015 at 2:11 PM, Josh Berkus j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-05 Thread Heikki Linnakangas


On 02/05/2015 11:28 PM, Robert Haas wrote:

On Thu, Feb 5, 2015 at 2:11 PM, Josh Berkus j...@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



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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-05 Thread Josh Berkus

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 j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-05 Thread Andres Freund

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-05 Thread Heikki Linnakangas


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



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

Re: [HACKERS] Redesigning checkpoint_segments

On Wed, Feb 4, 2015 at 4:41 PM, Josh Berkus j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

 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


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

Re: [HACKERS] Redesigning checkpoint_segments

On Thu, Feb 5, 2015 at 4:42 PM, Heikki Linnakangas
hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-05 Thread David Steele

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
da...@pgmasters.net




signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-05 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Robert Haas

On Tue, Feb 3, 2015 at 4:18 PM, Josh Berkus j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Robert Haas

On Wed, Feb 4, 2015 at 1:05 PM, Josh Berkus j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Josh Berkus

On 02/04/2015 09:28 AM, Robert Haas wrote:
 On Tue, Feb 3, 2015 at 4:18 PM, Josh Berkus j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Josh Berkus

On 02/04/2015 12:06 PM, Robert Haas wrote:
 On Wed, Feb 4, 2015 at 1:05 PM, Josh Berkus j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Amit Kapila

On Thu, Feb 5, 2015 at 3:11 AM, Josh Berkus j...@agliodbs.com wrote:

 On 02/04/2015 12:06 PM, Robert Haas wrote:
  On Wed, Feb 4, 2015 at 1:05 PM, Josh Berkus j...@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.

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?


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Venkata Balaji N

Missed adding pgsql-hackers group while replying.

Regards,
Venkata Balaji N

On Thu, Feb 5, 2015 at 12:48 PM, Venkata Balaji N nag1...@gmail.com wrote:



 On Fri, Jan 30, 2015 at 7:58 PM, Heikki Linnakangas 
 hlinnakan...@vmware.com wrote:

 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.


 Yes, the pg_xlog directory size peaked to 160MB at times and most of the
 time stayed at 128MB. I did make an observation in an other round of latest
 test, my observations are below.

  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?


 Thanks for the explanation. I see the below note from the patch, i think
 it should also say that minimum wal size on the disk will be
 min_recycle_wal_size during the light load and idle situations.

 I think the name of the parameter name min_recycle_wal_size implies
 something slightly different. It does not give an impression that it is the
 minimum wal size on the disk during light loads. I agree with Josh Berkus
 that the parameter (min_recycle_wal_size) name must be something like
 min_wal_size which makes more sense.

 +   varname*wal_recycle_min_size*/ puts a minimum on the amount of
 WAL files
 +   recycled for future usage; that much WAL is always recycled for future
 use,
 +   even if the system is idle and the WAL usage estimate suggests that
 little
 +   WAL is needed.
 +  /para

 Note : in wal.sgml, the parameter name is mentioned as
 wal_recycle_min_size. That must be changed to min_recycle_wal_size.


 Another round of test : I raised checkpoint_wal_size to 10512 MB which is
 about 10GB and kept min_recycle_wal_size at 128 MB (with checkpoint_timeout
 = 5min). The checkpoints timed out and started recycling about 2 GB
 segments regularly, below are the checkpoint logs -

 I started loading the data of size more than 100GB.

 TimeStamp=2015-02-05 10:22:40.323 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint complete: wrote 83998 buffers (64.1%); 0 transaction log file(s)
 added, 0 removed, 135 recycled; write=95.687 s, sync=25.845 s,
 total=121.866 s; sync files=18, longest=10.306 s, average=1.435 s;
 distance=2271524 KB, estimate=2300497 KB
 TimeStamp=2015-02-05 10:25:38.875 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint starting: time
 TimeStamp=2015-02-05 10:27:50.955 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint complete: wrote 83216 buffers (63.5%); 0 transaction log file(s)
 added, 0 removed, 146 recycled; write=96.951 s, sync=34.814 s,
 total=132.079 s; sync files=18, longest=9.535 s, average=1.934 s;
 distance=2229416 KB, estimate=2293388 KB
 TimeStamp=2015-02-05 10:30:38.786 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint starting: time
 TimeStamp=2015-02-05 10:32:20.332 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint complete: wrote 82409 buffers (62.9%); 0 transaction log file(s)
 added, 0 removed, 131 recycled; write=94.712 s, sync=6.516 s, total=101.545
 s; sync files=18, longest=2.645 s, average=0.362 s; distance=2131805 KB,
 estimate=2277230 KB
 TimeStamp=2015-02-05 10:35:38.788 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint starting: time
 TimeStamp=2015-02-05 10:37:35.883 GMT-10 DB= SID=54d2af22.65b4 User= LOG:
 checkpoint complete: wrote 87821 buffers (67.0%); 0 transaction log file(s)
 added, 0 removed, 134 recycled; write=99.461

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread David Steele

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
da...@pgmasters.net




signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-04 Thread Jim Nasby


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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Heikki Linnakangas


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



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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Kevin Grittner

Heikki Linnakangas hlinnakan...@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/4A44E58C022500027FCF@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.3040...@2ndquadrant.com#4bccdad5.3040...@2ndquadrant.com

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Robert Haas

On Tue, Feb 3, 2015 at 7:31 AM, Heikki Linnakangas
hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Robert Haas

On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Petr Jelinek


On 03/02/15 16:50, Robert Haas wrote:

On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Heikki Linnakangas


On 02/03/2015 05:19 PM, Robert Haas wrote:

On Tue, Feb 3, 2015 at 7:31 AM, Heikki Linnakangas
hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Heikki Linnakangas


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



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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-03 Thread Josh Berkus

On 02/03/2015 07:50 AM, Robert Haas wrote:
 On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
 hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-02 Thread Robert Haas

On Fri, Jan 30, 2015 at 3:58 AM, Heikki Linnakangas
hlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-02-02 Thread Andres Freund

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-30 Thread Heikki Linnakangas


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



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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-12 Thread Amit Kapila

On Fri, Jan 2, 2015 at 3:27 PM, Heikki Linnakangas hlinnakan...@vmware.com
wrote:

 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.


Doesn't this indicate that we should have some co-relation
between checkpoint_wal_size and wal_keep_segments?



 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.


I am thinking that it might make sense to have checkpoint_wal_size
equal to size of wal_keep_segments incase wal_keep_segments is
greater than checkpoint_wal_size size.  It will not make any difference
in retaining wal segments, but I think it can make checkpoint trigger
at more appropriate intervals.  Won't this help in addressing the above
situation explained by you to an extent as it will make a new checkpoint
to start little later such that it will help in removing segments between
90-95 one cycle earlier.



 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.


Are you imagining some other background process to do this
activity?  Does it make sense if we try to do the same in
foreground (I understand that it can impact performance of that
session, but such a thing can maintain the WAL size better)?


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-05 Thread Heikki Linnakangas


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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-05 Thread Andres Freund

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-05 Thread Heikki Linnakangas


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



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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-05 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-04 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-03 Thread Heikki Linnakangas


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


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

Re: [HACKERS] Redesigning checkpoint_segments

2015-01-02 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2014-12-31 Thread Heikki Linnakangas


(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

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6bcb106..34f9466 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1325,7 +1325,7 @@ include_dir 'conf.d'
 40% of RAM to varnameshared_buffers/varname will work better than a
 smaller amount.  Larger settings for varnameshared_buffers/varname
 usually require a corresponding increase in
-varnamecheckpoint_segments/varname, in order to spread out the
+varnamecheckpoint_wal_size/varname, in order to spread out the
 process of writing large quantities of new or changed data over a
 longer period of time.
/para
@@ -2394,18 +2394,21 @@ include_dir 'conf.d'
  titleCheckpoints/title
 
 variablelist
- varlistentry id=guc-checkpoint-segments xreflabel=checkpoint_segments
-  termvarnamecheckpoint_segments/varname (typeinteger/type)
+ varlistentry id=guc-checkpoint-wal-size xreflabel=checkpoint_wal_size
+  termvarnamecheckpoint_wal_size/varname (typeinteger/type)/term
   indexterm
-   primaryvarnamecheckpoint_segments/ configuration parameter/primary
+   primaryvarnamecheckpoint_wal_size/ configuration parameter/primary
   /indexterm
   /term
   listitem
para
-Maximum number of log file segments between automatic WAL
-checkpoints (each segment is normally 16 megabytes). The default
-is three segments.  Increasing this parameter can increase the
-amount of time needed for crash recovery.
+Maximum size to let the WAL grow to between automatic WAL
+checkpoints. This is a soft limit; WAL size can exceed
+varnamecheckpoint_wal_size/ under special circumstances, like
+under heavy load, a failing varnamearchive_command/, or a high
+varnamewal_keep_segments/ setting. The default is 128 MB.
+Increasing this parameter can increase the amount of time needed for
+crash recovery.
 This parameter can only be set in the filenamepostgresql.conf/
 file or on the server command line.
/para
@@ -2458,7 +2461,7 @@ include_dir 'conf.d'
 Write a message to the server log if checkpoints caused by
 the filling of checkpoint segment files happen closer together
 than this many seconds (which suggests that
-varnamecheckpoint_segments/ ought to be raised).  The default is
+varnamecheckpoint_wal_size/ ought to be raised).  The default is
 30 seconds (literal30s/).  Zero disables the warning.
 No

Re: [HACKERS] Redesigning checkpoint_segments

2014-12-31 Thread Heikki Linnakangas


On 09/01/2013 10:37 AM, Amit Kapila wrote:

On Sat, Aug 24, 2013 at 2:38 AM, Heikki Linnakangas
hlinnakan...@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



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

Re: [HACKERS] Redesigning checkpoint_segments

2014-12-31 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2014-01-31 Thread Bruce Momjian

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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-09-01 Thread Amit Kapila

On Sat, Aug 24, 2013 at 2:38 AM, Heikki Linnakangas
hlinnakan...@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

Re: [HACKERS] Redesigning checkpoint_segments

2013-08-23 Thread Heikki Linnakangas


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-08-23 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-07-08 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-07-03 Thread Peter Eisentraut

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.



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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-08 Thread Greg Smith


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 USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-08 Thread Craig Ringer

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



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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-08 Thread Craig Ringer

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



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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-07 Thread Robert Haas

On Thu, Jun 6, 2013 at 10:43 PM, Greg Smith g...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-07 Thread Kevin Grittner

Robert Haas robertmh...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-07 Thread Robert Haas

On Fri, Jun 7, 2013 at 3:14 PM, Kevin Grittner kgri...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-07 Thread Kevin Grittner

Robert Haas robertmh...@gmail.com wrote:
 Kevin Grittner kgri...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Daniel Farina

On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@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.


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

Re: [HACKERS] Redesigning checkpoint_segments



On 6/5/2013 10:54 PM, Peter Geoghegan wrote:

On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Daniel Farina

On Wed, Jun 5, 2013 at 11:05 PM, Joshua D. Drake j...@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 j...@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.


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Harold Giménez

Hi,

On Wed, Jun 5, 2013 at 11:05 PM, Joshua D. Drake j...@commandprompt.comwrote:


 On 6/5/2013 10:54 PM, Peter Geoghegan wrote:

 On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@commandprompt.com
 wrote:


 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.


 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 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.

Regards,

-Harold

Re: [HACKERS] Redesigning checkpoint_segments



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



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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Peter Geoghegan

On Wed, Jun 5, 2013 at 11:28 PM, Joshua D. Drake j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments


On 6/5/2013 11:25 PM, Harold Giménez wrote:


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.


Except that you likely can't increase pg_xlog space (easily). The point 
here is to have overflow, think swap 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.




I would think read only is less unfriendly than an all out failure. 
Consider if done correctly, the database would move back into read-write 
mode once the problem was resolved.


JD

Re: [HACKERS] Redesigning checkpoint_segments



On 6/5/2013 11:31 PM, Peter Geoghegan wrote:

On Wed, Jun 5, 2013 at 11:28 PM, Joshua D. Drake j...@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


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

Re: [HACKERS] Redesigning checkpoint_segments


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

Re: [HACKERS] Redesigning checkpoint_segments


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


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

Re: [HACKERS] Redesigning checkpoint_segments



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

Re: [HACKERS] Redesigning checkpoint_segments


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


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

Re: [HACKERS] Redesigning checkpoint_segments


On 05.06.2013 22:18, Kevin Grittner wrote:

Heikki Linnakangashlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments


On 05.06.2013 22:24, Fujii Masao wrote:

On Thu, Jun 6, 2013 at 3:35 AM, Heikki Linnakangas
hlinnakan...@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.linnakan...@iki.fi
Date:   Wed Jun 9 15:04:07 2010 +

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Kevin Grittner

Heikki Linnakangas hlinnakan...@vmware.com wrote:
 On 05.06.2013 22:18, Kevin Grittner wrote:
 Heikki Linnakangashlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments


On 06.06.2013 15:31, Kevin Grittner wrote:

Heikki Linnakangashlinnakan...@vmware.com  wrote:

On 05.06.2013 22:18, Kevin Grittner wrote:

Heikki Linnakangashlinnakan...@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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Josh Berkus

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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Josh Berkus


 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


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

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Jeff Janes

On Wed, Jun 5, 2013 at 8:20 PM, Joshua D. Drake j...@commandprompt.comwrote:

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.comhttp://www.postgresql.org/message-id/cam3swzqcynxvpaskr-pxm8deqh7_qevw7uqbhpcsg1fpsxk...@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.

If speed of archiving is the fundamental bottleneck on the system, how does
that bottleneck get communicated forward to the user? PANICs are a
horrible way of doing it, throttling the writing of WAL (and hence the
acceptance of COMMITs) seems like a reasonable alternative . Maybe speed
of archiving is not the fundamental bottleneck on your systems, but...

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.

They do different things. One writes buffers out to make room for incoming
ones. One writes them out (and fsyncs the underlying files) to allow redo
pointer to advance (limiting soft recovery time) and xlogs to be recycled
(limiting disk space).

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.

Yeah, I think the limit of 1 hr is rather nanny-ish. I know what I'm
doing, and I want the freedom to go longer if that is what I want to do.

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 find bgwriter to be almost worthless, at least since the fsync queue
compaction code went in. When io is free-flowing the kernel accepts writes
almost instantaneously, and so the backends can write out dirty buffers
themselves very quickly and it is not worth off-loading to a background
process. When IO is constipated, it would be worth off-loading except in
those circumstances the bgwriter cannot possibly keep up.

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.

Which is what archiving does. But then you have a to put a lot of thought
into how to clean up the archive, assuming your policy is not to keep it
forever. keep_segments can be a nice compromise.

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?

Without checkpoints, how would the redo pointer ever advance?

If the system is io limited during recovery, then checkpoint_segments is a
fairly natural way to put a limit on how long recovery from a soft crash
will take. If the system is CPU limited during recovery, then
checkpoint_timeout is a fairly natural way to put a limit on how long
recovery will take. It is probably possible to come with a single merged
setting that is better than both of those in almost all circumstances, but
how much work would that take

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Jeff Janes

On Thu, Jun 6, 2013 at 1:42 AM, Joshua D. Drake j...@commandprompt.comwrote:



 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.


It primarily writes out based on how many buffers have recently needed to
be evicted in order to make room to read in new ones.  There are secondary
clamp limits based on an interval (it does enough work to circle the buffer
pool once every 2 minutes) and another on max pages written but the main
one is based on recent usage.  I've never really understood the point of
those secondary clamps.


 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?



Presumably someone cares about disk space of PGDATA, but it is probably a
different person, at a different time, on a different time scale.  PGDATA
is a long term planning issue, pg_xlog is an operational issue.  If the
checkpoint had completed 30 seconds earlier or the archive_command had
completed 30 seconds earlier (or the commit rate had been throttled for 30
seconds), then pg_xlog would not have run out of space in the first place.
 Having averted the crisis, maybe it will never arise again, or maybe it
will but we will be able to avoid it again.  If we delay running out of
room on PGDATA for 30 seconds, well, we still ran out of room.

Cheers,

Jeff

Re: [HACKERS] Redesigning checkpoint_segments