Winery readers regularly out of ~~postgresql connections~~ threads

mars 01 23:25:07 gloin001 gunicorn[1850140]: {"asctime": "2025-03-01 23:25:07,885", "levelname": "ERROR", "processName": "MainProcess", "process": 1850140, "threadName": "MainThread", "pathname": "/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/core/api/__init__.py", "lineno": 439, "funcName": "error_handler", "name": "swh.core.api", "message": "can't start new thread", "exc_info": "Traceback (most recent call last):
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/flask/app.py\", line 880, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/flask/app.py\", line 865, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/api/server.py\", line 31, in w
    return f(*a, **kw)
           ^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/core/api/negotiation.py\", line 153, in newf
    return f.negotiator(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/core/api/negotiation.py\", line 81, in __call__
    result = self.func(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/core/api/__init__.py\", line 551, in f
    ret = obj_meth(**kw)
          ^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/objstorage.py\", line 41, in get
    return self.winery.get(self._hash(obj_id))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/objstorage.py\", line 109, in get
    roshard = self.roshard(name)
              ^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/objstorage.py\", line 88, in roshard
    shard = ROShard(name, **self.args)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/roshard.py\", line 315, in __init__
    self.throttler = Throttler(**kwargs)
                     ^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/throttler.py\", line 178, in __init__
    self.read = IOThrottler(\"read\", **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/throttler.py\", line 106, in __init__
    self.init_db()
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/throttler.py\", line 113, in init_db
    with self.pool.connection() as db, db.transaction(), db.cursor() as cur:
         ^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/swh/objstorage/backends/winery/database.py\", line 23, in pool
    self._pool = ConnectionPool(
                 ^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/psycopg_pool/pool.py\", line 101, in __init__
    self._open()
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/psycopg_pool/pool.py\", line 380, in _open
    self._start_workers()
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/psycopg_pool/pool.py\", line 395, in _start_workers
    self._sched_runner = spawn(self._sched.run, name=f\"{self.name}-scheduler\")
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/psycopg_pool/_acompat.py\", line 131, in spawn
    t.start()
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/sentry_sdk/utils.py\", line 1711, in runner
    return sentry_patched_function(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/srv/winery/venv-20240505.1/lib/python3.11/site-packages/sentry_sdk/integrations/threading.py\", line 78, in sentry_start
    return old_start(self, *a, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File \"/usr/lib/python3.11/threading.py\", line 957, in start
    _start_new_thread(self._bootstrap, ())
RuntimeError: can't start new thread"}

A restart of the readers solve the issue for a dozen of hours

added platform::CEA winery labels

changed title from Winery readers regularly out of postgresql connections to Winery readers regularly out of {++}postgresql connections{+ threads+}

changed the description

Here's the explanation:

• winery initializes one throttler per ROShard instance (one for each available shard).
• Each throttler initialization initializes a new psycopg pool, which each use two connection management threads
• We have passed 10k readonly shards
• We have 32 gunicorn workers
• The default thread limit for a systemd service is 15% of pid_max (~= 630 000)
• 10k readonly shards * 32 gunicorn workers * 2 threads = 640k threads, blowing up the limit

swh@gloin001:~$ systemctl show swh-winery@reader | grep Task
TasksCurrent=391449
TasksAccounting=yes
TasksMax=629145

The TasksCurrent value would keep growing until the RuntimeError

I've hot patched winery on gloin001 and 002 to share one throttler instance across all ROShards:

swh@gloin001:~$ systemctl show swh-winery@reader | grep Task
TasksCurrent=545
TasksAccounting=yes
TasksMax=629145

mentioned in commit olasd/swh-objstorage@7b378834

assigned to @olasd