Re: buffercache/bgwriter

Hi,

I have very bad bgwriter statistics on a server which runs since many weeks
and it is still the same after a recent restart.
There are roughly 50% of buffers written by the backend processes and the
rest by checkpoints.
The statistics below are from a server with 140GB RAM, 32GB shared_buffers
and a runtime of one hour.

As you can see in the pg_buffercache view that there are most buffers
without usagecount - so they are as free or even virgen as they can be.
At the same time I have 53% percent of the dirty buffers written by the
backend process.

I want to tune the database to achieve a ratio of max 10% backend writer vs.
90% checkpoint or bgwriter writes.
But I don't understand how postgres is unable to fetch a free buffer.
Does any body have an idea?

I'm running postgres 8.4.4 64 Bit on linux.

Best Regards,
Uwe

background writer stats
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
3 | 0 | 99754 | 0
| 0 | 115307 | 246173
(1 row)

background writer relative stats
checkpoints_timed | minutes_between_checkpoint | buffers_checkpoint |
buffers_clean | buffers_backend | total_writes | avg_checkpoint_write
-------------------+----------------------------+--------------------+---------------+-----------------+--------------+----------------------
100% | 10 | 46% |
0% | 53% | 0.933 MB/s | 259.000 MB
(1 row)

postgres=# select usagecount,count(*),isdirty from pg_buffercache group by
isdirty,usagecount order by isdirty,usagecount;
usagecount | count | isdirty
------------+---------+---------
1 | 31035 | f
2 | 13109 | f
3 | 184290 | f
4 | 6581 | f
5 | 912068 | f
1 | 6 | t
2 | 35 | t
3 | 48 | t
4 | 53 | t
5 | 43066 | t
| 3004013 |
(11 rows)

Wednesday, March 23, 2011, 1:51:31 PM you wrote:

[rearranged for quoting]

> background writer stats
> checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
> maxwritten_clean | buffers_backend | buffers_alloc
> -------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
> 3 | 0 | 99754 | 0
> | 0 | 115307 | 246173
> (1 row)

buffers_clean = 0 ?!

> But I don't understand how postgres is unable to fetch a free buffer.
> Does any body have an idea?

Somehow looks like the bgwriter is completely disabled. How are the
relevant settings in your postgresql.conf?

--
Jochen Erwied | home: jochen(at)erwied(dot)eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)de +49-173-5404164

Hi Jochen,

yes, I had that impression too.
But it is running. ...And has almost no effect. I changed all parameter to
the most aggressive, but....
Before I restarted the server I had a percentage of writes by the bgwriter
of less that 1 percent.

postgres=# select name,setting from pg_settings where name like 'bgw%';
name | setting
-------------------------+---------
bgwriter_delay | 10
bgwriter_lru_maxpages | 1000
bgwriter_lru_multiplier | 10

Best...
Uwe

Uwe Bartels
Systemarchitect - Freelancer
mailto: uwe(dot)bartels(at)gmail(dot)com
tel: +49 172 3899006
profile: https://www.xing.com/profile/Uwe_Bartels
website: http://www.uwebartels.com

On 23 March 2011 14:19, Jochen Erwied <jochen(at)pgsql-performance(dot)erwied(dot)eu>wrote:

> Wednesday, March 23, 2011, 1:51:31 PM you wrote:
>
> [rearranged for quoting]
>
> > background writer stats
> > checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean
> |
> > maxwritten_clean | buffers_backend | buffers_alloc
> >
> -------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
> > 3 | 0 | 99754 | 0
> > | 0 | 115307 | 246173
> > (1 row)
>
> buffers_clean = 0 ?!
>
> > But I don't understand how postgres is unable to fetch a free buffer.
> > Does any body have an idea?
>
> Somehow looks like the bgwriter is completely disabled. How are the
> relevant settings in your postgresql.conf?
>
>
> --
> Jochen Erwied | home: jochen(at)erwied(dot)eu +49-208-38800-18, FAX:
> -19
> Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX:
> -50
> D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)de
> +49-173-5404164
>
>

