Zope + Postgresql = poor performance
Hi Zopistas, I'm having extremely poor performance with zope 2.1.6 postgresql 7.0.2 pygresqlda 0.3rjr2 actualy zope create just one connection to postgres, so when postgres is executing one query all the rest are queued. Just image the mess So, can one tell me where to look at ? Upgrading zope is a no no at the moment. Please reply directly cause i'm not on the list. Thanx. -- ______________________________________________________ Felipe Alvarez Harnecker. QlSoftware. Tels. 665.99.41 - 09.874.60.17 e-mail: felipe.alvarez@qlsoft.cl http://qlsoft.cl/ http://ql.cl/ ______________________________________________________
Felipe Alvarez Harnecker wrote:
Hi Zopistas,
I'm having extremely poor performance with
zope 2.1.6 postgresql 7.0.2 pygresqlda 0.3rjr2
actualy zope create just one connection to postgres, so when postgres is executing one query all the rest are queued. Just image the mess
So, can one tell me where to look at ?
You are probably running zope single-threaded - this may happen if you start zope up from pcgi ( or just say so in your zope start file) ------------ Hannu
I don't think single threaded necessarily means slower... Perhaps the performance problem lies elsewhere.
From: Hannu Krosing <hannu@tm.ee> Date: Fri, 16 Feb 2001 00:30:18 +0200 To: felipe.alvarez@qlsoft.cl Cc: zope@zope.org, Hung Jung Lu <hungjunglu@hotmail.com> Subject: Re: [Zope] Zope + Postgresql = poor performance
Felipe Alvarez Harnecker wrote:
Hi Zopistas,
I'm having extremely poor performance with
zope 2.1.6 postgresql 7.0.2 pygresqlda 0.3rjr2
actualy zope create just one connection to postgres, so when postgres is executing one query all the rest are queued. Just image the mess
So, can one tell me where to look at ?
You are probably running zope single-threaded - this may happen if you start zope up from pcgi ( or just say so in your zope start file)
------------ Hannu
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
marc lindahl wrote:
I don't think single threaded necessarily means slower... Perhaps the performance problem lies elsewhere.
Unfortunately single-threaded in zope means "when postgres
is executing one query all the rest are queued" as it allocates one connection per thread.
Felipe Alvarez Harnecker wrote:
Hi Zopistas,
I'm having extremely poor performance with
zope 2.1.6 postgresql 7.0.2 pygresqlda 0.3rjr2
actualy zope create just one connection to postgres, so when postgres is executing one query all the rest are queued. Just image the mess
So, can one tell me where to look at ?
You are probably running zope single-threaded - this may happen if you start zope up from pcgi ( or just say so in your zope start file)
------------ Hannu
Meaning all other zope connections (nonpostgres) are blocked too?
From: Hannu Krosing <hannu@tm.ee> Date: Fri, 16 Feb 2001 02:07:24 +0200 To: marc lindahl <marc@bowery.com> Cc: zope@zope.org Subject: Re: [Zope] Zope + Postgresql = poor performance
marc lindahl wrote:
I don't think single threaded necessarily means slower... Perhaps the performance problem lies elsewhere.
Unfortunately single-threaded in zope means "when postgres
is executing one query all the rest are queued" as it allocates one connection per thread.
marc lindahl wrote:
Meaning all other zope connections (nonpostgres) are blocked too?
Currently I guess it blocks all connections in that thread. It used to be much worse - blocking all other threads too It actually depends on the _pgmodule.so - if it gives up thread locks while the query is running. It used not to do it, but newer ones have this around PQexec. /* gets result */ Py_BEGIN_ALLOW_THREADS self->last_result = PQexec(self->pgcnx->cnx, query); Py_END_ALLOW_THREADS And thus they let other threads to run. Felipe's problem may be old version of _pgmodule.c from pygresqlda 0.3rjr2 (and not from postgres distribution) -------- Hannu
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. In fact, threaded-ness has little effect on overall _total_ speed. But, a threaded solution can be very useful where there is a large range of queries running, i.e. some of which require only a short time to execute and some of which require a long time to execute. In a threaded environment, the short running tasks can steal time from the long running tasks. In a single threaded model, once a long running task starts, everyone else is blocked until the big task terminates. This gives a pyschological impression of poor performance, because the ones who are doing small units of work are blocked for arbitrary amounts of times by the hogs on the system. That said, you have two choices, stay with the simpler pygresqlda and make it harder to launch horrible queries. Or install ZPoPyDa and PoPy, which DO thread. (pychopg (sp?) is a new da just released, search freshmeat, also mutithreaded.). 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.
From: Hannu Krosing <hannu@tm.ee> Date: Fri, 16 Feb 2001 00:30:18 +0200 To: felipe.alvarez@qlsoft.cl Cc: zope@zope.org, Hung Jung Lu <hungjunglu@hotmail.com> Subject: Re: [Zope] Zope + Postgresql = poor performance
Felipe Alvarez Harnecker wrote:
Hi Zopistas,
I'm having extremely poor performance with
zope 2.1.6 postgresql 7.0.2 pygresqlda 0.3rjr2
actualy zope create just one connection to postgres, so when postgres is executing one query all the rest are queued. Just image the mess
So, can one tell me where to look at ?
You are probably running zope single-threaded - this may happen if you start zope up from pcgi ( or just say so in your zope start file)
------------ Hannu
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
+-------[ 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|
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.
i have tried this with ZPyGresDa, and it doesn't work well. some users will get a cache not found error. can' t really remember the real error message. but this was quite sometime back, during zope-2.1 era. haven't tried it yet with zope 2.3, or even with PoPy. will do and report back -- http://www.kedai.com.my/kk http://www.kedai.com.my/eZine Strange how laughter looks like crying with no sound, and raindrops taste like tears .. without the pain
On Fri, Feb 16, 2001 at 10:56:23AM +0800, Bak@kedai wrote:
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.
i have tried this with ZPyGresDa, and it doesn't work well. some users will get a cache not found error. can' t really remember the real error message. but this was quite sometime back, during zope-2.1 era.
haven't tried it yet with zope 2.3, or even with PoPy.
This sounds like the cache entry bug that was squashed in 2.2.5b1: - The ZSQLMethods code to handle query caching had a bug that could cause a KeyError in certain cicumstances. -- Martijn Pieters | Software Engineer mailto:mj@digicool.com | Digital Creations http://www.digicool.com/ | Creators of Zope http://www.zope.org/ ---------------------------------------------
participants (7)
-
Andrew Kenneth Milton -
Bak＠kedai -
Felipe Alvarez Harnecker -
Hannu Krosing -
Jim Penny -
marc lindahl -
Martijn Pieters