|
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----- In response to "Galantucci Giovanni"
<Giovanni.Galantucci@xxxxxxxxxx>: > 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@xxxxxxxxxxxxxxxx]
> Inviato: lunedì 17 settembre 2007 12.22 > A: Heikki Linnakangas > Cc: Galantucci Giovanni;
pgsql-performance@xxxxxxxxxxxxxx > Oggetto: Re: DELETE queries slow down > > "Heikki Linnakangas"
<heikki@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxx 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. |
