21

I have a situation where sqlalchemy keeps running out of active connections from time to time due to high traffic loads, and I would like to run some tests to verify and optimize the pooling parameters per our use case. However, I cannot find a straightforward way of polling for the count of active connections.

Current setup is on the lines:

args = ...
mapping = {
        'pool_size': 10,
        'max_overflow': 10,
        'pool_timeout': 30,
        'pool_recycle': 1800
    }
engine = sqlalchemy.create_engine(*args, **mapping)

The max connections on the MySQL server is set to 200 and there are about 20 web servers and celery boxes total connecting to it.

Priyeshj
  • 1,295
  • 2
  • 17
  • 32

1 Answers1

25

The default QueuePool has a status method that returns the following:

def status(self):
        return "Pool size: %d  Connections in pool: %d "\
            "Current Overflow: %d Current Checked out "\
            "connections: %d" % (self.size(),
                                 self.checkedin(),
                                 self.overflow(),
                                 self.checkedout())

Pool.checkedout() will return the number of checked out connections.

Shantanu
  • 5
  • 3
jumbopap
  • 3,969
  • 5
  • 27
  • 47
  • github link is dead. – PankajKushwaha Jul 28 '20 at 04:20
  • 1
    Here's a more permanent Github link to the status function: https://github.com/sqlalchemy/sqlalchemy/blob/d5c89a541f5233baf6b6a7498746820caa7b407f/lib/sqlalchemy/pool/impl.py#L193 . Note that the newest version of the function can be seen at https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/pool/impl.py . – L42 Sep 18 '20 at 07:40
  • 11
    From the engine `engine = sqlalchemy.create_engine(*args, **mapping)` -> `engine.pool.status()` – Neara Nov 12 '20 at 14:58