[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.