Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group