Zope 2.7 / ZPsyopgDA / PostgreSQL7.3.4 My problem is that we're running a lot of little websites on a single server. Each site has its own zope/zpsyopg instance and its own postgresql database. But all databases are served by a single postmaster (postgresql instance). The issue arising is that I'm reaching max_connections (and as this ties into memory, there are limits to how high I can set this without affecting the performance of the machine), while most connections sit around being idle most of the time. I had a look at pgpool, but if I understand it correctly, it won't help (or will any other pooling solution), as it requires as many connections to the backend, as it's serving to clients. (Or did I get this wrong?) I do understand how I can reduce the number of threads within Zope to reduce the number of connections, but that only goes so far. Is there a way how I can configure my system, so that idle connections expire when not used for a certain time? Or is there another solution to the problem? Thanks DR
David wrote at 2005-6-1 12:12 +0100:
... Is there a way how I can configure my system, so that idle connections expire when not used for a certain time?
In some sense, this is the case -- also not directly controlled by time. Idle connections "expire" when the DA object is flushed from the ZODB cache. The ZODB cache is not controlled by inactivity time but the the number of object that may be active in the cache. If the cache has too many objects, least recently used objects are flushed at transaction boundaries. This gives you 2 easy options to (partially) control the number of connections: * You can reduce the number of Zope worker threads (default 4). Each thread has its own ZODB cache. Therefore, the maximal number of needed connections is number of DA instances times number of threads * You can reduce the ZODB cache capacity (to increase the likelyhood that connections are expired). Your idea to expire connections after some inactivity time it difficult to implement on Zope's side. The standard Zope is passive -- there are no active components such as a scheduler which would supervise timeouts. Of course, a scheduler could be implemented (there are "scheduler" products around), but it would need to know which DAs should be supervised and how to expire them. A better chance would be to implement this on the Postgres Server side (although I do not know whether Postgres supports such an option). Finally, "ZmxODBCDA" (commercial, but reasonably priced) provides a DA which a connection pool. I do not know "ZmxODBCDA" in more detail, but I expect that the pool would be able to effectively restrict the number of concurrently open connections. Have a look at it. Even when you do not use it, you could learn how to implement a pool for yourself. -- Dieter
participants (2)
-
David -
Dieter Maurer