AkkomaGang/akkoma

Fork 92

SQL: Outlier query information gathering #784

New issue

Open

opened 2024-05-27 00:58:34 +00:00 by floatingghost · 8 comments

floatingghost commented

2024-05-27 00:58:34 +00:00

Owner

If you see this issue, go to

/phoenix/live_dashboard/ecto_stats?nav=outliers

and copy the outlier data here for query analysis to see if we can improve query design in any common places

template table


|  query | exec time | prop exec time | calls | sync IO time |
|--------|-----------|----------------|-------|--------------|

If you see this issue, go to `/phoenix/live_dashboard/ecto_stats?nav=outliers` and copy the outlier data here for query analysis to see if we can improve query design in any common places template table ``` | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------| ```

floatingghost commented

2024-05-27 01:02:26 +00:00

Author

Owner

Mine:

query	exec time	prop exec time	calls	sync IO time
SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1))	1006476.661775 seconds	47.6	47976	0 seconds
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	251523.734009 seconds	11.9	112616	0 seconds
SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3)	215632.590418 seconds	10.2	6981078	0 seconds
SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."permit_followback", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3)	105653.767247 seconds	5.0	2827509	0 seconds
SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL))	65583.501187 seconds	3.1	33633706	0 seconds
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."priority" IN ($6,$7,$8,$9)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state"	63982.806399 seconds	3.0	22013792	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	41508.531170 seconds	2.0	225232	0 seconds
SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2)	33832.399275 seconds	1.6	14454187	0 seconds

Mine: | query | exec time | prop exec time | calls | sync IO time | |--------|----------|-----------------|-----|---------------| | SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1)) | 1006476.661775 seconds | 47.6 | 47976 | 0 seconds | | SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 251523.734009 seconds | 11.9 |112616 | 0 seconds | | SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3) | 215632.590418 seconds | 10.2 | 6981078 | 0 seconds | |SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."permit_followback", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3) | 105653.767247 seconds | 5.0 | 2827509 | 0 seconds | | SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL)) | 65583.501187 seconds | 3.1 | 33633706 | 0 seconds | | UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."priority" IN ($6,$7,$8,$9)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state" | 63982.806399 seconds | 3.0 | 22013792 | 0 seconds | | SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 41508.531170 seconds | 2.0 | 225232 | 0 seconds | | SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2) | 33832.399275 seconds | 1.6 | 14454187 | 0 seconds |

norm commented

2024-05-27 05:28:53 +00:00

Contributor

Worth noting that the pg_stat_statements extension required for this data isn't enabled by default in Postgres.

To enable the extension, modify postgresql.conf with the following settings:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Then restart Postgres. Next connect to the akkoma database with psql and run

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Steps copied from here.

Worth noting that the `pg_stat_statements` extension required for this data isn't enabled by default in Postgres. To enable the extension, modify `postgresql.conf` with the following settings: ``` shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all ``` Then restart Postgres. Next connect to the akkoma database with psql and run ```sql CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` Steps copied from [here](https://www.bytebase.com/docs/slow-query/enable-pg-stat-statements-for-postgresql/).

YokaiRick commented

2024-05-27 15:35:38 +00:00

Contributor

Hi, here are mine. I actived the PG extenstion 9 hours ago to let it collect some stats.
Ill can post a update in a few days again, if you want.

