[Twisted-Python] Making ConnectionPool not pool
Hi, I have a really bad time with the combination of a low-volume service and adbapi.ConnectionPool, pyodbc, FreeTDS and Sybase. Basically my connections just time out and fail in weird, generic ways like: Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLEndTran)') (but in many others too, there is no real pattern) In my desperation, I’m employing for-loops for the SQL queries now. :( Since there isn’t much traffic (yet) I would like to just make ConnectionPool close the connections and re-open fresh ones, as soon as they are necessary. Is there some straight-forward way to do that? Or any better approach I’ve overlooked? TIA, Hynek
On Tue, Nov 27, 2012 at 4:16 AM, Hynek Schlawack
Hi,
I have a really bad time with the combination of a low-volume service and adbapi.ConnectionPool, pyodbc, FreeTDS and Sybase.
Basically my connections just time out and fail in weird, generic ways like:
Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLEndTran)')
(but in many others too, there is no real pattern)
Have you tried enabling reconnects (cp_reconnect=True)? If "select 1" doesn't work with your database you may also have to pass in custom cp_good_sql.
In my desperation, I’m employing for-loops for the SQL queries now. :(
Since there isn’t much traffic (yet) I would like to just make ConnectionPool close the connections and re-open fresh ones, as soon as they are necessary.
Is there some straight-forward way to do that? Or any better approach I’ve overlooked?
Don't use ConnectionPool at all. Just have a function that does the SQL connect etc usually normal DB-API, and call it with twisted.internet.threads.deferToThread: def dbTxn(x): conn = db.connect(...) cursor = conn.cursor() cursor.execute() result = cursor.fetchall() conn.close() return result deferredResult = deferToThread(dbtxn, argForX) -- Itamar Turner-Trauring, Future Foundries LLC http://futurefoundries.com/ — Twisted consulting, training and support.
I have a really bad time with the combination of a low-volume service and adbapi.ConnectionPool, pyodbc, FreeTDS and Sybase.
Basically my connections just time out and fail in weird, generic ways like:
Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLEndTran)')
(but in many others too, there is no real pattern) Have you tried enabling reconnects (cp_reconnect=True)? If "select 1" doesn't work with your database you may also have to pass in custom cp_good_sql.
Yep, and the connections *do* heal after dying. I don't have to restart twistd, it just takes a few failed requests to warm up again. I tried to do a for loop like: succ = False for _ in range(self.dbpool.max + 1): try: yield self.dbpool.runOperation(self.dbpool.good_sql) succ = True except Exception: pass if not succ: raise pyodbc.Error(b'Found no healthy connection.') …for new connections, but that helped only a bit. I presume once it finds a working connection, it keeps using it. But I still had failure within the session later.
In my desperation, I’m employing for-loops for the SQL queries now. :(
Since there isn’t much traffic (yet) I would like to just make ConnectionPool close the connections and re-open fresh ones, as soon as they are necessary.
Is there some straight-forward way to do that? Or any better approach I’ve overlooked? Don't use ConnectionPool at all. Just have a function that does the SQL connect etc usually normal DB-API, and call it with twisted.internet.threads.deferToThread:
def dbTxn(x): conn = db.connect(...) cursor = conn.cursor() cursor.execute() result = cursor.fetchall() conn.close() return result
deferredResult = deferToThread(dbtxn, argForX)
Sounds like a solid backup plan, thanks! The only reason I’d prefer to stay with pooling is that I very much expect the traffic to rise and wouldn’t really want to change away and go back later. :( Cheers, Hynek
On 12:49 pm, hs@ox.cx wrote:
The only reason I’d prefer to stay with pooling is that I very much expect the traffic to rise and wouldn’t really want to change away and go back later. :(
Then I'd strongly recommend taking a look at twext.enterprise: http://trac.calendarserver.org/browser/CalendarServer/trunk/twext/enterprise There's a good chance this will become part of Twisted as a replacement for twisted.enterprise at some point. So any experience you get with it now should serve you well in the future. Plus, if you find it also can't deal with your situation, then it'd be nice if we knew that now. :) Jean-Paul
On 27/11/12 12:49, Hynek Schlawack wrote:
Yep, and the connections *do* heal after dying. I don't have to restart twistd, it just takes a few failed requests to warm up again.
Yes - one attempt per thread in the pool. It is a pain. But as per recent discussions on the list, there's no consensus about how to handle this.
Am 27.11.2012 um 14:39 schrieb exarkun@twistedmatrix.com:
The only reason I’d prefer to stay with pooling is that I very much expect the traffic to rise and wouldn’t really want to change away and go back later. :( Then I'd strongly recommend taking a look at twext.enterprise:
http://trac.calendarserver.org/browser/CalendarServer/trunk/twext/enterprise
There's a good chance this will become part of Twisted as a replacement for twisted.enterprise at some point. So any experience you get with it now should serve you well in the future. Plus, if you find it also can't deal with your situation, then it'd be nice if we knew that now. :)
Gladly! Do I see it correctly, that the current canonical way to use it, is to svn co it?
participants (4)
-
exarkun＠twistedmatrix.com -
Hynek Schlawack -
Itamar Turner-Trauring -
Phil Mayers