5

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?

davidism
  • 121,510
  • 29
  • 395
  • 339
Martin Thoma
  • 124,992
  • 159
  • 614
  • 958

1 Answers1

2

  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.
Nathan Wailes
  • 9,872
  • 7
  • 57
  • 95
  • Hm, I'll check that tomorrow. If you don't have `threaded=True`, will multiple users then be able to use the site at the same time (even if there are long-running queries)? – Martin Thoma Nov 06 '17 at 21:32
  • My understanding is that your *dev* server will not be able to handle multiple requests concurrently, but that your *prod* server should be using a WSGI server like `gunicorn` or `uWSGI` that will be able to spawn multiple instances of your app. To elaborate on the "your prod should be using a WSGI server": I looked at the Flask source and for the `run()` function it says "Do not use `run()` in a production setting. It is not intended to meet security and performance requirements for a production server." You can also see them say that here: http://flask.pocoo.org/docs/0.12/deploying/ – Nathan Wailes Nov 07 '17 at 03:02
  • Ok, but I don't use app.run anyway (see the link). I use WSGIServer – Martin Thoma Nov 07 '17 at 05:17
  • Hmm...then I guess all I can offer are my "How to investigate the issue" tips. – Nathan Wailes Nov 07 '17 at 06:16
  • In the interest of keeping this question on-topic, I've moved the "my situation" and "how I fixed it" parts of my answer to this question: https://stackoverflow.com/a/47151701/4115031 – Nathan Wailes Nov 07 '17 at 06:57