Re: [PERFORM] Performance problem on delete from for 10k rows. May
Hi All, I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. But if I want to delete the content of the table (~10k) it may be long to those 6 selects for each deleted rows. Why are those selects are there ? Are those select really run on each row deleted? I'm running version 7.4.5 on cygwin. I ran the same delete from pgAdminIII and I got 945562ms for all the deletes within the same transaction .. (so I was wrong saying it took less time in PgAdminIII... sorry about this). Do you have any idea why those 6 selects are there? Maybe I can drop indexes before deleting the content of the table. I didn't planned to because tables are quite small and it's more complicated in my environment. And tell me if I'm wrong but if I drop indexed do I have to reload all my stored procedure (to reset the planner related info)??? Remember having read that somewhere.. (was it in the Postgresql General Bit newletter ...anyway) Thanks for your help I really appréciate it :-) /David LOG: duration: 144.000 ms LOG: statement: DELETE FROM YN LOG: duration: 30.000 ms LOG: statement: DELETE FROM YO LOG: statement: SELECT 1 FROM ONLY public.yo x WHERE yotype = $1 AND yonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yn x WHERE ynyotype = $1 AND ynyonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yo x WHERE yotype = $1 AND yonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yr x WHERE yryotype = $1 AND yryonum = $2 FOR UPDATE OF x LOG: duration: 83.000 ms LOG: connection received: host=127.0.0.1 port=2196 LOG: connection authorized: user=admin database=webCatalog LOG: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end; LOG: duration: 2.000 ms LOG: statement: set client_encoding = 'UNICODE' LOG: duration: 0.000 ms LOG: statement: DELETE FROM IY LOG: duration: 71.000 ms LOG: statement: DELETE FROM IA LOG: duration: 17.000 ms LOG: statement: DELETE FROM IQ LOG: duration: 384.000 ms LOG: statement: DELETE FROM IC LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iq x WHERE iqicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ia x WHERE iaicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iy x WHERE iyicnumo = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iy x WHERE iyicnumr = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.il x WHERE ilicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.bd x WHERE bdicnum = $1 FOR UPDATE OF x LOG: duration: 656807.000 msMichael Fuhr wrote: --- DELETE FROM BM; DELETE FROM BD; DELETE FROM BO; DELETE FROM IL; DELETE FROM YR; DELETE FROM YN; DELETE FROM YO; DELETE FROM IY; DELETE FROM IA; DELETE FROM IQ; DELETE FROM IC; Michael Fuhr wrote: On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote: Il get this strange problem when deleting rows from a Java program. Sometime (For what I noticed it's not all the time) the server take almost forever to delete rows from table. Do other tables have foreign key references to the table you're deleting from? If so, are there indexes on the foreign key columns? Do you have triggers or rules on the table? Have you queried pg_locks during the long-lasting deletes to see if the deleting transaction is waiting for a lock on something? I rememeber having tried to delete the content of my table (IC) from PgAdminIII and I took couples of seconds!!! Not minutes. How many records did you delete in this case? If there are foreign key references, how many records were in the referencing tables? How repeatable is the disparity in delete time? A single test case might have been done under different conditions, so it might not mean much. No offense intended, but I remember doesn't carry as much weight as a documented example. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Performance problem on delete from for 10k rows. May
David Gagnon wrote: Hi All, I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. But if I want to delete the content of the table (~10k) it may be long to those 6 selects for each deleted rows. Why are those selects are there ? Are those select really run on each row deleted? You are using hibernate. Hibernate is generating them to lock the tables. I'm running version 7.4.5 on cygwin. I ran the same delete from pgAdminIII and I got 945562ms for all the deletes within the same transaction .. (so I was wrong saying it took less time in PgAdminIII... sorry about this). Do you have any idea why those 6 selects are there? Hibernate Maybe I can drop indexes before deleting the content of the table. I didn't planned to because tables are quite small and it's more complicated in my environment. And tell me if I'm wrong but if I drop indexed do I have to reload all my stored procedure (to reset the planner related info)??? Remember having read that somewhere.. (was it in the Postgresql General Bit newletter ...anyway) Thanks for your help I really appréciate it :-) /David LOG: duration: 144.000 ms LOG: statement: DELETE FROM YN LOG: duration: 30.000 ms LOG: statement: DELETE FROM YO LOG: statement: SELECT 1 FROM ONLY public.yo x WHERE yotype = $1 AND yonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yn x WHERE ynyotype = $1 AND ynyonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yo x WHERE yotype = $1 AND yonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yr x WHERE yryotype = $1 AND yryonum = $2 FOR UPDATE OF x LOG: duration: 83.000 ms LOG: connection received: host=127.0.0.1 port=2196 LOG: connection authorized: user=admin database=webCatalog LOG: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end; LOG: duration: 2.000 ms LOG: statement: set client_encoding = 'UNICODE' LOG: duration: 0.000 ms LOG: statement: DELETE FROM IY LOG: duration: 71.000 ms LOG: statement: DELETE FROM IA LOG: duration: 17.000 ms LOG: statement: DELETE FROM IQ LOG: duration: 384.000 ms LOG: statement: DELETE FROM IC LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iq x WHERE iqicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ia x WHERE iaicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iy x WHERE iyicnumo = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iy x WHERE iyicnumr = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.il x WHERE ilicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.bd x WHERE bdicnum = $1 FOR UPDATE OF x LOG: duration: 656807.000 msMichael Fuhr wrote: --- DELETE FROM BM; DELETE FROM BD; DELETE FROM BO; DELETE FROM IL; DELETE FROM YR; DELETE FROM YN; DELETE FROM YO; DELETE FROM IY; DELETE FROM IA; DELETE FROM IQ; DELETE FROM IC; Michael Fuhr wrote: On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote: Il get this strange problem when deleting rows from a Java program. Sometime (For what I noticed it's not all the time) the server take almost forever to delete rows from table. Do other tables have foreign key references to the table you're deleting from? If so, are there indexes on the foreign key columns? Do you have triggers or rules on the table? Have you queried pg_locks during the long-lasting deletes to see if the deleting transaction is waiting for a lock on something? I rememeber having tried to delete the content of my table (IC) from PgAdminIII and I took couples of seconds!!! Not minutes. How many records did you delete in this case? If there are foreign key references, how many records were in the referencing tables? How repeatable is the disparity in delete time? A single test case might have been done under different conditions, so it might not mean much. No offense intended, but I remember doesn't carry as much weight as a documented example. ---(end of
Re: [PERFORM] Performance problem on delete from for 10k rows. May
Hi I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. I think they come from the FK checking code. Try to run a VACUUM on the IC table just before you delete from the other tables; that should make the checking almost instantaneous (assuming the vacuuming actually empties the table, which would depend on other transactions). I'll try to vaccum first before I start the delete to see if it change something. There is probably a good reason why but I don't understant why in a foreign key check it need to check the date it points to. You delete a row from table IC and do a check for integrity on tables that have foreign keys on IC (make sense). But why checking back IC? I'm pretty sure there is a good reason but it seems to have a big performance impact... In this case. It means it's not really feasable to empty the content of a schema. The table has only 10k .. with a huge table it's not feasible just because the checks on itselft! Is someone can explain why there is this extra check? Is that can be fixed or improved? Thanks for your help /David LOG: duration: 144.000 ms LOG: statement: DELETE FROM YN LOG: duration: 30.000 ms LOG: statement: DELETE FROM YO LOG: statement: SELECT 1 FROM ONLY public.yo x WHERE yotype = $1 AND yonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yn x WHERE ynyotype = $1 AND ynyonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yo x WHERE yotype = $1 AND yonum = $2 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.yr x WHERE yryotype = $1 AND yryonum = $2 FOR UPDATE OF x LOG: duration: 83.000 ms LOG: connection received: host=127.0.0.1 port=2196 LOG: connection authorized: user=admin database=webCatalog LOG: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end; LOG: duration: 2.000 ms LOG: statement: set client_encoding = 'UNICODE' LOG: duration: 0.000 ms LOG: statement: DELETE FROM IY LOG: duration: 71.000 ms LOG: statement: DELETE FROM IA LOG: duration: 17.000 ms LOG: statement: DELETE FROM IQ LOG: duration: 384.000 ms LOG: statement: DELETE FROM IC LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iq x WHERE iqicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ia x WHERE iaicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iy x WHERE iyicnumo = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.iy x WHERE iyicnumr = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.il x WHERE ilicnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x LOG: statement: SELECT 1 FROM ONLY public.bd x WHERE bdicnum = $1 FOR UPDATE OF x LOG: duration: 656807.000 msMichael Fuhr wrote: It would be better to be able to use TRUNCATE to do this, but in 8.0 you can't if the tables have FKs. 8.1 is better on that regard ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Performance problem on delete from for 10k rows. May
On Wed, 16 Mar 2005, David Gagnon wrote: Hi I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. I think they come from the FK checking code. Try to run a VACUUM on the IC table just before you delete from the other tables; that should make the checking almost instantaneous (assuming the vacuuming actually empties the table, which would depend on other transactions). I'll try to vaccum first before I start the delete to see if it change something. There is probably a good reason why but I don't understant why in a foreign key check it need to check the date it points to. You delete a row from table IC and do a check for integrity on tables that have foreign keys on IC (make sense). But why checking back IC? Because in the general case there might be another row which satisfies the constraint added between the delete and the check. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Performance problem on delete from for 10k rows. May
Stephan Szabo wrote: On Wed, 16 Mar 2005, David Gagnon wrote: Hi I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. I think they come from the FK checking code. Try to run a VACUUM on the IC table just before you delete from the other tables; that should make the checking almost instantaneous (assuming the vacuuming actually empties the table, which would depend on other transactions). I'll try to vaccum first before I start the delete to see if it change something. There is probably a good reason why but I don't understant why in a foreign key check it need to check the date it points to. You delete a row from table IC and do a check for integrity on tables that have foreign keys on IC (make sense). But why checking back IC? Because in the general case there might be another row which satisfies the constraint added between the delete and the check. So it's means if I want to reset the shema with DELETE FROM Table statemnets I must first drop indexes, delete the data and then recreate indexes and reload stored procedure. Or I can suspend the foreign key check in the db right. I saw something on this. Is that possible to do this from the JDBC interface? Is there any other options I can consider ? Thanks for your help! /David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance problem on delete from for 10k rows. May
On Wed, 16 Mar 2005, David Gagnon wrote: Stephan Szabo wrote: On Wed, 16 Mar 2005, David Gagnon wrote: Hi I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY public.ic x WHERE icnum = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. I think they come from the FK checking code. Try to run a VACUUM on the IC table just before you delete from the other tables; that should make the checking almost instantaneous (assuming the vacuuming actually empties the table, which would depend on other transactions). I'll try to vaccum first before I start the delete to see if it change something. There is probably a good reason why but I don't understant why in a foreign key check it need to check the date it points to. You delete a row from table IC and do a check for integrity on tables that have foreign keys on IC (make sense). But why checking back IC? Because in the general case there might be another row which satisfies the constraint added between the delete and the check. So it's means if I want to reset the shema with DELETE FROM Table statemnets I must first drop indexes, delete the data and then recreate indexes and reload stored procedure. Or I can suspend the foreign key check in the db right. I saw something on this. Is that possible to do this from the JDBC interface? I think you can remove the constraints and re-add them after which should hopefully be fast (a vacuum on the tables after the delete and before the add might help, but I'm not sure). You could potentially defer the constraint if it were deferrable, but I don't think that would help any. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
Hi all, Il get this strange problem when deleting rows from a Java program. Sometime (For what I noticed it's not all the time) the server take almost forever to delete rows from table. Here It takes 20 minutes to delete the IC table. Java logs: INFO [Thread-386] (Dao.java:227) 2005-03-15 15:38:34,754 : Execution SQL file: resources/ukConfiguration/reset_application.sql DELETE FROM YR INFO [Thread-386] (Dao.java:227) 2005-03-15 15:38:34,964 : Execution SQL file: resources/inventory/item/reset_application.sql DELETE FROM IC INFO [Thread-386] (Dao.java:227) 2005-03-15 15:58:45,072 : Execution SQL file: resources/ukResource/reset_application.sql DELETE FROM RA I get this problem on my dev (Windows/7.4/Cygwin) environment. But now I see that it's also have this problem on my production env. Yes I tought I was maybe just a cygwin/Windows problem .. apparently not :- On my dev I can see the Postgresql related process running at almost 50% of CPU usage for all the time. So I suppose it's something inside Postgresql. I rememeber having tried to delete the content of my table (IC) from PgAdminIII and I took couples of seconds!!! Not minutes. So the process don't jam but take time .. any Idea what postgresql is doing during this time?? If you have any idea on what the problem could be... I really appreciate it. Thanks for any help! /David ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance problem on delete from for 10k rows. May takes 20 minutes through JDBC interface
On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote: Il get this strange problem when deleting rows from a Java program. Sometime (For what I noticed it's not all the time) the server take almost forever to delete rows from table. Do other tables have foreign key references to the table you're deleting from? If so, are there indexes on the foreign key columns? Do you have triggers or rules on the table? Have you queried pg_locks during the long-lasting deletes to see if the deleting transaction is waiting for a lock on something? I rememeber having tried to delete the content of my table (IC) from PgAdminIII and I took couples of seconds!!! Not minutes. How many records did you delete in this case? If there are foreign key references, how many records were in the referencing tables? How repeatable is the disparity in delete time? A single test case might have been done under different conditions, so it might not mean much. No offense intended, but I remember doesn't carry as much weight as a documented example. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