query	exec time	prop exec time	calls	sync IO time
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	102.813244 seconds	52.1	85	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	28.120333 seconds	14.2	170	0 seconds
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state"	8.065643 seconds	4.1	25400	0 seconds
SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL))	6.001011 seconds	3.0	25400	0 seconds
select installed_version from pg_available_extensions where name=$1	5.792762 seconds	2.9	5218	0 seconds
INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)	3.928702 seconds	2.0	3051	0 seconds
UPDATE "activities" AS a0 SET "recipients" = array_replace(a0."recipients",$1,$2) WHERE ($3 = ANY(a0."recipients"))	3.923397 seconds	2.0	2	0 seconds
SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2)	3.484691 seconds	1.8	2208	0 seconds
SELECT u0."id", u0."bio", u0."raw_bio", u0."email", u0."name", u0."nickname", u0."password_hash", u0."keys", u0."public_key", u0."ap_id", u0."avatar", u0."local", u0."follower_address", u0."following_address", u0."featured_address", u0."tags", u0."last_refreshed_at", u0."last_digest_emailed_at", u0."banner", u0."background", u0."note_count", u0."follower_count", u0."following_count", u0."is_locked", u0."is_confirmed", u0."password_reset_pending", u0."is_approved", u0."registration_reason", u0."confirmation_token", u0."default_scope", u0."domain_blocks", u0."is_active", u0."no_rich_text", u0."ap_enabled", u0."is_moderator", u0."is_admin", u0."show_role", u0."mastofe_settings", u0."uri", u0."hide_followers_count", u0."hide_follows_count", u0."hide_followers", u0."hide_follows", u0."hide_favorites", u0."email_notifications", u0."mascot", u0."emoji", u0."pleroma_settings_store", u0."fields", u0."raw_fields", u0."is_discoverable", u0."invisible", u0."allow_following_move", u0."skip_thread_containment", u0."actor_type", u0."also_known_as", u0."inbox", u0."shared_inbox", u0."last_active_at", u0."disclose_client", u0."pinned_objects", u0."is_suggested", u0."last_status_at", u0."language", u0."status_ttl_days", u0."permit_followback", u0."accepts_direct_messages_from", u0."notification_settings", u0."multi_factor_authentication_settings", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $5)) AND (u0."invisible" = $1) AND (u0."is_discoverable" = $2) ORDER BY u0."last_status_at" DESC NULLS LAST, u0."id" desc nulls last LIMIT $3 OFFSET $4	3.346299 seconds	1.7	7	0 seconds
WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"	2.877429 seconds	1.5	20916	0 seconds

Hi, here are mine. I actived the PG extenstion 9 hours ago to let it collect some stats. Ill can post a update in a few days again, if you want. | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------| SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 102.813244 seconds | 52.1 |85 |0 seconds SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 28.120333 seconds | 14.2 | 170 | 0 seconds UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state" | 8.065643 seconds | 4.1 | 25400 | 0 seconds SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL)) |6.001011 seconds |3.0 |25400 |0 seconds select installed_version from pg_available_extensions where name=$1 | 5.792762 seconds |2.9 |5218 |0 seconds INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) | 3.928702 seconds |2.0 | 3051 | 0 seconds UPDATE "activities" AS a0 SET "recipients" = array_replace(a0."recipients",$1,$2) WHERE ($3 = ANY(a0."recipients")) |3.923397 seconds | 2.0 | 2 | 0 seconds SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2) |3.484691 seconds |1.8 |2208 |0 seconds SELECT u0."id", u0."bio", u0."raw_bio", u0."email", u0."name", u0."nickname", u0."password_hash", u0."keys", u0."public_key", u0."ap_id", u0."avatar", u0."local", u0."follower_address", u0."following_address", u0."featured_address", u0."tags", u0."last_refreshed_at", u0."last_digest_emailed_at", u0."banner", u0."background", u0."note_count", u0."follower_count", u0."following_count", u0."is_locked", u0."is_confirmed", u0."password_reset_pending", u0."is_approved", u0."registration_reason", u0."confirmation_token", u0."default_scope", u0."domain_blocks", u0."is_active", u0."no_rich_text", u0."ap_enabled", u0."is_moderator", u0."is_admin", u0."show_role", u0."mastofe_settings", u0."uri", u0."hide_followers_count", u0."hide_follows_count", u0."hide_followers", u0."hide_follows", u0."hide_favorites", u0."email_notifications", u0."mascot", u0."emoji", u0."pleroma_settings_store", u0."fields", u0."raw_fields", u0."is_discoverable", u0."invisible", u0."allow_following_move", u0."skip_thread_containment", u0."actor_type", u0."also_known_as", u0."inbox", u0."shared_inbox", u0."last_active_at", u0."disclose_client", u0."pinned_objects", u0."is_suggested", u0."last_status_at", u0."language", u0."status_ttl_days", u0."permit_followback", u0."accepts_direct_messages_from", u0."notification_settings", u0."multi_factor_authentication_settings", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $5)) AND (u0."invisible" = $1) AND (u0."is_discoverable" = $2) ORDER BY u0."last_status_at" DESC NULLS LAST, u0."id" desc nulls last LIMIT $3 OFFSET $4 | 3.346299 seconds |1.7 |7 |0 seconds WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" | 2.877429 seconds |1.5 |20916 |0 seconds

norm commented

2024-05-27 16:39:38 +00:00

Contributor

Here's mine after about 12 hours (deleted the original since it wasn't run for long enough):

query	exec time	prop exec time	calls	sync IO time
SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1))	5402.192327 seconds	83.1	185	0 seconds
SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at", b2."id", b2."user_id", b2."activity_id", b2."inserted_at", b2."updated_at", NOT (t3."id" IS NULL) FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>$21) = COALESCE(a0."data"->$22->>$23, a0."data"->>$24) LEFT OUTER JOIN "bookmarks" AS b2 ON (b2."user_id" = $1) AND (b2."activity_id" = a0."id") LEFT OUTER JOIN "thread_mutes" AS t3 ON (t3."user_id" = $2) AND (t3."context" = a0."data"->>$25) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $26) AS f4 ON $27 INNER JOIN "users" AS u5 ON (a0."actor" = u5."ap_id") AND (u5."invisible" = $28) WHERE (a0."id" > $3) AND (a0."data"->>$29 = $4) AND (not (a0."actor" = ANY($5))) AND (((not (a0."recipients" && $6)) or a0."actor" = $7)) AND ((recipients_contain_blocked_domains(a0."recipients", $8) = $30) or a0."actor" = $9) AND (not (a0."data"->>$31 = $32 and a0."data"->$33 ?\| $10)) AND ((not (split_part(a0."actor", $34, $35) = ANY($11))) or a0."actor" = ANY($12)) AND ((not (split_part(o1."data"->>$36, $37, $38) = ANY($13))) or (o1."data"->>$39) = ANY($14)) AND (not ( a0."data"->>$40 = $41 and a0."actor" = ANY($15))) AND (not(o1."data"->>$42 = $43)) AND (a0."recipients" && $16 OR (a0."recipients" && $17 AND $18 = ANY(a0."recipients"))) AND (a0."id" > $19) ORDER BY a0."id" desc nulls last LIMIT $20	190.333149 seconds	2.9	1118	0 seconds
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	148.729416 seconds	2.3	133	0 seconds
SELECT o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = ANY($1)) AND (o0."inserted_at" >= $2) AND ((o0."worker" = $3) AND ((o0."queue" = $4) AND ((o0."args" @> $5 AND o0."args" <@ $6) AND $7))) LIMIT $8	100.318536 seconds	1.5	139889	0 seconds
SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at" FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>$6) = COALESCE(a0."data"->$7->>$8, a0."data"->>$9) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $10) AS f2 ON $11 INNER JOIN "users" AS u3 ON (a0."actor" = u3."ap_id") AND (u3."invisible" = $12) WHERE ($1 && a0."recipients") AND (a0."actor" = $2) AND (a0."data"->>$13 = ANY($3)) AND (a0."data"->>$14 != $15) AND (not(o1."data"->>$16 = $17)) AND (a0."id" > $4) ORDER BY a0."id" asc nulls last LIMIT $5	59.240213 seconds	0.9	2621	0 seconds
SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2)	51.662827 seconds	0.8	62745	0 seconds
INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)	48.560783 seconds	0.7	42695	0 seconds
SELECT u0."ap_id" FROM "users" AS u0 INNER JOIN "following_relationships" AS f1 ON (f1."following_id" = u0."id") AND (f1."follower_id" = $1) WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $4)) AND (u0."is_active" = $5) AND (u0."id" != $2) AND (f1."state" = $3)	31.241961 seconds	0.5	121338	0 seconds
WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"	23.282083 seconds	0.4	251051	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	21.774968 seconds	0.3	266	0 seconds