> Hi,
>
> I have very bad bgwriter statistics on a server which runs since many
> weeks
> and it is still the same after a recent restart.
> There are roughly 50% of buffers written by the backend processes and the
> rest by checkpoints.
> The statistics below are from a server with 140GB RAM, 32GB shared_buffers
> and a runtime of one hour.
>
> As you can see in the pg_buffercache view that there are most buffers
> without usagecount - so they are as free or even virgen as they can be.
> At the same time I have 53% percent of the dirty buffers written by the
> backend process.

There are some nice old threads dealing with this - see for example

http://postgresql.1045698.n5.nabble.com/Bgwriter-and-pg-stat-bgwriter-buffers-clean-aspects-td2071472.html

http://postgresql.1045698.n5.nabble.com/tuning-bgwriter-in-8-4-2-td1926854.html

and there even some nice external links to more detailed explanation

http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

regards
Tomas

Hi Thomas,

thanks, but there were no new informations in there for me.
this article
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm I know and
others on his website.

Best...
Uwe

On 23 March 2011 15:41, <tv(at)fuzzy(dot)cz> wrote:

> > Hi,
> >
> > I have very bad bgwriter statistics on a server which runs since many
> > weeks
> > and it is still the same after a recent restart.
> > There are roughly 50% of buffers written by the backend processes and the
> > rest by checkpoints.
> > The statistics below are from a server with 140GB RAM, 32GB
> shared_buffers
> > and a runtime of one hour.
> >
> > As you can see in the pg_buffercache view that there are most buffers
> > without usagecount - so they are as free or even virgen as they can be.
> > At the same time I have 53% percent of the dirty buffers written by the
> > backend process.
>
> There are some nice old threads dealing with this - see for example
>
>
> http://postgresql.1045698.n5.nabble.com/Bgwriter-and-pg-stat-bgwriter-buffers-clean-aspects-td2071472.html
>
>
> http://postgresql.1045698.n5.nabble.com/tuning-bgwriter-in-8-4-2-td1926854.html
>
> and there even some nice external links to more detailed explanation
>
> http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
>
> regards
> Tomas
>
>

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> owner(at)postgresql(dot)org] On Behalf Of tv(at)fuzzy(dot)cz
> Sent: Wednesday, March 23, 2011 10:42 AM
> To: Uwe Bartels
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] buffercache/bgwriter
>
> > Hi,
> >
> > I have very bad bgwriter statistics on a server which runs since many
> > weeks
> > and it is still the same after a recent restart.
> > There are roughly 50% of buffers written by the backend processes and
> the
> > rest by checkpoints.
> > The statistics below are from a server with 140GB RAM, 32GB
> shared_buffers
> > and a runtime of one hour.
> >
> > As you can see in the pg_buffercache view that there are most buffers
> > without usagecount - so they are as free or even virgen as they can
> be.
> > At the same time I have 53% percent of the dirty buffers written by
> the
> > backend process.
>
> There are some nice old threads dealing with this - see for example
>
> http://postgresql.1045698.n5.nabble.com/Bgwriter-and-pg-stat-bgwriter-
> buffers-clean-aspects-td2071472.html
>
> http://postgresql.1045698.n5.nabble.com/tuning-bgwriter-in-8-4-2-
> td1926854.html
>
> and there even some nice external links to more detailed explanation
>
> http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

The interesting question here is - with 3 million unallocated buffers, why is the DB evicting buffers (buffers_backend column) instead of allocating the unallocated buffers?

Brad.

Hi Brad,

yes. that's the question....
in the source code in freelist.c there is something that I don't understand.

This is the first try to get a free page. The second try scans used buffers.
What makes me wonder is the why postgres is checking for <<buf->usage_count
== 0>>
where usage_count is supposed to be NULL initially.

