Python flask-SQLAlchemy query time out error

Question

I am using flask-sqlalchemy library in python to management my database(my sql). However when I test query, it give me this error:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 10

Test code is simple:

 def test_query(self, i):
        random = str(i)
        result = Dao.query_user_by_nick("kael")
        print("end query at %s, id: %s" % (datetime.datetime.now(), random), "\n result: " + str(len(result)))



threads = []
    for i in range(0,100):
        test = threading.Thread(target=test_query, args=(self, str(i)))
        threads.append(test)
    for t in threads:
        t.start()

The implement of query_user_by_nick is:

@classmethod
def query_user_by_nick(self,name):
    user = User.query.filter_by(nick_name=name).all()
    return user

When I running the query in multi-thread, this error will always always happen, and I have no idea why it will be. I also insert some data in multi-thread and it works fine. I searched around and nothing inspired me. Someone said the connection will be limited to 15, but this database will be query very frequently and can not be just like 15 query at some time or similar. I am happy if anyone could give me any suggestion. Thanks in advance.

TheArchitect · Accepted Answer · 2017-05-15 22:55:25Z

4

If you think your database can handle it, you should be able to increase the SQLAlchemy pool settings via http://flask-sqlalchemy.pocoo.org/2.1/config/ :

SQLALCHEMY_MAX_OVERFLOW can be increased (from 10) to the max number of concurrent connections you want to allow
SQLALCHEMY_POOL_TIMEOUT can be increased to give more time for a waiting thread to get a connection from the pool if response time is not a major concern

answered May 15, 2017 at 22:55

TheArchitect

2,1731 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kael luo Over a year ago

Thank you, yeah, this do solve problem a bit, but when I increase threads, query time out error will happen too. Luckily I do not need support that much threads. So this is a good solution for me. Thank you

TheArchitect Over a year ago

Glad to read it helps. Please consider accepting the answer to keep the karma going. :)

Collectives™ on Stack Overflow

Python flask-SQLAlchemy query time out error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related