Here's mine after about 12 hours (deleted the original since it wasn't run for long enough): | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------| | SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1)) | 5402.192327 seconds | 83.1 | 185 | 0 seconds| SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at", b2."id", b2."user_id", b2."activity_id", b2."inserted_at", b2."updated_at", NOT (t3."id" IS NULL) FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>$21) = COALESCE(a0."data"->$22->>$23, a0."data"->>$24) LEFT OUTER JOIN "bookmarks" AS b2 ON (b2."user_id" = $1) AND (b2."activity_id" = a0."id") LEFT OUTER JOIN "thread_mutes" AS t3 ON (t3."user_id" = $2) AND (t3."context" = a0."data"->>$25) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $26) AS f4 ON $27 INNER JOIN "users" AS u5 ON (a0."actor" = u5."ap_id") AND (u5."invisible" = $28) WHERE (a0."id" > $3) AND (a0."data"->>$29 = $4) AND (not (a0."actor" = ANY($5))) AND (((not (a0."recipients" && $6)) or a0."actor" = $7)) AND ((recipients_contain_blocked_domains(a0."recipients", $8) = $30) or a0."actor" = $9) AND (not (a0."data"->>$31 = $32 and a0."data"->$33 ?\| $10)) AND ((not (split_part(a0."actor", $34, $35) = ANY($11))) or a0."actor" = ANY($12)) AND ((not (split_part(o1."data"->>$36, $37, $38) = ANY($13))) or (o1."data"->>$39) = ANY($14)) AND (not ( a0."data"->>$40 = $41 and a0."actor" = ANY($15))) AND (not(o1."data"->>$42 = $43)) AND (a0."recipients" && $16 OR (a0."recipients" && $17 AND $18 = ANY(a0."recipients"))) AND (a0."id" > $19) ORDER BY a0."id" desc nulls last LIMIT $20 | 190.333149 seconds | 2.9 | 1118 | 0 seconds| |SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 148.729416 seconds | 2.3 | 133 | 0 seconds| |SELECT o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = ANY($1)) AND (o0."inserted_at" >= $2) AND ((o0."worker" = $3) AND ((o0."queue" = $4) AND ((o0."args" @> $5 AND o0."args" <@ $6) AND $7))) LIMIT $8 | 100.318536 seconds | 1.5 | 139889 | 0 seconds| |SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at" FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>$6) = COALESCE(a0."data"->$7->>$8, a0."data"->>$9) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $10) AS f2 ON $11 INNER JOIN "users" AS u3 ON (a0."actor" = u3."ap_id") AND (u3."invisible" = $12) WHERE ($1 && a0."recipients") AND (a0."actor" = $2) AND (a0."data"->>$13 = ANY($3)) AND (a0."data"->>$14 != $15) AND (not(o1."data"->>$16 = $17)) AND (a0."id" > $4) ORDER BY a0."id" asc nulls last LIMIT $5 | 59.240213 seconds | 0.9 | 2621 | 0 seconds| |SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2) | 51.662827 seconds | 0.8 | 62745 | 0 seconds| |INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) | 48.560783 seconds | 0.7 | 42695 | 0 seconds| |SELECT u0."ap_id" FROM "users" AS u0 INNER JOIN "following_relationships" AS f1 ON (f1."following_id" = u0."id") AND (f1."follower_id" = $1) WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $4)) AND (u0."is_active" = $5) AND (u0."id" != $2) AND (f1."state" = $3) | 31.241961 seconds | 0.5 | 121338 | 0 seconds| |WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" | 23.282083 seconds | 0.4 | 251051 | 0 seconds| |SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 21.774968 seconds | 0.3 | 266 | 0 seconds |

Oneric commented

2024-05-27 17:09:27 +00:00

Member

worth noting the top offender by far for ihba and akko is the query of the attachment cleanup worker; also see #765 for this one

@YokaiRick since it doesn’t show up at all on your instance, can you confirm whether :instance, :cleanup_attachments is set to false (the default)?

