[PATCH 5 of 9 cubicweb] [server] implement dynamic database pooler

Philippe Pepiot ph at itsalwaysdns.eu
Tue Mar 31 18:29:00 CEST 2020


# HG changeset patch
# User Philippe Pepiot <ph at itsalwaysdns.eu>
# Date 1585575940 -7200
#      Mon Mar 30 15:45:40 2020 +0200
# Node ID 85ae938eed132372bc3726da59b01d8c5b9da5ab
# Parent  f08a3c909bfaaef5c07376ffc23d35987a33b5c3
# Available At https://philpep.org/pub/hg/cubicweb
#              hg pull https://philpep.org/pub/hg/cubicweb -r 85ae938eed13
[server] implement dynamic database pooler

Opening too much database connection has a cost at startup and also PostgreSQL
as a maximum number of connection (100 by default).
This get worse when starting multiple wsgi processes, since each process has
its own database pool.

Instead of opening `connections-pool-size` connections to the database at
startup, just open one and open more only when needed.

diff --git a/cubicweb/server/repository.py b/cubicweb/server/repository.py
--- a/cubicweb/server/repository.py
+++ b/cubicweb/server/repository.py
@@ -30,6 +30,7 @@ from itertools import chain
 from contextlib import contextmanager
 from logging import getLogger
 import queue
+import threading
 
 from logilab.common.decorators import cached, clear_cache
 
@@ -170,38 +171,53 @@ class _BaseCnxSet:
 
 class _CnxSetPool(_BaseCnxSet):
 
-    def __init__(self, source, size):
+    def __init__(self, source, min_size=1, max_size=4):
         super().__init__(source)
         self._cnxsets = []
         self._queue = queue.LifoQueue()
+        self.lock = threading.Lock()
+        self.min_size = min_size
+        self.max_size = max_size
 
-        for i in range(size):
+        for i in range(min_size):
             self._queue.put_nowait(self._new_cnxset())
 
     def _new_cnxset(self):
         cnxset = super()._new_cnxset()
-        self._cnxsets.append(cnxset)
+        with self.lock:
+            self._cnxsets.append(cnxset)
         return cnxset
 
+    def size(self):
+        with self.lock:
+            return len(self._cnxsets)
+
     def qsize(self):
         return self._queue.qsize()
 
     def get(self):
         try:
-            return self._queue.get(True, timeout=5)
+            cnxset = self._queue.get_nowait()
+            return cnxset
         except queue.Empty:
-            raise Exception('no connections set available after 5 secs, probably either a '
-                            'bug in code (too many uncommited/rolled back '
-                            'connections) or too much load on the server (in '
-                            'which case you can try to set a bigger '
-                            'connections pool size)')
+            if self.max_size and self.size() >= self.max_size:
+                try:
+                    return self._queue.get(True, timeout=5)
+                except queue.Empty:
+                    raise Exception('no connections set available after 5 secs, probably either a '
+                                    'bug in code (too many uncommited/rolled back '
+                                    'connections) or too much load on the server (in '
+                                    'which case you can try to set a bigger '
+                                    'connections pool size)')
+            return self._new_cnxset()
 
     def release(self, cnxset):
         self._queue.put_nowait(cnxset)
 
     def __iter__(self):
-        for cnxset in self._cnxsets:
-            yield cnxset
+        with self.lock:
+            for cnxset in self._cnxsets:
+                yield cnxset
 
     def close(self):
         while True:
@@ -218,7 +234,7 @@ class _CnxSetPool(_BaseCnxSet):
 def get_cnxset(source, size):
     if not size:
         return _BaseCnxSet(source)
-    return _CnxSetPool(source, size)
+    return _CnxSetPool(source, min_size=1, max_size=size)
 
 
 class Repository(object):



More information about the cubicweb-devel mailing list