How to regenerate connections in the QueuePool

[Sébastien LELONG]

Dear SQLAlchemists,

I'm currently developping an app which must deals with temporary database
failures (using MySQL server with SA 0.3.2, with the QueuePool). This is a
Twisted/SQLAlchemy based app. There is a retry mecanism: the app retries the
database access, sleeping a while between the tries. The test case is the
following:

1. The app is running and gets objects which the app must store in the
database. Objects are sent to the app through a twisted's deferred. This
means if an object cannot be stored, the retry mecanism (and its sleep) won't
block other objects process.
2. To test my code, I stop MySQL server while storing objects, thus entering
in the retry mode.
3. The MySQL server is not accessible anymore, and the pool tries to get
connections from its queue for each received object.
4. after a while, the pool cannot get connections anymore as the pool size
limit is reached: "QueuePool limit of size 5 overflow 10 reached, connection
timed out"
5. I restart the MySQL server, but the pool still can't create valid
connection, it gets stuck into the size limit checks.

I'd like to invalidate the whole pool connections, in order this one to
create/get freshly new connections. I tried the QueuePool.dispose() func,
which closes any connection remaining in the queue, but does not change the
value of the overflow: overflow is always > max_overflow, the pool still
cries "QueuePool limit of size 5 overflow 10 reached, connection timed
out"...

Do you have any idea how to "clean" the pool connections in a convenient
way ? Should the QueuePool.dispose() func adjust the overflow value ?

Thanks in advance for your help and advices.

Cheers,

Seb
--
Sébastien LELONG
sebastien.lelong[at]sirloon.net

[Michael Bayer]

i dont totally understand whats going on there. if the connections
cant open and then they all time out, eventually the "connectionrecord"
that is trying to make the connection should get returned to the pool,
once the reference to the connection in your code is released...so you
shouldnt need the dispose() in theory (but ive never tested such a
scenario so theres many reasons i could be wrong).

so to just get dispose() to work correctly, verify for me that this
patch works:

Index: lib/sqlalchemy/pool.py
===================================================================
--- lib/sqlalchemy/pool.py (revision 2152)
+++ lib/sqlalchemy/pool.py (working copy)
@@ -373,7 +373,8 @@
conn.close()
except Queue.Empty:
break
-
+ self._overflow = 0 - self.size()
+
def status(self):
tup = (self.size(), self.checkedin(), self.overflow(),
self.checkedout())
return "Pool size: %d Connections in pool: %d Current
Overflow: %d Current Checked out connections: %d" % tup

and then ill just check it in.

also, the connections you get back from the pool also have an
invalidate() method on them, which will cause them to reconnect the
next time they are accessed. not sure if that helps here.

[Sébastien LELONG]

Thanks Michael for your reply.

> so to just get dispose() to work correctly, verify for me that this
> patch works:

Yes, the patch works perfectly, the overflow is reset to its initial value as
expected.

> i dont totally understand whats going on there. if the connections
> cant open and then they all time out, eventually the "connectionrecord"
> that is trying to make the connection should get returned to the pool,
> once the reference to the connection in your code is released...so you
> shouldnt need the dispose() in theory (but ive never tested such a
> scenario so theres many reasons i could be wrong).

Well you'll find a test case as attachment, which describes what happening in
my app, specifically the retry mecanism. To run the test:

1. python test.py # check if everything runs OK
2. Stop MySQL server
3. re-run the test.py
4. Wait a little (try #2 or #3)
5. Restart the server
6. Or next try, the pool get stuck into its TimeoutError "max pool size
reached blabla".

In the test.py, there is a comment block, where the TimeoutError is catched.
If the pool is then cleaned (calling dispose), everything works ok (with the
patch).

This is why I primarily wanted to use pool.dispose(). Now I dig a little into
the pool.py module. Here's what I found as a possible bug: in the do_get
connection, when SA tries to create_connection() and fails (which is the case
here when mysql server is out), an exception is raised, the connection is not
returned *BUT* the overflow has been set to += 1. So there is a desync
between the overflow value and the real overflow state. This seems to explain
why SA still raise those TimeoutError when trying to reconnect.

Here's a patch which seems to get things work (it includes your previous).
This just adjust the overflow *once* the connection is actually created. See
attachment.

Hope it helps.

[Michael Bayer]

that *ROCKS* ! youre the new connection pool guy. that patch is in
r2158.