On Dec 12, 2007 10:37 AM, <
..hidden..> wrote:
Hi Chris,
I have a little familiarity with the db structure but by no means have a
complete knowledge set about its design. From what little I know it seems
to me that the database structure is the main problem for scalability. (I
know little or no perl so can't comment on optimisations there)
It looks like everything is stored in one main table (acc_trans) instead
of sub-tables. One of the things I like about this is that it makes
understanding the application design much easier and therefore lowers the
learning curve for customisation. If one looks at tinyerp or openbrave
there are over 150 tables! The downside is scalability.
We actually have about 88 at the moment and I am sure that this number will grow. However, the basic accounting information will probably be contained in a few tables which won't be much larger in number than what we have now.
Still 4000 transactions is not really a hell of a lot of processing for a
database. It should handle it no problem. Maybe the perl routines could be
optimised?
First the db is the bottleneck. PostgreSQL goes to 100% CPU utilization for about 10 minutes before the client connection times out from Apache.
Ok, it is a little more complex than this. The basic issue is that approx 4k invoices are paid using approx 170 payments.
This means that: 170 functions calls are made sequentially which:
1) Insert 8000 records into the db in 4000 insert statements.
2) Update another 4000 records. in 4000 update statements.
The problem is that I don't seem to have a good way of merging the data which is input with the insert and select statements. Hence we:
insert two rows
Update one row
Insert two rows
Update one row.
.....
Disk access is not a problem because the vast majority of inserts and writes occur within a few disk pages.
Think of the following pseudocode:
sub insert payment::
for each invoice:
insert payment rows
update invoice-- increment paid
End for each;
end sub;
BTW, this does work with 2700 invoices per page. However, 4000 ties up the server for a little while.....
Best Wishes,
Chris Travers