pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDELETE queries slow down
3+ messages / 3 participants
[nested] [flat]
* DELETE queries slow down
@ 2007-09-17 07:48 Galantucci Giovanni <Giovanni.Galantucci@italtel.it>
0 siblings, 1 reply; 3+ messages in thread
From: Galantucci Giovanni @ 2007-09-17 07:48 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have a problem with DELETE performance with postgres 7.4.
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DELETE queries slow down
@ 2007-09-17 09:18 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Galantucci Giovanni <Giovanni.Galantucci@italtel.it>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2007-09-17 09:18 UTC (permalink / raw)
To: Galantucci Giovanni <Giovanni.Galantucci@italtel.it>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DELETE queries slow down
@ 2007-09-17 10:21 Gregory Stark <stark@enterprisedb.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Gregory Stark @ 2007-09-17 10:21 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Galantucci Giovanni <Giovanni.Galantucci@italtel.it>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-09-17 10:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-17 07:48 DELETE queries slow down Galantucci Giovanni <Giovanni.Galantucci@italtel.it>
2007-09-17 09:18 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-09-17 10:21 ` Gregory Stark <stark@enterprisedb.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