pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete performance
7+ messages / 5 participants
[nested] [flat]
* Delete performance
@ 2011-05-31 00:08 Jarrod Chesney <jarrod.chesney@gmail.com>
2011-05-31 09:26 ` Re: Delete performance Grzegorz Jaśkiewicz <gryzman@gmail.com>
2011-05-31 23:11 ` Re: Delete performance Pierre C <lists@peufeu.com>
2011-06-01 06:14 ` Re: Delete performance Greg Smith <greg@2ndQuadrant.com>
0 siblings, 3 replies; 7+ messages in thread
From: Jarrod Chesney @ 2011-05-31 00:08 UTC (permalink / raw)
To: pgsql-performance
Hi All
My database uses joined table inheritance and my server version is 9.0
Version string PostgreSQL 9.0rc1 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.4.4-r1 p1.1, pie-0.4.5) 4.4.4, 64-bit
I have about 120,000 records in the table that everything else inherits from, if i truncate-cascaded this table it happens almost instantly. If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it takes close to 10 minutes.
My foreign keys to the base table are all set with "ON DELETE CASCADE". I've looked though all the feilds that relate to the "ID" in the base table and created btree indexes for them.
Can anyone outline what I need to verify/do to ensure i'm getting the best performance for my deletes?
Regards, Jarrod Chesney
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Delete performance
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
@ 2011-05-31 09:26 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Grzegorz Jaśkiewicz @ 2011-05-31 09:26 UTC (permalink / raw)
To: Jarrod Chesney <jarrod.chesney@gmail.com>; +Cc: pgsql-performance
9.0rc1 ?
You know that the stable 9.0 has been out for quite a while now.
Its not going to affect the delete speed in any way, but I would
generally advice you to upgrade it to the lates 9.0.x
As for the delete it self, check if you have indices on the tables
that refer the main table on the referred column. Often times that's
the issue.
Other thing is , number of triggers on the other tables.
--
GJ
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Delete performance
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
@ 2011-05-31 23:11 ` Pierre C <lists@peufeu.com>
2011-06-01 01:40 ` Re: Delete performance Craig Ringer <craig@postnewspapers.com.au>
2 siblings, 1 reply; 7+ messages in thread
From: Pierre C @ 2011-05-31 23:11 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, "Jarrod Chesney" <jarrod.chesney@gmail.com>
> If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
> takes close to 10 minutes.
Do you run those in a single transaction or do you use one transaction per
DELETE ?
In the latter case, postgres will ensure each transaction is commited to
disk, at each commit. Since this involves waiting for the physical I/O to
happen, it is slow. If you do it 30.000 times, it will be 30.000 times
slow.
Note that you should really do :
DELETE FROM table WHERE id IN (huge list of ids).
or
DELETE FROM table JOIN VALUES (list of ids) ON (...)
Also, check your foreign keys using cascading deletes have indexes in the
referencing tables. Without an index, finding the rows to cascade-delete
will be slow.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Delete performance
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
2011-05-31 23:11 ` Re: Delete performance Pierre C <lists@peufeu.com>
@ 2011-06-01 01:40 ` Craig Ringer <craig@postnewspapers.com.au>
2011-06-01 01:45 ` Re: Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Craig Ringer @ 2011-06-01 01:40 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; +Cc: pgsql-performance@postgresql.org, Jarrod Chesney <jarrod.chesney@gmail.com>
On 1/06/2011 7:11 AM, Pierre C wrote:
>> If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
>> takes close to 10 minutes.
>
> Do you run those in a single transaction or do you use one transaction
> per DELETE ?
>
> In the latter case, postgres will ensure each transaction is commited to
> disk, at each commit. Since this involves waiting for the physical I/O
> to happen, it is slow. If you do it 30.000 times, it will be 30.000
> times slow.
Not only that, but if you're doing it via some application the app has
to wait for Pg to respond before it can send the next query. This adds
even more delay, as do all the processor switches between Pg and your
application.
If you really must issue individual DELETE commands one-by-one, I
*think* you can use synchronous_commit=off or
SET LOCAL synchronous_commit TO OFF;
See:
http://www.postgresql.org/docs/current/static/runtime-config-wal.html
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Delete performance
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
2011-05-31 23:11 ` Re: Delete performance Pierre C <lists@peufeu.com>
2011-06-01 01:40 ` Re: Delete performance Craig Ringer <craig@postnewspapers.com.au>
@ 2011-06-01 01:45 ` Jarrod Chesney <jarrod.chesney@gmail.com>
2011-06-02 01:46 ` Re: Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jarrod Chesney @ 2011-06-01 01:45 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Pierre C <lists@peufeu.com>; pgsql-performance
I'm executing 30,000 single delete statements in one transaction.
At this point i'm looking into combining the multiple deletes into one statement and breaking my big transaction into smaller ones of about 100 deletes or so.
On 01/06/2011, at 11:40 AM, Craig Ringer wrote:
> On 1/06/2011 7:11 AM, Pierre C wrote:
>>> If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
>>> takes close to 10 minutes.
>>
>> Do you run those in a single transaction or do you use one transaction
>> per DELETE ?
>>
>> In the latter case, postgres will ensure each transaction is commited to
>> disk, at each commit. Since this involves waiting for the physical I/O
>> to happen, it is slow. If you do it 30.000 times, it will be 30.000
>> times slow.
>
> Not only that, but if you're doing it via some application the app has to wait for Pg to respond before it can send the next query. This adds even more delay, as do all the processor switches between Pg and your application.
>
> If you really must issue individual DELETE commands one-by-one, I *think* you can use synchronous_commit=off or
>
> SET LOCAL synchronous_commit TO OFF;
>
> See:
>
> http://www.postgresql.org/docs/current/static/runtime-config-wal.html
>
>
> --
> Craig Ringer
>
> Tech-related writing at http://soapyfrogs.blogspot.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Delete performance
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
2011-05-31 23:11 ` Re: Delete performance Pierre C <lists@peufeu.com>
2011-06-01 01:40 ` Re: Delete performance Craig Ringer <craig@postnewspapers.com.au>
2011-06-01 01:45 ` Re: Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
@ 2011-06-02 01:46 ` Jarrod Chesney <jarrod.chesney@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jarrod Chesney @ 2011-06-02 01:46 UTC (permalink / raw)
To: pgsql-performance
On 01/06/2011, at 11:45 AM, Jarrod Chesney wrote:
> I'm executing 30,000 single delete statements in one transaction.
>
> At this point i'm looking into combining the multiple deletes into one statement and breaking my big transaction into smaller ones of about 100 deletes or so.
>
> On 01/06/2011, at 11:40 AM, Craig Ringer wrote:
>
>> On 1/06/2011 7:11 AM, Pierre C wrote:
>>>> If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
>>>> takes close to 10 minutes.
>>>
>>> Do you run those in a single transaction or do you use one transaction
>>> per DELETE ?
>>>
>>> In the latter case, postgres will ensure each transaction is commited to
>>> disk, at each commit. Since this involves waiting for the physical I/O
>>> to happen, it is slow. If you do it 30.000 times, it will be 30.000
>>> times slow.
>>
>> Not only that, but if you're doing it via some application the app has to wait for Pg to respond before it can send the next query. This adds even more delay, as do all the processor switches between Pg and your application.
>>
>> If you really must issue individual DELETE commands one-by-one, I *think* you can use synchronous_commit=off or
>>
>> SET LOCAL synchronous_commit TO OFF;
>>
>> See:
>>
>> http://www.postgresql.org/docs/current/static/runtime-config-wal.html
>>
>>
>> --
>> Craig Ringer
>>
>> Tech-related writing at http://soapyfrogs.blogspot.com/
>
Apologies for top posting, Sorry.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Delete performance
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
@ 2011-06-01 06:14 ` Greg Smith <greg@2ndQuadrant.com>
2 siblings, 0 replies; 7+ messages in thread
From: Greg Smith @ 2011-06-01 06:14 UTC (permalink / raw)
To: pgsql-performance
On 05/30/2011 08:08 PM, Jarrod Chesney wrote:
> My database uses joined table inheritance and my server version is 9.0
> I have about 120,000 records in the table that everything else inherits from, if i truncate-cascaded this table it happens almost instantly. If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it takes close to 10 minutes.
>
> My foreign keys to the base table are all set with "ON DELETE CASCADE".
You may also want to make them DEFERRABLE and then use "SET CONSTRAINTS
ALL DEFERRABLE" so that the constraint checking all happens at one
time. This will cause more memory to be used, but all the constraint
related work will happen in a batch.
You mentioned inheritance. That can cause some unexpected problems
sometimes. You might want to do:
EXPLAIN DELETE FROM ...
To see how this is executing. EXPLAIN works fine on DELETE statements,
too, and it may highlight something strange about how the deletion is
happening. If you can, use EXPLAIN ANALYZE, but note that this will
actually execute the statement--the deletion will happen, it's not just
a test.
There may be a problem with the query plan for the deletion that's
actually causing the issue here, such as missing the right indexes. If
you have trouble reading it, http://explain.depesz.com/ is a good web
resources to help break down where the time is going.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-06-02 01:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-31 00:08 Delete performance Jarrod Chesney <jarrod.chesney@gmail.com>
2011-05-31 09:26 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2011-05-31 23:11 ` Pierre C <lists@peufeu.com>
2011-06-01 01:40 ` Craig Ringer <craig@postnewspapers.com.au>
2011-06-01 01:45 ` Jarrod Chesney <jarrod.chesney@gmail.com>
2011-06-02 01:46 ` Jarrod Chesney <jarrod.chesney@gmail.com>
2011-06-01 06:14 ` Greg Smith <greg@2ndQuadrant.com>
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