Обсуждение: Checkpoints are occurring too frequently...
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 ???
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?
"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....
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.
> 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/>
-----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
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.
"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
> 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/
"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
> 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/