pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbuffercache/bgwriter
14+ messages / 7 participants
[nested] [flat]
* buffercache/bgwriter
@ 2011-03-23 12:51 Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Uwe Bartels @ 2011-03-23 12:51 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 13:19 Jochen Erwied <jochen@pgsql-performance.erwied.eu>
parent: Uwe Bartels <uwe.bartels@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: Jochen Erwied @ 2011-03-23 13:19 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: pgsql-performance; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 13:39 Uwe Bartels <uwe.bartels@gmail.com>
parent: Jochen Erwied <jochen@pgsql-performance.erwied.eu>
1 sibling, 0 replies; 14+ messages in thread
From: Uwe Bartels @ 2011-03-23 13:39 UTC (permalink / raw)
To: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 14:41 tv@fuzzy.cz
parent: Uwe Bartels <uwe.bartels@gmail.com>
1 sibling, 2 replies; 14+ messages in thread
From: tv@fuzzy.cz @ 2011-03-23 14:41 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: pgsql-performance
> 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-td207147...
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 14:54 Uwe Bartels <uwe.bartels@gmail.com>
parent: tv@fuzzy.cz
1 sibling, 0 replies; 14+ messages in thread
From: Uwe Bartels @ 2011-03-23 14:54 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance
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-td207147...
>
>
> 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
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 14:58 Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com>
parent: tv@fuzzy.cz
1 sibling, 1 reply; 14+ messages in thread
From: Nicholson, Brad (Toronto, ON, CA) @ 2011-03-23 14:58 UTC (permalink / raw)
To: tv@fuzzy.cz <tv@fuzzy.cz>; Uwe Bartels <uwe.bartels@gmail.com>; +Cc: pgsql-performance
> -----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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 15:26 Uwe Bartels <uwe.bartels@gmail.com>
parent: Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com>
0 siblings, 1 reply; 14+ messages in thread
From: Uwe Bartels @ 2011-03-23 15:26 UTC (permalink / raw)
To: Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com>; +Cc: tv@fuzzy.cz <tv@fuzzy.cz>; pgsql-performance
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.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 15:36 Jeff Janes <jeff.janes@gmail.com>
parent: Jochen Erwied <jochen@pgsql-performance.erwied.eu>
1 sibling, 1 reply; 14+ messages in thread
From: Jeff Janes @ 2011-03-23 15:36 UTC (permalink / raw)
To: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; +Cc: Uwe Bartels <uwe.bartels@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 16:01 Jeff Janes <jeff.janes@gmail.com>
parent: Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Jeff Janes @ 2011-03-23 16:01 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com>; tv@fuzzy.cz <tv@fuzzy.cz>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 16:16 Uwe Bartels <uwe.bartels@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Uwe Bartels @ 2011-03-23 16:16 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; pgsql-performance
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 ;).
So everything is fine and there is no need/potential for optimization?
Best...
Uwe
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-23 20:23 Cédric Villemain <cedric.villemain.debian@gmail.com>
parent: Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Cédric Villemain @ 2011-03-23 20:23 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Jochen Erwied <jochen@pgsql-performance.erwied.eu>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-24 09:19 Uwe Bartels <uwe.bartels@gmail.com>
parent: Cédric Villemain <cedric.villemain.debian@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Uwe Bartels @ 2011-03-24 09:19 UTC (permalink / raw)
To: Cédric Villemain <cedric.villemain.debian@gmail.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Jochen Erwied <jochen@pgsql-performance.erwied.eu>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-28 06:02 Greg Smith <greg@2ndQuadrant.com>
parent: Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Greg Smith @ 2011-03-28 06:02 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: buffercache/bgwriter
@ 2011-03-28 20:23 Uwe Bartels <uwe.bartels@gmail.com>
parent: Greg Smith <greg@2ndQuadrant.com>
0 siblings, 0 replies; 14+ messages in thread
From: Uwe Bartels @ 2011-03-28 20:23 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2011-03-28 20:23 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-23 12:51 buffercache/bgwriter Uwe Bartels <uwe.bartels@gmail.com>
2011-03-23 13:19 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2011-03-23 13:39 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-23 15:36 ` Jeff Janes <jeff.janes@gmail.com>
2011-03-23 16:16 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-23 20:23 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
2011-03-24 09:19 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-28 06:02 ` Greg Smith <greg@2ndQuadrant.com>
2011-03-28 20:23 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-23 14:41 ` tv@fuzzy.cz
2011-03-23 14:54 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-23 14:58 ` Nicholson, Brad (Toronto, ON, CA) <bnicholson@hp.com>
2011-03-23 15:26 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-23 16:01 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox