that's correct, there are nightly (at least at the moment) processes that
insert around 2-3 mio rows and delete about the same amount. I can see
that
those 'checkpoints are occurring too frequently' messages are only logged
during that timeframe.
Perhaps you should increase the quantity of xlog PG is allowed to write
between each checkpoint (this is checkpoint_segments). Checkpointing every
10 seconds is going to slow down your inserts also, because of the need to
fsync()'ing all those pages, not to mention nuking your IO-bound SELECTs.
Increase it till it checkpoints every 5 minutes or something.
I assume that it's normal that so many INSERT's and DELETE's cause the
Well, also, do you use batch-processing or plpgsql or issue a huge mass
of individual INSERTs via some script ?
If you use a script, make sure that each INSERT doesn't have its own
transaction (I think you know that since with a few millions of rows it
would take forever... unless you can do 10000 commits/s, in which case
either you use 8.3 and have activated the "one fsync every N seconds"
feature, or your battery backed up cache works, or your disk is lying)...
If you use a script and the server is under heavy load you can :
BEGIN
Process N rows (use multi-values INSERT and DELETE WHERE .. IN (...)), or
execute a prepared statement multiple times, or copy to temp table and
process with SQL (usually much faster)
COMMIT
Sleep
Wash, rinse, repeat
background writer to choke a little bit. I guess I really need to adjust
the
processes to INSERT and DELETE rows in a slower pace if I want to do
other
queries during the same time.
cheers,
tom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
