[PATCH 9 of 9 cubicweb] [server] Make connection pooler configurable and set better default values

[Philippe Pepiot]

# HG changeset patch
# User Philippe Pepiot <ph at itsalwaysdns.eu>
# Date 1585671725 -7200
#      Tue Mar 31 18:22:05 2020 +0200
# Node ID c2137d681216048a170a33613c60d52cf0b56b06
# Parent  49905d1fcea9eb0d82143fbd30bcb973d3797274
# Available At https://philpep.org/pub/hg/cubicweb
#              hg pull https://philpep.org/pub/hg/cubicweb -r c2137d681216
[server] Make connection pooler configurable and set better default values

Drop the configuration connections-pool-size and add new configurations options:

* connections-pool-min-size. Set to 0 by default so we open connections only
  when needed. This avoid opening min-size*processes connections at startup,
  which is, it think, a good default.
* connections-pool-max-size. Set to 0 (unlimited) by default, so we move the
  bottleneck to postgresql.
* connections-idle-timeout. Set to 10 minutes. I don't have arguments about
  this except that this is the default in pgbouncer.

diff --git a/cubicweb/server/repository.py b/cubicweb/server/repository.py
--- a/cubicweb/server/repository.py
+++ b/cubicweb/server/repository.py
@@ -254,11 +254,16 @@ class _CnxSetPool(_BaseCnxSet):
 def get_cnxset(config, source, bootstrap=False):
     if not config['connections-pooler-enabled']:
         return _BaseCnxSet(source)
+    idle_timeout = config['connections-pool-idle-timeout']
     if bootstrap or config.quick_start:
-        max_size = 1
+        min_size, max_size = 0, 1
     else:
-        max_size = config['connections-pool-size']
-    return _CnxSetPool(source, min_size=1, max_size=max_size)
+        min_size, max_size = (
+            config['connections-pool-min-size'],
+            config['connections-pool-max-size'],
+        )
+    return _CnxSetPool(source, min_size=min_size, max_size=max_size,
+                       idle_timeout=idle_timeout)
 
 
 class Repository(object):
diff --git a/cubicweb/server/serverconfig.py b/cubicweb/server/serverconfig.py
--- a/cubicweb/server/serverconfig.py
+++ b/cubicweb/server/serverconfig.py
@@ -132,11 +132,22 @@ the repository rather than the user runn
           'help': 'Enable the connection pooler. Set to no if you use an external database pooler (e.g. pgbouncer)',
           'group': 'main', 'level': 3,
           }),
-        ('connections-pool-size',
+        ('connections-pool-max-size',
+         {'type' : 'int',
+          'default': 0,
+          'help': 'Maximum, per process, number of database connections. Default 0 (unlimited)',
+          'group': 'main', 'level': 3,
+          }),
+        ('connections-pool-min-size',
          {'type' : 'int',
-          'default': 4,
-          'help': 'size of the connections pool. Each source supporting multiple \
-connections will have this number of opened connections.',
+          'default': 0,
+          'help': 'Minimum, per process, number of database connections.',
+          'group': 'main', 'level': 3,
+          }),
+        ('connections-pool-idle-timeout',
+         {'type' : 'int',
+          'default': 600,
+          'help': "Start closing connection if the pool hasn't been empty for this many seconds",
           'group': 'main', 'level': 3,
           }),
         ('rql-cache-size',
diff --git a/cubicweb/test/unittest_cwconfig.py b/cubicweb/test/unittest_cwconfig.py
--- a/cubicweb/test/unittest_cwconfig.py
+++ b/cubicweb/test/unittest_cwconfig.py
@@ -196,12 +196,12 @@ class CubicWebConfigurationTC(BaseTestCa
             del os.environ['CW_BASE_URL']
 
     def test_config_value_from_environment_int(self):
-        self.assertEqual(self.config['connections-pool-size'], 4)
-        os.environ['CW_CONNECTIONS_POOL_SIZE'] = '6'
+        self.assertEqual(self.config['connections-pool-max-size'], 4)
+        os.environ['CW_CONNECTIONS_POOL_MAX_SIZE'] = '6'
         try:
-            self.assertEqual(self.config['connections-pool-size'], 6)
+            self.assertEqual(self.config['connections-pool-max-size'], 6)
         finally:
-            del os.environ['CW_CONNECTIONS_POOL_SIZE']
+            del os.environ['CW_CONNECTIONS_POOL_MAX_SIZE']
 
     def test_config_value_from_environment_yn(self):
         self.assertEqual(self.config['allow-email-login'], False)
diff --git a/doc/changes/3.28.rst b/doc/changes/3.28.rst
--- a/doc/changes/3.28.rst
+++ b/doc/changes/3.28.rst
@@ -6,3 +6,5 @@ Changes
 
 - the class cubicweb.view.EntityAdapter was moved to cubicweb.entity.EntityAdapter
   a deprecation warning is in place, but please update your source code accordingly.
+
+- The database pooler is now dynamic. New connections are opened when needed and closed after a configurable period of time. This can be configured through `connections-pooler-max-size` (default 0, unlimited), `connections-pooler-min-size` (default 0), and `connections-pooler-idle-timeout` (default 600 seconds). The old configuration `connections-pooler-size` has been dropped.