Postgres temporarily cannot handle large volumes of data

stephanie0x00 commented 10 months ago

When opening the 'Objects' page postgres temporarily times out while gathering all records for the page. This occurs when having a lot of objects (currently 27k objects). After waiting for some time it automatically resolves itself, however the user will think that one of the services might be down instead. This was found on the testserver.

Error logs are attached. postgres-crash-alexa1000.tar.gz

Screenshots postgres-connections

OpenKAT version commit 6d2f443f1eaedd1dfdc5735c2e44f34157b06adf (HEAD -> main, origin/main, origin/HEAD)

Environment:

Request Method: GET
Request URL: http://kattest01.openkat.dev/en/aaaa/objects/

Django Version: 4.2.7
Python Version: 3.11.7
Installed Applications:
['whitenoise.runserver_nostatic',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django.forms',
 'django_otp',
 'django_otp.plugins.otp_static',
 'django_otp.plugins.otp_totp',
 'two_factor',
 'account',
 'tools',
 'fmea',
 'crisis_room',
 'onboarding',
 'katalogus',
 'django_password_validators',
 'django_password_validators.password_history',
 'rest_framework',
 'tagulous',
 'compressor',
 'reports',
 'csp']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'rocky.middleware.remote_user.RemoteUserMiddleware',
 'django_otp.middleware.OTPMiddleware',
 'rocky.middleware.auth_required.AuthRequiredMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'rocky.middleware.onboarding.OnboardingMiddleware',
 'csp.middleware.CSPMiddleware']

Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/contrib/sessions/backends/base.py", line 187, in _get_session
    return self._session_cache
           ^^^^^^^^^^^^^^^^^^^

During handling of the above exception ('SessionStore' object has no attribute '_session_cache'), another exception occurred:
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 289, in ensure_connection
    self.connect()
    ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 270, in connect
    self.connection = self.get_new_connection(conn_params)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
    connection = self.Database.connect(**conn_params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The above exception (connection to server at "postgres" (172.24.0.2), port 5432 failed: FATAL:  remaining connection slots are reserved for non-replication superuser connections
) was the direct cause of the following exception:
  File "/usr/local/lib/python3.11/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/deprecation.py", line 133, in __call__
    response = self.process_request(request)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/auth/middleware.py", line 70, in process_request
    if request.user.is_authenticated:
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/functional.py", line 266, in inner
    self._setup()
    ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/functional.py", line 419, in _setup
    self._wrapped = self._setupfunc()
                    ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/auth/middleware.py", line 25, in <lambda>
    request.user = SimpleLazyObject(lambda: get_user(request))
                                            ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/auth/middleware.py", line 11, in get_user
    request._cached_user = auth.get_user(request)
                           ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/auth/__init__.py", line 191, in get_user
    user_id = _get_user_session_key(request)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/auth/__init__.py", line 60, in _get_user_session_key
    return get_user_model()._meta.pk.to_python(request.session[SESSION_KEY])
                                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/sessions/backends/base.py", line 53, in __getitem__
    return self._session[key]
           ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/sessions/backends/base.py", line 192, in _get_session
    self._session_cache = self.load()
                          ^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/sessions/backends/db.py", line 42, in load
    s = self._get_session_from_db()
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/contrib/sessions/backends/db.py", line 32, in _get_session_from_db
    return self.model.objects.get(

  File "/usr/local/lib/python3.11/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 633, in get
    num = len(clone)
          ^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 380, in __len__
    self._fetch_all()
    ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(

  File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1560, in execute_sql
    cursor = self.connection.cursor()
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 330, in cursor
    return self._cursor()
           ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 306, in _cursor
    self.ensure_connection()
    ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 288, in ensure_connection
    with self.wrap_database_errors:
    ^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 289, in ensure_connection
    self.connect()
    ^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/base.py", line 270, in connect
    self.connection = self.get_new_connection(conn_params)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
    connection = self.Database.connect(**conn_params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Exception Type: OperationalError at /en/aaaa/objects/
Exception Value: connection to server at "postgres" (172.24.0.2), port 5432 failed: FATAL:  remaining connection slots are reserved for non-replication superuser connections

underdarknl commented 10 months ago

Hm, This would mean some other service is using up too many postgres client slots or creating too many threads.

dekkers commented 8 months ago

It looks like this is caused by rocky or at least partly caused by rocky. When you go the katalogus page, it will fetch all images and for each request we use the database to check authentication and authorisation.

The question is why so many threads are created. Granian only seems to start 4 threads according to log messages, but if you go the katalogus page more threads and look at the number of threads with ps there are more threads. One reason might be that there are only 4 threads handling HTTP connections in Granian, but due to HTTP/2 multiplexing a single HTTP/2 connection might create multiple threads handling multiple requests of the HTTP/2 connection. Another reason might be that Django creates threads to run async code. We need to investigate this to get a better understanding.
Those pictures should be cached better. If we use the image hash as filename we can just cache them forever. We also have boefjes/normalizers with the same image, so with hashes we would also request those only once.
We could allow the images to be fetched without any authentication, but that would leak information about which boefjes/normalizers are available in the KAT installation.

underdarknl commented 8 months ago

same issue: https://github.com/minvws/nl-kat-coordination/issues/2404

minvws / nl-kat-coordination

Postgres temporarily cannot handle large volumes of data #2304