Delete performance

From: adey <adey11(at)gmail(dot)com>
To: Pgsql-Admin <pgsql-admin(at)postgresql(dot)org>
Subject: Delete performance
Date: 2006-02-21 07:02:53
Message-ID: 1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Joost Kraaijeveld 2006-02-21 09:21:31 Disk crash problems: postgres database not functioning anymore
Previous Message Chris Browne 2006-02-20 15:41:05 Re: Best filesystem