From: PFC <lists@peufeu.com>
To: Thomas Spreng <spreng@socket.ch>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Oddly slow queries
Date: Tue, 22 Apr 2008 12:29:29 +0200
Message-ID: <op.t90crftycigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <3D0211BE-8936-4705-964F-42E31263D88A@socket.ch>
References: <3D7728ED-3D36-47E6-9266-351073ED4E11@socket.ch>
<op.t9odybcgcigqcu@apollo13.peufeu.com>
<AE4F81FF-C8BE-44BC-A79D-48EC5BF20162@socket.ch>
<60y77du7qs.fsf@dba2.int.libertyrms.com>
<20B852C4-2ACA-445F-9289-558B6C86BAB4@socket.ch>
<dcc563d10804191004l1406fe89r5e2b7ee3931a4348@mail.gmail.com>
<3D0211BE-8936-4705-964F-42E31263D88A@socket.ch>
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, spreng@socket.ch, scott.marlowe@gmail.com, cbbrowne@acm.org
Subject: Re: Oddly slow queries
In-Reply-To: <op.t90crftycigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox