[Twisted-Python] Making ConnectionPool not pool

Hynek Schlawack hs at ox.cx
Tue Nov 27 05:49:11 MST 2012


> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/twisted-python/attachments/20121127/67ad1260/attachment.html>


More information about the Twisted-Python mailing list