Conversation

Notices

1. Embed this notice
   Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 08:04:36 JST Sexy Moon
   oh hey site is running like shit for no reason again
   • Embed this notice
     🌲-alist (threalist@social.fbxl.net)'s status on Wednesday, 20-Mar-2024 08:33:35 JST 🌲-alist

     in reply to
     Have you tried AI?
     Sexy Moon likes this.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 08:37:45 JST Sexy Moon

     in reply to

     • cjd
     @cjd still too busy to really get in there and find out why, just want to complain
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 08:37:46 JST cjd

     in reply to
     Slow queries ?
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:11:55 JST Sexy Moon

     in reply to

     • cjd
     @cjd I am getting trashed right now and it's all SELECT statements. not even the same statement.
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:11:57 JST cjd

     in reply to
     psql -c "ALTER SYSTEM SET log_min_duration_statement TO '5s'"
     
     tail -F /var/log/postgresql/postgresql-13-main.log
     
     Paste results here and crowdsource the solution
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:16:20 JST Sexy Moon

     in reply to

     • cjd
     @cjd let me disable the part that shows the arguments and I'll try
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:16:21 JST cjd

     in reply to
     tail -n 10000 /var/log/postgresql/postgresql-13-main.log | grep FROM | sort | uniq -c
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:23:46 JST Sexy Moon

     in reply to

     • cjd
     @cjd it's basically every query but here's all the ones over 100
     
     ```
     106 execute <unnamed>: SELECT TRUE FROM "thread_mutes" AS t0 WHERE (t0."user_id" = $1) AND (t0."context" = $2) LIMIT 1
     110 execute <unnamed>: SELECT b0."id", b0."user_id", b0."activity_id", b0."inserted_at", b0."updated_at" FROM "bookmarks" AS b0 WHERE (b0."user_id" = $1) AND (b0."activity_id" = $2)
     115 execute <unnamed>: 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."is_moderator", u0."is_admin", u0."show_role", 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."accepts_chat_messages", u0."last_active_at", u0."disclose_client", u0."pinned_objects", u0."is_suggested", u0."last_status_at", u0."birthday", u0."show_birthday", u0."language", u0."notification_settings", u0."blocks", u0."mutes", u0."muted_reblogs", u0."muted_notifications", u0."subscribers", u0."multi_factor_authentication_settings", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."ap_id" = $1)
     127 execute <unnamed>: SELECT TRUE FROM "user_relationships" AS u0 WHERE (((u0."relationship_type" = $1) AND (u0."source_id" = $2)) AND (u0."target_id" = $3)) LIMIT 1
     128 execute <unnamed>: SELECT u0."relationship_type", array_agg(u1."ap_id") FROM "user_relationships" AS u0 INNER JOIN "users" AS u1 ON u1."id" = u0."target_id" WHERE (u0."source_id" = $1) AND (u0."relationship_type" = ANY($2)) GROUP BY u0."relationship_type"
     145 execute <unnamed>: UPDATE "public"."oban_jobs" AS o0 SET "state" = $1, "attempted_at" = $2, "attempted_by" = $3, "attempt" = o0."attempt" + $4 WHERE (o0."id" IN (SELECT so0."id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = 'available') AND (so0."queue" = $5) AND (so0."attempt" < so0."max_attempts") ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $6 FOR UPDATE SKIP LOCKED)) 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"
     182 execute <unnamed>: 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)
     186 execute <unnamed>: SELECT f0."id", f0."user_id", f0."filter_id", f0."hide", f0."whole_word", f0."phrase", f0."context", f0."expires_at", f0."inserted_at", f0."updated_at" FROM "filters" AS f0 WHERE ((f0."expires_at" IS NULL) OR (f0."expires_at" > $1)) AND (f0."hide") AND (f0."user_id" = $2) ORDER BY f0."id" DESC
     197 execute <unnamed>: SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at", o0."id" FROM "objects" AS o0 WHERE (o0."id" = $1)
     222 execute <unnamed>: SELECT count(*) FROM "chat_message_references" AS c0 WHERE (c0."chat_id" = $1) AND (c0."unread" = TRUE)
     232 execute <unnamed>: SELECT c0."id", c0."object_id", c0."chat_id", c0."unread", c0."inserted_at", c0."updated_at" FROM "chat_message_references" AS c0 WHERE (c0."chat_id" = $1) ORDER BY c0."id" DESC LIMIT 1
     247 execute <unnamed>: SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at" FROM "activities" AS a0 INNER JOIN "users" AS u1 ON (a0."actor" = u1."ap_id") AND (u1."is_active" = TRUE) WHERE (associated_object_id((a0."data")) = $1) AND ((a0."data")->>'type' = $2)
     276 execute <unnamed>: SELECT o0."id", o0."token", o0."refresh_token", o0."scopes", o0."valid_until", o0."user_id", o0."app_id", o0."inserted_at", o0."updated_at" FROM "oauth_tokens" AS o0 WHERE (o0."token" = $1)
     ```
     
     I'd post this as markdown but pleroma's option for formatting posts disappeared.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:28:41 JST Sexy Moon

     in reply to

     • cjd
     @cjd I am logging all queries, I can't post the video because it has private data in it but I recorded a video of the select statements going by and it's a super fast stream. I'm getting hit by thousands of statements
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:28:42 JST cjd

     in reply to
     What time are you using? 5s? Set to 20s then re-run. It will weed out the worst queries.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:29:08 JST Sexy Moon

     in reply to

     • cjd
     @cjd trying
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:32:35 JST Sexy Moon

     in reply to

     • cjd
     @cjd based on slow queries I should disable remote deletes again
   • Embed this notice
     ✙ dcc :pedomustdie: :phear_slackware: (dcc@annihilation.social)'s status on Wednesday, 20-Mar-2024 09:35:39 JST ✙ dcc :pedomustdie: :phear_slackware:

     in reply to

     • cjd
     @cjd @Moon Mrf https://git.logografos.com/?p=anni;a=blob;f=static/modules/no_incoming_deletes.ex;h=0c5d9e7505fd897aef658a065ed86e578f827bad;hb=ea33a0d3427f8b30b82a6ddbc0ff7429cfaf8d91
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:35:40 JST cjd

     in reply to
     Aha, that explains it. I remember Pete saying that is implemented the worst way possible so they should always be rejected. BTW how do you disable them? It's not a perf issue for me but I wouldn't mind getting in front of it.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:41:03 JST Sexy Moon

     in reply to

     • cjd
     @cjd this MRF:
     
     Pleroma.Web.ActivityPub.MRF.VocabularyPolicy,
     
     with this config:
     
     config :pleroma, :mrf_vocabulary,
     reject: [ "Delete" ]
     Doughnut Lollipop 【記録係】:blobfoxgooglymlem: likes this.
   • Embed this notice
     ✙ dcc :pedomustdie: :phear_slackware: (dcc@annihilation.social)'s status on Wednesday, 20-Mar-2024 09:43:28 JST ✙ dcc :pedomustdie: :phear_slackware:

     in reply to

     • cjd
     @cjd @Moon >docker container which has worked
     That won't be for much longer :mel_laugh:
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:43:29 JST cjd

     in reply to

     • ✙ dcc :pedomustdie: :phear_slackware:
     Right, now I remember my instance is a docker container which has worked just fine and dandy for at least a year and I have no intention of touching it.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:47:58 JST Sexy Moon

     in reply to

     • cjd
     • ✙ dcc :pedomustdie: :phear_slackware:
     @cjd @dcc I think something is happening to the company that makes docker? something like that
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:47:59 JST cjd

     in reply to

     • ✙ dcc :pedomustdie: :phear_slackware:
     I have docker containers that have run happily for *years*.
     
     > Professional sysadmins hate him.
     > Get super high uptime with near zero effort using this one weird trick.
   • Embed this notice
     ✙ dcc :pedomustdie: :phear_slackware: (dcc@annihilation.social)'s status on Wednesday, 20-Mar-2024 09:48:28 JST ✙ dcc :pedomustdie: :phear_slackware:

     in reply to

     • cjd
     @cjd @Moon >> Professional sysadmins hate him.
     Hate is the wrong word, laugh is more correct
     >> Get super high uptime with near zero effort using this one weird trick.
     They call me 99 for a reason (no i dont use shitdocker)
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 09:52:45 JST Sexy Moon

     in reply to

     • cjd
     @cjd I am actually not sure if the MRF is working, if I configured it correctly. I don't see anything in my logs saying the activities were rejected and I'm seeing a few go through
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 09:52:46 JST cjd

     in reply to
     Oh yes cool, I can do that from the admin panel.
   • Embed this notice
     Sexy Moon (moon@shitposter.club)'s status on Wednesday, 20-Mar-2024 20:28:10 JST Sexy Moon

     in reply to

     • BSD/r000t
     • cjd
     @cjd @r000t I am conferring with the Pleroma team on this to try to track down performance issues that primarily affect SPC. They did find that deleting activities causes a subsequent delete of notifications related to that activity, that delete is one of the slow queries in my logs. We're already in discussion on how to improve this and in general make Mastodon mass-deleting activities not destroy Pleroma servers.
   • Embed this notice
     cjd (cjd@pkteerium.xyz)'s status on Wednesday, 20-Mar-2024 20:28:11 JST cjd

     in reply to

     • BSD/r000t
     It could be a cascading delete, but I think the issue is that when a user gets banned on mastodon, it sends a shower of delete requests out for everything they ever posted.
   • Embed this notice
     BSD/r000t (r000t@ligma.pro)'s status on Wednesday, 20-Mar-2024 20:28:13 JST BSD/r000t

     in reply to

     • cjd

     @cjd
     How the fuck is it anything other than looking up a single record and erasing it?

     It shouldn't even require rebuilding anything until the next vacuum. It should be no more intense than viewing a status.
     @Moon