Checkpoints are occurring too frequently...

Post by Greg Patnude
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

Greg Patnude

2005-07-12 18:29:58 UTC

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Scott Marlowe

2005-07-12 18:39:41 UTC

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

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.

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

Greg Patnude

2005-07-12 20:55:45 UTC

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Scott Marlowe

2005-07-12 21:09:11 UTC

Post by Greg Patnude
-----Original Message-----
Sent: Tuesday, July 12, 2005 11:40 AM
To: Greg Patnude
Subject: Re: [GENERAL] Checkpoints are occurring too frequently...

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

Post by Greg Patnude
or 75 records in a table. The parent table has an inherited table

(history)

Post by Greg Patnude
derived from it with an update rule on the parent table that inserts a

copy

Post by Greg Patnude
of the unchanged record (prior to the update) into the history table. So

Post by Greg Patnude
for every 50 updates -- I expect to see 50 inserts into the child.

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2005-07-12 21:21:57 UTC

Post by Scott Marlowe
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

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

Joshua D. Drake

2005-07-12 21:36:35 UTC

Post by Tom Lane
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

Post by Tom Lane
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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

2005-07-12 21:45:04 UTC

Post by Joshua D. Drake

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Joshua D. Drake

2005-07-12 21:46:35 UTC

Post by Tom Lane
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.

Post by Tom Lane
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/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Christopher Browne

2005-07-12 20:38:14 UTC