Hello,
I have faced a problem in my production server (gevent based); when a
connection attempt is made and MySQL server does not respond (due to listen
backlog full), the whole application hangs. This seems to be because
SQLAlchemy QueuePool does not allow multiple connection attempts
simultaneously. It is waiting for overflow count lock. I suggest that we
allow multiple connection attempts at the same time as I don't see any side
effects of doing so. Details follow.
Details of the problem:
python-sqlalchemy 0.7.4-1
python 2.7.3-0ubuntu2
python-gevent 1.0~b1-1
mysql-server 5.5.34-0ubuntu0.12.0
I opened a gevent backdoor connection to the hung server and created a test
method and ran it.
def test():
import pdb
pdb.set_trace()
import sqlalchemy
import mysql.connector
p = sqlalchemy.pool.manage(mysql.connector, pool_size=128)
p.connect(host='myhost', port=3306, user='myuser', password='mypassword',
buffered=True)
It ran with following trace (excerpt):
(Pdb) step
--Call--
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(931)connect()
-> def connect(self, *args, **kw):
[...]
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(210)connect()
-> return _ConnectionFairy(self).checkout()
[...]
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(371)__init__()
-> rec = self._connection_record = pool._do_get()
[...]
(Pdb)
--Call--
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(671)_do_get()
-> def _do_get(self):
[...]
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(676)_do_get()
-> except sqla_queue.Empty:
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(677)_do_get()
-> if self._max_overflow > -1 and \
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(678)_do_get()
-> self._overflow >= self._max_overflow:
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(687)_do_get()
-> if self._overflow_lock is not None:
(Pdb)
> /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py(688)_do_get()
-> self._overflow_lock.acquire()
(Pdb)
> /usr/lib/pymodules/python2.7/gevent/coros.py(98)acquire()
-> def acquire(self, blocking=True, timeout=None):
Analysis:
Before making a connection attempt the overflow counter lock is obtained
and it is being released only after the connection either succeeds or
fails. In my case, a connection remained hung possibly because of a surge
in new DB connections and SYN backlog overflew on the database server (I
have since added a timeout and tuned my database server to have much higher
backlog). While this connection didn't respond, any new connection attempt
as seen in the above trace waited trying to acquire overflow lock. The
whole application became in capable of serving requests. Cause is this code:
class QueuePool(Pool):
def _do_get(self):
[...]
if self._overflow_lock is not None:
self._overflow_lock.acquire()
if self._max_overflow > -1 and \
self._overflow >= self._max_overflow:
if self._overflow_lock is not None:
self._overflow_lock.release()
return self._do_get()
try:
con = self._create_connection()
self._overflow += 1
finally:
if self._overflow_lock is not None:
self._overflow_lock.release()
return con
Changeset 5f0a7bb cleaned up this code but does not seem to have changed
the flow (behaviour should be the same on trunk). Since disabling the
overflow with max_overflow = -1 does not use lock at all, this behaviour is
possibly an oversight rather than intended behaviour.
Possible solution:
Since the overflow lock seems to be to only maintain overflow count, I
suggest that we increment the counter *before* connection attempt, don't
hold the lock during connection attempt and then decrement the counter in
case of an error. If there is interest in doing this, I shall find time for
a patch and possibly a test case.
Thank you,
--
Sunil Mohan Adapa
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
