Thread: DELETE queries slow down
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.
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 do a vacuum analyze every minute.
What can I do to avoid or at least limit that problem?
I will be graceful to everyone who could help me.
Hi,
Gianluca
Internet Email Confidentiality Footer
********************************************************************************************************************************************
La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i destinatari/autorizzati siete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul contenuto di tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in materia di protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne immediata notizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o riprodurne in alcun modo il contenuto.
This e-mail and its attachments are intended for the addressee(s) only and are confidential and/or may contain legally privileged information. If you have received this message by mistake or are not one of the addressees above, you may take no action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the error which has occurred.
********************************************************************************************************************************************
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
"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
I perform simple INSERT and simple where-clause DELETE. I also force a commit after every DELETE. My two tables are about these: TABLE_A Column_1 | column2 | ....... TABLE_B Column_1B foreign key references TABLE_A(column_1) on delete cascade | ......... Every row in TABLE_B is also present in TABLE_A, but the contrary is not true. After hours in which I insert and delete only on TABLE_A (everything ok), I start inserting also on TABLE_B, exploiting theconstrain on column_1B. After the first DELETE I perform on both tables, each following DELETE lasts for minutes, withcpu usage on 99,9%. I tried also to perform a VACUUM after each DELETE, but had no benefits. Even the EXPLAIN ANALYZE of the DELETE shows no changes with respect to the previous DELETEs: it uses an index on column_1of TABLE_A. My doubt is that the query planner is not enough fast to follow sudden changes in the way I use the DB, is there a way inwhich I can help it to adjust its statistics and its query planner more quickly? My other doubt is that the foreign key on TABLE_B is a problem when I try to delete from TABLE_A, and postgres tries to findnonexistent constrained rows on TABLE_B. Thank you for our help Gianluca Galantucci -----Messaggio originale----- Da: Gregory Stark [mailto:stark@enterprisedb.com] Inviato: lunedì 17 settembre 2007 12.22 A: Heikki Linnakangas Cc: Galantucci Giovanni; pgsql-performance@postgresql.org Oggetto: Re: DELETE queries slow down "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 Internet Email Confidentiality Footer ----------------------------------------------------------------------------------------------------- La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta unicamentealla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i destinatari/autorizzatisiete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul contenutodi tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in materiadi protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne immediatanotizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o riprodurnein alcun modo il contenuto. This e-mail and its attachments are intended for the addressee(s) only and are confidential and/or may contain legally privilegedinformation. If you have received this message by mistake or are not one of the addressees above, you may takeno action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the errorwhich has occurred. -----------------------------------------------------------------------------------------------------
In response to "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>: > I perform simple INSERT and simple where-clause DELETE. > I also force a commit after every DELETE. Do you mean that you delete 1 row at a time? This is slower than batching your deletes. > My two tables are about these: > > TABLE_A > Column_1 | column2 | ....... > > TABLE_B > Column_1B foreign key references TABLE_A(column_1) on delete cascade | ......... > > Every row in TABLE_B is also present in TABLE_A, but the contrary is not true. > After hours in which I insert and delete only on TABLE_A (everything ok), I start inserting also on TABLE_B, exploitingthe constrain on column_1B. After the first DELETE I perform on both tables, each following DELETE lasts for minutes,with cpu usage on 99,9%. > I tried also to perform a VACUUM after each DELETE, but had no benefits. > Even the EXPLAIN ANALYZE of the DELETE shows no changes with respect to the previous DELETEs: it uses an index on column_1of TABLE_A. Are you unable to provide these details? (i.e. output of explain, the actual table schema, actual queries) Without them, the question is very vague and difficult to give advice on. If the planner comes up with the same plan whether running fast or slow, the question is what part of that plan is no longer valid (what part's actual time no longer matches it's predicted time) > My doubt is that the query planner is not enough fast to follow sudden changes in the way I use the DB, is there a wayin which I can help it to adjust its statistics and its query planner more quickly? See: http://www.postgresql.org/docs/8.2/static/sql-analyze.html which also has links to other information on this topic. If you can demonstrate that the statistics are stale, you might benefit from manual analyze after large operations. > My other doubt is that the foreign key on TABLE_B is a problem when I try to delete from TABLE_A, and postgres tries tofind nonexistent constrained rows on TABLE_B. It's quite possible, considering the fact that you seem to be CPU bound. > > -----Messaggio originale----- > Da: Gregory Stark [mailto:stark@enterprisedb.com] > Inviato: lunedì 17 settembre 2007 12.22 > A: Heikki Linnakangas > Cc: Galantucci Giovanni; pgsql-performance@postgresql.org > Oggetto: Re: DELETE queries slow down > > "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. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
No, I perform a single DELETE for about 80000/100000 rows at a time.
Yesterday I tried to raise the parameter default_statistics_target on the file postgresql.conf, setting it to 50 (previously it was set to 10) and everything went ok.
It seems that postgres needs some time to adapt itself to sudden changes in the way I use the DB, maybe to adapt its planner to the new way of use. I think that tuning this parameter could be enough to help postgres update it’s planner faster.
Do you think it could be reasonable?
-----Messaggio originale-----
Da: Bill Moran [mailto:wmoran@collaborativefusion.com]
Inviato: martedì 18 settembre 2007 18.19
A: Galantucci Giovanni
Cc: pgsql-performance@postgresql.org
Oggetto: Re: [PERFORM] R: DELETE queries slow down
In response to "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>:
> I perform simple INSERT and simple where-clause DELETE.
> I also force a commit after every DELETE.
Do you mean that you delete 1 row at a time? This is slower than
batching your deletes.
> My two tables are about these:
>
> TABLE_A
> Column_1 | column2 | .......
>
> TABLE_B
> Column_1B foreign key references TABLE_A(column_1) on delete cascade | .........
>
> Every row in TABLE_B is also present in TABLE_A, but the contrary is not true.
> After hours in which I insert and delete only on TABLE_A (everything ok), I start inserting also on TABLE_B, exploiting the constrain on column_1B. After the first DELETE I perform on both tables, each following DELETE lasts for minutes, with cpu usage on 99,9%.
> I tried also to perform a VACUUM after each DELETE, but had no benefits.
> Even the EXPLAIN ANALYZE of the DELETE shows no changes with respect to the previous DELETEs: it uses an index on column_1 of TABLE_A.
Are you unable to provide these details? (i.e. output of explain, the
actual table schema, actual queries) Without them, the question is
very vague and difficult to give advice on.
If the planner comes up with the same plan whether running fast or slow,
the question is what part of that plan is no longer valid (what part's
actual time no longer matches it's predicted time)
> My doubt is that the query planner is not enough fast to follow sudden changes in the way I use the DB, is there a way in which I can help it to adjust its statistics and its query planner more quickly?
See:
http://www.postgresql.org/docs/8.2/static/sql-analyze.html
which also has links to other information on this topic.
If you can demonstrate that the statistics are stale, you might benefit
from manual analyze after large operations.
> My other doubt is that the foreign key on TABLE_B is a problem when I try to delete from TABLE_A, and postgres tries to find nonexistent constrained rows on TABLE_B.
It's quite possible, considering the fact that you seem to be CPU bound.
>
> -----Messaggio originale-----
> Da: Gregory Stark [mailto:stark@enterprisedb.com]
> Inviato: lunedì 17 settembre 2007 12.22
> A: Heikki Linnakangas
> Cc: Galantucci Giovanni; pgsql-performance@postgresql.org
> Oggetto: Re: DELETE queries slow down
>
> "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.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
Internet Email Confidentiality Footer
********************************************************************************************************************************************
La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i destinatari/autorizzati siete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul contenuto di tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in materia di protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne immediata notizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o riprodurne in alcun modo il contenuto.
This e-mail and its attachments are intended for the addressee(s) only and are confidential and/or may contain legally privileged information. If you have received this message by mistake or are not one of the addressees above, you may take no action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the error which has occurred.
********************************************************************************************************************************************
"Galantucci Giovanni" <Giovanni.Galantucci@italtel.it> wrote: > > No, I perform a single DELETE for about 80000/100000 rows at a time. > > Yesterday I tried to raise the parameter default_statistics_target on the file postgresql.conf, setting it to 50 (previouslyit was set to 10) and everything went ok. > > It seems that postgres needs some time to adapt itself to sudden changes in the way I use the DB, maybe to adapt its plannerto the new way of use. I think that tuning this parameter could be enough to help postgres update it's planner faster. > > Do you think it could be reasonable? Based on the information you've given and the responses you've made, I think you're as likely to roll a 1d6 and get the right solution as anything else. Good luck. > -----Messaggio originale----- > Da: Bill Moran [mailto:wmoran@collaborativefusion.com] > Inviato: martedì 18 settembre 2007 18.19 > A: Galantucci Giovanni > Cc: pgsql-performance@postgresql.org > Oggetto: Re: [PERFORM] R: DELETE queries slow down > > > > In response to "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>: > > > > > I perform simple INSERT and simple where-clause DELETE. > > > I also force a commit after every DELETE. > > > > Do you mean that you delete 1 row at a time? This is slower than > > batching your deletes. > > > > > My two tables are about these: > > > > > > TABLE_A > > > Column_1 | column2 | ....... > > > > > > TABLE_B > > > Column_1B foreign key references TABLE_A(column_1) on delete cascade | ......... > > > > > > Every row in TABLE_B is also present in TABLE_A, but the contrary is not true. > > > After hours in which I insert and delete only on TABLE_A (everything ok), I start inserting also on TABLE_B, exploitingthe constrain on column_1B. After the first DELETE I perform on both tables, each following DELETE lasts for minutes,with cpu usage on 99,9%. > > > I tried also to perform a VACUUM after each DELETE, but had no benefits. > > > Even the EXPLAIN ANALYZE of the DELETE shows no changes with respect to the previous DELETEs: it uses an index on column_1of TABLE_A. > > > > Are you unable to provide these details? (i.e. output of explain, the > > actual table schema, actual queries) Without them, the question is > > very vague and difficult to give advice on. > > > > If the planner comes up with the same plan whether running fast or slow, > > the question is what part of that plan is no longer valid (what part's > > actual time no longer matches it's predicted time) > > > > > My doubt is that the query planner is not enough fast to follow sudden changes in the way I use the DB, is there a wayin which I can help it to adjust its statistics and its query planner more quickly? > > > > See: > > http://www.postgresql.org/docs/8.2/static/sql-analyze.html > > which also has links to other information on this topic. > > > > If you can demonstrate that the statistics are stale, you might benefit > > from manual analyze after large operations. > > > > > My other doubt is that the foreign key on TABLE_B is a problem when I try to delete from TABLE_A, and postgres triesto find nonexistent constrained rows on TABLE_B. > > > > It's quite possible, considering the fact that you seem to be CPU bound. > > > > > > > > -----Messaggio originale----- > > > Da: Gregory Stark [mailto:stark@enterprisedb.com] > > > Inviato: lunedì 17 settembre 2007 12.22 > > > A: Heikki Linnakangas > > > Cc: Galantucci Giovanni; pgsql-performance@postgresql.org > > > Oggetto: Re: DELETE queries slow down > > > > > > "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. > > > > -- > > Bill Moran > > Collaborative Fusion Inc. > > http://people.collaborativefusion.com/~wmoran/ > > > > wmoran@collaborativefusion.com > > Phone: 412-422-3463x4023 > > > Internet Email Confidentiality Footer > ----------------------------------------------------------------------------------------------------- > La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta unicamentealla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i destinatari/autorizzatisiete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul contenutodi tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in materiadi protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne immediatanotizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o riprodurnein alcun modo il contenuto. > > This e-mail and its attachments are intended for the addressee(s) only and are confidential and/or may contain legallyprivileged information. If you have received this message by mistake or are not one of the addressees above, you maytake no action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the errorwhich has occurred. > ----------------------------------------------------------------------------------------------------- > > > > > > > > -- Bill Moran Collaborative Fusion Inc. wmoran@collaborativefusion.com Phone: 412-422-3463x4023
"Galantucci Giovanni" <Giovanni.Galantucci@italtel.it> wrote:
>
> No, I perform a single DELETE for about 80000/100000 rows at a time.
>
> Yesterday I tried to raise the parameter default_statistics_target on the file postgresql.conf, setting it to 50 (previously it was set to 10) and everything went ok.
>
> It seems that postgres needs some time to adapt itself to sudden changes in the way I use the DB, maybe to adapt its planner to the new way of use. I think that tuning this parameter could be enough to help postgres update it's planner faster.
>
> Do you think it could be reasonable?
Based on the information you've given and the responses you've made,
I think you're as likely to roll a 1d6 and get the right solution as
anything else.
Good luck.
<...snip...>
LOL ... hit the nail right on the head ...
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)