Tag Archives: python flask

How to deal with the exception of database connection pool in Python flash

When testing the flash program, there is an exception
in it

sqlalchemy session pool over flow

When I saw the problem for the first time, manual operation could not be repeated

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:

Referring to the online materials, I did a lot of connection tests with locust and repeated them. From the running records, it seems to be related to the occurrence of an exception

in order to confirm, a function is specially made, which does nothing but throws an exception. As a result, locust only performs a few
connections for ten times, and then starts to reproduce the session poll exception mentioned above. Very accurate

the problem is clear, and the rest becomes simple. After_ Call session.remove in the request procedure to
app.shutdown_ Context process. Then turn off app. Debug

at this time, restart the locust test, execute the connection 4000 times, and the session pool exception never appears again

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

reference links

http://www.karoltomala.com/blog/?p=579

http://gexp.iteye.com/blog/737408

https://www.gaott.info/sqlalchemy-auto-commit/

This is more important:
https://github.com/mitsuhiko/flask/issues/661

The main point of this question:

1 must be in app.teardown_ In the process of appcontext, releasing session

2 should turn off app. Debug = true. When this switch is turned on, if an exception occurs,
app.teardown_ If the appcontext is not guaranteed to be executed, the session pool will soon be exhausted. In future database
access operations, any operation will encounter the above-mentioned exception

conclusion

1 the remove call of session should not be called after_ Request, but in app. Teardown_ Appcontext

2 note that app. Debug = true will cause some program behaviors to be different from those of the formal runtime. We need to pay attention to this