On Dec 12, 2007 10:37 AM, <[EMAIL PROTECTED]> 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
-------------------------------------------------------------------------
SF.Net email is sponsored by:
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services
for just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
Ledger-smb-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
