From: Gregory Stark <stark@enterprisedb.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Galantucci Giovanni <Giovanni.Galantucci@italtel.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: DELETE queries slow down
Date: Mon, 17 Sep 2007 11:21:44 +0100
Message-ID: <873axdpp6v.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <46EE4676.8040406@enterprisedb.com>
References: <AF4EB29748E178458A0067661112724B0FCF74@BESONE.corp.dom>
<46EE4676.8040406@enterprisedb.com>
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Galantucci Giovanni wrote:
>
>> 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!
What do the inserts and deletes actually look like? Are there subqueries or
joins or are they just inserting values and deleting simple where clauses?
And are these in autocommit mode or are you running multiple commands in a
single transaction?
Generally it's faster to run more commands in a single transaction but what
I'm worried about is that you may have a transaction open which you aren't
committing for a long time. This can stop vacuum from being able to clean up
dead space and if it's in the middle of a query can actually cause vacuum to
get stuck waiting for the query to finish using the page it's using.
--
Gregory Stark
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: stark@enterprisedb.com, heikki@enterprisedb.com, Giovanni.Galantucci@italtel.it
Subject: Re: DELETE queries slow down
In-Reply-To: <873axdpp6v.fsf@oxford.xeocode.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