while (StrategyControl->firstFreeBuffer >= 0)
{
buf = &BufferDescriptors[StrategyControl->firstFreeBuffer];
Assert(buf->freeNext != FREENEXT_NOT_IN_LIST);

/* Unconditionally remove buffer from freelist */
StrategyControl->firstFreeBuffer = buf->freeNext;
buf->freeNext = FREENEXT_NOT_IN_LIST;

/*
* If the buffer is pinned or has a nonzero usage_count, we cannot
use
* it; discard it and retry. (This can only happen if VACUUM put a
* valid buffer in the freelist and then someone else used it before
* we got to it. It's probably impossible altogether as of 8.3, but
* we'd better check anyway.)
*/
LockBufHdr(buf);
if (buf->refcount == 0 && buf->usage_count == 0)
{
if (strategy != NULL)
AddBufferToRing(strategy, buf);
return buf;
}
UnlockBufHdr(buf);
}

Best...
Uwe

On 23 March 2011 15:58, Nicholson, Brad (Toronto, ON, CA) <bnicholson(at)hp(dot)com
> wrote:

>
>
> > -----Original Message-----
> > From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> > owner(at)postgresql(dot)org] On Behalf Of tv(at)fuzzy(dot)cz
> > Sent: Wednesday, March 23, 2011 10:42 AM
> > To: Uwe Bartels
> > Cc: pgsql-performance(at)postgresql(dot)org
> > Subject: Re: [PERFORM] buffercache/bgwriter
> >
> > > Hi,
> > >
> > > I have very bad bgwriter statistics on a server which runs since many
> > > weeks
> > > and it is still the same after a recent restart.
> > > There are roughly 50% of buffers written by the backend processes and
> > the
> > > rest by checkpoints.
> > > The statistics below are from a server with 140GB RAM, 32GB
> > shared_buffers
> > > and a runtime of one hour.
> > >
> > > As you can see in the pg_buffercache view that there are most buffers
> > > without usagecount - so they are as free or even virgen as they can
> > be.
> > > At the same time I have 53% percent of the dirty buffers written by
> > the
> > > backend process.
> >
> > There are some nice old threads dealing with this - see for example
> >
> > http://postgresql.1045698.n5.nabble.com/Bgwriter-and-pg-stat-bgwriter-
> > buffers-clean-aspects-td2071472.html
> >
> > http://postgresql.1045698.n5.nabble.com/tuning-bgwriter-in-8-4-2-
> > td1926854.html
> >
> > and there even some nice external links to more detailed explanation
> >
> > http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
>
> The interesting question here is - with 3 million unallocated buffers, why
> is the DB evicting buffers (buffers_backend column) instead of allocating
> the unallocated buffers?
>
> Brad.
>

On Wed, Mar 23, 2011 at 6:19 AM, Jochen Erwied
<jochen(at)pgsql-performance(dot)erwied(dot)eu> wrote:
> Wednesday, March 23, 2011, 1:51:31 PM you wrote:
>
> [rearranged for quoting]
>
>> background writer stats
>>  checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
>> maxwritten_clean | buffers_backend | buffers_alloc
>> -------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
>>                  3 |               0 |              99754 |             0
>> |                0 |          115307 |        246173
>> (1 row)
>
> buffers_clean = 0 ?!
>
>> But I don't understand how postgres is unable to fetch a free buffer.
>> Does any body have an idea?
>
> Somehow looks like the bgwriter is completely disabled. How are the
> relevant settings in your postgresql.conf?

I suspect the work load is entirely bulk inserts, and is using a
Buffer Access Strategy. By design, bulk inserts generally write out
their own buffers.

Cheers,

Jeff

On Wed, Mar 23, 2011 at 8:26 AM, Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> wrote:
> Hi Brad,
>
> yes. that's the question....
> in the source code in freelist.c there is something that I don't understand.
>
> This is the first try to get a free page. The second try scans used buffers.
> What makes me wonder is the why postgres is checking for <<buf->usage_count
> == 0>>
> where usage_count is supposed to be NULL initially.

