thread-index: Acf5DASgQ4eLy2e7QqWrBpmn1gjoNw==
Content-Class: urn:content-classes:message
Importance: normal
Message-ID: <46EE4676.8040406@enterprisedb.com>
Date: Mon, 17 Sep 2007 10:18:46 +0100
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: DELETE queries slow down
References: <AF4EB29748E178458A0067661112724B0FCF74@BESONE.corp.dom>
In-Reply-To: <AF4EB29748E178458A0067661112724B0FCF74@BESONE.corp.dom>
Content-Type: text/plain;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Galantucci Giovanni wrote:
> I have a problem with DELETE performance with postgres 7.4.

You should consider upgrading. While I don't recall any particular
enhancements that would directly help with this problem, 8.2 is
generally faster.

> I have a database with 2 great tables (about 150,000 rows) continuously
> updated, with 1000 - 1200 INSERT per second and 2 or 3 huge DELETE per
> minute, in which we delete almost all the rows inserted in the 2 tables
> during the previous minute.
> 
> I have a single, indexed foreign key between the 2 tables.
> 
>  
> 
> In this scenario we have always a problem with the delete:
> 
> For 1 or 2 hours we update only one table, and everything goes ok, where
> DELETE last at most 6 or 7 seconds.
> 
> Then for a minute we do INSERT on both table, and everything continue
> going ok, with DELETE that last about 10 seconds.
> 
> From that moment on, DELETES become timeless, and last for 240 and more
> seconds! 
> 
> Then I can't recover from this state because INSERT continue with the
> same rate and DELETE become more and more slow.

I suspect that at first the tables fit in memory, and operations are
therefore fast. But after they grow beyond a certain point, they no
longer fit in memory, and you start doing I/O which is slow.

> I do a vacuum analyze every minute.

I'd suggest doing a VACUUM (no analyze) after every DELETE.

Have you checked the EXPLAIN ANALYZE output of the DELETE? It might be
choosing a bad plan after the table grows.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com