circles
circles copied to clipboard
Published
20 hours ago •
nextcloud
nextcloud
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 same problem happens with MariaDB 10.4.13, Nextcloud 22.2.3, Circles app version 22.1.1.
In my case database server is separate so it is easy to see the traffic to/from database; often, DB sends 50-100Mbps of traffic to Nextcloud for periods of several minutes up to half an hour. During the time users activelly use Nextcloud, DB almost constantly emits around 20Mbps of traffic (we have ~30 active users, mainly using talk and light Onlyoffice work).
When I look at the DB, there are the queries that repeat over and over again. I have put them into the attachment. db-nextcloud.txt
What is strange is that SELECT queries each one have "LIMIT" and "OFFSET" where OFFSET is fixed to 50, and LIMIT varies by increasing for 50 by each query:
... LIMIT 3149200 OFFSET 50
... LIMIT 3149250 OFFSET 50
... LIMIT 3149300 OFFSET 50
... LIMIT 3149350 OFFSET 50
... LIMIT 3149400 OFFSET 50
... LIMIT 3149450 OFFSET 50
... LIMIT 3149500 OFFSET 50
... LIMIT 3149550 OFFSET 50
... LIMIT 3149600 OFFSET 50
...
I'd say that SQL query is being wrongly assembled.
@mkrinkl It might be useful to rename this ticket to specify that this is a Circles related issue.
The instance where we're seeing the load issue has 282 users and 36 circles in total. Happy to provide more metrics as needed.
