[PATCH 6 of 9 cubicweb] [server] dynamically close idle database connections

[Philippe Pepiot]

# HG changeset patch
# User Philippe Pepiot <ph at itsalwaysdns.eu>
# Date 1585575972 -7200
#      Mon Mar 30 15:46:12 2020 +0200
# Node ID e49e03b590e087b88d1d79f0472ccf5eac62c0e2
# Parent  85ae938eed132372bc3726da59b01d8c5b9da5ab
# Available At https://philpep.org/pub/hg/cubicweb
#              hg pull https://philpep.org/pub/hg/cubicweb -r e49e03b590e0
[server] dynamically close idle database connections

When pool hasn't been empty for `idle_timeout` time, start closing connections.

diff --git a/cubicweb/server/repository.py b/cubicweb/server/repository.py
--- a/cubicweb/server/repository.py
+++ b/cubicweb/server/repository.py
@@ -31,6 +31,7 @@ from contextlib import contextmanager
 from logging import getLogger
 import queue
 import threading
+import time
 
 from logilab.common.decorators import cached, clear_cache
 
@@ -171,13 +172,15 @@ class _BaseCnxSet:
 
 class _CnxSetPool(_BaseCnxSet):
 
-    def __init__(self, source, min_size=1, max_size=4):
+    def __init__(self, source, min_size=1, max_size=4, idle_timeout=300):
         super().__init__(source)
         self._cnxsets = []
         self._queue = queue.LifoQueue()
         self.lock = threading.Lock()
         self.min_size = min_size
         self.max_size = max_size
+        self.idle = time.time()
+        self.idle_timeout = idle_timeout
 
         for i in range(min_size):
             self._queue.put_nowait(self._new_cnxset())
@@ -188,6 +191,19 @@ class _CnxSetPool(_BaseCnxSet):
             self._cnxsets.append(cnxset)
         return cnxset
 
+    def _close_idle_cnxset(self):
+        # close connections not being used since idle_timeout
+        if abs(time.time() - self.idle) > self.idle_timeout and self.size() > self.min_size:
+            try:
+                cnxset = self._queue.get_nowait()
+            except queue.Empty:
+                # the queue has been used since we checked it size
+                pass
+            else:
+                cnxset.close(True)
+                with self.lock:
+                    self._cnxsets.remove(cnxset)
+
     def size(self):
         with self.lock:
             return len(self._cnxsets)
@@ -198,8 +214,11 @@ class _CnxSetPool(_BaseCnxSet):
     def get(self):
         try:
             cnxset = self._queue.get_nowait()
+            self._close_idle_cnxset()
             return cnxset
         except queue.Empty:
+            # reset idle time
+            self.idle = time.time()
             if self.max_size and self.size() >= self.max_size:
                 try:
                     return self._queue.get(True, timeout=5)
@@ -213,6 +232,7 @@ class _CnxSetPool(_BaseCnxSet):
 
     def release(self, cnxset):
         self._queue.put_nowait(cnxset)
+        self._close_idle_cnxset()
 
     def __iter__(self):
         with self.lock: