X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 335B09DC86D for ; Tue, 21 Feb 2006 03:02:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10690-07 for ; Tue, 21 Feb 2006 03:02:54 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.203]) by postgresql.org (Postfix) with ESMTP id F3DF09DC892 for ; Tue, 21 Feb 2006 03:02:52 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i3so801789wra for ; Mon, 20 Feb 2006 23:02:53 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=IRHCp5qS5av5o4vw2ZZahRTvtBdcbSMbMZLFvE+JzoIQSdSmkeGXpJnm5oytup7N7xHYH3+Kn0MvGfFxo3KcjlO0ZSwrYhKlZTpAywd5FGkLX8ntokcA2pcguFZxf9nql61R+Y9mf5XtpyVhH+6e7WkskV0smdJ99cHSbitUzRo= Received: by 10.65.196.17 with SMTP id y17mr1253916qbp; Mon, 20 Feb 2006 23:02:53 -0800 (PST) Received: by 10.65.52.11 with HTTP; Mon, 20 Feb 2006 23:02:53 -0800 (PST) Message-ID: <1c66bda80602202302u2f6f6bc0jcfc3c728940ad2d0@mail.gmail.com> Date: Tue, 21 Feb 2006 20:02:53 +1300 From: adey To: Pgsql-Admin Subject: Delete performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14141_11554028.1140505373487" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.756 required=5 tests=[AWL=0.113, HTML_00_10=0.642, HTML_MESSAGE=0.001] X-Spam-Score: 0.756 X-Spam-Level: X-Archive-Number: 200602/237 X-Sequence-Number: 20658 ------=_Part_14141_11554028.1140505373487 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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? ------=_Part_14141_11554028.1140505373487 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Please give me some guidance?
We are attempting many deletes in our production database for the firs= t 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 h= its. 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 h= ave fsync set to default (true) with default 8 buffers. Postgres=20 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 dail= y, but no VACUUM FULL for months. Delete is being performed on a parent tab= le of 11 million rows, related to 5 child tables by foreign keys with ON DE= LETE CASCADE. We have followed previous advice in this forum and tweaked / = increased the "famous" performance parameters in v7 such as effec= tive_cache_size, vacuum_mem and buffer size with associated SHMMAX increase= .
 
Where to next please?
------=_Part_14141_11554028.1140505373487--