From: adey <adey11@gmail.com>
To: Pgsql-Admin <pgsql-admin@postgresql.org>
Subject: Delete performance
Date: Tue, 21 Feb 2006 20:02:53 +1300
Message-ID: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> (raw)
Please give me some guidance?
We are attempting many deletes in our production database for the first
time, and we're getting nowhere fast.
The SQL runs for more than 12 hours to delete 2 million rows, and hasn't
finished each time we've tried it as we've had to cancel it.
I have tried running queries for locks, current activity, and buffer hits. I
can see row locks on the affected tables for the delete PID, but no
significant buffer hits or changes in row numbers while it is running. We
have fsync set to default (true) with default 8 buffers. Postgres 7.4.2 is
running on Debian on a 4 processor server with 4gb RAM. TOP shows cache
increasing slowly, and postmaster using at least 1 CPU 100%. pg_clog files
swap about every 4 hours. We Vacuum (no parms) and ANALYZE daily, but no
VACUUM FULL for months. Delete is being performed on a parent table of 11
million rows, related to 5 child tables by foreign keys with ON DELETE
CASCADE. We have followed previous advice in this forum and tweaked /
increased the "famous" performance parameters in v7 such as
effective_cache_size, vacuum_mem and buffer size with associated SHMMAX
increase.
Where to next please?
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: adey11@gmail.com
Subject: Re: Delete performance
In-Reply-To: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com>
References: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.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