Thread: checkpoint_timeout
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
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 = 5minAnd 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 orcheckpoint_timeout = 5minTnhks,Rafael Domiciano
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.
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 = 5minAnd 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 orcheckpoint_timeout = 5minTnhks,Rafael Domiciano
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
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 DomicianoPostgres DBA2008/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 = 5minAnd 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 orcheckpoint_timeout = 5minTnhks,Rafael Domiciano
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.
Hello Brad,
#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 bgwriter was added to help alleviate this problem.
> 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.
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.
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
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,
#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 bgwriter was added to help alleviate this problem.
> 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.
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.
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.orgHello 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/roundI 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 bgwriter was added to help alleviate this problem.
> 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.
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.
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.orgHello 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/roundI 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 bgwriter was added to help alleviate this problem.
> 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.
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.
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 -0200To: jcleyva@hotmail.comCC: bnichols@ca.afilias.info; chirag.dave@gmail.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] checkpoint_timeoutHello 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.orgHello 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/roundI 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 bgwriter was added to help alleviate this problem.
> 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.
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.
>>> "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
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
>>> "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
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.
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 GrittnerI've tuned an 8.2 server with settings similar to this, and it made a
<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
world of difference on smoothing out performance. Note that bgwriter
tends to use cpu and memory bandwidth up, so avoid going crazy on it.Seconded. A lot of hard work went into making the bgwriter much
> 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.
easier to adjust, and much less likely to even need adjusting in 8.3.
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.