Horton

Thread

Commits

  1. Increase upper bound for bgwriter_lru_maxpages.

  1. Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2016-11-28T19:40:53Z

    With current limits, the most bgwriter can do (with 8k pages) is 1000 
pages * 100 times/sec = 780MB/s. It's not hard to exceed that with 
modern hardware. Should we increase the limit on bgwriter_lru_maxpages?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  2. Re: Time to up bgwriter_lru_maxpages?

    Devrim GÜNDÜZ <devrim@gunduz.org> — 2016-11-28T19:53:22Z

    Hi,

On Mon, 2016-11-28 at 11:40 -0800, Jim Nasby wrote:
> With current limits, the most bgwriter can do (with 8k pages) is 1000 
> pages * 100 times/sec = 780MB/s. It's not hard to exceed that with 
> modern hardware. Should we increase the limit on bgwriter_lru_maxpages?

+1 for that. I've seen many cases that we need more than 1000.

Regards, 
-- 
Devrim Gündüz
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

  3. Re: Time to up bgwriter_lru_maxpages?

    Joshua D. Drake <jd@commandprompt.com> — 2016-11-28T19:53:41Z

    On 11/28/2016 11:40 AM, Jim Nasby wrote:
> With current limits, the most bgwriter can do (with 8k pages) is 1000
> pages * 100 times/sec = 780MB/s. It's not hard to exceed that with
> modern hardware. Should we increase the limit on bgwriter_lru_maxpages?

Considering a single SSD can do 70% of that limit, I would say yes.

JD



-- 
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

  4. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2016-11-28T21:20:22Z

    On 11/28/16 11:53 AM, Joshua D. Drake wrote:
> On 11/28/2016 11:40 AM, Jim Nasby wrote:
>> With current limits, the most bgwriter can do (with 8k pages) is 1000
>> pages * 100 times/sec = 780MB/s. It's not hard to exceed that with
>> modern hardware. Should we increase the limit on bgwriter_lru_maxpages?
>
> Considering a single SSD can do 70% of that limit, I would say yes.

Next question becomes... should there even be an upper limit?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  5. Re: Time to up bgwriter_lru_maxpages?

    Michael Paquier <michael.paquier@gmail.com> — 2016-11-29T04:22:33Z

    On Tue, Nov 29, 2016 at 6:20 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 11/28/16 11:53 AM, Joshua D. Drake wrote:
>>
>> On 11/28/2016 11:40 AM, Jim Nasby wrote:
>>>
>>> With current limits, the most bgwriter can do (with 8k pages) is 1000
>>> pages * 100 times/sec = 780MB/s. It's not hard to exceed that with
>>> modern hardware. Should we increase the limit on bgwriter_lru_maxpages?
>>
>>
>> Considering a single SSD can do 70% of that limit, I would say yes.
>
>
> Next question becomes... should there even be an upper limit?

Looking at the log history the current default dates of cfeca621, it
would be time to raise the bar a little bit more. Even an utterly high
value could make sense for testing.
-- 
Michael

  6. Re: Time to up bgwriter_lru_maxpages?

    Jeff Janes <jeff.janes@gmail.com> — 2016-11-29T17:58:50Z

    On Mon, Nov 28, 2016 at 1:20 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

> On 11/28/16 11:53 AM, Joshua D. Drake wrote:
>
>> On 11/28/2016 11:40 AM, Jim Nasby wrote:
>>
>>> With current limits, the most bgwriter can do (with 8k pages) is 1000
>>> pages * 100 times/sec = 780MB/s. It's not hard to exceed that with
>>> modern hardware. Should we increase the limit on bgwriter_lru_maxpages?
>>>
>>
>> Considering a single SSD can do 70% of that limit, I would say yes.
>>
>
> Next question becomes... should there even be an upper limit?


Where the contortions needed to prevent calculation overflow become
annoying?

I'm not a big fan of nannyism in general, but the limits on this parameter
seem particularly pointless.  You can't write out more buffers than exist
in the dirty state, nor more than implied by bgwriter_lru_multiplier.  So
what is really the worse that can happen if you make it too high?


Cheers,

Jeff

  7. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-01-31T22:07:12Z

    On 11/29/16 9:58 AM, Jeff Janes wrote:
