From b64a3dd87a4204ce6d4f2793a7a3f7fbf72eb01f Mon Sep 17 00:00:00 2001 From: Taem Park Date: Tue, 21 Aug 2018 10:54:45 -0400 Subject: Add LIFO for connection pooling Added new "lifo" mode to :class:`.QueuePool`, typically enabled by setting the flag :paramref:`.create_engine.pool_use_lifo` to True. "lifo" mode means the same connection just checked in will be the first to be checked out again, allowing excess connections to be cleaned up from the server side during periods of the pool being only partially utilized. Pull request courtesy Taem Park. Change-Id: Idb5e299c5082b3e6b547bd03022acf65fdc34f35 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/467 --- lib/sqlalchemy/engine/__init__.py | 15 +++++++++++++++ lib/sqlalchemy/engine/strategies.py | 3 ++- lib/sqlalchemy/pool/impl.py | 19 +++++++++++++++++-- lib/sqlalchemy/util/queue.py | 13 +++++++++++-- 4 files changed, 45 insertions(+), 5 deletions(-) (limited to 'lib/sqlalchemy') diff --git a/lib/sqlalchemy/engine/__init__.py b/lib/sqlalchemy/engine/__init__.py index b0d765b8e..d7197fe74 100644 --- a/lib/sqlalchemy/engine/__init__.py +++ b/lib/sqlalchemy/engine/__init__.py @@ -399,6 +399,21 @@ def create_engine(*args, **kwargs): up on getting a connection from the pool. This is only used with :class:`~sqlalchemy.pool.QueuePool`. + :param pool_use_lifo=False: use LIFO (last-in-first-out) when retrieving + connections from :class:`.QueuePool` instead of FIFO + (first-in-first-out). Using LIFO, a server-side timeout scheme can + reduce the number of connections used during non- peak periods of + use. When planning for server-side timeouts, ensure that a recycle or + pre-ping strategy is in use to gracefully handle stale connections. + + .. versionadded:: 1.3 + + .. seealso:: + + :ref:`pool_use_lifo` + + :ref:`pool_disconnects` + :param plugins: string list of plugin names to load. See :class:`.CreateEnginePlugin` for background. diff --git a/lib/sqlalchemy/engine/strategies.py b/lib/sqlalchemy/engine/strategies.py index 0ec6aa06f..d4f5185de 100644 --- a/lib/sqlalchemy/engine/strategies.py +++ b/lib/sqlalchemy/engine/strategies.py @@ -123,7 +123,8 @@ class DefaultEngineStrategy(EngineStrategy): 'events': 'pool_events', 'use_threadlocal': 'pool_threadlocal', 'reset_on_return': 'pool_reset_on_return', - 'pre_ping': 'pool_pre_ping'} + 'pre_ping': 'pool_pre_ping', + 'use_lifo': 'pool_use_lifo'} for k in util.get_cls_kwargs(poolclass): tk = translate.get(k, k) if tk in kwargs: diff --git a/lib/sqlalchemy/pool/impl.py b/lib/sqlalchemy/pool/impl.py index c45661895..3058d6247 100644 --- a/lib/sqlalchemy/pool/impl.py +++ b/lib/sqlalchemy/pool/impl.py @@ -30,7 +30,7 @@ class QueuePool(Pool): """ - def __init__(self, creator, pool_size=5, max_overflow=10, timeout=30, + def __init__(self, creator, pool_size=5, max_overflow=10, timeout=30, use_lifo=False, **kw): r""" Construct a QueuePool. @@ -63,6 +63,21 @@ class QueuePool(Pool): :param timeout: The number of seconds to wait before giving up on returning a connection. Defaults to 30. + :param use_lifo: use LIFO (last-in-first-out) when retrieving + connections instead of FIFO (first-in-first-out). Using LIFO, a + server-side timeout scheme can reduce the number of connections used + during non-peak periods of use. When planning for server-side + timeouts, ensure that a recycle or pre-ping strategy is in use to + gracefully handle stale connections. + + .. versionadded:: 1.3 + + .. seealso:: + + :ref:`pool_use_lifo` + + :ref:`pool_disconnects` + :param \**kw: Other keyword arguments including :paramref:`.Pool.recycle`, :paramref:`.Pool.echo`, :paramref:`.Pool.reset_on_return` and others are passed to the @@ -70,7 +85,7 @@ class QueuePool(Pool): """ Pool.__init__(self, creator, **kw) - self._pool = sqla_queue.Queue(pool_size) + self._pool = sqla_queue.Queue(pool_size, use_lifo=use_lifo) self._overflow = 0 - pool_size self._max_overflow = max_overflow self._timeout = timeout diff --git a/lib/sqlalchemy/util/queue.py b/lib/sqlalchemy/util/queue.py index 1958702c7..640f70ea9 100644 --- a/lib/sqlalchemy/util/queue.py +++ b/lib/sqlalchemy/util/queue.py @@ -39,10 +39,12 @@ class Full(Exception): class Queue: - def __init__(self, maxsize=0): + def __init__(self, maxsize=0, use_lifo=False): """Initialize a queue object with a given maximum size. If `maxsize` is <= 0, the queue size is infinite. + + If `use_lifo` is True, this Queue acts like a Stack (LIFO). """ self._init(maxsize) @@ -57,6 +59,8 @@ class Queue: # Notify not_full whenever an item is removed from the queue; # a thread waiting to put is notified then. self.not_full = threading.Condition(self.mutex) + # If this queue uses LIFO or FIFO + self.use_lifo = use_lifo def qsize(self): """Return the approximate size of the queue (not reliable!).""" @@ -196,4 +200,9 @@ class Queue: # Get an item from the queue def _get(self): - return self.queue.popleft() + if self.use_lifo: + # LIFO + return self.queue.pop() + else: + # FIFO + return self.queue.popleft() -- cgit v1.2.1