Re: [PERFORM] Is postgresql ca do the job for software deployed in ASP
Hi All, First thanks for your help everyone! Mikael Carneholm wrote: Do you really need to create one *DB* per client - that is, is one schema (in the same DB) per client out of the question? If not, I would look into moving all reference tables (read-only data, constants and such) into a common schema (with read permission granted to each client/role), that way reducing the amount of objects needed to be created/maintained and at the same time reducing the memory requirements (lots of shared objects == lots of reused shared buffers). For my application there is very little info I can share. Maybe less than 10 on 100 actually so I not sure it worth it ... Set the default_tablespace variable per client (login role) also so that the I/O load can be balanced. A system based on Opterons such as the HP DL385 or DL585 with two CPUs (or four if you go for the 585), 8-16Gb of RAM and a decent storage system with 14-28 disks could be worth evaluating. /Mikael I look into the HP DL385 and DL585 on HP site and they are price between 3000 and 15000$$ (base price). Thats quite a difference? So is the HP DL385 with 2 cpus will do the job ? http://h71016.www7.hp.com/dstore/ctoBases.asp?jumpid=re_NSS_dl585storageserveroi=E9CEDBEID=19701SBLID=ProductLineId=450FamilyId=2230LowBaseId=LowPrice=familyviewgroup=405viewtype=Matrix http://h71016.www7.hp.com/dstore/ctoBases.asp?ProductLineId=431FamilyId=2048jumpid=re_hphqiss/Ovw_Buy/DL385 I will look more deeply into them in detail trying to understand something ... Thanks for your help! Best Regards /David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Gagnon Sent: den 3 juli 2006 13:42 To: pgsql-performance@postgresql.org Subject: [PERFORM] Is postgresql ca do the job for software deployed in ASP ou SaaS mode? Hi all, I've been working on my personal project for 3.5 years now. I developed an ERP system in web/java. Now the people I will work with suggest to offers it in Saas mode. Which means my customer will connect to my website and found they ERP software and data there. It's not the deployment I planned initially so if you can just validate some technicals points to be sure it's not crazy using Postgresl here and not a big $$$ db to do the job. Typically I will have 1db per client and around 150 tables per db. So since I hope I didn`t work all those year for nothing .. I expect to have bunch of clients witch means the same amount of db since I have 1 db/client. Can I hope having several hundred of db on 1 db server? Like 250 dbs = 250 client = 360 000 tables !!! So is there a limit for the number of db in the db server ?(this spec is not on the website) What about the performance? Can I expect to have the same performance? Since I put everything on the web I do needs an High Availability infrastructure. I looked into SlonyI and Mammoth to replicate the db but since SlonyI use triggers can I expect it to do the job? Is Mammoth is the only available solution? Last question and not the least I'm reading this performance list for several years now and know suggestion about hardware to run postgresl is discussed. Since I wrote software there severals points about hardware that I don`t understand. Do you have any suggestion of platform to run into my Saas configuration? I do need the WISE one! I'm pretty sure that if I was a big company I would be able throw bunch of but it's not my case. I'm pretty sure it exists out there some piece of Hardware that would do the job perfectly with a fair price. So far I did understand that Postgresql loves Opteron and I have looked into the dl145 series of HP. I did understand that Dell Hardware it`s not reliable. But it's still not clear what should be my requirement for memory, disk, nb cpu, cpu power, etc. I'm pretty sure it`s better to have more slower CPUs that having the latest Opteron available on the market, or more slower servers that having the fastest one... am I right? But agains what it`s the optimal choice? Thanks you to share your knowledge on those point. I do consider using Postgresql is the Smart choice in my project since the beginning but before putting all the money (That I don`t have ..:-)) to buy some hardware I just want to be sure I'm not crazy! Thanks for your help I really appreciate it!! Best Regards /David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Is postgresql ca do the job for software deployed in ASP
Mikael Carneholm wrote: For my application there is very little info I can share. Maybe less than 10 on 100 actually so I not sure it worth it ... Ok, so 90% of the tables are being written to - this either means that your application uses very little constants, or that it has access to constans that are stored somewhere else (eg, a JMX Mbean that's initialized from property files on application startup). Would it be too much work to redesign the DB model to support more than one client? Yes configuration are in property files or somewhere else. I will keep this solution in mind but for now I really think that would really complicated for what it will give in return... I look into the HP DL385 and DL585 on HP site and they are price between 3000 and 15000$$ (base price). Thats quite a difference? So is the HP DL385 with 2 cpus will do the job ? Yeah, there's quite a difference on the price tags between those two. I'd vote for the DL385 since the sockets for the two extra CPU's won't give you linear scalability per $ in the end. A single machine may be cheaper to administrate, but if administration costs are irrelevant/negligible I'd go for several 2-socket machines instead of one 4-socket machine. I do need 2 machines since I need an HA solution. So on top of those question I try to figure out if Slony-I can do the job in my scenario or do I need the Mammoth solution. I'm searching the list right now and there is not a lot of info... :-( Any Idea? So thanks for the info about the DL385 I will look deeply into it ! Best Regards /David /Mikael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Gagnon Sent: den 3 juli 2006 13:42 To: pgsql-performance@postgresql.org Subject: [PERFORM] Is postgresql ca do the job for software deployed in ASP ou SaaS mode? Hi all, I've been working on my personal project for 3.5 years now. I developed an ERP system in web/java. Now the people I will work with suggest to offers it in Saas mode. Which means my customer will connect to my website and found they ERP software and data there. It's not the deployment I planned initially so if you can just validate some technicals points to be sure it's not crazy using Postgresl here and not a big $$$ db to do the job. Typically I will have 1db per client and around 150 tables per db. So since I hope I didn`t work all those year for nothing .. I expect to have bunch of clients witch means the same amount of db since I have 1 db/client. Can I hope having several hundred of db on 1 db server? Like 250 dbs = 250 client = 360 000 tables !!! So is there a limit for the number of db in the db server ?(this spec is not on the website) What about the performance? Can I expect to have the same performance? Since I put everything on the web I do needs an High Availability infrastructure. I looked into SlonyI and Mammoth to replicate the db but since SlonyI use triggers can I expect it to do the job? Is Mammoth is the only available solution? Last question and not the least I'm reading this performance list for several years now and know suggestion about hardware to run postgresl is discussed. Since I wrote software there severals points about hardware that I don`t understand. Do you have any suggestion of platform to run into my Saas configuration? I do need the WISE one! I'm pretty sure that if I was a big company I would be able throw bunch of but it's not my case. I'm pretty sure it exists out there some piece of Hardware that would do the job perfectly with a fair price. So far I did understand that Postgresql loves Opteron and I have looked into the dl145 series of HP. I did understand that Dell Hardware it`s not reliable. But it's still not clear what should be my requirement for memory, disk, nb cpu, cpu power, etc. I'm pretty sure it`s better to have more slower CPUs that having the latest Opteron available on the market, or more slower servers that having the fastest one... am I right? But agains what it`s the optimal choice? Thanks you to share your knowledge on those point. I do consider using Postgresql is the Smart choice in my project since the beginning but before putting all the money (That I don`t have ..:-)) to buy some hardware I just want to be sure I'm not crazy! Thanks for your help I really appreciate it!! Best Regards /David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Is postgresql ca do the job for software deployed in ASP ou SaaS mode?
Hi all, I've been working on my personal project for 3.5 years now. I developed an ERP system in web/java. Now the people I will work with suggest to offers it in Saas mode. Which means my customer will connect to my website and found they ERP software and data there. It's not the deployment I planned initially so if you can just validate some technicals points to be sure it's not crazy using Postgresl here and not a big $$$ db to do the job. Typically I will have 1db per client and around 150 tables per db. So since I hope I didn`t work all those year for nothing .. I expect to have bunch of clients witch means the same amount of db since I have 1 db/client. Can I hope having several hundred of db on 1 db server? Like 250 dbs = 250 client = 360 000 tables !!! So is there a limit for the number of db in the db server ?(this spec is not on the website) What about the performance? Can I expect to have the same performance? Since I put everything on the web I do needs an High Availability infrastructure. I looked into SlonyI and Mammoth to replicate the db but since SlonyI use triggers can I expect it to do the job? Is Mammoth is the only available solution? Last question and not the least I'm reading this performance list for several years now and know suggestion about hardware to run postgresl is discussed. Since I wrote software there severals points about hardware that I don`t understand. Do you have any suggestion of platform to run into my Saas configuration? I do need the WISE one! I'm pretty sure that if I was a big company I would be able throw bunch of but it's not my case. I'm pretty sure it exists out there some piece of Hardware that would do the job perfectly with a fair price. So far I did understand that Postgresql loves Opteron and I have looked into the dl145 series of HP. I did understand that Dell Hardware it`s not reliable. But it's still not clear what should be my requirement for memory, disk, nb cpu, cpu power, etc. I'm pretty sure it`s better to have more slower CPUs that having the latest Opteron available on the market, or more slower servers that having the fastest one... am I right? But agains what it`s the optimal choice? Thanks you to share your knowledge on those point. I do consider using Postgresql is the Smart choice in my project since the beginning but before putting all the money (That I don`t have ..:-)) to buy some hardware I just want to be sure I'm not crazy! Thanks for your help I really appreciate it!! Best Regards /David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Please help with this explain analyse...
Hi all,
I don't understand why this request take so long. Maybe I read the
analyse correctly but It seem that the first line(Nested Loop Left Join
...) take all the time. But I don't understand where the performance
problem is ??? All the time is passed in the first line ...
Thanks for your help!
/David
explain analyse SELECT *
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
INNER JOIN GL ON CS.CSGLNUM = GL.GLNUM AND
GL.GLSOCTRL = 1
INNER JOIN RR ON CR.CRRRNUM = RR.RRNUM
LEFT OUTER JOIN YR ON YR.YRYOTYPE = 'Client' AND
YR.YRYONUM = 'Comptabilite.Recevable.Regroupement' AND YR.YRREF = RR.RRNUM
WHERE CRYPNUM = 'M'
AND CRDATE + INTERVAL '0 days' = '2005-01-28'
Nested Loop Left Join (cost=0.00..42.12 rows=1 width=8143) (actual
time=15.254..200198.524 rows=8335 loops=1)
Join Filter: ((inner.yrref)::text = (outer.rrnum)::text)
- Nested Loop (cost=0.00..36.12 rows=1 width=7217) (actual
time=0.441..2719.821 rows=8335 loops=1)
- Nested Loop (cost=0.00..30.12 rows=1 width=1580) (actual
time=0.242..1837.413 rows=8335 loops=1)
- Nested Loop (cost=0.00..18.07 rows=2 width=752)
(actual time=0.145..548.607 rows=13587 loops=1)
- Seq Scan on gl (cost=0.00..5.21 rows=1
width=608) (actual time=0.036..0.617 rows=1 loops=1)
Filter: (glsoctrl = 1)
- Index Scan using cs_pk on cs (cost=0.00..12.83
rows=2 width=144) (actual time=0.087..444.999 rows=13587 loops=1)
Index Cond: (('M'::text = (cs.csypnum)::text)
AND ((cs.csglnum)::text = (outer.glnum)::text))
- Index Scan using cr_pk on cr (cost=0.00..6.02 rows=1
width=828) (actual time=0.073..0.077 rows=1 loops=13587)
Index Cond: (((cr.crypnum)::text = 'M'::text) AND
(cr.crnum = outer.cscrnum))
Filter: ((crdate + '00:00:00'::interval) =
'2005-01-28 00:00:00'::timestamp without time zone)
- Index Scan using rr_pk on rr (cost=0.00..5.99 rows=1
width=5637) (actual time=0.062..0.069 rows=1 loops=8335)
Index Cond: ((outer.crrrnum)::text = (rr.rrnum)::text)
- Index Scan using yr_idx1 on yr (cost=0.00..5.99 rows=1
width=926) (actual time=0.127..17.379 rows=1154 loops=8335)
Index Cond: (((yryotype)::text = 'Client'::text) AND
((yryonum)::text = 'Comptabilite.Recevable.Regroupement'::text))
Total runtime: 200235.732 ms
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PERFORM] Why the planner is not using the INDEX .
Hi all,
If you can just help my understanding the choice of the planner.
Here is the Query:
explain analyse SELECT IRNUM FROM IR
INNER JOIN IT ON IT.ITIRNUM = ANY ('{1000, 2000}') AND
IT.ITYPNUM = 'M' AND IR.IRYPNUM = IT.ITYPNUM AND IR.IRNUM = IT.ITIRNUM
WHERE IRNUM = ANY ('{1000, 2000}') and IRYPNUM = 'M'
Here is the Query plan:
QUERY PLAN
Hash Join (cost=1142.47..5581.75 rows=87 width=4) (actual
time=125.000..203.000 rows=2 loops=1)
Hash Cond: ("outer".itirnum = "inner".irnum)
- Seq Scan on it
(cost=0.00..3093.45 rows=31646 width=9) (actual
time=0.000..78.000 rows=2 loops=1)
Filter: ((itirnum = ANY ('{1000,2000}'::integer[])) AND
((itypnum)::text = 'M'::text))
- Hash (cost=1142.09..1142.09
rows=151 width=37) (actual time=125.000..125.000 rows=0 loops=1)
- Index Scan using ir_pk on ir (cost=0.00..1142.09 rows=151 width=37) (actual
time=0.000..125.000 rows=2 loops=1)
Index Cond: ((irypnum)::text = 'M'::text)
Filter: (irnum = ANY ('{1000,2000}'::integer[]))
Total
runtime: 203.000 ms
I don't understand why the planner do a Seq Scan (Seq Scan on table
IT ..) instead of passing by the followin index:
ALTER TABLE IT ADD CONSTRAINT IT_IR_FK foreign key
(ITYPNUM,ITIRNUM) references IR (IRYPNUM, IRNUM) ON UPDATE CASCADE;
I tried some stuff but I'm not able to change this behavior. The IT
and IR table may be quite huge (from 20k to 1600k rows) so I think
doing a SEQ SCAN is not a good idea.. am I wrong? Is this query plan
is oki for you ?
Thanks for your help.
/David
P.S.: I'm using postgresql 8.0.3 on windows and I change those setting
in my postgresql.conf :
shared_buffers = 12000 # min 16, at least max_connections*2, 8KB
each
work_mem = 15000 # min 64, size in KB
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
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
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
[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