The code comment preceding that check seems to explain that it is
probably not needed but simply done from an abundance of caution.

>         /*
>          * If the buffer is pinned or has a nonzero usage_count, we cannot
> use
>          * it; discard it and retry.  (This can only happen if VACUUM put a
>          * valid buffer in the freelist and then someone else used it before
>          * we got to it.  It's probably impossible altogether as of 8.3, but
>          * we'd better check anyway.)

Seems like maybe an Assert would be called for.

Cheers,

Jeff

On 23 March 2011 16:36, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Mar 23, 2011 at 6:19 AM, Jochen Erwied
> <jochen(at)pgsql-performance(dot)erwied(dot)eu> wrote:
> > Wednesday, March 23, 2011, 1:51:31 PM you wrote:
> >
> > [rearranged for quoting]
> >
> >> background writer stats
> >> checkpoints_timed | checkpoints_req | buffers_checkpoint |
> buffers_clean |
> >> maxwritten_clean | buffers_backend | buffers_alloc
> >>
> -------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
> >> 3 | 0 | 99754 |
> 0
> >> | 0 | 115307 | 246173
> >> (1 row)
> >
> > buffers_clean = 0 ?!
> >
> >> But I don't understand how postgres is unable to fetch a free buffer.
> >> Does any body have an idea?
> >
> > Somehow looks like the bgwriter is completely disabled. How are the
> > relevant settings in your postgresql.conf?
>
> I suspect the work load is entirely bulk inserts, and is using a
> Buffer Access Strategy. By design, bulk inserts generally write out
> their own buffers.
>
> Cheers,
>
> Jeff
>

Yes. that's true. We are converting databases from one schema into another
with a lot of computing in between.
But most of the written data is accessed soon for other conversions.
OK. That sounds very simple and thus trustable ;).

So everything is fine and there is no need/potential for optimization?

Best...
Uwe

2011/3/23 Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com>:
> On 23 March 2011 16:36, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>
>> On Wed, Mar 23, 2011 at 6:19 AM, Jochen Erwied
>> <jochen(at)pgsql-performance(dot)erwied(dot)eu> wrote:
>> > Wednesday, March 23, 2011, 1:51:31 PM you wrote:
>> >
>> > [rearranged for quoting]
>> >
>> >> background writer stats
>> >>  checkpoints_timed | checkpoints_req | buffers_checkpoint |
>> >> buffers_clean |
>> >> maxwritten_clean | buffers_backend | buffers_alloc
>> >>
>> >> -------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
>> >>                  3 |               0 |              99754 |
>> >> 0
>> >> |                0 |          115307 |        246173
>> >> (1 row)
>> >
>> > buffers_clean = 0 ?!
>> >
>> >> But I don't understand how postgres is unable to fetch a free buffer.
>> >> Does any body have an idea?
>> >
>> > Somehow looks like the bgwriter is completely disabled. How are the
>> > relevant settings in your postgresql.conf?
>>
>> I suspect the work load is entirely bulk inserts, and is using a
>> Buffer Access Strategy.  By design, bulk inserts generally write out
>> their own buffers.
>>
>> Cheers,
>>
>> Jeff
>
> Yes. that's true. We are converting databases from one schema into another
> with a lot of computing in between.
> But most of the written data is accessed soon for other conversions.
> OK. That sounds very simple and thus trustable ;).

yes, it is.

>
> So everything is fine and there is no need/potential for optimization?
>

There are probably room for improvements, without more thinking, I
would suggest:

* review bufferstrategy to increase the buffer size for the pool when
there is a lot of free buffers
* have a bgwriter working just behind the seqscan (and probably a
biger pool of buffers anyway)
* do not use the special bufferstrategy when the buffer cache has
more than X% of free pages
* add more :)

I believe it should be ok to do good improvement for special case
easely identifiable like yours.

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

Hi Cédric,

OK, sounds promising. But all of these improvements are for the postgres
developers.
For me as an administrator I can't do a thing right now. OK.

Thanks for you suggestions. I think for batchjobs other that just COPY they
could speed up the process quite well if now the backend process has to do
all (or 50%) of the writings.

It would also be good to see how many buffers were written by backend
processes grouped by Buffer Access Strategy - to better distinguish evil
backend writes from wanted backend writes.

Best Regards,
Uwe

On 23 March 2011 21:23, Cédric Villemain
<cedric(dot)villemain(dot)debian(at)gmail(dot)com>wrote:

> 2011/3/23 Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com>:
> > On 23 March 2011 16:36, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> >>
> >> On Wed, Mar 23, 2011 at 6:19 AM, Jochen Erwied
> >> <jochen(at)pgsql-performance(dot)erwied(dot)eu> wrote:
> >> > Wednesday, March 23, 2011, 1:51:31 PM you wrote:
> >> >
> >> > [rearranged for quoting]
> >> >
> >> >> background writer stats
> >> >> checkpoints_timed | checkpoints_req | buffers_checkpoint |
> >> >> buffers_clean |
> >> >> maxwritten_clean | buffers_backend | buffers_alloc
> >> >>
> >> >>
> -------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
> >> >> 3 | 0 | 99754 |
> >> >> 0
> >> >> | 0 | 115307 | 246173
> >> >> (1 row)
> >> >
> >> > buffers_clean = 0 ?!
> >> >
> >> >> But I don't understand how postgres is unable to fetch a free buffer.
> >> >> Does any body have an idea?
> >> >
> >> > Somehow looks like the bgwriter is completely disabled. How are the
> >> > relevant settings in your postgresql.conf?
> >>
> >> I suspect the work load is entirely bulk inserts, and is using a
> >> Buffer Access Strategy. By design, bulk inserts generally write out
> >> their own buffers.
> >>
> >> Cheers,
> >>
> >> Jeff
> >
> > Yes. that's true. We are converting databases from one schema into
> another
> > with a lot of computing in between.
> > But most of the written data is accessed soon for other conversions.
> > OK. That sounds very simple and thus trustable ;).
>
> yes, it is.
>
> >
> > So everything is fine and there is no need/potential for optimization?
> >
>
> There are probably room for improvements, without more thinking, I
> would suggest:
>
> * review bufferstrategy to increase the buffer size for the pool when
> there is a lot of free buffers
> * have a bgwriter working just behind the seqscan (and probably a
> biger pool of buffers anyway)
> * do not use the special bufferstrategy when the buffer cache has
> more than X% of free pages
> * add more :)
>
> I believe it should be ok to do good improvement for special case
> easely identifiable like yours.
>
> --
> Cédric Villemain 2ndQuadrant
> http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
>

On 03/24/2011 05:19 AM, Uwe Bartels wrote:
> It would also be good to see how many buffers were written by backend
> processes grouped by Buffer Access Strategy - to better distinguish
> evil backend writes from wanted backend writes.

Since all these writes are being cached by the operating system, which
strategy writes them out isn't that useful to track. The only really
"evil" type of writes are ones where the background writer doesn't
absorb the fsync calls and the backends have to do that themselves. And
as of V9.1, that is something you can distinguish in pg_stat_bgwriter
(and it's also less likely to happen, too)

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

OK. Thanks.

Uwe

On 28 March 2011 08:02, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> On 03/24/2011 05:19 AM, Uwe Bartels wrote:
>
>> It would also be good to see how many buffers were written by backend
>> processes grouped by Buffer Access Strategy - to better distinguish evil
>> backend writes from wanted backend writes.
>>
>
> Since all these writes are being cached by the operating system, which
> strategy writes them out isn't that useful to track. The only really "evil"
> type of writes are ones where the background writer doesn't absorb the fsync
> calls and the backends have to do that themselves. And as of V9.1, that is
> something you can distinguish in pg_stat_bgwriter (and it's also less likely
> to happen, too)
>
> --
> Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>