Re: Checkpoints are occurring too frequently...

On Tue, 2005-07-12 at 15:55, Greg Patnude wrote:
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe(at)g2switchworks(dot)com]
> Sent: Tuesday, July 12, 2005 11:40 AM
> To: Greg Patnude
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Checkpoints are occurring too frequently...
>
> On Tue, 2005-07-12 at 13:29, Greg Patnude wrote:
> > "Scott Marlowe" <smarlowe(at)g2switchworks(dot)com> wrote in message
> > news:1121191522(dot)8208(dot)231(dot)camel(at)state(dot)g2switchworks(dot)com(dot)(dot)(dot)
> > > 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.