RE: [Zope] Oracle or PostGreSQL connection pool
-----Original Message----- From: Nemeth Miklos [mailto:nemeth@iqsoft.hu]
Do the Oracle or PostgreSQL database adapters and the ZSQL technology support connection pooling?
Oracle Yes, PostgreSQL is not written by DC so I don't know. I suspect yes.
If I create a connection object, supposedly it may only be used by a single thread.
Not on Oracle. The Oracle DA is fully concurrent. Postgres, I don't know. Possible not. Note that if a DA is not concurrent it's not Zope's fault, it's usually because the client libraries to interface that database are not concurrent.
I'd like to use ZSQL methods but my application should be very concurrent.
Then stick with Oracle. -Michel
Michel Pelletier wrote:
-----Original Message----- From: Nemeth Miklos [mailto:nemeth@iqsoft.hu]
Do the Oracle or PostgreSQL database adapters and the ZSQL technology support connection pooling?
Oracle Yes, PostgreSQL is not written by DC so I don't know. I suspect yes.
If I create a connection object, supposedly it may only be used by a single thread.
Not on Oracle. The Oracle DA is fully concurrent. Postgres, I don't know. Possible not. Note that if a DA is not concurrent it's not Zope's fault, it's usually because the client libraries to interface that database are not concurrent.
I guess that what he meant is _not_ concurrency per se, but whetaher you can have multiple _connections_ with different transactions for each running thread. So when I define a DA and I have 3 working threads running concurrently, will Zope automatically get a separate _connection_ for each thread, either form a pool or pre-allocated per thread ? ----------- Hannu
does anyone know how to set properties in the dtml-calendar tag like bordercolor? is it something like <dtml-calendar "bordercolor=ffffff">? kyle
Hi Kyle, Check the archives. There was an extensive coverage and examples.. Adonis -- Adonis El Fakih, Technical Support, www.ayna.com On Sat, 15 Jan 2000, Kyle Burnett wrote:
does anyone know how to set properties in the dtml-calendar tag like bordercolor?
is it something like <dtml-calendar "bordercolor=ffffff">?
kyle
_______________________________________________ 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 )
does anyone know how to set properties in the dtml-calendar tag like bordercolor?
is it something like <dtml-calendar "bordercolor=ffffff">?
kyle
<dtml-calendar> <dtml-call "setCalendar('bordercolor', 'ffffff')"> ... </dtml-calendar>
Hannu Krosing wrote:
Michel Pelletier wrote:
-----Original Message----- From: Nemeth Miklos [mailto:nemeth@iqsoft.hu]
Do the Oracle or PostgreSQL database adapters and the ZSQL technology support connection pooling?
Oracle Yes, PostgreSQL is not written by DC so I don't know. I suspect yes.
If I create a connection object, supposedly it may only be used by a single thread.
Not on Oracle. The Oracle DA is fully concurrent. Postgres, I don't know. Possible not. Note that if a DA is not concurrent it's not Zope's fault, it's usually because the client libraries to interface that database are not concurrent.
I guess that what he meant is _not_ concurrency per se, but whetaher you can have multiple _connections_ with different transactions for each running thread.
If a DA supports "multiple _connections_ with different transactions for each running thread" as you perfectly described, this is enough for me, and I'd regard this a concurrent behaviour.
So when I define a DA and I have 3 working threads running concurrently, will Zope automatically get a separate _connection_ for each thread, either form a pool or pre-allocated per thread ?
I created a test application, which assures this concurrent behaviour of ZOracleDA. I defined a lengthy Oracle operation called Sleep: create or replace procedure Sleep(iDuration in number) is st number; dif number; begin st := dbms_utility.get_time; dif := 0; while dif < iDuration loop dif := dbms_utility.get_time - st; end loop; end; and then I invoked it with different arguments from concurrent clients (ie browser windows). Everything worked fine! The only problem I found was, when I started several lengthy operations (with iDuration of 6000 (60 sec)) and cancelled the requests by pressing the browser's stop button. Zope did not like this test, some requests intended to be short (ie iDuration = 100) returned sometimes after 20 seconds. But Zope did not crashed. And what's more: ZOracleDA autometically commited transactions for successful requests, and rolled back on exceptions. Did you know that! Amazing! On the whole, I am content with ZOracleDA, it should be improved (better stored proc support, bind variable support), but at the moment it can be used in a production environment. The only thing I am a bit afraid of is the stability of ZOracleDA in real production environments. The most terrible thing that may happen with a developer is when his tools behaved OK during initial benchmarks and development, and just before sending the application into production it starts breaking. I would be glad of hearing some success story with ZOracleDA. NM
On 1/15/00 6:45 PM, Hannu Krosing at hannu@tm.ee wrote:
I guess that what he meant is _not_ concurrency per se, but whetaher you can have multiple _connections_ with different transactions for each running thread.
Yes.
So when I define a DA and I have 3 working threads running concurrently, will Zope automatically get a separate _connection_ for each thread, either form a pool or pre-allocated per thread ?
It's much more advanced than either of those, but you will get a seperate connection for each application thread. Jim and I are hoping to publish a small whitepaper on this in the very near future (i.e. within a few days) that covers the connection management scheme. Chris -- | Christopher Petrilli Python Powered Digital Creations, Inc. | petrilli@digicool.com http://www.digicool.com
Hannu Krosing wrote:
Michel Pelletier wrote:
-----Original Message----- From: Nemeth Miklos [mailto:nemeth@iqsoft.hu]
Do the Oracle or PostgreSQL database adapters and the ZSQL technology support connection pooling?
Oracle Yes, PostgreSQL is not written by DC so I don't know. I suspect yes.
If I create a connection object, supposedly it may only be used by a single thread.
Not on Oracle. The Oracle DA is fully concurrent. Postgres, I don't know. Possible not. Note that if a DA is not concurrent it's not Zope's fault, it's usually because the client libraries to interface that database are not concurrent.
I guess that what he meant is _not_ concurrency per se, but whetaher you can have multiple _connections_ with different transactions for each running thread.
So when I define a DA and I have 3 working threads running concurrently, will Zope automatically get a separate _connection_ for each thread, either form a pool or pre-allocated per thread ?
The short answer is yes. Here's a longer explanation. Zope maintains a pool of ZODB database connections. Currently this pool size is set to 7 (for non-version connections). (There ought to be a way to adjust this without hacking Python code, and there will be in 2.2.) Practically speaking, the ZODB connection pool size is the lesser of the number of threads and 7. What does this have to do with Oracle or Postgres connections? When you define a database connection object in Zope, there could be a copy of the connection object for each ZODB connection, depending on how heavily the connection object is used. Each copy of the connection object will have it's own connection to the underlying database. This means that there will be an RDBMS connection pool for each Zope database connection object. The size of this connection pool is the lesser of the number of zope threads and the size of the ZODB connection pool. RDBMS connections will be added and removed from the connection pool based on need. Consider the following example. Suppose we have a Zope process using ZServer with the default thread/pool configuration and a single ZOracleDA database connection object. If the site is handling alot of requests that require access to Oracle, then there will probably be 4 oracle connections, since the default number of Zope threads is 4. If the nuber of requests requiring Oracle support drops off, then will the number of Oracle connections. In fact, the number of Oracle connections could drop to zero. If the number of requests pick up, then so will the number of Oracle connections. This all happens automatically as a by-product of the standard Zope management of ZODB database objects. One caveate is that to get the benefit of connection pooling, the database adapter needs to be fully threaded. This is the case for Oracle. I'm not sure what the statis is for the Postgres DA. We plan to put together a more extensive explanation of this in the form of a white paper or how to. I hope this explanation helps in the mean time. Jim -- Jim Fulton mailto:jim@digicool.com Python Powered! Technical Director (888) 344-4332 http://www.python.org Digital Creations http://www.digicool.com http://www.zope.org Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email address may not be added to any commercial mail list with out my permission. Violation of my privacy with advertising or SPAM will result in a suit for a MINIMUM of $500 damages/incident, $1500 for repeats.
Hi! Is there any way to implement the following thing? I have an Oracle database, and by design every user that logs into the site will be connected to the DB with his/her login/password (that is, his/her Oracle login/password is the same as for WWW). I am considering using Zope for the site, but the question is - how can I create one ZOracleDA connection for every user, and how can I use diferent connections for different users? Cuurently the site is in the process of development, and we are considering different techiniques. Zope is one of them; another possible solution will be CGIs + small server (on WWW side) that will handle long-time pool of connections from WWW to Oracle (CGIs will not be connected to Oracle directly, but will connect to the server, use a connection from the pool, etc.) On Mon, 17 Jan 2000, Jim Fulton wrote:
Here's a longer explanation. Zope maintains a pool of ZODB database connections. Currently this pool size is set to 7 (for non-version connections). (There ought to be a way to adjust this without hacking Python code, and there will be in 2.2.) Practically speaking, the ZODB connection pool size is the lesser of the number of threads and 7. What does this have to do with Oracle or Postgres connections?
When you define a database connection object in Zope, there could be a copy of the connection object for each ZODB connection, depending on how heavily the connection object is used. Each copy of the connection object will have it's own connection to the underlying database. This means that there will be an RDBMS connection pool for each Zope database connection object. The size of this connection pool is the lesser of the number of zope threads and the size of the ZODB connection pool. RDBMS connections will be added and removed from the connection pool based on need.
Consider the following example. Suppose we have a Zope process using ZServer with the default thread/pool configuration and a single ZOracleDA database connection object. If the site is handling alot of requests that require access to Oracle, then there will probably be 4 oracle connections, since the default number of Zope threads is 4. If the nuber of requests requiring Oracle support drops off, then will the number of Oracle connections. In fact, the number of Oracle connections could drop to zero. If the number of requests pick up, then so will the number of Oracle connections. This all happens automatically as a by-product of the standard Zope management of ZODB database objects.
One caveate is that to get the benefit of connection pooling, the database adapter needs to be fully threaded. This is the case for Oracle. I'm not sure what the statis is for the Postgres DA.
We plan to put together a more extensive explanation of this in the form of a white paper or how to. I hope this explanation helps in the mean time.
Jim
-- Jim Fulton mailto:jim@digicool.com Python Powered! Technical Director (888) 344-4332 http://www.python.org Digital Creations http://www.digicool.com http://www.zope.org
Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email address may not be added to any commercial mail list with out my permission. Violation of my privacy with advertising or SPAM will result in a suit for a MINIMUM of $500 damages/incident, $1500 for repeats.
Oleg. ---- Oleg Broytmann Foundation for Effective Policies phd@phd.russ.ru Programmers don't die, they just GOSUB without RETURN.
Oleg Broytmann wrote:
Hi!
Is there any way to implement the following thing?
Yes.
I have an Oracle database, and by design every user that logs into the site will be connected to the DB with his/her login/password (that is, his/her Oracle login/password is the same as for WWW).
I am considering using Zope for the site, but the question is - how can I create one ZOracleDA connection for every user, and how can I use diferent connections for different users?
You can't. However, you could create a new kind of connection object that did this for you. This should be a reasonably straightforward hack of ZOracleDA: - Take a database name through the web. - Maintain an internal pool of actual connections, one per user. If this becomes expensive, then provide some logic for limiting the pool size by retiring unused conections or by only keeping connections for some function of a user hash. Jim -- Jim Fulton mailto:jim@digicool.com Technical Director (888) 344-4332 Python Powered! Digital Creations http://www.digicool.com http://www.python.org Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email address may not be added to any commercial mail list with out my permission. Violation of my privacy with advertising or SPAM will result in a suit for a MINIMUM of $500 damages/incident, $1500 for repeats.
On Tue, 18 Jan 2000, Jim Fulton wrote:
how can I create one ZOracleDA connection for every user, and how can I use diferent connections for different users?
You can't. However, you could create a new kind of connection object that did this for you. This should be a reasonably straightforward hack of ZOracleDA:
Thanks. Will think of it. Oleg. ---- Oleg Broytmann Foundation for Effective Policies phd@phd.russ.ru Programmers don't die, they just GOSUB without RETURN.
On 18 Jan 00, at 11:36, Jim Fulton wrote:
I create one ZOracleDA connection for every user, and how can I use diferent connections for different users?
You can't. However, you could create a new kind of connection object that did this for you. This should be a reasonably straightforward hack of ZOracleDA:
- Take a database name through the web.
- Maintain an internal pool of actual connections, one per user. If this becomes expensive, then provide some logic for limiting the pool size by retiring unused conections or by only keeping connections for some function of a user hash.
I need the opposite functionality. I can only have one open connection to my oracle server to avoid license overflows. How can I limit ZOracleDA to use only one connection, and serialize all requests through the one connection? I know thats slower, but thats the price we're un-willing to pay.. get it? Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax netmeeting: ils://ils.murkworks.com AOL-IM: BKClements
On Tue, 18 Jan 2000, Jim Fulton wrote:
I have an Oracle database, and by design every user that logs into the site will be connected to the DB with his/her login/password (that is, his/her Oracle login/password is the same as for WWW).
I am considering using Zope for the site, but the question is - how can I create one ZOracleDA connection for every user, and how can I use diferent connections for different users?
You can't. However, you could create a new kind of connection object that did this for you. This should be a reasonably straightforward hack of ZOracleDA:
- Take a database name through the web.
Not a database name - it's fixed - but login/password. Well, I can do it, but how can I pass this information to Connection object? Well, recently I read the code for Postgres, MySQL and Oracle adapters, and a lot of code in lib/python/Shared/DC/ZRDB, so I thing I do understand what is Connection object, adapter and so on.` It seems I need to write a Connection object based on Shared.DC.ZRDB.THUNK.THUNKED_TM or Shared.DC.ZRDB.THUNK.TM, maintain a pool of actual database connections, and pass queries down to db_connection.query(). But how can I find the db_connection? It is an object from my pool of connection, but I need to know 2 things: 1) login/password; I thing the pool will be just a dictionary indexed by (login, password) tuples. Does this information available in the query() method? I can stuff login/password into a REQUEST object, but is the REQUEST object available in the query() ? 2) whether the connection is actually running a query; if the query is in the process - I need to open new connection for the same user. How do I ask the connection object "Are you running a query"? Oleg. ---- Oleg Broytmann Foundation for Effective Policies phd@phd.russ.ru Programmers don't die, they just GOSUB without RETURN.
participants (10)
-
al028823＠alumail.uji.es -
Brad Clements -
Christopher Petrilli -
Hannu Krosing -
Jim Fulton -
Kyle Burnett -
Michel Pelletier -
Nemeth Miklos -
Oleg Broytmann -
technews＠egsx.com