Re: [GENERAL] sudden drop in delete performance
surabhi.ahuja wrote: after my update I had my entire data drectory PGDATA removed, i had done initdb again and did lot of inserts (the inserts have given the similar performance) So you _didn't_ vacuum analyze. You need one right at this point, or the database is optimizing your queries using statistical data that is no longer accurate (If I understand correctly, the statistics describe an empty database - I need to read up on this topic sometime, I use it a lot...). i then do a remove from the db, which is taking time. when i had postgres 8.0.0 i did not turn fsyn off. that time i had moved the pg_xlog directory to a diff partition and created a link from PGDATA to its new location. (i did this because i had heard tha it boosts performanne) I'm rather certain creating a tablespace on that partition would be faster than a symlink, although I suppose the filesystem cache will help a bit. but I am doing the same here also (i mean with Postgres 8.1.5) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: 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: [GENERAL] sudden drop in delete performance
after my update I had my entire data drectory PGDATA removed, i had done initdb again and did lot of inserts (the inserts have given the similar performance) i then do a remove from the db, which is taking time. when i had postgres 8.0.0 i did not turn fsyn off. that time i had moved the pg_xlog directory to a diff partition and created a link from PGDATA to its new location. (i did this because i had heard tha it boosts performanne) but I am doing the same here also (i mean with Postgres 8.1.5) the only thing that i havent changed is the jdbc jar which is still the one that i used with Postgres 8.0.0 (but will that make it slow?) thanks, surabhi From: [EMAIL PROTECTED] on behalf of Ben Trewern Sent: Wed 11/29/2006 7:13 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] sudden drop in delete performance Did you 'vacuum analyze' after you did the update? Make sure you have the correct indexes in place on your foreign keys. Did you have fsync off on your previous installation? Give some more details and I'm sure people will be able to give better advice than me. Regards, Ben surabhi.ahuja [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Nothing else has changed. Please help thanks regards Surabhi
Re: [GENERAL] sudden drop in delete performance
In response to surabhi.ahuja [EMAIL PROTECTED]: after my update I had my entire data drectory PGDATA removed, i had done initdb again and did lot of inserts (the inserts have given the similar performance) Did you do a VACUUM ANALYZE after loading the new database? Are you sure your indexes were recreated correctly? Ben already asked those two questions, in addition to the fsync one. They're important. It would also be useful to provide EXPLAIN ANALYZE output for the query that's giving you trouble. Without it, the people on this list would require some supernatural psychic ability to diagnose your problem. i then do a remove from the db, which is taking time. when i had postgres 8.0.0 i did not turn fsyn off. that time i had moved the pg_xlog directory to a diff partition and created a link from PGDATA to its new location. (i did this because i had heard tha it boosts performanne) but I am doing the same here also (i mean with Postgres 8.1.5) the only thing that i havent changed is the jdbc jar which is still the one that i used with Postgres 8.0.0 (but will that make it slow?) thanks, surabhi From: [EMAIL PROTECTED] on behalf of Ben Trewern Sent: Wed 11/29/2006 7:13 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] sudden drop in delete performance Did you 'vacuum analyze' after you did the update? Make sure you have the correct indexes in place on your foreign keys. Did you have fsync off on your previous installation? Give some more details and I'm sure people will be able to give better advice than me. Regards, Ben surabhi.ahuja [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Nothing else has changed. Please help thanks regards Surabhi -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] sudden drop in delete performance
Did you 'vacuum analyze' after you did the update? Make sure you have the correct indexes in place on your foreign keys. Did you have fsync off on your previous installation? Give some more details and I'm sure people will be able to give better advice than me. Regards, Ben surabhi.ahuja [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Nothing else has changed. Please help thanks regards Surabhi
[GENERAL] sudden drop in delete performance
I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Nothing else has changed. Please help thanks regards Surabhi
Re: [GENERAL] sudden drop in delete performance
surabhi.ahuja [EMAIL PROTECTED] writes: I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Did you remember to re-ANALYZE your tables after transferring your database? If so, what does EXPLAIN ANALYZE show for the slow delete? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
