From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Galantucci Giovanni <Giovanni.Galantucci@italtel.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: DELETE queries slow down
Date: Mon, 17 Sep 2007 10:18:46 +0100
Message-ID: <46EE4676.8040406@enterprisedb.com> (raw)
In-Reply-To: <AF4EB29748E178458A0067661112724B0FCF74@BESONE.corp.dom>
References: <AF4EB29748E178458A0067661112724B0FCF74@BESONE.corp.dom>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, Giovanni.Galantucci@italtel.it
Subject: Re: DELETE queries slow down
In-Reply-To: <46EE4676.8040406@enterprisedb.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