>
>         Considering a single SSD can do 70% of that limit, I would say yes.
>
>
>     Next question becomes... should there even be an upper limit?
>
>
> Where the contortions needed to prevent calculation overflow become
> annoying?
>
> I'm not a big fan of nannyism in general, but the limits on this
> parameter seem particularly pointless.  You can't write out more buffers
> than exist in the dirty state, nor more than implied
> by bgwriter_lru_multiplier.  So what is really the worse that can happen
> if you make it too high?

Attached is a patch that ups the limit to INT_MAX / 2, which is the same 
as shared_buffers.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  8. Re: Time to up bgwriter_lru_maxpages?

    Robert Haas <robertmhaas@gmail.com> — 2017-02-01T18:27:31Z

    On Tue, Jan 31, 2017 at 5:07 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 11/29/16 9:58 AM, Jeff Janes wrote:
>>         Considering a single SSD can do 70% of that limit, I would say
>> yes.
>>
>>     Next question becomes... should there even be an upper limit?
>>
>>
>> Where the contortions needed to prevent calculation overflow become
>> annoying?
>>
>> I'm not a big fan of nannyism in general, but the limits on this
>> parameter seem particularly pointless.  You can't write out more buffers
>> than exist in the dirty state, nor more than implied
>> by bgwriter_lru_multiplier.  So what is really the worse that can happen
>> if you make it too high?
>
>
> Attached is a patch that ups the limit to INT_MAX / 2, which is the same as
> shared_buffers.

This looks fine to me.

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

  9. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-01T22:01:43Z

    On 2/1/17 10:27 AM, Robert Haas wrote:
> On Tue, Jan 31, 2017 at 5:07 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>> On 11/29/16 9:58 AM, Jeff Janes wrote:
>>>         Considering a single SSD can do 70% of that limit, I would say
>>> yes.
>>>
>>>     Next question becomes... should there even be an upper limit?
>>>
>>>
>>> Where the contortions needed to prevent calculation overflow become
>>> annoying?
>>>
>>> I'm not a big fan of nannyism in general, but the limits on this
>>> parameter seem particularly pointless.  You can't write out more buffers
>>> than exist in the dirty state, nor more than implied
>>> by bgwriter_lru_multiplier.  So what is really the worse that can happen
>>> if you make it too high?
>>
>>
>> Attached is a patch that ups the limit to INT_MAX / 2, which is the same as
>> shared_buffers.
>
> This looks fine to me.

If someone wants to proactively commit this, the CF entry is 
https://commitfest.postgresql.org/13/979/. (BTW, the Jan. CF is still 
showing as in-progress...)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  10. Re: Time to up bgwriter_lru_maxpages?

    Michael Paquier <michael.paquier@gmail.com> — 2017-02-01T23:36:11Z

    On Thu, Feb 2, 2017 at 7:01 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 2/1/17 10:27 AM, Robert Haas wrote:
>> This looks fine to me.

This could go without the comments, they are likely going to be
forgotten if any updates happen in the future.

> If someone wants to proactively commit this, the CF entry is
> https://commitfest.postgresql.org/13/979/.
> (BTW, the Jan. CF is still showing as in-progress...)

WIP.
-- 
Michael

  11. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-02T00:17:46Z

    On 2/1/17 3:36 PM, Michael Paquier wrote:
> On Thu, Feb 2, 2017 at 7:01 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>> On 2/1/17 10:27 AM, Robert Haas wrote:
>>> This looks fine to me.
> This could go without the comments, they are likely going to be
> forgotten if any updates happen in the future.

I'm confused... I put the comments in there so if max shared buffers 
ever changed the other one would hopefully be updated as well.

Speaking of which... I have a meeting in 15 minutes to discuss moving to 
a server with 4TB of memory. With current limits shared buffers maxes at 
16TB, which isn't all that far in the future. While 16TB of shared 
buffers might not be a good idea, it's not going to be terribly long 
before we start getting questions about it.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  12. Re: Time to up bgwriter_lru_maxpages?

    Michael Paquier <michael.paquier@gmail.com> — 2017-02-02T00:22:46Z

    On Thu, Feb 2, 2017 at 9:17 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> Speaking of which... I have a meeting in 15 minutes to discuss moving to a
> server with 4TB of memory. With current limits shared buffers maxes at 16TB,
> which isn't all that far in the future. While 16TB of shared buffers might
> not be a good idea, it's not going to be terribly long before we start
> getting questions about it.

Time for int64 GUCs?
-- 
Michael

  13. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-02T00:27:40Z

    On 2017-02-02 09:22:46 +0900, Michael Paquier wrote:
> On Thu, Feb 2, 2017 at 9:17 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> > Speaking of which... I have a meeting in 15 minutes to discuss moving to a
> > server with 4TB of memory. With current limits shared buffers maxes at 16TB,
> > which isn't all that far in the future. While 16TB of shared buffers might
> > not be a good idea, it's not going to be terribly long before we start
> > getting questions about it.
> 
> Time for int64 GUCs?

I don't think the GUC bit is the hard part.  We'd possibly need some
trickery (like not storing bufferid in BufferDesc anymore) to avoid
increasing memory usage.

- Andres

  14. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-02T00:28:35Z

    On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
> With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
> * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
> hardware. Should we increase the limit on bgwriter_lru_maxpages?

FWIW, I think working on replacing bgwriter (e.g. by working on the
patch I send with a POC replacement) wholesale is a better approach than
spending time increasing limits.

- Andres

  15. Re: Time to up bgwriter_lru_maxpages?

    Robert Haas <robertmhaas@gmail.com> — 2017-02-02T01:30:30Z

    On Wed, Feb 1, 2017 at 7:28 PM, Andres Freund <andres@anarazel.de> wrote:
> On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
>> With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
>> * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
>> hardware. Should we increase the limit on bgwriter_lru_maxpages?
>
> FWIW, I think working on replacing bgwriter (e.g. by working on the
> patch I send with a POC replacement) wholesale is a better approach than
> spending time increasing limits.

I'm happy to see it replaced, but increasing the limits is about three
orders of magnitude less work than replacing it, so let's not block
this on the theory that the other thing would be better.

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

  16. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-02T01:35:37Z

    On 2017-02-01 20:30:30 -0500, Robert Haas wrote:
> On Wed, Feb 1, 2017 at 7:28 PM, Andres Freund <andres@anarazel.de> wrote:
> > On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
> >> With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
> >> * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
> >> hardware. Should we increase the limit on bgwriter_lru_maxpages?
> >
> > FWIW, I think working on replacing bgwriter (e.g. by working on the
> > patch I send with a POC replacement) wholesale is a better approach than
> > spending time increasing limits.
> 
> I'm happy to see it replaced, but increasing the limits is about three
> orders of magnitude less work than replacing it, so let's not block
> this on the theory that the other thing would be better.

I seriously doubt you can meaningfully exceed 780MB/s with the current
bgwriter. So it's not like the limits are all that relevant right now.

  17. Re: Time to up bgwriter_lru_maxpages?

    Robert Haas <robertmhaas@gmail.com> — 2017-02-02T01:38:58Z

    On Wed, Feb 1, 2017 at 8:35 PM, Andres Freund <andres@anarazel.de> wrote:
> On 2017-02-01 20:30:30 -0500, Robert Haas wrote:
>> On Wed, Feb 1, 2017 at 7:28 PM, Andres Freund <andres@anarazel.de> wrote:
>> > On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
>> >> With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
>> >> * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
>> >> hardware. Should we increase the limit on bgwriter_lru_maxpages?
>> >
>> > FWIW, I think working on replacing bgwriter (e.g. by working on the
>> > patch I send with a POC replacement) wholesale is a better approach than
>> > spending time increasing limits.
>>
>> I'm happy to see it replaced, but increasing the limits is about three
>> orders of magnitude less work than replacing it, so let's not block
>> this on the theory that the other thing would be better.
>
> I seriously doubt you can meaningfully exceed 780MB/s with the current
> bgwriter. So it's not like the limits are all that relevant right now.

Sigh.  The patch is harmless and there are 4 or 5 votes in favor of
it, one of which clearly states that the person involved has hit seen
it be a problem in real workloads.  Do we really have to argue about
this?

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

  18. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-02T01:41:11Z

    On 2017-02-01 20:38:58 -0500, Robert Haas wrote:
> On Wed, Feb 1, 2017 at 8:35 PM, Andres Freund <andres@anarazel.de> wrote:
> > On 2017-02-01 20:30:30 -0500, Robert Haas wrote:
> >> On Wed, Feb 1, 2017 at 7:28 PM, Andres Freund <andres@anarazel.de> wrote:
> >> > On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
> >> >> With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
> >> >> * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
> >> >> hardware. Should we increase the limit on bgwriter_lru_maxpages?
> >> >
> >> > FWIW, I think working on replacing bgwriter (e.g. by working on the
> >> > patch I send with a POC replacement) wholesale is a better approach than
> >> > spending time increasing limits.
> >>
> >> I'm happy to see it replaced, but increasing the limits is about three
> >> orders of magnitude less work than replacing it, so let's not block
> >> this on the theory that the other thing would be better.
> >
> > I seriously doubt you can meaningfully exceed 780MB/s with the current
> > bgwriter. So it's not like the limits are all that relevant right now.
> 
> Sigh.  The patch is harmless and there are 4 or 5 votes in favor of
> it, one of which clearly states that the person involved has hit seen
> it be a problem in real workloads.  Do we really have to argue about
> this?

I don't mind increasing the limit, it's harmless. I just seriously doubt
it actually addresses any sort of problem.

  19. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-02T02:47:05Z

    On 2/1/17 4:27 PM, Andres Freund wrote:
> On 2017-02-02 09:22:46 +0900, Michael Paquier wrote:
>> On Thu, Feb 2, 2017 at 9:17 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>>> Speaking of which... I have a meeting in 15 minutes to discuss moving to a
>>> server with 4TB of memory. With current limits shared buffers maxes at 16TB,
>>> which isn't all that far in the future. While 16TB of shared buffers might
>>> not be a good idea, it's not going to be terribly long before we start
>>> getting questions about it.
>>
>> Time for int64 GUCs?
>
> I don't think the GUC bit is the hard part.  We'd possibly need some
> trickery (like not storing bufferid in BufferDesc anymore) to avoid
> increasing memory usage.

Before doing that the first thing to look at would be why the limit is 
currently INT_MAX / 2 instead of INT_MAX.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  20. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-02T19:41:44Z

    On 2/1/17 4:28 PM, Andres Freund wrote:
> On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
>> With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
>> * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
>> hardware. Should we increase the limit on bgwriter_lru_maxpages?
>
> FWIW, I think working on replacing bgwriter (e.g. by working on the
> patch I send with a POC replacement) wholesale is a better approach than
> spending time increasing limits.

Do you have a link to that? I'm not seeing anything in the archives.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  21. Re: Time to up bgwriter_lru_maxpages?

    Robert Haas <robertmhaas@gmail.com> — 2017-02-02T19:47:53Z

    On Wed, Feb 1, 2017 at 9:47 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> Before doing that the first thing to look at would be why the limit is
> currently INT_MAX / 2 instead of INT_MAX.

Generally the rationale for GUCs with limits of that sort is that
there is or might be code someplace that multiplies the value by 2 and
expects the result not to overflow.

I expect that increasing the maximum value of shared_buffers beyond
what can be stored by an integer could have a noticeable distributed
performance cost for the entire system.  It might be a pretty small
cost, but then again maybe not; for example, BufferDesc's buf_id
member would have to get wider, and probably the freeNext member, too.
Andres already did unspeakable things to make a BufferDesc fit into
one cache line for performance reasons, so that wouldn't be great
news.

Anyway, I committed the patch posted here.  Or the important line out
of the two, anyway.  :-)

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

  22. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-02T19:50:35Z

    On 2017-02-02 11:41:44 -0800, Jim Nasby wrote:
> On 2/1/17 4:28 PM, Andres Freund wrote:
> > On 2016-11-28 11:40:53 -0800, Jim Nasby wrote:
> > > With current limits, the most bgwriter can do (with 8k pages) is 1000 pages
> > > * 100 times/sec = 780MB/s. It's not hard to exceed that with modern
> > > hardware. Should we increase the limit on bgwriter_lru_maxpages?
> >
> > FWIW, I think working on replacing bgwriter (e.g. by working on the
> > patch I send with a POC replacement) wholesale is a better approach than
> > spending time increasing limits.
>
> Do you have a link to that? I'm not seeing anything in the archives.

Not at hand, but I can just give you the patches.  These are very likely
to conflict, but it shouldn't be too hard to resolve...

What it basically does is move as much of the clock-sweep to bgwriter,
which keeps a certain number of clean pages available.  There's a
lock-free ringbuffer that backends can just pick pages off.

The approach with the ringbuffer has the advantage that with relatively
small changes we can scale to having multiple bgwriters (needs some
changes in the ringbuf implementation, but not that many).

Andres

  23. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-02T19:52:39Z

    On 2017-02-02 14:47:53 -0500, Robert Haas wrote:
> I expect that increasing the maximum value of shared_buffers beyond
> what can be stored by an integer could have a noticeable distributed
> performance cost for the entire system.  It might be a pretty small
> cost, but then again maybe not; for example, BufferDesc's buf_id
> member would have to get wider, and probably the freeNext member, too.
> Andres already did unspeakable things to make a BufferDesc fit into
> one cache line for performance reasons, so that wouldn't be great
> news.

Yea, we'd have to get rid of BufferDesc's buf_id - but that's not that
hard, just a bit verbose. You can get the buf_id already with a tiny bit
of pointer math.  I don't think there should be too many other changes,
but I might be wrong.

Andres

  24. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-04T00:12:48Z

    On 2/2/17 1:50 PM, Andres Freund wrote:
>>> FWIW, I think working on replacing bgwriter (e.g. by working on the
>>> patch I send with a POC replacement) wholesale is a better approach than
>>> spending time increasing limits.
>> Do you have a link to that? I'm not seeing anything in the archives.
> Not at hand, but I can just give you the patches.  These are very likely
> to conflict, but it shouldn't be too hard to resolve...
>
> What it basically does is move as much of the clock-sweep to bgwriter,
> which keeps a certain number of clean pages available.  There's a
> lock-free ringbuffer that backends can just pick pages off.
>
> The approach with the ringbuffer has the advantage that with relatively
> small changes we can scale to having multiple bgwriters (needs some
> changes in the ringbuf implementation, but not that many).

Interesting. Probably kills a couple birds with one stone:

- This should be a lot cheaper for backends then the clock sweep
- Current bgwriter can easily get stuck a full scan ahead of the clock 
if shared_buffers is very large, due to forcibly scanning all of shared 
buffers every 2 minutes.
- The ringbuffers in shared buffers can be problematic. One possible way 
of solving that is to get rid of ringbuffers entirely and rely on 
different initial values for usage_count instead, but that's not 
desirable if it just means more clock sweep work for backends.

FWIW, I started looking into this stuff because of a customer system 
where shared buffers is currently ~4x larger than the cluster, yet 
there's a non-trivial amount of buffers being written by backends.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  25. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-04T00:20:30Z

    On 2017-02-03 18:12:48 -0600, Jim Nasby wrote:
> Interesting. Probably kills a couple birds with one stone:
> 
> - This should be a lot cheaper for backends then the clock sweep

Right, that's one of the motivations - the current method is pretty much
guaranteed to create the worst cacheline bouncing possible.


> - The ringbuffers in shared buffers can be problematic. One possible way of
> solving that is to get rid of ringbuffers entirely and rely on different
> initial values for usage_count instead, but that's not desirable if it just
> means more clock sweep work for backends.

I'm not quite sure which ringbuffer you're referring to here? If to the
new one, why is it problematic?


> FWIW, I started looking into this stuff because of a customer system where
> shared buffers is currently ~4x larger than the cluster, yet there's a
> non-trivial amount of buffers being written by backends.

That's probably not related to bgwriter then. If there's free pages
available (which there have to be if s_b is bigger than the cluster)
there'll be no clock sweep / victim buffers.  I strongly suspect that
you're seeing backend writes due to the write ringbuffers, e.g. by
seqscans, copy, vacuum - those won't be affected in either case.

I'd suggest installing pg_stat_statements and enabling track_io_timings
- that'll tell you which statements triggered backend writes and how
long they took.

Greetings,

Andres Freund

  26. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-04T01:26:55Z

    On 2/3/17 6:20 PM, Andres Freund wrote:
>> - The ringbuffers in shared buffers can be problematic. One possible way of
>> solving that is to get rid of ringbuffers entirely and rely on different
>> initial values for usage_count instead, but that's not desirable if it just
>> means more clock sweep work for backends.
> I'm not quite sure which ringbuffer you're referring to here? If to the
> new one, why is it problematic?

No, I mean the non-default BufferAccessStrategy's.

>> FWIW, I started looking into this stuff because of a customer system where
>> shared buffers is currently ~4x larger than the cluster, yet there's a
>> non-trivial amount of buffers being written by backends.
> That's probably not related to bgwriter then. If there's free pages
> available (which there have to be if s_b is bigger than the cluster)
> there'll be no clock sweep / victim buffers.  I strongly suspect that
> you're seeing backend writes due to the write ringbuffers, e.g. by

Right.

> seqscans, copy, vacuum - those won't be affected in either case.

Yeah, my idea there is that if there was a separate process responsible 
for the clock sweep then perhaps instead of ring buffers we could simply 
use different initial values for usage_count to control how likely 
things were to get evicted. Trying to do that with all the backends 
needing to run the clock would presumably be a really bad idea though.

