Sql Alchemy QueuePool limit overflow

Question

I have a Sql Alchemy application that is returning TimeOut:

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

I read in a different post that this happens when I don't close the session but I don't know if this applies to my code:

I connect to the database in the init.py:

from .dbmodels import (
    DBSession,
    Base,    

engine = create_engine("mysql://" + loadConfigVar("user") + ":" + loadConfigVar("password") + "@" + loadConfigVar("host") + "/" + loadConfigVar("schema"))

#Sets the engine to the session and the Base model class
DBSession.configure(bind=engine)
Base.metadata.bind = engine

Then in another python file I'm gathering some data in two functions but using DBSession that I initialized in init.py:

from .dbmodels import DBSession
from .dbmodels import resourcestatsModel

def getFeaturedGroups(max = 1):

    try:
        #Get the number of download per resource
        transaction.commit()
        rescount = DBSession.connection().execute("select resource_id,count(resource_id) as total FROM resourcestats")

        #Move the data to an array
        resources = []
        data = {}
        for row in rescount:
            data["resource_id"] = row.resource_id
            data["total"] = row.total
            resources.append(data)

        #Get the list of groups
        group_list = toolkit.get_action('group_list')({}, {})
        for group in group_list:
            #Get the details of each group
            group_info = toolkit.get_action('group_show')({}, {'id': group})
            #Count the features of the group
            addFesturedCount(resources,group,group_info)

        #Order the FeaturedGroups by total
        FeaturedGroups.sort(key=lambda x: x["total"],reverse=True)

        print FeaturedGroups
        #Move the data of the group to the result array.
        result = []
        count = 0
        for group in FeaturedGroups:
            group_info = toolkit.get_action('group_show')({}, {'id': group["group_id"]})
            result.append(group_info)
            count = count +1
            if count == max:
                break

        return result
    except:
        return []


    def getResourceStats(resourceID):
        transaction.commit()
        return  DBSession.query(resourcestatsModel).filter_by(resource_id = resourceID).count()

The session variables are created like this:

#Basic SQLAlchemy types
from sqlalchemy import (
    Column,
    Text,
    DateTime,
    Integer,
    ForeignKey
    )
# Use SQLAlchemy declarative type
from sqlalchemy.ext.declarative import declarative_base

#
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    )

#Use Zope' sqlalchemy  transaction manager
from zope.sqlalchemy import ZopeTransactionExtension

#Main plugin session
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

Because the session is created in the init.py and in subsequent code I just use it; at which point do I need to close the session? Or what else do I need to do to manage the pool size?

Where is getResourceStats used? It looks like half the code is missing - can you add it all in or it may be impossible to diagnose the problem. — Tom Dalton
– Tom Dalton, Commented Nov 13, 2014 at 17:53
Does this code work as-is? The import statement looks really strange, in addition to the issues mentioned by @TomDalton. At what point does the error occur? Is this the Minimal, Complete, Verifiable example? IOW, can you reduce this at all (or significantly) and still exhibit the same behavior? There seems to be a lot of business logic in there that is very unlikely the cause of the problem. Try removing it, and posting a full working (syntactically correct) example, and I'd be happy to help. — cod3monk3y
– cod3monk3y, Commented Nov 15, 2014 at 16:33

Minh-Hung Nguyen · Accepted Answer · 2014-11-17 02:15:31Z

86

+50

You can manage pool size by adding parameters pool_size and max_overflow in function create_engine

engine = create_engine("mysql://" + loadConfigVar("user") + ":" + loadConfigVar("password") + "@" + loadConfigVar("host") + "/" + loadConfigVar("schema"), 
                        pool_size=20, max_overflow=0)

Reference is here

You don't need to close the session, but the connection should be closed after your transaction has been done. Replace:

rescount = DBSession.connection().execute("select resource_id,count(resource_id) as total FROM resourcestats")

By:

connection = DBSession.connection()
try:
    rescount = connection.execute("select resource_id,count(resource_id) as total FROM resourcestats")
    #do something
finally:
    connection.close()

Reference is here

Also, notice that mysql's connection that have been stale is closed after a particular period of time (this period can be configured in MySQL, I don't remember the default value), so you need passing pool_recycle value to your engine creation

answered Nov 17, 2014 at 2:15

Minh-Hung Nguyen

1,2841 gold badge16 silver badges21 bronze badges

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

3 Comments

Ludecan Over a year ago

Default timeout for inactive sessions in MySQL is 8 hours for both interactive and non-interactive connections: [1] dev.mysql.com/doc/refman/5.7/en/… [2] dev.mysql.com/doc/refman/5.7/en/…

Rahul Kumar Over a year ago

You mentioned we should close connection and not session. But fastAPI example says we should close session. fastapi.tiangolo.com/tutorial/sql-databases/…

Peter Over a year ago

Closing a session doesn't close the connection, but it does prevent the error. If you import contextlib.closing, then the code just needs to be with closing(Session()) as session: # do stuff

Игор · Accepted Answer · 2018-12-10 23:13:58Z

7

Add following method to your code. It will automatically close all unused/hanging connections and prevent bottleneck in your code. Especially if you are using following syntax Model.query.filter_by(attribute=var).first() and relationships / lazy loading.

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

Documentation on this is available here: http://flask.pocoo.org/docs/1.0/appcontext/

answered Dec 10, 2018 at 23:13

Игор

3753 gold badges10 silver badges24 bronze badges

3 Comments

Callam Delaney Over a year ago

Not request context?

nikk wong Over a year ago

I've tried this in our application and it doesn't fix the issue for us personally.

Georgios Over a year ago

In case you use the Flask-SQLAlchemy module this is already configured, check here.

DKing · Accepted Answer · 2023-10-19 09:52:55Z

1

rescount = DBSession.connection().execute()

rescount is <class 'sqlalchemy.engine.cursor.CursorResult'> type.

You should call the close() func.

edited Oct 19, 2023 at 9:52

DKing

6056 silver badges18 bronze badges

answered Oct 12, 2022 at 11:34

user20221776

111 bronze badge

Collectives™ on Stack Overflow

Sql Alchemy QueuePool limit overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related