Increased server load

Hello,

Recently, we have started experiencing an increased load on our nextcloud server and the circles seems to be contributing. More precisely the db requests.

Processes

2699 postgres  20   0  215808  24484  21672 S  14.0   0.6  41:17.21 postgres                                                                                                                                                               
1946 postgres  20   0  216512  24740  21928 R  13.3   0.6  42:20.52 postgres                                                                                                                                                               
4709 postgres  20   0  216256  24652  21852 R  13.0   0.6  39:31.74 postgres                                                                                                                                                               
4729 postgres  20   0  215808  24652  21852 R  13.0   0.6  39:33.07 postgres                                                                                                                                                               
1439 postgres  20   0  215808  24856  22048 R  12.6   0.6  42:47.67 postgres                                                                                                                                                               
4639 postgres  20   0  216512  24528  21728 R  12.6   0.6  39:31.71 postgres                                                                                                                                                               
2350 postgres  20   0  216512  24804  21992 R  12.3   0.6  41:36.24 postgres                                                                                                                                                               
3496 postgres  20   0  215808  24476  21668 R  12.3   0.6  40:33.31 postgres                                                                                                                                                               
2008 postgres  20   0  215808  24856  22048 R  11.6   0.6  42:10.75 postgres                                                                                                                                                               
4025 postgres  20   0  216516  24604  21788 R  11.6   0.6  39:57.84 postgres

Running queries

13752 |                 |           | 
13750 |                 |           | 
13749 |                 |           | 
13751 |                 |           | 
1946 | 00:00:00.001445 | nextcloud | SELECT DISTINCT "single"."unique_id", "single"."name", "single"."display_name", "single"."sanitized_name", "single"."source", "single"."description", "single"."settings", "single"."config", "single"."contact_addressbook", "single"."contact_groupname", "single"."creation" FROM "oc_circles_circle" "single" LEFT JOIN "oc_circles_member" "single_member" ON "single_member"."circle_id" = "single"."unique_id" WHERE (("single_member"."user_id" = $1) AND ("single_member"."user_type" = $2) AND ("single_member"."instance" = $3) AND ("single_member"."level" >= $4)) AND (("single"."config" & 1) > $5) GROUP BY "single"."creation", "single"."unique_id", "single"."name", "single"."display_name", "single"."sanitized_name", "single"."source", "single"."description", "single"."settings", "single"."config", "single"."contact_addressbook", "single"."contact_groupname" ORDER BY "single"."creation" asc
3496 | 00:00:00.00178  | nextcloud | SELECT DISTINCT "single"."unique_id", "single"."name", "single"."display_name", "single"."sanitized_name", "single"."source", "single"."description", "single"."settings", "single"."config", "single"."contact_addressbook", "single"."contact_groupname", "single"."creation" FROM "oc_circles_circle" "single" LEFT JOIN "oc_circles_member" "single_member" ON "single_member"."circle_id" = "single"."unique_id" WHERE (("single_member"."user_id" = $1) AND ("single_member"."user_type" = $2) AND ("single_member"."instance" = $3) AND ("single_member"."level" >= $4)) AND (("single"."config" & 1) > $5) GROUP BY "single"."creation", "single"."unique_id", "single"."name", "single"."display_name", "single"."sanitized_name", "single"."source", "single"."description", "single"."settings", "single"."config", "single"."contact_addressbook", "single"."contact_groupname" ORDER BY "single"."creation" asc
4639 | 00:00:00.001997 | nextcloud | SELECT DISTINCT "single"."unique_id", "single"."name", "single"."display_name", "single"."sanitized_name", "single"."source", "single"."description", "single"."settings", "single"."config", "single"."contact_addressbook", "single"."contact_groupname", "single"."creation" FROM "oc_circles_circle" "single" LEFT JOIN "oc_circles_member" "single_member" ON "single_member"."circle_id" = "single"."unique_id" WHERE (("single_member"."user_id" = $1) AND ("single_member"."user_type" = $2) AND ("single_member"."instance" = $3) AND ("single_member"."level" >= $4)) AND (("single"."config" & 1) > $5) GROUP BY "single"."creation", "single"."unique_id", "single"."name", "single"."display_name", "single"."sanitized_name", "single"."source", "single"."description", "single"."settings", "single"."config", "single"."contact_addressbook", "single"."contact_groupname" ORDER BY "single"."creation" asc
2350 | 00:00:00.003498 | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
4025 | 00:00:00.004066 | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
4709 | 00:00:00.007261 | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
2699 | 00:00:00.007548 | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
1439 | 00:00:00.00946  | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
4729 | 00:00:00.011144 | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
2008 | 00:00:00.011845 | nextcloud | SELECT "share"."id", "share"."share_type", "share"."share_with", "share"."uid_owner", "share"."uid_initiator", "share"."parent", "share"."item_type", "share"."item_source", "share"."item_target", "share"."file_source", "share"."file_target", "share"."permissions", "share"."stime", "share"."accepted", "share"."expiration", "share"."token", "share"."mail_send", "share_circle"."unique_id" AS "share_circle_unique_id", "share_circle"."name" AS "share_circle_name", "share_circle"."display_name" AS "share_circle_display_name", "share_circle"."sanitized_name" AS "share_circle_sanitized_name", "share_circle"."source" AS "share_circle_source", "share_circle"."description" AS "share_circle_description", "share_circle"."settings" AS "share_circle_settings", "share_circle"."config" AS "share_circle_config", "share_circle"."contact_addressbook" AS "share_circle_contact_addressbook", "share_circle"."contact_groupname" AS "share_circle_contact_groupname", "share_circle"."creation" AS "share_circle_creation", "share_circle_o
(15 rows)

There does not seem to be any long running ones

pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
pid | duration | query | state 
-----+----------+-------+-------
(0 rows)

Would you please be so kind as to advise what other logs or configuration files you might need to see in order to assist determining if this is an issue with circles what exactly is going on with so many postgres processes eating up resources?

Thanks!

The report names no source files, tests, or code entry points; begin with the repeated PostgreSQL queries shown in the issue and trace them into the circles database-access path. The issue has no reproduction, proposed change, or acceptance criterion, so define the responsible query path and a measurable reduction in server load before implementing and testing a fix.