Re: Performance Problems

Hi,
i came across a problem for which I don't really have an explanation. If
anyone has some ideas, help would be greatly appreciated.

Here we go...

I run postgers 7.2.1 on a SunFire, 1 GB memory.
I run a perl batch job (DBI, same machine) that does the following.

1. Iterates through all the records in a table (total 4500 records) by
selecting one record at the time
2. The record then is used to select a couple of records from another
table (total 400,000 records in the table)
and then selects a couple of records (average 10-15), complements
the records with additional information
and stores the information in a temp table.
3. The temp table is then consulted, one record selected and then
inserted in a results table.
the results table is growing by 4,500 records a day. the temp table
is primarily used to apply a logic by sorting
records.

The process:
The whole process loops 4,500 times.
For every loop
- a temp table is cleared
- 10-15 records are inserted into the temp table
- one record is selected from the temp table and inserted into the
results table.
in-between some calculations are done in perl.

the total number of selects is ca. 400,000
total number of inserts of table deletes are 4,500
total number of inserts are ca. 400,000

The problem:
initially if am able to evaluate 5-6 records a second and insert them
into the results table, however towards the end
of the evaluation, the figure drops to under 2. I optimized the code,
use transaction blocks and tuned the database (has 700mb of memory
allocated to it). What I don't quite understand is why the system slows
down. since the process steps are the same. Could perl be the reason ?

Any ideas ?

Thanks
Alex

Alex Paulusberger <alexp(at)meta-bit(dot)com> writes:
> initially if am able to evaluate 5-6 records a second and insert them
> into the results table, however towards the end
> of the evaluation, the figure drops to under 2.

Do you have suitable index(es) on the temp table? Is the system actually
using it/them for your queries?

regards, tom lane

Another thought...

Alex Paulusberger <alexp(at)meta-bit(dot)com> writes:
> The whole process loops 4,500 times.
> For every loop
> - a temp table is cleared

How exactly are you clearing the temp table? DELETE FROM isn't a good
plan because you'll still have dead tuples in there. You could do a
DELETE FROM and then VACUUM, but I'd suggest TRUNCATE instead.

regards, tom lane

Tom,
thanks. I do use DELETE FROM since truncate is not an option in
trnansaction blocks
and within DBI , autocommit has to be turned off when connecting to the DB.

But maybe you are right, and the overhead not being able to truncate
tables is bigger than
not using transaction blocks.

Regards
Alex

Tom Lane wrote:

>Another thought...
>
>Alex Paulusberger <alexp(at)meta-bit(dot)com> writes:
>
>>The whole process loops 4,500 times.
>>For every loop
>>- a temp table is cleared
>>
>
>How exactly are you clearing the temp table? DELETE FROM isn't a good
>plan because you'll still have dead tuples in there. You could do a
>DELETE FROM and then VACUUM, but I'd suggest TRUNCATE instead.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>

Alex,

You're able to alter the autocommit value during processing:

$dbh->{AutoCommit} = 0;
$dbh->do( qq{insert into yucky values ('A')} );
$dbh->commit;

$dbh->{AutoCommit} = 1;

$dbh->do( qq{truncate table yucky} );

$dbh->{AutoCommit} = 0;
$dbh->do( qq{insert into yucky values ('A')} );
$dbh->commit;

Do you need the use of a temp database table? If so have you
looked at using a memory resident table (DBD::AnyData)?

Another thing I've found that helps is to cache reference or lookup data.
Memoize is an easy way to cache. This only works for data that doesn't
change between queries select x from y where z = 1 always returns 'F' ...

Tom

On Fri, Aug 23, 2002 at 11:21:22PM +0900, Alex Paulusberger wrote:
> Tom,
> thanks. I do use DELETE FROM since truncate is not an option in
> trnansaction blocks
> and within DBI , autocommit has to be turned off when connecting to the DB.
>
> But maybe you are right, and the overhead not being able to truncate
> tables is bigger than
> not using transaction blocks.
>
> Regards
> Alex
>
> Tom Lane wrote:
>
> >Another thought...
> >
> >Alex Paulusberger <alexp(at)meta-bit(dot)com> writes:
> >
> >>The whole process loops 4,500 times.
> >>For every loop
> >>- a temp table is cleared
> >>
> >
> >How exactly are you clearing the temp table? DELETE FROM isn't a good
> >plan because you'll still have dead tuples in there. You could do a
> >DELETE FROM and then VACUUM, but I'd suggest TRUNCATE instead.