[Question] Troubleshooting idle connections in cvat_db

[mmoyano-sigmacognition]

Hi,

I recently opened this issue due to server availability problems, when using CVAT version 2.11.2, caused by reaching the maximum number of connections (200, which I increased to 500). After successfully monitoring all the connected IPs, I proceeded to check active connections to the cvat_db, and noticed many idle connections, like the ones shown below:

psql cvat
SELECT pid, usename, datname, state, query, state_change FROM pg_stat_activity order by state_change;
pid | usename | datname | state | query | state_change
----+----------+----------+-----+-------+--------------
... | anonymized | cvat | idle | SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1 LIMIT 21 | 2025-02-24 08:19:29.901414+00

... | anonymized | cvat | idle | SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1 LIMIT 21 | 2025-02-24 07:36:58.635467+00

... | anonymized | cvat | idle | SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1 LIMIT 21 | 2025-02-21 18:53:09.882007+00

Although I show just three connections here, there are 53 connections in total, and only one of them is active. Currently, one user is labeling, which is why there should be at least one active connection.

When I run the query above, the following result is printed::

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1 LIMIT 21;
 id | password | last_login | is_superuser | username | first_name | last_name | email | is_staff | is_active | date_joined      
---+-----------+----------+--------------+-----------+------------+-----------+------+--------+---------+-------------
  1 | anonymized_pwd | 2025-02-21 18:43:35.628501+00 | t | anonymized_user |  |  |  | t | t | 2024-04-02 08:14:00.765091+00
(1 row)

Could you help me understand the origin of these idle connections? What could be causing them to stay in an idle state? Any guidance on how to address this issue would be greatly appreciated.

Thanks in advance. Cheers,
Manuel

[azhavoro]

Have you tried reproducing this on the latest version?
Do you have a scenario to reproduce this problem on a fresh install?

We don't change the default Django settings, according to the documentation Django should close the connection after every request to the db: https://docs.djangoproject.com/en/4.2/ref/settings/#std-setting-CONN_MAX_AGE. That's why the behavior looks strange

[mmoyano-sigmacognition]

Hi @azhavoro,

I have one instance running version 2.20.0, but the limitation I'm facing is that I'm the only one allowed to use it. Because of this, only a few connections (around four or five) are opened, making it difficult for me to determine their origin. When I label, a connection opens and then closes when I finish, which is the expected behaviour. However, some connections remain idle, and I don't understand why.

I want to test it on the new version with the usual volume of labelers, but unfortunately, that's not possible right now😕. That's why I'm looking for ideas, because I'm unable to reproduce the issue by myself.

Thank you very much for the quick response. Cheers,
Manuel

[bedilbek]

So what's is the update on this issue? And what's the real cause? @azhavoro

[imimko]

Hi!

Do you have message no space left on device in logs cvat_db?

If yes, try to increase memory for cvat_db in docker compose

 cvat_db:
    container_name: cvat_db
    ...
    shm_size: "1g"

It worked for me.

[mmoyano-sigmacognition]

Hi @bsekachev,

Apologies for not replying sooner, I didn't receive the updates by email. The issue is still happening, and I haven't received the message @imimko got (thanks for participating :)). No updates here then?

Thanks in advance. Cheers,
Manuel

[bsekachev]

Unfortunately, I do not think we may help here.
Trying to assume why it happens in your environment is like "reading the tea leaves".

The problem here is that we cannot reproduce it on our side, even when dealing with highly loaded app.cvat.ai.

Maybe some database transactions are not closed in your code.
Maybe there are infrastructure issues, IDK.

[mmoyano-sigmacognition]

It's a shame you couldn't reproduce this issue on your end @bsekachev. In any case, thank you so much for your time, and I will continue investigating what's going on. If I come across a solution, I'll be sure to post it here.

Once again, thank you for your time 😊.

[wantguns]

I used pgbouncer as a workaround for this issue back when we were using v2.7.2 -- #9180 (comment) (this snippet is essentially doing the same on the latest release, but jfyi, I did not face this issue on the latest release):

# Change the CVAT_POSTGRES_HOST env variable in all the relevant services
x-backend-env: &backend-env
  CVAT_POSTGRES_HOST: 'pgbouncer' # changed
  CVAT_REDIS_INMEM_HOST: cvat_redis_inmem
  CVAT_REDIS_INMEM_PORT: 6379
  CVAT_REDIS_ONDISK_HOST: cvat_redis_ondisk
  CVAT_REDIS_ONDISK_PORT: 6666
  CVAT_LOG_IMPORT_ERRORS: 'true'
  CVAT_ALLOW_STATIC_CACHE: '${CVAT_ALLOW_STATIC_CACHE:-no}'
  DJANGO_LOG_SERVER_HOST: vector
  DJANGO_LOG_SERVER_PORT: 80
  no_proxy: clickhouse,grafana,vector,nuclio,opa,${no_proxy:-}
  SMOKESCREEN_OPTS: ${SMOKESCREEN_OPTS:-}

# Add pgbouncer
  pgbouncer:
    image: docker.io/bitnami/pgbouncer:1
    container_name: pgbouncer
    restart: always
    ports:
      - 5432:5432
    environment:
      - PGBOUNCER_DATABASE=*
      - PGBOUNCER_AUTH_TYPE=trust
      - PGBOUNCER_PORT=5432
      - PGBOUNCER_AUTH_USER=root
      - PGBOUNCER_MAX_CLIENT_CONN=500
      - PGBOUNCER_DEFAULT_POOL_SIZE=50

      - POSTGRESQL_HOST=cvat_db
      - POSTGRESQL_PORT=6432
      - POSTGRESQL_USERNAME=root
    networks:
      - cvat

# Change PG ports
  cvat_db:
    container_name: cvat_db
    image: postgres:15-alpine
    restart: always
    environment:
      POSTGRES_USER: root
      POSTGRES_DB: cvat
      PGPORT: 6432 # added
      POSTGRES_HOST_AUTH_METHOD: trust
    volumes:
      - cvat_db:/var/lib/postgresql/data
    networks:
      - cvat