Thread: Checkpoints are occurring too frequently...

Checkpoints are occurring too frequently...

From
"Greg Patnude"
Date:
LOG:  checkpoints are occurring too frequently (19 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
LOG:  checkpoints are occurring too frequently (12 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
LOG:  checkpoints are occurring too frequently (12 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
LOG:  checkpoints are occurring too frequently (11 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
LOG:  checkpoints are occurring too frequently (12 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".

What does this mean and what causes it ???



Re: Checkpoints are occurring too frequently...

From
Scott Marlowe
Date:
On Tue, 2005-07-12 at 13:04, Greg Patnude wrote:
> LOG:  checkpoints are occurring too frequently (19 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (11 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
>
> What does this mean and what causes it ???

Lots of updates or inserts.

What are you doing with your database?  Are you running some application
that is doing a lot of small inserts maybe?

Re: Checkpoints are occurring too frequently...

From
"Greg Patnude"
Date:
"Scott Marlowe" <smarlowe@g2switchworks.com> wrote in message
news:1121191522.8208.231.camel@state.g2switchworks.com...
> On Tue, 2005-07-12 at 13:04, Greg Patnude wrote:
>> LOG:  checkpoints are occurring too frequently (19 seconds apart)
>> HINT:  Consider increasing the configuration parameter
>> "checkpoint_segments".
>> LOG:  checkpoints are occurring too frequently (12 seconds apart)
>> HINT:  Consider increasing the configuration parameter
>> "checkpoint_segments".
>> LOG:  checkpoints are occurring too frequently (12 seconds apart)
>> HINT:  Consider increasing the configuration parameter
>> "checkpoint_segments".
>> LOG:  checkpoints are occurring too frequently (11 seconds apart)
>> HINT:  Consider increasing the configuration parameter
>> "checkpoint_segments".
>> LOG:  checkpoints are occurring too frequently (12 seconds apart)
>> HINT:  Consider increasing the configuration parameter
>> "checkpoint_segments".
>>
>> What does this mean and what causes it ???
>
> Lots of updates or inserts.
>
> What are you doing with your database?  Are you running some application
> that is doing a lot of small inserts maybe?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
Sort of...  I have a stored procedure that gets called and updates either 50
or 75 records in a table. The parent table has an inherited table (history)
derived from it with an update rule on the parent table that inserts a copy
of the unchanged record (prior to the update) into the history table. So --
for every 50 updates -- I expect to see 50 inserts into the child.

G. Patnude....



Re: Checkpoints are occurring too frequently...

From
Scott Marlowe
Date:
On Tue, 2005-07-12 at 13:29, Greg Patnude wrote:
> "Scott Marlowe" <smarlowe@g2switchworks.com> wrote in message
> news:1121191522.8208.231.camel@state.g2switchworks.com...
> > On Tue, 2005-07-12 at 13:04, Greg Patnude wrote:
> >> LOG:  checkpoints are occurring too frequently (19 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (11 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >>
> >> What does this mean and what causes it ???
> >
> > Lots of updates or inserts.
> >
> > What are you doing with your database?  Are you running some application
> > that is doing a lot of small inserts maybe?
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
> Sort of...  I have a stored procedure that gets called and updates either 50
> or 75 records in a table. The parent table has an inherited table (history)
> derived from it with an update rule on the parent table that inserts a copy
> of the unchanged record (prior to the update) into the history table. So --
> for every 50 updates -- I expect to see 50 inserts into the child.

So, how often is this running?  Once a second, once a minute, once and
hour?  If it's only running once an hour, then something else is wrong.

Re: Checkpoints are occurring too frequently...

From
Christopher Browne
Date:
> LOG:  checkpoints are occurring too frequently (19 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (11 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments".
>
> What does this mean and what causes it ???

This generally happens as a result of having LOTS of updates going
into the database.

If you're doing bulk loads of data, this is absolutely normal.

It probably is a good idea to increase the specified config parameter;
that can improve performance for most of the cases where the message
comes up.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/rdbms.html
Rules of  the Evil  Overlord #153.  "My Legions of  Terror will  be an
equal-opportunity employer. Conversely, when  it is prophesied that no
man  can defeat  me, I  will  keep in  mind the  increasing number  of
non-traditional gender roles." <http://www.eviloverlord.com/>

Re: Checkpoints are occurring too frequently...

From
"Greg Patnude"
Date:

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Tuesday, July 12, 2005 11:40 AM
To: Greg Patnude
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Checkpoints are occurring too frequently...

On Tue, 2005-07-12 at 13:29, Greg Patnude wrote:
> "Scott Marlowe" <smarlowe@g2switchworks.com> wrote in message
> news:1121191522.8208.231.camel@state.g2switchworks.com...
> > On Tue, 2005-07-12 at 13:04, Greg Patnude wrote:
> >> LOG:  checkpoints are occurring too frequently (19 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (11 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> >> HINT:  Consider increasing the configuration parameter
> >> "checkpoint_segments".
> >>
> >> What does this mean and what causes it ???
> >
> > Lots of updates or inserts.
> >
> > What are you doing with your database?  Are you running some application
> > that is doing a lot of small inserts maybe?
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
> Sort of...  I have a stored procedure that gets called and updates either
50
> or 75 records in a table. The parent table has an inherited table
(history)
> derived from it with an update rule on the parent table that inserts a
copy
> of the unchanged record (prior to the update) into the history table. So
--
> for every 50 updates -- I expect to see 50 inserts into the child.

So, how often is this running?  Once a second, once a minute, once and
hour?  If it's only running once an hour, then something else is wrong.
[GP->]
I've been running it about 2 or 3 times a minute on average... I increased
the " checkpoint_segments" param in postgreSQL.conf from the "factory
default' of 1 to 5... this seems to clear up most of the noise....


Greg Patnude

Re: Checkpoints are occurring too frequently...

From
Scott Marlowe
Date:
On Tue, 2005-07-12 at 15:55, Greg Patnude wrote:
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
> Sent: Tuesday, July 12, 2005 11:40 AM
> To: Greg Patnude
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Checkpoints are occurring too frequently...
>
> On Tue, 2005-07-12 at 13:29, Greg Patnude wrote:
> > "Scott Marlowe" <smarlowe@g2switchworks.com> wrote in message
> > news:1121191522.8208.231.camel@state.g2switchworks.com...
> > > On Tue, 2005-07-12 at 13:04, Greg Patnude wrote:
> > >> LOG:  checkpoints are occurring too frequently (19 seconds apart)
> > >> HINT:  Consider increasing the configuration parameter
> > >> "checkpoint_segments".
> > >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> > >> HINT:  Consider increasing the configuration parameter
> > >> "checkpoint_segments".
> > >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> > >> HINT:  Consider increasing the configuration parameter
> > >> "checkpoint_segments".
> > >> LOG:  checkpoints are occurring too frequently (11 seconds apart)
> > >> HINT:  Consider increasing the configuration parameter
> > >> "checkpoint_segments".
> > >> LOG:  checkpoints are occurring too frequently (12 seconds apart)
> > >> HINT:  Consider increasing the configuration parameter
> > >> "checkpoint_segments".
> > >>
> > >> What does this mean and what causes it ???
> > >
> > > Lots of updates or inserts.
> > >
> > > What are you doing with your database?  Are you running some application
> > > that is doing a lot of small inserts maybe?
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: explain analyze is your friend
> > >
> > Sort of...  I have a stored procedure that gets called and updates either
> 50
> > or 75 records in a table. The parent table has an inherited table
> (history)
> > derived from it with an update rule on the parent table that inserts a
> copy
> > of the unchanged record (prior to the update) into the history table. So
> --
> > for every 50 updates -- I expect to see 50 inserts into the child.
>
> So, how often is this running?  Once a second, once a minute, once and
> hour?  If it's only running once an hour, then something else is wrong.
> [GP->]
> I've been running it about 2 or 3 times a minute on average... I increased
> the " checkpoint_segments" param in postgreSQL.conf from the "factory
> default' of 1 to 5... this seems to clear up most of the noise....

Well, if you've got that many updates happening all the time, you'll
want to read up on the fsm settings and probably either use the
autovacuum daemon or schedule a regularly running cron job to vacuum the
database or those tables.

Re: Checkpoints are occurring too frequently...

From
Tom Lane
Date:
"Greg Patnude" <gpatnude@hotmail.com> writes:
> From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
>> So, how often is this running?  Once a second, once a minute, once and
>> hour?  If it's only running once an hour, then something else is wrong.

> I've been running it about 2 or 3 times a minute on average... I increased
> the " checkpoint_segments" param in postgreSQL.conf from the "factory
> default' of 1 to 5... this seems to clear up most of the noise....

The "factory default" has never been 1; AFAIR it's always been 3,
and like many of the other defaults that's aimed for small-and-slow
machines.  If you're not short of disk space, something like 30
is reasonable.  (Note this can cost you 32MB per increment, so a
setting of 30 means you're willing to invest up to a gigabyte of
disk space in WAL.  Time was when that was an astonishing amount
of disk, but no more...)

            regards, tom lane

Re: Checkpoints are occurring too frequently...

From
"Joshua D. Drake"
Date:
> The "factory default" has never been 1; AFAIR it's always been 3,
> and like many of the other defaults that's aimed for small-and-slow
> machines.  If you're not short of disk space, something like 30
> is reasonable.  (Note this can cost you 32MB per increment, so a
> setting of 30 means you're willing to invest up to a gigabyte of
> disk space in WAL.  Time was when that was an astonishing amount
> of disk, but no more...)

As this is a fairly common problem, should we consider increasing the
default to 10?

J


>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Checkpoints are occurring too frequently...

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
>> The "factory default" has never been 1; AFAIR it's always been 3,
>> and like many of the other defaults that's aimed for small-and-slow
>> machines.  If you're not short of disk space, something like 30
>> is reasonable.  (Note this can cost you 32MB per increment, so a
>> setting of 30 means you're willing to invest up to a gigabyte of
>> disk space in WAL.  Time was when that was an astonishing amount
>> of disk, but no more...)

> As this is a fairly common problem, should we consider increasing the
> default to 10?

Well, 3 is a 100Mb commitment, 10 would be about 300Mb, which is a lot
for some people.  I'm more inclined to leave it where it is and remind
people to read the tuning documents.

It's been suggested in the past that we ought to document multiple sets
of parameter choices from "small test platform" to "big fast machine";
MySQL have done something of the sort for a long time.

            regards, tom lane

Re: Checkpoints are occurring too frequently...

From
"Joshua D. Drake"
Date:
> It's been suggested in the past that we ought to document multiple sets
> of parameter choices from "small test platform" to "big fast machine";
> MySQL have done something of the sort for a long time.

That is probably a good idea.

>
>             regards, tom lane


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/