Re: Persistent connections to database?

["Chris Travers" <..hidden..>]

On Nov 16, 2007 5:11 PM, Charley Tiggs <..hidden..> wrote:
> Not using departments or projects.  Didn't notice any excessive
> swapping.  I do notice that it takes a very long time to save an invoice
> that has 20 or more line items on it.
>
> Charley
>
>
Ok, there is a known issue with performance which occurs in somewhat
large datasets by LSMB standards.  In some cases, this can cause
sudden drops in performance when creating or posting new items.  Note
that in the cases I witnesed the problem on, it was an issue of only 2
concurrent users (and only 1 GB RAM), but we saw a sudden drop in
performance (what used to take a 3 seconds took 45 seconds to a
minute.  Needless to say that this is unacceptable either for a web
site or a POS installation).  The extra time was all DB query time.
The solution was to add a project and a department and run vacuum
analyze (the issue was a set of 2 nested loop joins between a 500k
table and empty tables-- following the analyze, it reverted again to a
merge join).

I don't know how much thought is given to concurrency in the part of
the planner (I am guessing it does take this into account because
there is a lot of work in making concurrent queries work better
together), but if it is forcing a plan change, this could result in
sudden performance ceilings which are not very forgiving.

Best Wishes,
Chris Travers