Thread: buffercache/bgwriter

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@erwied.eu     +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 |   work: joe@mbs-software.de  +49-2151-7294-24, FAX: -50
D-45470 Muelheim  | mobile: jochen.erwied@vodafone.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

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of tv@fuzzy.cz
> Sent: Wednesday, March 23, 2011 10:42 AM
> To: Uwe Bartels
> Cc: pgsql-performance@postgresql.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@pgsql-performance.erwied.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.bartels@gmail.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

2011/3/23 Uwe Bartels <uwe.bartels@gmail.com>:
> On 23 March 2011 16:36, Jeff Janes <jeff.janes@gmail.com> wrote:
>>
>> On Wed, Mar 23, 2011 at 6:19 AM, Jochen Erwied
>> <jochen@pgsql-performance.erwied.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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books