worth noting the top offender by far for ihba and akko is the query of the attachment cleanup worker; also see #765 for this one @YokaiRick since it doesn’t show up at all on your instance, can you confirm whether `:instance, :cleanup_attachments` is set to false *(the default)*?

YokaiRick commented

2024-05-27 18:00:38 +00:00

Contributor

@Oneric nope, i didn't deactivate it: here is my instance part:

config :pleroma, :instance,
  name: "n0ids space",
  email: "rick@XXX.space",
  notify_email: "rick@XXX.space",
  limit: 5000,
  registrations_open: false,
  export_prometheus_metrics: true,
  public: true,
  max_pinned_statuses: 10

and i grepped for the default stuff also:

/opt/akkoma/releases/3.13.2-0-g050bc74/sys.config : {extended_nickname_format,true},{cleanup_attachments,false},{

i have a single user instance and tbh i can't remember when it was the last time i did delete a post.. maybe thats why?

@Oneric nope, i didn't deactivate it: here is my instance part: ``` config :pleroma, :instance, name: "n0ids space", email: "rick@XXX.space", notify_email: "rick@XXX.space", limit: 5000, registrations_open: false, export_prometheus_metrics: true, public: true, max_pinned_statuses: 10 ``` and i grepped for the default stuff also: ``` /opt/akkoma/releases/3.13.2-0-g050bc74/sys.config : {extended_nickname_format,true},{cleanup_attachments,false},{ ``` i have a single user instance and tbh i can't remember when it was the last time i did delete a post.. maybe thats why?

Oneric commented

2024-05-27 21:38:49 +00:00

Member

Yeah, if you didn’t set it at all it defaults to false; but never deleting posts would amount to the same effect; thanks!

Yeah, if you didn’t set it at all it defaults to `false`; but never deleting posts would amount to the same effect; thanks!

Oneric referenced this issue from a commit

2024-06-03 22:19:27 +00:00

Don't try to cleanup remote attachments

Oneric referenced this issue

2024-06-03 22:28:25 +00:00

Fix various attachment cleanup issues #789

Oneric referenced this issue from a commit

2024-06-04 00:50:43 +00:00

Don't try to cleanup remote attachments

Oneric referenced this issue from a commit

2024-06-04 00:51:04 +00:00

Don't try to cleanup remote attachments

Oneric referenced this issue from a commit

2024-06-07 17:05:13 +00:00

Don't try to cleanup remote attachments

Oneric referenced this issue from a commit

2024-07-06 01:22:34 +00:00

Don't try to cleanup remote attachments

Oneric commented

2024-11-16 01:19:51 +00:00

Member

After ~2 months:

query	exec time	prop exec time	calls	sync IO time
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	1180.404619 seconds	31.3	14430	0 seconds
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state"	681.908258 seconds	18.1	4306129	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	482.949110 seconds	12.8	28860	0 seconds
SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL))	295.477402 seconds	7.8	4306009	0 seconds
WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"	111.632691 seconds	3.0	811222	0 seconds
INSERT INTO "activities" ("data","local","actor","recipients","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)	111.281034 seconds	3.0	103563	0 seconds
SELECT pg_notify($1, payload) FROM json_array_elements_text($2::json) AS payload	84.501494 seconds	2.2	4888789	0 seconds
INSERT INTO "public"."oban_jobs" ("args","meta","state","max_attempts","queue","worker","errors","tags","attempt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT DO NOTHING RETURNING "id"	79.974538 seconds	2.1	285420	0 seconds
INSERT INTO "public"."oban_peers" AS o0 ("name","node","started_at","expires_at") VALUES ($1,$2,$3,$4) ON CONFLICT ("name") DO UPDATE SET "expires_at" = $5	40.555948 seconds	1.1	303523	0 seconds
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1, "scheduled_at" = $2, "errors" = array_append(o0."errors", $3) WHERE (o0."id" = $4)	38.076888 seconds	1.0	149192	0 seconds

A month prior

query	exec time	prop exec time	calls	sync IO time	comment
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	709.823132 seconds	28.6	9458	0 seconds	stat query; can be replaced with cheaper instance table query
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state"	602.376685 seconds	24.3	2821512	0 seconds	reduce unecessary job churn
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	295.784966 seconds	11.9	18916	0 seconds	also `pleroma/stats.ex`, run twice for local and remote; combine into single query?
SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL))	187.170853 seconds	7.5	2821392	0 seconds	reduce unecessary job churn
INSERT INTO "activities" ("data","local","actor","recipients","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)	67.182495 seconds	2.7	63141	0 seconds	(nothing directly actionable; though cutting down on indexes might help, see #764)
WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"	65.859543 seconds	2.7	488066	0 seconds	reduce unecessary job churn
SELECT pg_notify($1, payload) FROM json_array_elements_text($2::json) AS payload	54.175138 seconds	2.2	3188920	0 seconds	also from Oban; reduce unecessary job churn
INSERT INTO "public"."oban_jobs" ("args","meta","state","max_attempts","queue","worker","errors","tags","attempt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT DO NOTHING RETURNING "id"	47.815033 seconds	1.9	173582	0 seconds	reduce unecessary job churn
INSERT INTO "public"."oban_peers" AS o0 ("name","node","started_at","expires_at") VALUES ($1,$2,$3,$4) ON CONFLICT ("name") DO UPDATE SET "expires_at" = $5	26.374679 seconds	1.1	198884	0 seconds	reduce unecessary job churn
SELECT o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = ANY($1)) AND (o0."inserted_at" >= $2) AND ((o0."worker" = $3) AND ((o0."queue" = $4) AND ((o0."args" @> $5 AND o0."args" <@ $6) AND $7))) LIMIT $8	23.199854 seconds	0.9	143579	0 seconds	reduce unecessary job churn

Top offender here is the query used to get the list and count of remote isntaces used in prometheus stat reporting, the peers mastodon API and possibly some other pleroma API. This was also a high offender in previous reports, but got overshadowed by the awful attachment cleanup perf.
This can be replaced with a query of the instances table which EXPLAIN suggests is much cheaper. Results don’t match exactly, but nothing to worry about imho.

Third-highest offender is again a stat query counting local and remtote users. Note how it’s run exactly twice as often as the instance query; this i because remote and local users are counted individually; combining this query should half the perf cost; both do a full table scan.

Insertion of activites also shows up; there’s nothing we can do directly, though cutting down on unecessary indices (see #764) might help — and in particular the counter_cache table (which is populated by an insertion trigger) and it’s related indexes could be dropped; it’s only used to report non-critical stats in some Pleroma Admin API, but I suspect it of being relatively costly on activity operations.

Everything else is Oban related; we currently create way too many jobs known to be noops; cutting down on this might help here too (i have a half-finished patch, but no ETA) (this also helps with PostgreSQL bloat from deleted rows)

Here reports for a young, private instance with very few users. Outlier data gathering was turned on after a bit more than a month after creation and ran for about two (one) month. Prometheus is enabled *(the default setting)* and actively used. Attachment cleanup is turned on, but the still-not-merged optimisation patches are also applied locally explaining the absence of this query. **After ~2 months**: | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------|-| | SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 1180.404619 seconds | 31.3 | 14430 | 0 seconds | | UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state" | 681.908258 seconds | 18.1 | 4306129 | 0 seconds | | SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 482.949110 seconds | 12.8 | 28860 | 0 seconds | | SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL)) | 295.477402 seconds | 7.8 | 4306009 | 0 seconds | | WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" | 111.632691 seconds | 3.0 | 811222 | 0 seconds | | INSERT INTO "activities" ("data","local","actor","recipients","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) | 111.281034 seconds | 3.0 | 103563 | 0 seconds | | SELECT pg_notify($1, payload) FROM json_array_elements_text($2::json) AS payload | 84.501494 seconds | 2.2 | 4888789 | 0 seconds | | INSERT INTO "public"."oban_jobs" ("args","meta","state","max_attempts","queue","worker","errors","tags","attempt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT DO NOTHING RETURNING "id" | 79.974538 seconds | 2.1 | 285420 | 0 seconds | | INSERT INTO "public"."oban_peers" AS o0 ("name","node","started_at","expires_at") VALUES ($1,$2,$3,$4) ON CONFLICT ("name") DO UPDATE SET "expires_at" = $5 | 40.555948 seconds | 1.1 | 303523 | 0 seconds | | UPDATE "public"."oban_jobs" AS o0 SET "state" = $1, "scheduled_at" = $2, "errors" = array_append(o0."errors", $3) WHERE (o0."id" = $4) | 38.076888 seconds | 1.0 | 149192 | 0 seconds | <details> <summary>A month prior</summary> | query | exec time | prop exec time | calls | sync IO time | | comment | |--------|-----------|----------------|-------|--------------|-|---------| | SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 709.823132 seconds | 28.6 | 9458 | 0 seconds | | stat query; can be replaced with cheaper instance table query | | UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state" | 602.376685 seconds | 24.3 | 2821512 | 0 seconds | | reduce unecessary job churn | | SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 295.784966 seconds | 11.9 | 18916 | 0 seconds | | also `pleroma/stats.ex`, run twice for local and remote; combine into single query? | | SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL)) | 187.170853 seconds | 7.5 | 2821392 | 0 seconds | | reduce unecessary job churn | | INSERT INTO "activities" ("data","local","actor","recipients","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) | 67.182495 seconds | 2.7 | 63141 | 0 seconds | | *(nothing directly actionable; though cutting down on indexes might help, see #764)* | | WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" | 65.859543 seconds | 2.7 | 488066 | 0 seconds | | reduce unecessary job churn | | SELECT pg_notify($1, payload) FROM json_array_elements_text($2::json) AS payload | 54.175138 seconds | 2.2 | 3188920 | 0 seconds | | also from Oban; reduce unecessary job churn | | INSERT INTO "public"."oban_jobs" ("args","meta","state","max_attempts","queue","worker","errors","tags","attempt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ON CONFLICT DO NOTHING RETURNING "id" | 47.815033 seconds | 1.9 | 173582 | 0 seconds | | reduce unecessary job churn | | INSERT INTO "public"."oban_peers" AS o0 ("name","node","started_at","expires_at") VALUES ($1,$2,$3,$4) ON CONFLICT ("name") DO UPDATE SET "expires_at" = $5 | 26.374679 seconds | 1.1 | 198884 | 0 seconds | | reduce unecessary job churn | | SELECT o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = ANY($1)) AND (o0."inserted_at" >= $2) AND ((o0."worker" = $3) AND ((o0."queue" = $4) AND ((o0."args" @> $5 AND o0."args" <@ $6) AND $7))) LIMIT $8 | 23.199854 seconds | 0.9 | 143579 | 0 seconds | | reduce unecessary job churn | </details> Top offender here is the query used to get the list and count of remote isntaces used in prometheus stat reporting, the `peers` mastodon API and possibly some other pleroma API. This was also a high offender in previous reports, but got overshadowed by the awful attachment cleanup perf. This can be replaced with a query of the instances table which `EXPLAIN` suggests is _much_ cheaper. Results don’t match exactly, but nothing to worry about imho. Third-highest offender is again a stat query counting local and remtote users. Note how it’s run exactly twice as often as the instance query; this i because remote and local users are counted individually; combining this query should half the perf cost; both do a full table scan. Insertion of activites also shows up; there’s nothing we can do directly, though cutting down on unecessary indices (see #764) might help — and in particular the `counter_cache` table (which is populated by an insertion trigger) and it’s related indexes could be dropped; it’s only used to report non-critical stats in some Pleroma Admin API, but I suspect it of being relatively costly on activity operations. Everything else is Oban related; we currently create way too many jobs known to be noops; cutting down on this might help here too *(i have a half-finished patch, but no ETA)* *(this also helps with PostgreSQL bloat from deleted rows)*

Oneric referenced this issue from a commit

2024-11-16 23:42:01 +00:00

Don't try to cleanup remote attachments