On 02/17/2016 11:33 AM, Uri Okrent wrote:
Maybe this is a psycopg question and if so please say so. I have a multi-threaded server which maintains a thread-pool (and a corresponding connection pool) for servicing requests. In order to mitigate python's high-water-mark memory usage behavior for large queries, I'm attempting to handle queries in particular using a forked subprocess from the request thread. I'm using the connection invalidation recipe described here (the second one that adds listeners to the Pool): http://docs.sqlalchemy.org/en/latest/core/pooling.html#using-connection-pools-with-multiprocessing It seems to be working correctly -- that is, I can see that the child process is indeed creating a new connection. However, I'm still experiencing intermittent hangs in the child process during connection creation. I've gotten a stack trace using gdb, and I think I understand what is going on but I'm not sure how to protect the critical section. It looks like threads creating connections in the parent process acquire some threading synchronization primitive inside psycopg's _connect function (that's in c so I didn't see the actual source). This apparently occurs occasionally at the same time as the fork, so that the child process never sees the primitive release in the parent process and hangs forever. Interestingly, hangs stop after the server has been running for a while, presumably because the parent process is warmed up and has a full connection pool, and is no longer creating connections. Here is my stack on a hung process: #17 <built-in function _connect> #19 file '/usr/lib64/python2.6/site-packages/psycopg2/__init__.py', in 'connect' #24 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py', in 'connect' #29 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/strategies.py', in 'connect' #33 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in '__connect' #36 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 'get_connection' #39 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 'checkout' #43 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in '_checkout' #47 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 'connect' #50 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py', in '_wrap_pool_connect' #54 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py', in 'contextual_connect' #58 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in '_connection_for_bind' #61 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in '_connection_for_bind' #65 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in 'connection' #70 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '_connection_from_session' #74 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '_execute_and_instances' #77 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '__iter__' #91 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '__getitem__' #99 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 'first' I'm using sqlalchemy 1.0.12 and psycopg 2.5.3 My quick and dirty fix would be to fill the connection pool in the parent process by force before servicing requests,
well I'd not want to transfer a psycopg2 connection from a parent to a child fork, because now that same filehandle is in both processes and you'll get unsafe concurrent access on it.
I've used multiprocessing with psycopg2 for years in a wide variety of scenarios and I've never seen it hanging on the actual psycopg2.connect call. But perhaps that's because I've never called fork() from inside a thread that is not the main thread - if that is what's triggering it here, I'd use a pattern such as a process pool or similar where the forking is done from the main thread ahead of time.
but that is a hack,
and in case of an invalidated connection the server would be susceptible to the issue again while recreating the invalid connection in the parent process. I apparently need to synchronize my fork in one thread with connections being created in others, but I'm not sure how to do that. Any pointers would be great. TIA, Uri -- 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
-- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
