[Zope] Zope + Postgresql = poor performance

[Andrew Kenneth Milton]

+-------[ Jim Penny ]----------------------
| On Thu, Feb 15, 2001 at 05:50:45PM -0500, marc lindahl wrote:
| > I don't think single threaded necessarily means slower...
| > Perhaps the performance problem lies elsewhere.
| 
| pygresqlda sequentializes queries.  Period.
| 

Not quite true... well its true, but there is a way around it..

| Again, using a threaded DA favors short running queries
| at the expense of long running queries.  Single threaded
| DA favor long running queries at the expense of short running
| queries.  Choose your poison.

It's not so much long running as to what they're accessing, and how.
If the table or the row is locked, then you have to wait, so you end up
with very little concurrency.

If your query is pretty much the same query over and over (like a main
webpage query), you should turn query caching on on the advanced tab for
your ZSQL query. This will tremendously speed up accesses, and leave your
db available for queries which can't be cached (searches etc). If you're
worried about missing an update, set the cache time low to say 60 seconds,
if you're happy to wait 5 mins, set it to 5 mins. All queries with the
same arguments will come out of the cache.

There is also a 2nd trick, which is to have multiple connections to
the same database. You place known long queries on one connection (searches),
short queries on another, and write queries on another. This helps
quite a bit as well. The more you can divide your queries up into
groups and the more simultaneous connections your db can take, the better this
solutions works (beware diminishing returns however).

Low numbers (up to about 4 or 5) work ok with MySQL, anything more and it
starts to thrash a bit. Postgres has a hi scalability so you can pump
this number up a bit further.

-- 
Totally Holistic Enterprises Internet|  P:+61 7 3870 0066   | Andrew Milton
The Internet (Aust) Pty Ltd          |  F:+61 7 3870 4477   | 
ACN: 082 081 472 ABN: 83 082 081 472 |  M:+61 416 022 411   | Carpe Daemon
PO Box 837 Indooroopilly QLD 4068    |akm@theinternet.com.au|