CODEWITHSUNDEEP
pythonmysqlflasksqlalchemyflask-sqlalchemy
Martin Thoma
Martin Thoma

Reputation: 136635

How can I investigate SQLAlchemy QueuePool limit overflow?

I get

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

I have seen the questions:

as well as their answers. I tried them and I tried to reproduce my problem in a very simplified codebase. I can't reproduce it.

What can I do to find / fix the problem?

For example, can I get the current "usage" of the QueuePool within Python?

(Also, but probably better another question: Why shouldn't I set the queue overflow to -1?)

What I didn't try so far is setting app.config['SQLALCHEMY_POOL_RECYCLE'] (to be set here). I guess I would have to set it to a low value in order to fix this problem, but I might get timeouts in other places then, right?

Upvotes: 6

Views: 7508

Answers (1)

Nathan Wailes
Nathan Wailes

Reputation: 12252

  1. Run show PROCESSLIST against your MySQL database to show the connections / pool / usage.

    • In PyCharm, open the "Database" sidebar (usually on the right side), then right-click on your database / schema and select "Open Console". Paste in the command above and click the green "Execute" button.
    • What I noticed was that when I was using the API, each API call would open up a separate connection, and when they hit 20 connections, the next API call would cause the error, which would say TimeoutError: QueuePool limit of size 10 overflow 10 reached (10 + 10 = 20).
    • When I got rid of threaded=True, I only ever saw one connection open.

  2. Commit or stash all of your existing changes, then create a branch and start stripping out code / simplifying your app piece-by-piece and seeing if the issue still pops up.

    • I didn't use this technique in this case, but I've used it successfully when dealing with other seriously-hard-to-track-down bugs like this.
    • Since you mentioned not being able to reproduce the issue with a simple example, I figured it'd be worth suggesting this technique as a way of arriving at a minimal example.

  3. (In combination with #2) Try reducing the SQLALCHEMY_POOL_SIZE Flask-SQLAlchemy setting to make it quicker to reproduce the bug.

    • The idea here is to make the crash happen sooner.
    • I didn't do this in this case.

Upvotes: 2

Related Questions