Sql Alchemy connection time Out

Question

I am using sqlalchemy with MySQL, and executing query with sql expression. When executing a number of query then it time out. I found an answer but it is not clear to me. Please, any one can help me?

TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

Solution 1

Whenever you create a new session in your code, make sure you close it. Just call session.close()

When I got this error I thought I was closing all of my sessions, but I looked carefully and there was one new method where I wasn't. Closing the session in that method fixed this error for me.

Solution 2

In multi-thread mode, if your concurrent request num is much more than the db connection pool size, it will throw the Queue Pool limit of size 5 overflow 10 reached error. try with this:

engine = create_engine('mysql://', convert_unicode=True, 
pool_size=20, max_overflow=100)

to add the pool size

Add: the method above is not a correct way. The actual reason is that db connection pool is used up, and no other available connection. The most probably situation is you miss to release connection. For example:

@app.teardown_appcontext
def shutdown_session(exception=None):
    db_session.remove()

Comments

[Daniel Kluev]

It depends on how are you using sessions and how do you create your engine. If you use thread-local sessions, and amount of threads is larger than your connections pool size you defined in engine(), you just need to raise size of pool. If you use single-threaded application, then Greg is correct and you are leaking non-closed sessions. You can fix this by either enforcing single-session-per-thread behavior, or ensuring you always close your sessions, for example with with syntax.

[mdob]

is it absolutely necessary to close sessions? Shouldn't be the connection back to pool when session is deleted (out of scope)?

[CadentOrange]

This is something I've found perplexing. Why doesn't close() get called when the session goes out of scope?

[whooot]

Make sure you also close the sessions when exceptions fire. The sqlalchemy docs suggest using a context manager to deal with this (among other solutions).

[Jakobovski]

FYI: This is for flask only.

[Jeff K]

I don't see how to 'close the sessions' as I'm calling engine.execute(statement).