Re: Oddly slow queries

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