Hi,
I am experiencing some strange behavior when executing a not too complicated
pl/perl function.
The Function is not too complicated. It does...
1. Selects about 20 Records from Table A ( - loops though the list and
deletes in total about 50k records in Table B2. For each record form Table A it
then selects Records from Table C - loops through these records about 50K in
total - for each runs a query 3 Tables, 10-20M records - inserts a record
in Table B .. about 50K3. Returns some stats on the whole operation (100
records).
I am using PL/Perl for this and everything runs pretty well.
In a single execution i.e. if I only process 1 records the whole process is
done within a few milliseconds.
The system has 16BG of Memory, and fast disks.
Now here is what I noticed.
a) if I run it in the morning, processing starts very slow, but after a few
thousand records it will speed up until I actually get about 100 records
processed per millisecond.
b) it sometime takes about 5-10k records till i really get up to speed. meaning
the first few hundreds can take up to 1-2 minutes.
c) if i run the same job a few hrs later (we run it twice a day) it generally
runs much faster. Even though we added more data to one of the big tables it
selects from.
d) this however starts again the next day. ( not much data has been changed
between the 2nd run of the day and the first one of the next one, but yet it
will start crawling again.
e) sometime the 2nd run of the day can also be slow and even though the data in
the system does not change by a large margin, run times of the jobs vary by a
large amount. from 17-50 minutes.
Here are my questions:
A) I am running the Auto vacuum. Is it possible that this for some reason slows
down the system?
B) Are the query planner stats re-set over night i.e. date change? This
behavior is consistent. i.e. Every morning the processing is slow, afternoon
generally much faster.
C) Does pl/perl have some memory issues?
D) If i run above job processing 50 records, but stop the process after 10K,
then run it again it will run fast for the first 10K then slow down.
My assumption is that the query planner keeps the stats for a particular record
based on a time stamp (used in a select) and every day it will forget about
that. Is there a way to let the query planner keep stats for a function
permanently?
Thanks for any advise.
Alex
_________________________________________________________________
If It Exists, You'll Find it on SEEK Australia's #1 job site
http://clk.atdmt.com/NMN/go/157639755/direct/01/
