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
