Thread: buffercache/bgwriter

buffercache/bgwriter

From
Uwe Bartels
Date:
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)


Re: buffercache/bgwriter

From
Jochen Erwied
Date:
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


Re: buffercache/bgwriter

From
Uwe Bartels
Date:
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.bartels@gmail.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@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?


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


Re: buffercache/bgwriter

From
tv@fuzzy.cz
Date:
> 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


Re: buffercache/bgwriter

From
Uwe Bartels
Date:
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@fuzzy.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


Re: buffercache/bgwriter

From
"Nicholson, Brad (Toronto, ON, CA)"
Date:

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

Re: buffercache/bgwriter

From
Uwe Bartels
Date:
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@hp.com> wrote:


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

Re: buffercache/bgwriter

From
Jeff Janes
Date:
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

Re: buffercache/bgwriter

From
Uwe Bartels
Date:
On 23 March 2011 16:36, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Mar 23, 2011 at 6:19 AM, Jochen Erwied
> 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

Re: buffercache/bgwriter

From
Jeff Janes
Date:
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

Re: buffercache/bgwriter

From
Cédric Villemain
Date:
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

Re: buffercache/bgwriter

From
Uwe Bartels
Date:
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.villemain.debian@gmail.com> wrote:
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

Re: buffercache/bgwriter

From
Greg Smith
Date:
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


Re: buffercache/bgwriter

From
Uwe Bartels
Date:
OK. Thanks.

Uwe


On 28 March 2011 08:02, Greg Smith <greg@2ndquadrant.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@2ndQuadrant.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance