pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcheckpoint_timeout
16+ messages / 6 participants
[nested] [flat]
* checkpoint_timeout
@ 2008-10-07 20:21 Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
0 siblings, 1 reply; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-07 20:21 UTC (permalink / raw)
To: pgsql-admin
Hello there,
I'm having some slowndowns in my postgresql server, and I had been reading
something about checkpoints, that could resolve my problem.
Now, I have:
checkpoint_segments = 30 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min
And I percebed that this slowndown is occuring for 5 min...! So I'm suposing
that the problem is in the checkpoint_timeout.
What's the impact to chang it to:
checkpoint_timeout = 2min or
checkpoint_timeout = 5min
Tnhks,
Rafael Domiciano
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-07 21:13 ` chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: chirag.dave@gmail.com @ 2008-10-07 21:13 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: pgsql-admin
What version of postgres are you running ? and please also provide more
information about your suspected slowdown ?
On Tue, Oct 7, 2008 at 4:21 PM, Rafael Domiciano <rafael.domiciano@gmail.com
> wrote:
> Hello there,
> I'm having some slowndowns in my postgresql server, and I had been reading
> something about checkpoints, that could resolve my problem.
> Now, I have:
> checkpoint_segments = 30 # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min
>
> And I percebed that this slowndown is occuring for 5 min...! So I'm
> suposing that the problem is in the checkpoint_timeout.
>
> What's the impact to chang it to:
> checkpoint_timeout = 2min or
> checkpoint_timeout = 5min
>
> Tnhks,
>
> Rafael Domiciano
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
@ 2008-10-08 12:31 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:15 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 2 replies; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-08 12:31 UTC (permalink / raw)
To: chirag.dave@gmail.com; +Cc: pgsql-admin
The server is running Postgres 8.2.4When the server is doing too much
operations like I, U or D the postgres stop a while each 5 min...!
I mean "stopping" that I don't receive any response from postgres, and the
process the all people are running stay there, waiting for something.
I read a several names to it like: gap, stall, slowdown... I don't know the
write expression for this situation.
I hope you can help me.
Thnks,
Rafael Domiciano
Postgres DBA
2008/10/7 <chirag.dave@gmail.com>
> What version of postgres are you running ? and please also provide more
> information about your suspected slowdown ?
>
>
>
>
>
> On Tue, Oct 7, 2008 at 4:21 PM, Rafael Domiciano <
> rafael.domiciano@gmail.com> wrote:
>
>> Hello there,
>> I'm having some slowndowns in my postgresql server, and I had been reading
>> something about checkpoints, that could resolve my problem.
>> Now, I have:
>> checkpoint_segments = 30 # in logfile segments, min 1, 16MB
>> each
>> #checkpoint_timeout = 5min
>>
>> And I percebed that this slowndown is occuring for 5 min...! So I'm
>> suposing that the problem is in the checkpoint_timeout.
>>
>> What's the impact to chang it to:
>> checkpoint_timeout = 2min or
>> checkpoint_timeout = 5min
>>
>> Tnhks,
>>
>> Rafael Domiciano
>>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-08 14:15 ` chirag.dave@gmail.com
1 sibling, 0 replies; 16+ messages in thread
From: chirag.dave@gmail.com @ 2008-10-08 14:15 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: pgsql-admin
Again very little information to work with but I would start with looking at
postgres logs. you should enable checkpoint_timeout to a very high number,
so you have an entry in the log anytime checkpoint occurs and set your
log_min_duration_statement to number which your considered to be long
running, to log all slower statement.
Chirag Dave
DBA, Afilias
On Wed, Oct 8, 2008 at 8:31 AM, Rafael Domiciano <rafael.domiciano@gmail.com
> wrote:
> The server is running Postgres 8.2.4When the server is doing too much
> operations like I, U or D the postgres stop a while each 5 min...!
> I mean "stopping" that I don't receive any response from postgres, and the
> process the all people are running stay there, waiting for something.
> I read a several names to it like: gap, stall, slowdown... I don't know the
> write expression for this situation.
>
> I hope you can help me.
>
> Thnks,
>
> Rafael Domiciano
> Postgres DBA
>
> 2008/10/7 <chirag.dave@gmail.com>
>
> What version of postgres are you running ? and please also provide more
>> information about your suspected slowdown ?
>>
>>
>>
>>
>>
>> On Tue, Oct 7, 2008 at 4:21 PM, Rafael Domiciano <
>> rafael.domiciano@gmail.com> wrote:
>>
>>> Hello there,
>>> I'm having some slowndowns in my postgresql server, and I had been
>>> reading something about checkpoints, that could resolve my problem.
>>> Now, I have:
>>> checkpoint_segments = 30 # in logfile segments, min 1,
>>> 16MB each
>>> #checkpoint_timeout = 5min
>>>
>>> And I percebed that this slowndown is occuring for 5 min...! So I'm
>>> suposing that the problem is in the checkpoint_timeout.
>>>
>>> What's the impact to chang it to:
>>> checkpoint_timeout = 2min or
>>> checkpoint_timeout = 5min
>>>
>>> Tnhks,
>>>
>>> Rafael Domiciano
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-08 14:23 ` Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Brad Nicholson @ 2008-10-08 14:23 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: pgsql-admin
On Wed, 2008-10-08 at 10:31 -0200, Rafael Domiciano wrote:
> The server is running Postgres 8.2.4
> When the server is doing too much operations like I, U or D the
> postgres stop a while each 5 min...!
> I mean "stopping" that I don't receive any response from postgres, and
> the process the all people are running stay there, waiting for
> something.
> I read a several names to it like: gap, stall, slowdown... I don't
> know the write expression for this situation.
The bgwriter was added to help alleviate this problem.
What are your settings for that? The solution may be to push dirty
buffers to disk more aggressively via the bgwriter so you have less work
to do at checkpoint time.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
@ 2008-10-08 14:34 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-08 14:34 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-admin
Hello Brad,
All of my bgwriter parameters is set on default. Following:#bgwriter_delay =
200ms # 10-10000ms between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers
scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
I am trying now to increase the checkpoint_segments to 40...
2008/10/8 Brad Nicholson <bnichols@ca.afilias.info>
> On Wed, 2008-10-08 at 10:31 -0200, Rafael Domiciano wrote:
> > The server is running Postgres 8.2.4
> > When the server is doing too much operations like I, U or D the
> > postgres stop a while each 5 min...!
> > I mean "stopping" that I don't receive any response from postgres, and
> > the process the all people are running stay there, waiting for
> > something.
> > I read a several names to it like: gap, stall, slowdown... I don't
> > know the write expression for this situation.
>
> The bgwriter was added to help alleviate this problem.
>
> What are your settings for that? The solution may be to push dirty
> buffers to disk more aggressively via the bgwriter so you have less work
> to do at checkpoint time.
>
>
> --
> Brad Nicholson 416-673-4106
> Database Administrator, Afilias Canada Corp.
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-08 15:11 ` Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:18 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Julio Leyva @ 2008-10-08 15:11 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; Brad Nicholson <bnichols@ca.afilias.info>; +Cc: chirag.dave@gmail.com; pgsql-admin
We have a transactional D.B writing directly to postgres (8.3) and these parameters have worked ok for us
checkpoint_timeout = 5min
checkpoint_segments = 10
wal_buffers = 1024kB
Date: Wed, 8 Oct 2008 12:34:00 -0200
From: rafael.domiciano@gmail.com
To: bnichols@ca.afilias.info
Subject: Re: [ADMIN] checkpoint_timeout
CC: chirag.dave@gmail.com; pgsql-admin@postgresql.org
Hello Brad,
All of my bgwriter parameters is set on default. Following:#bgwriter_delay = 200ms # 10-10000ms between rounds#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
I am trying now to increase the checkpoint_segments to 40...
2008/10/8 Brad Nicholson <bnichols@ca.afilias.info>
On Wed, 2008-10-08 at 10:31 -0200, Rafael Domiciano wrote:
> The server is running Postgres 8.2.4
> When the server is doing too much operations like I, U or D the
> postgres stop a while each 5 min...!
> I mean "stopping" that I don't receive any response from postgres, and
> the process the all people are running stay there, waiting for
> something.
> I read a several names to it like: gap, stall, slowdown... I don't
> know the write expression for this situation.
The bgwriter was added to help alleviate this problem.
What are your settings for that? The solution may be to push dirty
buffers to disk more aggressively via the bgwriter so you have less work
to do at checkpoint time.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
@ 2008-10-08 16:18 ` Rafael Domiciano <rafael.domiciano@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-08 16:18 UTC (permalink / raw)
To: Julio Leyva <jcleyva@hotmail.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-admin
Hello Julio,
If I set my postgresql server to checkpoint_segments = 10 aren't going to be
poor segments to my database?
2008/10/8 Julio Leyva <jcleyva@hotmail.com>
> We have a transactional D.B writing directly to postgres (8.3) and these
> parameters have worked ok for us
>
>
> checkpoint_timeout = 5min
> checkpoint_segments = 10
> wal_buffers = 1024kB
>
>
>
> ------------------------------
> Date: Wed, 8 Oct 2008 12:34:00 -0200
> From: rafael.domiciano@gmail.com
> To: bnichols@ca.afilias.info
> Subject: Re: [ADMIN] checkpoint_timeout
> CC: chirag.dave@gmail.com; pgsql-admin@postgresql.org
>
>
> Hello Brad,
>
> All of my bgwriter parameters is set on default. Following:#bgwriter_delay
> = 200ms # 10-10000ms between rounds
> #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
> scanned/round
> #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
> #bgwriter_all_percent = 0.333 # 0-100% of all buffers
> scanned/round
> #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
>
> I am trying now to increase the checkpoint_segments to 40...
>
> 2008/10/8 Brad Nicholson <bnichols@ca.afilias.info>
>
> On Wed, 2008-10-08 at 10:31 -0200, Rafael Domiciano wrote:
> > The server is running Postgres 8.2.4
> > When the server is doing too much operations like I, U or D the
> > postgres stop a while each 5 min...!
> > I mean "stopping" that I don't receive any response from postgres, and
> > the process the all people are running stay there, waiting for
> > something.
> > I read a several names to it like: gap, stall, slowdown... I don't
> > know the write expression for this situation.
>
> The bgwriter was added to help alleviate this problem.
>
> What are your settings for that? The solution may be to push dirty
> buffers to disk more aggressively via the bgwriter so you have less work
> to do at checkpoint time.
>
>
> --
> Brad Nicholson 416-673-4106
> Database Administrator, Afilias Canada Corp.
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
@ 2008-10-08 16:19 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-08 16:19 UTC (permalink / raw)
To: Julio Leyva <jcleyva@hotmail.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-admin
Hello Julio,
If I set my postgresql server to checkpoint_segments = 10 aren't going to be
poor segments?
2008/10/8 Julio Leyva <jcleyva@hotmail.com>
> We have a transactional D.B writing directly to postgres (8.3) and these
> parameters have worked ok for us
>
>
> checkpoint_timeout = 5min
> checkpoint_segments = 10
> wal_buffers = 1024kB
>
>
>
> ------------------------------
> Date: Wed, 8 Oct 2008 12:34:00 -0200
> From: rafael.domiciano@gmail.com
> To: bnichols@ca.afilias.info
> Subject: Re: [ADMIN] checkpoint_timeout
> CC: chirag.dave@gmail.com; pgsql-admin@postgresql.org
>
>
> Hello Brad,
>
> All of my bgwriter parameters is set on default. Following:#bgwriter_delay
> = 200ms # 10-10000ms between rounds
> #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
> scanned/round
> #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
> #bgwriter_all_percent = 0.333 # 0-100% of all buffers
> scanned/round
> #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
>
> I am trying now to increase the checkpoint_segments to 40...
>
> 2008/10/8 Brad Nicholson <bnichols@ca.afilias.info>
>
> On Wed, 2008-10-08 at 10:31 -0200, Rafael Domiciano wrote:
> > The server is running Postgres 8.2.4
> > When the server is doing too much operations like I, U or D the
> > postgres stop a while each 5 min...!
> > I mean "stopping" that I don't receive any response from postgres, and
> > the process the all people are running stay there, waiting for
> > something.
> > I read a several names to it like: gap, stall, slowdown... I don't
> > know the write expression for this situation.
>
> The bgwriter was added to help alleviate this problem.
>
> What are your settings for that? The solution may be to push dirty
> buffers to disk more aggressively via the bgwriter so you have less work
> to do at checkpoint time.
>
>
> --
> Brad Nicholson 416-673-4106
> Database Administrator, Afilias Canada Corp.
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-08 16:34 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-08 16:34 UTC (permalink / raw)
To: Julio Leyva <jcleyva@hotmail.com>; +Cc: pgsql-admin
The wal_buffers is 64Kb (The default)...What Does wal_buffers do?
2008/10/8 Julio Leyva <jcleyva@hotmail.com>
> well it depends , our D.B is really high in transactions, and
> checkpoints_segments along with the wal_buffers are ok. you need to play
> with both of them
>
>
>
>
>
>
> ------------------------------
> Date: Wed, 8 Oct 2008 14:19:37 -0200
> From: rafael.domiciano@gmail.com
> To: jcleyva@hotmail.com
> Subject: Re: [ADMIN] checkpoint_timeout
> CC: bnichols@ca.afilias.info; chirag.dave@gmail.com;
> pgsql-admin@postgresql.org
>
>
> Hello Julio,
> If I set my postgresql server to checkpoint_segments = 10 aren't going to
> be poor segments?
>
>
> 2008/10/8 Julio Leyva <jcleyva@hotmail.com>
>
> We have a transactional D.B writing directly to postgres (8.3) and these
> parameters have worked ok for us
>
>
> checkpoint_timeout = 5min
> checkpoint_segments = 10
> wal_buffers = 1024kB
>
>
>
> ------------------------------
> Date: Wed, 8 Oct 2008 12:34:00 -0200
> From: rafael.domiciano@gmail.com
> To: bnichols@ca.afilias.info
> Subject: Re: [ADMIN] checkpoint_timeout
> CC: chirag.dave@gmail.com; pgsql-admin@postgresql.org
>
>
> Hello Brad,
>
> All of my bgwriter parameters is set on default. Following:#bgwriter_delay
> = 200ms # 10-10000ms between rounds
> #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
> scanned/round
> #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
> #bgwriter_all_percent = 0.333 # 0-100% of all buffers
> scanned/round
> #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
>
> I am trying now to increase the checkpoint_segments to 40...
>
> 2008/10/8 Brad Nicholson <bnichols@ca.afilias.info>
>
> On Wed, 2008-10-08 at 10:31 -0200, Rafael Domiciano wrote:
> > The server is running Postgres 8.2.4
> > When the server is doing too much operations like I, U or D the
> > postgres stop a while each 5 min...!
> > I mean "stopping" that I don't receive any response from postgres, and
> > the process the all people are running stay there, waiting for
> > something.
> > I read a several names to it like: gap, stall, slowdown... I don't
> > know the write expression for this situation.
>
> The bgwriter was added to help alleviate this problem.
>
> What are your settings for that? The solution may be to push dirty
> buffers to disk more aggressively via the bgwriter so you have less work
> to do at checkpoint time.
>
>
> --
> Brad Nicholson 416-673-4106
> Database Administrator, Afilias Canada Corp.
>
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-08 16:46 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 17:54 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2008-10-08 16:46 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; Julio Leyva <jcleyva@hotmail.com>; +Cc: pgsql-admin
>>> "Rafael Domiciano" <rafael.domiciano@gmail.com> wrote:
> What Does wal_buffers do?
http://www.postgresql.org/docs/8.2/interactive/runtime-config-wal.html#GUC-WAL-BUFFERS
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-10-08 17:54 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 18:08 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-08 17:54 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Julio Leyva <jcleyva@hotmail.com>; pgsql-admin
Right...I going to restart my server only tonight, the access along the day
is too much to do now...!
Maybe increasing a little bit could do difference?
I thougth to increase to around 256 Kb!
I have a doubt... what's the chance to my server get instable?
Tnhks All
2008/10/8 Kevin Grittner <Kevin.Grittner@wicourts.gov>
> >>> "Rafael Domiciano" <rafael.domiciano@gmail.com> wrote:
> > What Does wal_buffers do?
>
>
> http://www.postgresql.org/docs/8.2/interactive/runtime-config-wal.html#GUC-WAL-BUFFERS
>
> -Kevin
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 17:54 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-08 18:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 18:16 ` Re: checkpoint_timeout Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2008-10-08 18:08 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Julio Leyva <jcleyva@hotmail.com>; pgsql-admin
>>> "Rafael Domiciano" <rafael.domiciano@gmail.com> wrote:
> Right...I going to restart my server only tonight, the access along
the day
> is too much to do now...!
> Maybe increasing a little bit could do difference?
> I thougth to increase to around 256 Kb!
> I have a doubt... what's the chance to my server get instable?
Changing wal_buffers to that isn't at all likely to cause problems,
unless you are teetering right on the edge of exceeding your shmmax
setting. It's also unlikely to help much, in my opinion.
If the problem is checkpoints (which seems likely but far from a sure
thing based on the information provided), aggressive background writer
setting might be your best bet under 8.2.X. To solve similar problems
we had to go to the following, although many on these lists feel that
settings this aggressive are rarely needed, so use at your own risk.
#bgwriter_delay = 200ms
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10.0
bgwriter_all_maxpages = 600
Even better would be to go to the latest revision of the 8.3 release,
which at this writing is 8.3.4. In that release PostgreSQL spreads
out the work of a checkpoint to minimize this problem.
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 17:54 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 18:08 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-10-08 18:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-09 15:02 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2008-10-08 18:16 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Rafael Domiciano <rafael.domiciano@gmail.com>; Julio Leyva <jcleyva@hotmail.com>; pgsql-admin
On Wed, Oct 8, 2008 at 12:08 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>
> If the problem is checkpoints (which seems likely but far from a sure
> thing based on the information provided), aggressive background writer
> setting might be your best bet under 8.2.X. To solve similar problems
> we had to go to the following, although many on these lists feel that
> settings this aggressive are rarely needed, so use at your own risk.
>
> #bgwriter_delay = 200ms
> bgwriter_lru_percent = 20.0
> bgwriter_lru_maxpages = 200
> bgwriter_all_percent = 10.0
> bgwriter_all_maxpages = 600
I've tuned an 8.2 server with settings similar to this, and it made a
world of difference on smoothing out performance. Note that bgwriter
tends to use cpu and memory bandwidth up, so avoid going crazy on it.
> Even better would be to go to the latest revision of the 8.3 release,
> which at this writing is 8.3.4. In that release PostgreSQL spreads
> out the work of a checkpoint to minimize this problem.
Seconded. A lot of hard work went into making the bgwriter much
easier to adjust, and much less likely to even need adjusting in 8.3.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 17:54 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 18:08 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 18:16 ` Re: checkpoint_timeout Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-10-09 15:02 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-09 15:06 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 16+ messages in thread
From: Rafael Domiciano @ 2008-10-09 15:02 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Julio Leyva <jcleyva@hotmail.com>; pgsql-admin
Hi there.
I increased the wal_buffers and no change made.
I'm beggining to think that my problem is not related to "checkpoint & cia"
as I said early.
I have decreased the checkpoint_timeout to 3 min and the slow down
continuin' ocorring after each 5 min.
In this "slowdown" the postgres does nothing, it's simply stop doing the
operations. The softwares still waiting for the response.
This is a dedicated server, the only program another postgres that runs is
Slony-I
2008/10/8 Scott Marlowe <scott.marlowe@gmail.com>
> On Wed, Oct 8, 2008 at 12:08 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
> >
> > If the problem is checkpoints (which seems likely but far from a sure
> > thing based on the information provided), aggressive background writer
> > setting might be your best bet under 8.2.X. To solve similar problems
> > we had to go to the following, although many on these lists feel that
> > settings this aggressive are rarely needed, so use at your own risk.
> >
> > #bgwriter_delay = 200ms
> > bgwriter_lru_percent = 20.0
> > bgwriter_lru_maxpages = 200
> > bgwriter_all_percent = 10.0
> > bgwriter_all_maxpages = 600
>
> I've tuned an 8.2 server with settings similar to this, and it made a
> world of difference on smoothing out performance. Note that bgwriter
> tends to use cpu and memory bandwidth up, so avoid going crazy on it.
>
> > Even better would be to go to the latest revision of the 8.3 release,
> > which at this writing is 8.3.4. In that release PostgreSQL spreads
> > out the work of a checkpoint to minimize this problem.
>
> Seconded. A lot of hard work went into making the bgwriter much
> easier to adjust, and much less likely to even need adjusting in 8.3.
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: checkpoint_timeout
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` Re: checkpoint_timeout chirag.dave@gmail.com
2008-10-08 12:31 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:23 ` Re: checkpoint_timeout Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Re: checkpoint_timeout Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:19 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 17:54 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 18:08 ` Re: checkpoint_timeout Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 18:16 ` Re: checkpoint_timeout Scott Marlowe <scott.marlowe@gmail.com>
2008-10-09 15:02 ` Re: checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
@ 2008-10-09 15:06 ` Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 0 replies; 16+ messages in thread
From: Brad Nicholson @ 2008-10-09 15:06 UTC (permalink / raw)
To: Rafael Domiciano <rafael.domiciano@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Julio Leyva <jcleyva@hotmail.com>; pgsql-admin
On Thu, 2008-10-09 at 13:02 -0200, Rafael Domiciano wrote:
> Hi there.
>
>
> I increased the wal_buffers and no change made.
> I'm beggining to think that my problem is not related to "checkpoint &
> cia" as I said early.
> I have decreased the checkpoint_timeout to 3 min and the slow down
> continuin' ocorring after each 5 min.
>
>
> In this "slowdown" the postgres does nothing, it's simply stop doing
> the operations. The softwares still waiting for the response.
> This is a dedicated server, the only program another postgres that
> runs is Slony-I
This sounds like checkpoint issues to me. Try adjusting the bgwriter
settings as suggested.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2008-10-09 15:06 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-07 20:21 checkpoint_timeout Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-07 21:13 ` chirag.dave@gmail.com
2008-10-08 12:31 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 14:15 ` chirag.dave@gmail.com
2008-10-08 14:23 ` Brad Nicholson <bnichols@ca.afilias.info>
2008-10-08 14:34 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 15:11 ` Julio Leyva <jcleyva@hotmail.com>
2008-10-08 16:18 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:19 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:34 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 16:46 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 17:54 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-08 18:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-10-08 18:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-09 15:02 ` Rafael Domiciano <rafael.domiciano@gmail.com>
2008-10-09 15:06 ` Brad Nicholson <bnichols@ca.afilias.info>
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