SQL Woes under 1.6

Linas Vepstas linas@linas.org
Wed, 25 Jul 2001 19:38:05 -0500


On Tue, Jul 24, 2001 at 08:22:39AM -0400, Michael T. Garrison Stuber was heard to remark:
>


I just dropped patches that should fix all of your performance problems.
In 'single-user-update' mode,  data loads from teh sql database should be 
1.5x faster than comparable loads from the XML flat file, at least for 
large datasets (measured at 3.5 seconds on a 700MHz on a dataset wtih 
3K transactions and 150 accounts, vs. 4.8 seconds loading from file).

Hitting the 'save' button should be more or less a no-op.

'exit' will stall for a few seconds, while a 'vacuum analyze' is issued.
the vacuum reclaims storage, and analyze does some performance tuning.
doing this clips off another 20% in performance based on my measurements.

I'm happy -- about 3 days ago, it was taking something like 4 minutes to
do the same. 

(these patches should appear in 1.6.2)

---------
multi-user mode is still a can of performance worms, and will be a good 
bit slower for now. The working assumptions there is that there are 
millions of transactions, so you can't load all of them.  But to load 
only a subset is hard -- it takes about 12 millisecs to load one 
transaction when loading them one at a time. (about 75 transactions 
a second). 90% of the cpu cycles are burned in the postgres server.
so 'basic' tuning won't do the trick ... some different algorithm is
needed.


--linas


-- 
I'm very PUBLIC-MINDED, I'm helping a NIGERIAN get his $25,000,000 back!