Thread: sudden drop in delete performance

"surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in> 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

""surabhi.ahuja"" <surabhi.ahuja@iiitb.ac.in> wrote in message news:8626C1B7EB748940BCDD7596134632BE3986BB@jal.iiitb.ac.in...

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

In response to "surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in>:

> 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: pgsql-general-owner@postgresql.org 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"" <surabhi.ahuja@iiitb.ac.in> wrote in message
news:8626C1B7EB748940BCDD7596134632BE3986BB@jal.iiitb.ac.in...
>     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.

wmoran@collaborativefusion.com
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.
****************************************************************

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
alban@magproductions.nl

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 //