> I'd suggest installing pg_stat_statements and enabling track_io_timings
> - that'll tell you which statements triggered backend writes and how
> long they took.

Good idea.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  27. Re: Time to up bgwriter_lru_maxpages?

    Andres Freund <andres@anarazel.de> — 2017-02-04T01:34:46Z

    On 2017-02-03 19:26:55 -0600, Jim Nasby wrote:
> On 2/3/17 6:20 PM, Andres Freund wrote:
> > > - The ringbuffers in shared buffers can be problematic. One possible way of
> > > solving that is to get rid of ringbuffers entirely and rely on different
> > > initial values for usage_count instead, but that's not desirable if it just
> > > means more clock sweep work for backends.
> > I'm not quite sure which ringbuffer you're referring to here? If to the
> > new one, why is it problematic?
> 
> No, I mean the non-default BufferAccessStrategy's.

That's not a ringbuffer that's a buffer ring ;)

  28. Re: Time to up bgwriter_lru_maxpages?

    Jim Nasby <jim.nasby@bluetreble.com> — 2017-02-13T03:21:16Z

    On 2/3/17 7:34 PM, Andres Freund wrote:
> On 2017-02-03 19:26:55 -0600, Jim Nasby wrote:
>> On 2/3/17 6:20 PM, Andres Freund wrote:
>>>> - The ringbuffers in shared buffers can be problematic. One possible way of
>>>> solving that is to get rid of ringbuffers entirely and rely on different
>>>> initial values for usage_count instead, but that's not desirable if it just
>>>> means more clock sweep work for backends.
>>> I'm not quite sure which ringbuffer you're referring to here? If to the
>>> new one, why is it problematic?
>>
>> No, I mean the non-default BufferAccessStrategy's.
>
> That's not a ringbuffer that's a buffer ring ;)

In my defense, there's at least one mention of 'ring buffer' in 
storage/buffers. I thought there were more.

Yet more reason to just get rid of them ;P
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

  29. Re: Time to up bgwriter_lru_maxpages?

    David Steele <david@pgmasters.net> — 2017-03-16T16:39:20Z

    On 2/2/17 2:47 PM, Robert Haas wrote:
> On Wed, Feb 1, 2017 at 9:47 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>> Before doing that the first thing to look at would be why the limit is
>> currently INT_MAX / 2 instead of INT_MAX.
> 
> Generally the rationale for GUCs with limits of that sort is that
> there is or might be code someplace that multiplies the value by 2 and
> expects the result not to overflow.
> 
> I expect that increasing the maximum value of shared_buffers beyond
> what can be stored by an integer could have a noticeable distributed
> performance cost for the entire system.  It might be a pretty small
> cost, but then again maybe not; for example, BufferDesc's buf_id
> member would have to get wider, and probably the freeNext member, too.
> Andres already did unspeakable things to make a BufferDesc fit into
> one cache line for performance reasons, so that wouldn't be great
> news.
> 
> Anyway, I committed the patch posted here.  Or the important line out
> of the two, anyway.  :-)

It seems that this submission should be marked as "Committed" with
Robert as the committer.  Am I missing something?

-- 
-David
david@pgmasters.net

  30. Re: Time to up bgwriter_lru_maxpages?

    Robert Haas <robertmhaas@gmail.com> — 2017-03-16T16:41:25Z

    On Thu, Mar 16, 2017 at 12:39 PM, David Steele <david@pgmasters.net> wrote:
>> Anyway, I committed the patch posted here.  Or the important line out
>> of the two, anyway.  :-)
>
> It seems that this submission should be marked as "Committed" with
> Robert as the committer.  Am I missing something?

I think you are right.  Sorry that I missed that step.

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

  31. Re: Time to up bgwriter_lru_maxpages?

    David Steele <david@pgmasters.net> — 2017-03-16T16:43:41Z

    On 3/16/17 12:41 PM, Robert Haas wrote:
> On Thu, Mar 16, 2017 at 12:39 PM, David Steele <david@pgmasters.net> wrote:
>>> Anyway, I committed the patch posted here.  Or the important line out
>>> of the two, anyway.  :-)
>>
>> It seems that this submission should be marked as "Committed" with
>> Robert as the committer.  Am I missing something?
> 
> I think you are right.  Sorry that I missed that step.

Done.

-- 
-David
david@pgmasters.net