Thread: 7.0.2 -> 7.1 performance drop

7.0.2 -> 7.1 performance drop

From
Frank Bax
Date:
When I upgraded from 7.0.2 to 7.1, one of my batch scripts went from 10 to
20 minutes?

Some time ago I wrote a script that imports a 1.5M text file into 3 tables
(about 7K, 9K, 9.5K rows).  I had set the script to run nightly when the
input file was changing regularly.  However, the input hasn't changed now
for several weeks.  Last week I upgraded from 7.0.2 to 7.1 and now notice
that the script takes 20min instead of 10min.  I'm running on an OpenBSD
box and pg releases were installed from 2.8 and 2.9 packages respectively.
The OpenBSD upgrade is not a factor, because I upgraded that first and was
running 7.0.2 under 2.9 (script was still 10 min) for about three weeks
before the pg upgrade.  The script just deletes all rows in tables, imports
data as seperate inserts for each row, then does vaccum analyse on the
tables.  I've read that delete/rebuild of indexes will likely improve
performance of this script, but the runtime itself is not really a concern
here, just the change in runtime between releases.  I'm curious what
changed between releases to cause the slower processing?

Frank



Re: 7.0.2 -> 7.1 performance drop

From
"Brent R. Matzelle"
Date:
--- Frank Bax <fbax@sympatico.ca> wrote:
> The script just deletes all rows in
> tables, imports
> data as seperate inserts for each row, then does vaccum
> analyse on the
> tables.  I've read that delete/rebuild of indexes will likely
> improve
> performance of this script, but the runtime itself is not
> really a concern
> here, just the change in runtime between releases.  I'm
> curious what
> changed between releases to cause the slower processing?

Your script performs 3 separate actions.  Do you know which one
(delete, import, vacuum analyze) is slower when using the new
version of PG?  That will help pinpoint the problem so that we
can more easily solve the problem.

Brent

Brent

__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

Re: 7.0.2 -> 7.1 performance drop

From
Frank Bax
Date:
The script spent 20.91 of 21.19 minutes on the import process.
I guess we can rule out delete and vacuum as the source of problem.

Expecting more questions like yours, I did a little more research.  The
import is all INSERT's except for two selects.  I realised I could rewrite
a portion of the code to remove one of those SELECT's.  Guess What no
effect on run-time.  Isn't that curious?  So I pondered that for a while
and thought that if removeing one SELECT didn't make any difference, then
making changes to the second SELECT (which was on same table) wsn't likely
to change anything either.  If that's true then the problem was only
INSERT's.  A little more pondering and I remember that I looked a little
closer at startup parms during the PG upgrade.  I had removed -F option.
Put it back in and runtime dropped to 10 minutes again - problem solved!

Frank

Brent R. Matzelle <bmatzelle@yahoo.com> wrote:
>--- Frank Bax <fbax@sympatico.ca> wrote:
>> The script just deletes all rows in tables, imports
>> data as seperate inserts for each row, then does vacuum
>> analyse on the tables.  I've read that delete/rebuild
>> of indexes will likely improve performance of this
>> script, but the runtime itself is not really a concern
>> here, just the change in runtime between releases.  I'm
>> curious what changed between releases to cause the
>> slower processing?
>
>Your script performs 3 separate actions.  Do you know which one
>(delete, import, vacuum analyze) is slower when using the new
>version of PG?  That will help pinpoint the problem so that we
>can more easily solve the problem.
>
>Brent

Re: 7.0.2 -> 7.1 performance drop

From
Alvaro Herrera
Date:
On Mon, 12 Nov 2001, Frank Bax wrote:

> Expecting more questions like yours, I did a little more research.  The
> import is all INSERT's except for two selects.  I realised I could rewrite
> a portion of the code to remove one of those SELECT's.  Guess What no
> effect on run-time.  Isn't that curious?  So I pondered that for a while
> and thought that if removeing one SELECT didn't make any difference, then
> making changes to the second SELECT (which was on same table) wsn't likely
> to change anything either.  If that's true then the problem was only
> INSERT's.  A little more pondering and I remember that I looked a little
> closer at startup parms during the PG upgrade.  I had removed -F option.
> Put it back in and runtime dropped to 10 minutes again - problem solved!

Maybe you are paying too much transaction overhead. Is your script one
single transaction? If you explicitly make it so by means of enclosing
it all in a BEGIN-COMMIT block (or several, depending on how much INSERT
clauses you have) it will perhaps run much faster.

HTH...

--
Alvaro Herrera (<alvherre[@]atentus.com>)
"Crear es tan dificil como ser libre" (Elsa Triolet)


Re: 7.0.2 -> 7.1 performance drop

From
Doug McNaught
Date:
Frank Bax <fbax@sympatico.ca> writes:

>            A little more pondering and I remember that I looked a little
> closer at startup parms during the PG upgrade.  I had removed -F option.
> Put it back in and runtime dropped to 10 minutes again - problem solved!

You do understand the implications of -F for your data integrity,
right?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: 7.0.2 -> 7.1 performance drop

From
Jason Earl
Date:
Doug McNaught <doug@wireboard.com> writes:

> Frank Bax <fbax@sympatico.ca> writes:
>
> >            A little more pondering and I remember that I looked a little
> > closer at startup parms during the PG upgrade.  I had removed -F option.
> > Put it back in and runtime dropped to 10 minutes again - problem solved!
>
> You do understand the implications of -F for your data integrity,
> right?

Of course, he is rebuilding these tables from scratch every night.  My
guess is that integrity is not much of an issue.

On the other hand Frank, If you could work your inserts into one large
transaction you would probably get quite a bit of the benefits of
removing the -F option without risking the integrity of your database.

Jason

Re: 7.0.2 -> 7.1 performance drop

From
Frank Bax
Date:
At 05:13 PM 11/12/01 -0700, Jason Earl wrote:
>Doug McNaught <doug@wireboard.com> writes:
>
>> Frank Bax <fbax@sympatico.ca> writes:
>>
>> >            A little more pondering and I remember that I looked a little
>> > closer at startup parms during the PG upgrade.  I had removed -F option.
>> > Put it back in and runtime dropped to 10 minutes again - problem solved!
>>
>> You do understand the implications of -F for your data integrity,
>> right?
>
>Of course, he is rebuilding these tables from scratch every night.  My
>guess is that integrity is not much of an issue.
>
>On the other hand Frank, If you could work your inserts into one large
>transaction you would probably get quite a bit of the benefits of
>removing the -F option without risking the integrity of your database.

On the other hand, I do have other tables to worry about on my system...

I read up on -F during upgrade; that's why I decided to take it out;
default install from OpenBSD packages has this option included in suggested
startup script.

I was just confirming that this option accounted for all the time
difference in processing of my script.

I plan to investigate the impact of using transactions without -F in the
near future.

Thanks for the help guys.  I've been unable to access email for a couple of
days.  Sorry about delay in replying.

Frank