From: Hancock, D. (DHANCOCK) <DHA...@ar...> - 2002-05-02 22:11:06
|
I only recollect that it was reported that there was no speed boost using MySQL, and nobody else had reported any results. My testing has shown about 10:1 performance boost using DBPool.py to pool connections to Oracle. After looking at the database machine I'm connecting to, I can tell you for certain that the connections are being pooled and allocated in a nice round-robin fashion. It's to be expected that MySQL doesn't show a performance boost, because the "cost of connection" is very low. Oracle's cost of connection is high, and that's why it speeds up so nicely when the connections are pooled. Cheers! -- David Hancock | dha...@ar... | 410-266-4384 -----Original Message----- From: Costas Malamas [mailto:co...@ho...] Sent: Thursday, May 02, 2002 5:57 PM To: web...@li... Subject: [Webware-discuss] DB Connection Pulling I remember there was a conversation here about DBPool.py and how it is not really pooling DB Connections. Has anybody attacked this problem? Thanks, Costas |
From: <el...@in...> - 2002-05-03 02:10:07
|
David Hancock wrote: >>My testing has shown about 10:1 performance boost using DBPool.py to pool connections to Oracle. After looking at the database machine I'm connecting to, I can tell you for certain that the connections are being pooled and allocated in a nice round-robin fashion.<< I agree. When using PostgreSQL on Linux, each connection shows up as a separate thread, and each shows its status (idle, etc.) It's pretty clear tht connections are being pooled, and are being allocated nicely. ...Edmund. |
From: Rolf H. <ro...@gi...> - 2002-05-03 02:20:51
|
At the risk of sounding stupid, how are you folks doing this type of testing? I.e. how are you telling for certain that the connections are being allocated in a round-robin fashion? Thanks, Rolf On Thursday, May 2, 2002, at 10:10 PM, el...@in... wrote: > > David Hancock wrote: > >>> My testing has shown about 10:1 performance boost using DBPool.py to >>> pool > connections to Oracle. After looking at the database machine I'm > connecting > to, I can tell you for certain that the connections are being pooled and > allocated in a nice round-robin fashion.<< > > I agree. When using PostgreSQL on Linux, each connection shows up as a > separate thread, and each shows its status (idle, etc.) It's pretty > clear > tht connections are being pooled, and are being allocated nicely. > > ...Edmund. > > > _______________________________________________________________ > > Have big pipes? SourceForge.net is looking for download mirrors. We > supply > the hardware. You get the recognition. Email Us: > ban...@so... > _______________________________________________ > Webware-discuss mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/webware-discuss > |
From: Chuck E. <ChuckEsterbrook@StockAlerts.com> - 2002-05-03 14:45:40
|
On Thursday 02 May 2002 07:20 pm, Rolf Hanson wrote: > At the risk of sounding stupid, how are you folks doing this type of > testing? > I.e. how are you telling for certain that the connections are being > allocated in a > round-robin fashion? I only ever tested for speed, which I did via some benchmark code I wrote for MiddleKit. DBPool is based on Python's Queue so it probably benefits greatly from that. Hopefully someone can give (or has given) you an answer about their assertion of the round robin. fyi, -Chuck |
From: Geoffrey T. <gta...@na...> - 2002-05-03 18:51:49
|
Karl Putland wrote: > Chuck Esterbrook wrote: > > >On Friday 03 May 2002 06:55 am, Costas Malamas wrote: > > > >>Well, I am convinced, I'll go ahead and try it out. > >> > >>What I am not following from the docs though is how do you use > >>DBPool; I am not familiar with threading (at all!), so I don't get > >>the setup. E.g. do you call DBPool once in your > application (somehow > >>have a central DBPool that you get connections from) or do can you > >>call it whenever you need a connection and it will "know" which > >>connection to give back? I am guessing the former --in which case, > >>where in WK is the best place to put it? subclass Application? > >> > > > >Every time you think of subclassing Application just to store > >application wide resources, you could just as easily (or > more easily) > >get along with a module for this purpose and put the objects > in module > >level variables. > > > >This might also lead to further uses of that module in other > settings. > > > >Surprisingly, there is not yet a Wiki page on this that I see. > > > >Looking back at the MiddleKit code that uses the DBPool, it > simply gets > >a connection like this: > > conn = self._pool.getConnection() > > > >So as you guessed, you ask the pool for a connection > whenever you need > >one. When you lose the reference to the connection, it will > go back in > >the pool for other threads to use. > > > >Maybe the doc string needs further improvement. > > > > > >-Chuck > > > > The one thing that I never figured out was how to use DBPool > in a fasion > that actually allowed the pool to exits once and keep the pool of > connections. > This was my motication to write my own pooling mechanism. > I see now that you store is as an instance variable somewhere in a > module that gets > used in the application. This was not obvious. It would be more > obvious if the > DBPool used it's own class variables or module variables to store the > pool eg. > ConnectionPool stores the pool in a class variable with locks > around the > pool > creation. All instances of ConnectionPool in my case share the same > physical > pool of connections. Doesn't that limit you to a single ConnectionPool? What if you want multiple pools for different databases? Also, my intuition tells me that if I have 3 different instances of ConnectionPool, then they are really 3 different pools. I wouldn't expect them to share the pool. I personally think that shared global resources like a connection pool are best modeled in Python using module-level variables. I agree that DbPool's documentation could be beefed up, but I think the idea is right. - Geoff |
From: Karl P. <ka...@pu...> - 2002-05-03 19:00:40
|
Geoffrey Talvola wrote: >Karl Putland wrote: > >>The one thing that I never figured out was how to use DBPool >>in a fasion >>that actually allowed the pool to exits once and keep the pool of >>connections. >>This was my motication to write my own pooling mechanism. >>I see now that you store is as an instance variable somewhere in a >>module that gets >>used in the application. This was not obvious. It would be more >>obvious if the >>DBPool used it's own class variables or module variables to store the >>pool eg. >>ConnectionPool stores the pool in a class variable with locks >>around the >>pool >>creation. All instances of ConnectionPool in my case share the same >>physical >>pool of connections. >> > >Doesn't that limit you to a single ConnectionPool? What if you want >multiple pools for different databases? > >Also, my intuition tells me that if I have 3 different instances of >ConnectionPool, then they are really 3 different pools. I wouldn't expect >them to share the pool. > >I personally think that shared global resources like a connection pool are >best modeled in Python using module-level variables. I agree that DbPool's >documentation could be beefed up, but I think the idea is right. > >- Geoff > Yes, but that's all I needed. It would be trivial to change ConnectionPool to store the pool in an instance variable. It would then exibit the same behavior as DBPool. Just so I understand it, where do you initialize the pool? In a module that gets imported? eg. #myutils.py someConnectionPool = ConnectionPool(blah) Then from other places, #SomeServlet.py from myutils import someConnectionPool def getData: conn = someConnectionPool.get() cur = conn.cursor() result = cur.execute(someSQL) cur.close() conn.commit() someConnectionPool.put(conn) return result Would the construction of the pool in myutils.py need to be thread protected? --Karl |
From: Luke O. <lu...@ro...> - 2002-05-03 22:39:49
|
> Doesn't that limit you to a single ConnectionPool? What > if you want > multiple pools for different databases? Here's how I do it (similar to a few other folks): module (DatabaseWrap) which holds the connectionpools and provides a few simplified query functions. module level dictionary called _stores keyed by DSN. DatabaseWrap.getConn(dsn) returns a connection for a specific database name, by locking and then checking for the name in _stores. if it doesn't exist, it creates a new DBPool in _stores. def getConn(self, DSN): self.lock() dbpool = _stores.get(DSN, None) if dbpool is None: _stores[DSN] = DBPool(....) dbpool = _stores[DSN] self.unlock() return dbpool.getConnection() This could easily be changed to map a databasename to a group of connection parameters for python DBI syntax, as opposed to using DSNs. (through a configurable file.) Luke ===== ------------------ Sanity is a form of conformity. -John Nash ------------------ __________________________________________________ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com |
From: Geoffrey T. <gta...@na...> - 2002-05-03 19:12:08
|
Karl Putland wrote: > Geoffrey Talvola wrote: > > >Karl Putland wrote: > > > >>The one thing that I never figured out was how to use DBPool > >>in a fasion > >>that actually allowed the pool to exits once and keep the pool of > >>connections. > >>This was my motication to write my own pooling mechanism. > >>I see now that you store is as an instance variable somewhere in a > >>module that gets > >>used in the application. This was not obvious. It would be more > >>obvious if the > >>DBPool used it's own class variables or module variables to > store the > >>pool eg. > >>ConnectionPool stores the pool in a class variable with locks > >>around the > >>pool > >>creation. All instances of ConnectionPool in my case share > the same > >>physical > >>pool of connections. > >> > > > >Doesn't that limit you to a single ConnectionPool? What if you want > >multiple pools for different databases? > > > >Also, my intuition tells me that if I have 3 different instances of > >ConnectionPool, then they are really 3 different pools. I > wouldn't expect > >them to share the pool. > > > >I personally think that shared global resources like a > connection pool are > >best modeled in Python using module-level variables. I > agree that DbPool's > >documentation could be beefed up, but I think the idea is right. > > > >- Geoff > > > > Yes, but that's all I needed. > It would be trivial to change ConnectionPool to store the pool in an > instance variable. > It would then exibit the same behavior as DBPool. > > Just so I understand it, where do you initialize the pool? > In a module that gets imported? eg. > > #myutils.py > someConnectionPool = ConnectionPool(blah) > > > Then from other places, > > #SomeServlet.py > from myutils import someConnectionPool > def getData: > conn = someConnectionPool.get() > cur = conn.cursor() > result = cur.execute(someSQL) > cur.close() > conn.commit() > someConnectionPool.put(conn) > return result Yes, that's the idea. > Would the construction of the pool in myutils.py need to be thread > protected? No, because Python guarantees (with some internal locking) that a module only gets imported once, even if there are multiple threads importing it at the same time. That's one of the reasons why it's a good idea to use module-level variables to store globals like this -- you get thread-safe initialization "for free". If you would rather have the pool be already constructed when the appserver starts up, you can put "import myutils" into the __init__.py file in your context directory. It will then get imported when the appserver starts up, before any requests come in. (It might help make the first request respond a bit faster, that's all.) - Geoff |
From: Edmund L. <el...@in...> - 2002-05-03 19:42:25
|
Geoff wrote: >Doesn't that limit you to a single ConnectionPool? What if you want >multiple pools for different databases? I have some code that builds on a rewrite of DBPool to give multiple named pools to different databases. With the code, one uses a configuration file to specify DB pools, connection strings, and default pools. Then, if you want say a connection from the Oracle pool, you'd ask for it by doing conn = GlobalPool.getConnection('<pool name>') where GlobalPool is an instance of a class called MultiDBPool. Each pool within the global pool can be to the same or different DB, using same or different DB modules and connection strings. I also rewrote DBPool a bit to properly implement the "State" pattern (separation of implementation from public interface). I wasn't happy about the mingling of interface and implementation in DBPool since it made it harder than necssary to add new methods, etc. I'd be happy to post it somewhere for possible inclusion into Webware. Is anybody interested? ...Edmund. |
From: Geoffrey T. <gta...@na...> - 2002-05-06 14:00:04
|
Edmund Lian wrote: > I also rewrote DBPool a bit to properly implement the "State" pattern > (separation of implementation from public interface). I > wasn't happy about > the mingling of interface and implementation in DBPool since > it made it > harder than necssary to add new methods, etc. > > I'd be happy to post it somewhere for possible inclusion into > Webware. Is > anybody interested? > You could upload a tarball to Webware's Patch page on SourceForge, in which case it'll get considered for inclusion in the next Webware release. - Geoff |
From: Edmund L. <el...@in...> - 2002-05-06 15:13:00
|
Geoff wrote: >You could upload a tarball to Webware's Patch page on SourceForge, >in which case it'll get considered for inclusion in the next >Webware release. OK, I'll do this, thanks! ...Edmund. |
From: Chuck E. <ChuckEsterbrook@StockAlerts.com> - 2002-05-02 23:29:44
|
I did, in fact, experience a speedup when using DBPool with MySQL, so I removed that comment from the source code. I use DBPool on my production site (via a MiddleKit setting) with MySQL at 20 connections. -Chuck On Thursday 02 May 2002 03:11 pm, Hancock, David \(DHANCOCK\) wrote: > I only recollect that it was reported that there was no speed boost > using MySQL, and nobody else had reported any results. > > My testing has shown about 10:1 performance boost using DBPool.py to > pool connections to Oracle. After looking at the database machine I'm > connecting to, I can tell you for certain that the connections are > being pooled and allocated in a nice round-robin fashion. > > It's to be expected that MySQL doesn't show a performance boost, > because the "cost of connection" is very low. Oracle's cost of > connection is high, and that's why it speeds up so nicely when the > connections are pooled. > > Cheers! |