search

MbinOrg / mbin

Mbin: a federated content aggregator, voting, discussion and microblogging platform (By the community, for the community)
https://joinmbin.org
GNU Affero General Public License v3.0
241 stars 17 forks source link

Long duration queries #1230

Open melroy89 opened 3 days ago

melroy89 commented 3 days ago

Describe the investigation

Try to investigate long running PostgreSQL queries (as well as very often running queries, which might also have a high impact).

Queries like: SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes';

Or: SELECT (total_exec_time / 1000 / 60) as total_min, mean_exec_time as avg_ms, calls, query FROM pg_stat_statements WHERE mean_exec_time > 100 ORDER BY 1 DESC LIMIT 500;

Please combine this last pg_stat_statements query with SELECT stats_reset FROM pg_stat_statements_info; information, so we better understand the statistics when the last reset was taken place.

Or any other stats that might help us.

Please do NOT sent truncated queries.

The above queries could give some insides. Also setting log_min_duration_statement to a specific duration, should create a log duration log. Also add to which value did you set log_min_duration_statement (this value is in microseconds, so don't set it too low please). A good value might be between 200 to 1000 μs, but feel free to play around.

Potentially improve PostgreSQL setup (eg. commit_delay, however this should not be the only solution).

On which Mbin instance did you find the bug?

Which Mbin version was running on the instance?

1.7.2

Expected outcome & potential solutions

Additional context

Hereby a request for every Mbin instance owner to provide the requested data (if you want to)

melroy89 commented 3 days ago

SELECT query 100% CPU? What is this only "SELECT" query doing??

image

@BentiGorlich I saw something similar on your duration stats (albeit it not using 100% CPU but still):

image

You're were saying it could be pgbouncer. Are we sure? What is going on.

Jerry-infosecexchange commented 3 days ago

Here is an extract of postgres logs. I enabled logging of long queries at 2024-11-19 16:44:14.625.
kbin.log.gz There are many other mbin related errors in the log file, so I'm including the whole thing in case it's of interest.

melroy89 commented 3 days ago

@Jerry-infosecexchange can you also say to which value did you set log_min_duration_statement?

Jerry-infosecexchange commented 3 days ago

Apologies. log_min_duration_statement = 500

melroy89 commented 3 days ago

that is only 500ms (0.5 seconds). Just saying.. You might want to increase this number, so we get better stats? Agreed, this might actually be a good value for now! Thanks.

I do see a lot of ERROR: deadlock detected. That doesn't sound good.

BentiGorlich commented 3 days ago

that is only 500ms (0.5 seconds). Just saying.. You might want to increase this number, so we get better stats?

If it is the frontend calling the code 500ms is on the brink of the user not wanting to wait anymore, so I think it is good to have it. At least for later 😁

I do see a lot of ERROR: deadlock detected. That doesn't sound good.

That is not a problem. The calling code will just be called ahaon after the transaction was rolled back due to the deadlock

melroy89 commented 3 days ago

The calling code will just be called ahaon after the transaction was rolled back due to the deadlock

Don't we want to avoid dead locks if possible? Possibly by changing the order or COMMIT;..

BentiGorlich commented 3 days ago

Since deadlocks occurr when 2 different processes try to exclusively access the same resource this is something that will just occurr in a highly multithreaded application like a website. Sure we want to avoid it where possible, its just not that often that it actually is possible

BentiGorlich commented 3 days ago

I think a big problem is the comment fetch. I think if we just cache the result without user specific filters and apply the filters to the cached IDs afterwards we would save a lot of CPU time. Then not all users will have the same amount of comments in a page, but I think that would be fine

melroy89 commented 3 days ago

I think if we just cache the result without user specific filters and apply the filters to the cached IDs afterwards we would save a lot of CPU time.

@BentiGorlich Yesn't, I'm of course not against caching. So yes we should implement caching where possible. But look at my initial investigations below, by just analyzing the data I have. Caching is only part of the solution, we definitely need to tackle those long running queries (some of which took almost 2 minutes to complete and some queries still almost 1 minute). Caching won't solve that (it might only temporally hide this problem).

Here is my log for now.. I'm not running log duration for that long. My setting for log_min_duration_statement is 400ms.

Feel free to share your log as well BentiGorlich.

postgresql-16-main.log

melroy89 commented 2 days ago

Since I now have only data from myself and from Jerry from the log_min_duration_statement log. I did an initial investigation.

I used duration: \d{4} regex to get duration of minimal 4 digits from my log. And I on Jerry's server log I could use duration: \d{6} (6 digits!) as well as 5 digits, since he got longer running queries.

All selected queries below have 2 lines, the first line is the SQL query, the second line is the parameters on this query (+ timestamp).

Jerry's log

So the 6 digits! (in ms) duration queries from fedia.io, with 182 total hits from only running a couple of hours with this setting on, is:

So the 6 digits query below took 101782 ms. Auw.. that is 1.7 minutes!

SELECT e0_.body AS body_0, e0_.lang AS lang_1, e0_.is_adult AS is_adult_2, e0_.favourite_count AS favourite_count_3, e0_.last_active AS last_active_4, e0_.ip AS ip_5, e0_.mentions AS mentions_6, e0_.id AS id_7, e0_.body_ts AS body_ts_8, e0_.up_votes AS up_votes_9, e0_.down_votes AS down_votes_10, e0_.ap_like_count AS ap_like_count_11, e0_.ap_dislike_count AS ap_dislike_count_12, e0_.ap_share_count AS ap_share_count_13, e0_.visibility AS visibility_14, e0_.ap_id AS ap_id_15, e0_.edited_at AS edited_at_16, e0_.created_at AS created_at_17, u1_.email AS email_18, u1_.username AS username_19, u1_.roles AS roles_20, u1_.followers_count AS followers_count_21, u1_.homepage AS homepage_22, u1_.about AS about_23, u1_.last_active AS last_active_24, u1_.marked_for_deletion_at AS marked_for_deletion_at_25, u1_.fields AS fields_26, u1_.oauth_azure_id AS oauth_azure_id_27, u1_.oauth_github_id AS oauth_github_id_28, u1_.oauth_google_id AS oauth_google_id_29, u1_.oauth_facebook_id AS oauth_facebook_id_30, u1_.oauth_privacyportal_id AS oauth_privacyportal_id_31, u1_.oauth_keycloak_id AS oauth_keycloak_id_32, u1_.oauth_simple_login_id AS oauth_simple_login_id_33, u1_.oauth_discord_id AS oauth_discord_id_34, u1_.oauth_zitadel_id AS oauth_zitadel_id_35, u1_.oauth_authentik_id AS oauth_authentik_id_36, u1_.hide_adult AS hide_adult_37, u1_.preferred_languages AS preferred_languages_38, u1_.featured_magazines AS featured_magazines_39, u1_.show_profile_subscriptions AS show_profile_subscriptions_40, u1_.show_profile_followings AS show_profile_followings_41, u1_.notify_on_new_entry AS notify_on_new_entry_42, u1_.notify_on_new_entry_reply AS notify_on_new_entry_reply_43, u1_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_44, u1_.notify_on_new_post AS notify_on_new_post_45, u1_.notify_on_new_post_reply AS notify_on_new_post_reply_46, u1_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_47, u1_.add_mentions_entries AS add_mentions_entries_48, u1_.add_mentions_posts AS add_mentions_posts_49, u1_.is_banned AS is_banned_50, u1_.is_verified AS is_verified_51, u1_.is_deleted AS is_deleted_52, u1_.custom_css AS custom_css_53, u1_.ignore_magazines_custom_css AS ignore_magazines_custom_css_54, u1_.id AS id_55, u1_.password AS password_56, u1_.totp_secret AS totp_secret_57, u1_.totp_backup_codes AS totp_backup_codes_58, u1_.type AS type_59, u1_.ap_id AS ap_id_60, u1_.ap_profile_id AS ap_profile_id_61, u1_.ap_public_url AS ap_public_url_62, u1_.ap_followers_url AS ap_followers_url_63, u1_.ap_attributed_to_url AS ap_attributed_to_url_64, u1_.ap_featured_url AS ap_featured_url_65, u1_.ap_followers_count AS ap_followers_count_66, u1_.ap_inbox_url AS ap_inbox_url_67, u1_.ap_domain AS ap_domain_68, u1_.ap_preferred_username AS ap_preferred_username_69, u1_.ap_discoverable AS ap_discoverable_70, u1_.ap_manually_approves_followers AS ap_manually_approves_followers_71, u1_.private_key AS private_key_72, u1_.public_key AS public_key_73, u1_.ap_fetched_at AS ap_fetched_at_74, u1_.ap_deleted_at AS ap_deleted_at_75, u1_.ap_timeout_at AS ap_timeout_at_76, u1_.visibility AS visibility_77, u1_.created_at AS created_at_78, e0_.user_id AS user_id_79, e0_.entry_id AS entry_id_80, e0_.magazine_id AS magazine_id_81, e0_.image_id AS image_id_82, e0_.parent_id AS parent_id_83, e0_.root_id AS root_id_84, u1_.avatar_id AS avatar_id_85, u1_.cover_id AS cover_id_86 FROM entry_comment e0_ INNER JOIN "user" u1_ ON e0_.user_id = u1_.id INNER JOIN entry e2_ ON e0_.entry_id = e2_.id INNER JOIN entry e3_ ON e0_.entry_id = e3_.id INNER JOIN magazine m4_ ON e2_.magazine_id = m4_.id WHERE e0_.visibility IN ($1, $2, $3) AND u1_.visibility IN ($4) AND e2_.visibility = $5 AND e0_.parent_id IS NULL AND m4_.is_adult = $6 AND e2_.is_adult = $7 AND (NOT (EXISTS (SELECT h5_.id FROM hashtag_link h5_ INNER JOIN hashtag h6_ ON h5_.hashtag_id = h6_.id WHERE h6_.banned = true AND h5_.entry_comment_id = e0_.id))) ORDER BY e0_.up_votes + e0_.favourite_count - e0_.down_votes DESC, e0_.created_at DESC, e0_.id DESC LIMIT 15 OFFSET 1743135

2024-11-19 18:00:32.296 UTC [2198679] kbin@kbin DETAIL:  parameters: $1 = 'soft_deleted', $2 = 'visible', $3 = 'trashed', $4 = 'visible', $5 = 'visible', $6 = 'f', $7 = 'f'

Also with 5 digit count ms duration, I see a lot of these queries above. But also new once:

SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_7 FROM (SELECT e0_.body AS body_0, e0_.lang AS lang_1, e0_.is_adult AS is_adult_2, e0_.favourite_count AS favourite_count_3, e0_.last_active AS last_active_4, e0_.ip AS ip_5, e0_.mentions AS mentions_6, e0_.id AS id_7, e0_.body_ts AS body_ts_8, e0_.up_votes AS up_votes_9, e0_.down_votes AS down_votes_10, e0_.ap_like_count AS ap_like_count_11, e0_.ap_dislike_count AS ap_dislike_count_12, e0_.ap_share_count AS ap_share_count_13, e0_.visibility AS visibility_14, e0_.ap_id AS ap_id_15, e0_.edited_at AS edited_at_16, e0_.created_at AS created_at_17, u1_.email AS email_18, u1_.username AS username_19, u1_.roles AS roles_20, u1_.followers_count AS followers_count_21, u1_.homepage AS homepage_22, u1_.about AS about_23, u1_.last_active AS last_active_24, u1_.marked_for_deletion_at AS marked_for_deletion_at_25, u1_.fields AS fields_26, u1_.oauth_azure_id AS oauth_azure_id_27, u1_.oauth_github_id AS oauth_github_id_28, u1_.oauth_google_id AS oauth_google_id_29, u1_.oauth_facebook_id AS oauth_facebook_id_30, u1_.oauth_privacyportal_id AS oauth_privacyportal_id_31, u1_.oauth_keycloak_id AS oauth_keycloak_id_32, u1_.oauth_simple_login_id AS oauth_simple_login_id_33, u1_.oauth_discord_id AS oauth_discord_id_34, u1_.oauth_zitadel_id AS oauth_zitadel_id_35, u1_.oauth_authentik_id AS oauth_authentik_id_36, u1_.hide_adult AS hide_adult_37, u1_.preferred_languages AS preferred_languages_38, u1_.featured_magazines AS featured_magazines_39, u1_.show_profile_subscriptions AS show_profile_subscriptions_40, u1_.show_profile_followings AS show_profile_followings_41, u1_.notify_on_new_entry AS notify_on_new_entry_42, u1_.notify_on_new_entry_reply AS notify_on_new_entry_reply_43, u1_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_44, u1_.notify_on_new_post AS notify_on_new_post_45, u1_.notify_on_new_post_reply AS notify_on_new_post_reply_46, u1_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_47, u1_.add_mentions_entries AS add_mentions_entries_48, u1_.add_mentions_posts AS add_mentions_posts_49, u1_.is_banned AS is_banned_50, u1_.is_verified AS is_verified_51, u1_.is_deleted AS is_deleted_52, u1_.custom_css AS custom_css_53, u1_.ignore_magazines_custom_css AS ignore_magazines_custom_css_54, u1_.id AS id_55, u1_.password AS password_56, u1_.totp_secret AS totp_secret_57, u1_.totp_backup_codes AS totp_backup_codes_58, u1_.type AS type_59, u1_.ap_id AS ap_id_60, u1_.ap_profile_id AS ap_profile_id_61, u1_.ap_public_url AS ap_public_url_62, u1_.ap_followers_url AS ap_followers_url_63, u1_.ap_attributed_to_url AS ap_attributed_to_url_64, u1_.ap_featured_url AS ap_featured_url_65, u1_.ap_followers_count AS ap_followers_count_66, u1_.ap_inbox_url AS ap_inbox_url_67, u1_.ap_domain AS ap_domain_68, u1_.ap_preferred_username AS ap_preferred_username_69, u1_.ap_discoverable AS ap_discoverable_70, u1_.ap_manually_approves_followers AS ap_manually_approves_followers_71, u1_.private_key AS private_key_72, u1_.public_key AS public_key_73, u1_.ap_fetched_at AS ap_fetched_at_74, u1_.ap_deleted_at AS ap_deleted_at_75, u1_.ap_timeout_at AS ap_timeout_at_76, u1_.visibility AS visibility_77, u1_.created_at AS created_at_78 FROM entry_comment e0_ INNER JOIN "user" u1_ ON e0_.user_id = u1_.id INNER JOIN entry e2_ ON e0_.entry_id = e2_.id INNER JOIN entry e3_ ON e0_.entry_id = e3_.id INNER JOIN magazine m4_ ON e2_.magazine_id = m4_.id WHERE e0_.visibility IN ($1, $2, $3) AND u1_.visibility IN ($4) AND e2_.visibility = $5 AND e0_.parent_id IS NULL AND m4_.is_adult = $6 AND e2_.is_adult = $7 AND (NOT (EXISTS (SELECT h5_.id FROM hashtag_link h5_ INNER JOIN hashtag h6_ ON h5_.hashtag_id = h6_.id WHERE h6_.banned = true AND h5_.entry_comment_id = e0_.id))) ORDER BY e0_.up_votes + e0_.favourite_count - e0_.down_votes DESC, e0_.created_at DESC, e0_.id DESC) dctrn_result) dctrn_table

2024-11-19 16:55:50.055 UTC [1986575] kbin@kbin DETAIL:  parameters: $1 = 'soft_deleted', $2 = 'visible', $3 = 'trashed', $4 = 'visible', $5 = 'visible', $6 = 'f', $7 = 'f'

And:


UPDATE "user" SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

2024-11-19 16:47:24.663 UTC [1957826] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 16:47:11+00', $2 = '2023-07-08 09:23:13+00', $3 = '145544'
UPDATE magazine SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

2024-11-19 16:52:52.121 UTC [1974723] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 16:52:39+00', $2 = '2023-06-16 14:59:57+00', $3 = '4969'

And also:

UPDATE instance SET last_failed_deliver = $1, failed_delivers = $2 WHERE id = $3

2024-11-19 17:23:52.820 UTC [2030990] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 17:23:42+00', $2 = '193', $3 = '39'
UPDATE instance SET last_successful_receive = $1 WHERE id = $2

2024-11-19 18:14:20.996 UTC [2250178] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 18:13:46+00', $2 = '27'

Melroy's log

Like I said before, I don't have longer duration queries then 4 digits duration.

UPDATE magazine SET subscriptions_count = $1, ap_followers_count = $2, ap_fetched_at = $3, created_at = $4 WHERE id = $5

2024-11-19 23:31:06.572 CET [180564] kbin@kbin DETAIL:  parameters: $1 = '5926', $2 = '5926', $3 = '2024-11-19 23:31:01+01', $4 = '2020-06-06 22:08:20+02', $5 = '765'
UPDATE "user" SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

2024-11-19 23:29:33.225 CET [177433] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 23:29:30+01', $2 = '2023-06-22 12:11:41+02', $3 = '356318'
SELECT sess_data, sess_lifetime FROM sessions WHERE sess_id = $1 FOR UPDATE

2024-11-19 23:12:53.043 CET [147742] kbin@kbin DETAIL:  parameters: $1 = 'secret_string'
UPDATE "user" SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

2024-11-19 23:11:00.007 CET [143931] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 23:10:55+01', $2 = '2023-07-17 23:19:21+02', $3 = '75171'

Same as Jerry saw:

SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_7 FROM (SELECT e0_.body AS body_0, e0_.lang AS lang_1, e0_.is_adult AS is_adult_2, e0_.favourite_count AS favourite_count_3, e0_.last_active AS last_active_4, e0_.ip AS ip_5, e0_.mentions AS mentions_6, e0_.id AS id_7, e0_.body_ts AS body_ts_8, e0_.up_votes AS up_votes_9, e0_.down_votes AS down_votes_10, e0_.ap_like_count AS ap_like_count_11, e0_.ap_dislike_count AS ap_dislike_count_12, e0_.ap_share_count AS ap_share_count_13, e0_.visibility AS visibility_14, e0_.ap_id AS ap_id_15, e0_.edited_at AS edited_at_16, e0_.created_at AS created_at_17, u1_.email AS email_18, u1_.username AS username_19, u1_.roles AS roles_20, u1_.followers_count AS followers_count_21, u1_.homepage AS homepage_22, u1_.about AS about_23, u1_.last_active AS last_active_24, u1_.marked_for_deletion_at AS marked_for_deletion_at_25, u1_.fields AS fields_26, u1_.oauth_azure_id AS oauth_azure_id_27, u1_.oauth_github_id AS oauth_github_id_28, u1_.oauth_google_id AS oauth_google_id_29, u1_.oauth_facebook_id AS oauth_facebook_id_30, u1_.oauth_privacyportal_id AS oauth_privacyportal_id_31, u1_.oauth_keycloak_id AS oauth_keycloak_id_32, u1_.oauth_simple_login_id AS oauth_simple_login_id_33, u1_.oauth_discord_id AS oauth_discord_id_34, u1_.oauth_zitadel_id AS oauth_zitadel_id_35, u1_.oauth_authentik_id AS oauth_authentik_id_36, u1_.hide_adult AS hide_adult_37, u1_.preferred_languages AS preferred_languages_38, u1_.featured_magazines AS featured_magazines_39, u1_.show_profile_subscriptions AS show_profile_subscriptions_40, u1_.show_profile_followings AS show_profile_followings_41, u1_.notify_on_new_entry AS notify_on_new_entry_42, u1_.notify_on_new_entry_reply AS notify_on_new_entry_reply_43, u1_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_44, u1_.notify_on_new_post AS notify_on_new_post_45, u1_.notify_on_new_post_reply AS notify_on_new_post_reply_46, u1_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_47, u1_.add_mentions_entries AS add_mentions_entries_48, u1_.add_mentions_posts AS add_mentions_posts_49, u1_.is_banned AS is_banned_50, u1_.is_verified AS is_verified_51, u1_.is_deleted AS is_deleted_52, u1_.custom_css AS custom_css_53, u1_.ignore_magazines_custom_css AS ignore_magazines_custom_css_54, u1_.id AS id_55, u1_.password AS password_56, u1_.totp_secret AS totp_secret_57, u1_.totp_backup_codes AS totp_backup_codes_58, u1_.type AS type_59, u1_.ap_id AS ap_id_60, u1_.ap_profile_id AS ap_profile_id_61, u1_.ap_public_url AS ap_public_url_62, u1_.ap_followers_url AS ap_followers_url_63, u1_.ap_attributed_to_url AS ap_attributed_to_url_64, u1_.ap_featured_url AS ap_featured_url_65, u1_.ap_followers_count AS ap_followers_count_66, u1_.ap_inbox_url AS ap_inbox_url_67, u1_.ap_domain AS ap_domain_68, u1_.ap_preferred_username AS ap_preferred_username_69, u1_.ap_discoverable AS ap_discoverable_70, u1_.ap_manually_approves_followers AS ap_manually_approves_followers_71, u1_.private_key AS private_key_72, u1_.public_key AS public_key_73, u1_.ap_fetched_at AS ap_fetched_at_74, u1_.ap_deleted_at AS ap_deleted_at_75, u1_.ap_timeout_at AS ap_timeout_at_76, u1_.visibility AS visibility_77, u1_.created_at AS created_at_78 FROM entry_comment e0_ INNER JOIN "user" u1_ ON e0_.user_id = u1_.id INNER JOIN entry e2_ ON e0_.entry_id = e2_.id AND (e2_.magazine_id = $1) INNER JOIN entry e3_ ON e0_.entry_id = e3_.id INNER JOIN magazine m4_ ON e2_.magazine_id = m4_.id WHERE e0_.visibility IN ($2, $3, $4) AND u1_.visibility IN ($5) AND e2_.visibility = $6 AND e0_.parent_id IS NULL AND m4_.is_adult = $7 AND e2_.is_adult = $8 AND (NOT (EXISTS (SELECT h5_.id FROM hashtag_link h5_ INNER JOIN hashtag h6_ ON h5_.hashtag_id = h6_.id WHERE h6_.banned = true AND h5_.entry_comment_id = e0_.id))) ORDER BY e0_.created_at DESC, e0_.created_at DESC, e0_.id DESC) dctrn_result) dctrn_table

2024-11-19 22:49:17.699 CET [101677] kbin@kbin DETAIL:  parameters: $1 = '347', $2 = 'soft_deleted', $3 = 'visible', $4 = 'trashed', $5 = 'visible', $6 = 'visible', $7 = 'f', $8 = 'f'

Same as Jerry:

UPDATE magazine SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

2024-11-19 20:26:32.622 CET [2836306] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 20:26:27+01', $2 = '2023-06-03 18:20:25+02', $3 = '456'

Same as Jerry:

UPDATE "user" SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

2024-11-19 20:26:32.622 CET [2836416] kbin@kbin DETAIL:  parameters: $1 = '2024-11-19 20:26:27+01', $2 = '2024-06-22 14:29:35+02', $3 = '487354'

Inserts queries (which often take longer then select queries as expected):

INSERT INTO "user" (email, username, roles, followers_count, homepage, about, last_active, marked_for_deletion_at, fields, oauth_azure_id, oauth_github_id, oauth_google_id, oauth_facebook_id, oauth_privacyportal_id, oauth_keycloak_id, oauth_simple_login_id, oauth_discord_id, oauth_zitadel_id, oauth_authentik_id, hide_adult, preferred_languages, featured_magazines, show_profile_subscriptions, show_profile_followings, notify_on_new_entry, notify_on_new_entry_reply, notify_on_new_entry_comment_reply, notify_on_new_post, notify_on_new_post_reply, notify_on_new_post_comment_reply, add_mentions_entries, add_mentions_posts, is_banned, is_verified, is_deleted, custom_css, ignore_magazines_custom_css, id, password, totp_secret, totp_backup_codes, type, ap_id, ap_profile_id, ap_public_url, ap_followers_url, ap_attributed_to_url, ap_featured_url, ap_followers_count, ap_inbox_url, ap_domain, ap_preferred_username, ap_discoverable, ap_manually_approves_followers, private_key, public_key, ap_fetched_at, ap_deleted_at, ap_timeout_at, visibility, created_at, avatar_id, cover_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63)

2024-11-19 21:10:35.904 CET [2941547] kbin@kbin DETAIL:  parameters: $1 = 'Tonibort@mastodon.social', $2 = '@Tonibort@mastodon.social', $3 = '[]', $4 = '0', $5 = 'front', $6 = NULL, $7 = '2024-11-19 21:10:29+01', $8 = NULL, $9 = NULL, $10 = NULL, $11 = NULL, $12 = NULL, $13 = NULL, $14 = NULL, $15 = NULL, $16 = NULL, $17 = NULL, $18 = NULL, $19 = NULL, $20 = 't', $21 = '[]', $22 = 'N;', $23 = 'f', $24 = 't', $25 = 'f', $26 = 't', $27 = 't', $28 = 'f', $29 = 't', $30 = 't', $31 = 'f', $32 = 't', $33 = 'f', $34 = 'f', $35 = 'f', $36 = NULL, $37 = 'f', $38 = '706674', $39 = 'secret', $40 = NULL, $41 = '[]', $42 = 'Person', $43 = 'Tonibort@mastodon.social', $44 = 'https://mastodon.social/users/Tonibort', $45 = NULL, $46 = NULL, $47 = NULL, $48 = NULL, $49 = NULL, $50 = NULL, $51 = NULL, $52 = NULL, $53 = NULL, $54 = NULL, $55 = NULL, $56 = NULL, $57 = NULL, $58 = NULL, $59 = NULL, $60 = 'visible', $61 = '2024-11-19 21:10:29+01', $62 = NULL, $63 = NULL
2
BentiGorlich commented 2 days ago

I now turned on mkne. Small info: commit delay is in microseconds, not milliseconds 😅

BentiGorlich commented 2 days ago

Here are my findings from only 2 hours:

Selects

SELECT t0.slug AS slug_1, t0.title AS title_2, t0.url AS url_3, t0.body AS body_4, t0.type AS type_5, t0.lang AS lang_6, t0.is_oc AS is_oc_7, t0.has_embed AS has_embed_8, t0.comment_count AS comment_count_9, t0.favourite_count AS favourite_count_10, t0.score AS score_11, t0.is_adult AS is_adult_12, t0.sticky AS sticky_13, t0.last_active AS last_active_14, t0.ip AS ip_15, t0.mentions AS mentions_16, t0.id AS id_17, t0.title_ts AS title_ts_18, t0.body_ts AS body_ts_19, t0.up_votes AS up_votes_20, t0.down_votes AS down_votes_21, t0.ap_like_count AS ap_like_count_22, t0.ap_dislike_count AS ap_dislike_count_23, t0.ap_share_count AS ap_share_count_24, t0.ranking AS ranking_25, t0.visibility AS visibility_26, t0.ap_id AS ap_id_27, t0.edited_at AS edited_at_28, t0.created_at AS created_at_29, t0.user_id AS user_id_30, t0.magazine_id AS magazine_id_31, t0.image_id AS image_id_32, t0.domain_id AS domain_id_33 FROM entry t0 WHERE t0.domain_id = $1

Runtimes:

SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_7 FROM (SELECT e0_.body AS body_0, e0_.lang AS lang_1, e0_.is_adult AS is_adult_2, e0_.favourite_count AS favourite_count_3, e0_.last_active AS last_active_4, e0_.ip AS ip_5, e0_.mentions AS mentions_6, e0_.id AS id_7, e0_.body_ts AS body_ts_8, e0_.up_votes AS up_votes_9, e0_.down_votes AS down_votes_10, e0_.ap_like_count AS ap_like_count_11, e0_.ap_dislike_count AS ap_dislike_count_12, e0_.ap_share_count AS ap_share_count_13, e0_.visibility AS visibility_14, e0_.ap_id AS ap_id_15, e0_.edited_at AS edited_at_16, e0_.created_at AS created_at_17, u1_.email AS email_18, u1_.username AS username_19, u1_.roles AS roles_20, u1_.followers_count AS followers_count_21, u1_.homepage AS homepage_22, u1_.about AS about_23, u1_.last_active AS last_active_24, u1_.marked_for_deletion_at AS marked_for_deletion_at_25, u1_.fields AS fields_26, u1_.oauth_azure_id AS oauth_azure_id_27, u1_.oauth_github_id AS oauth_github_id_28, u1_.oauth_google_id AS oauth_google_id_29, u1_.oauth_facebook_id AS oauth_facebook_id_30, u1_.oauth_privacyportal_id AS oauth_privacyportal_id_31, u1_.oauth_keycloak_id AS oauth_keycloak_id_32, u1_.oauth_simple_login_id AS oauth_simple_login_id_33, u1_.oauth_discord_id AS oauth_discord_id_34, u1_.oauth_zitadel_id AS oauth_zitadel_id_35, u1_.oauth_authentik_id AS oauth_authentik_id_36, u1_.hide_adult AS hide_adult_37, u1_.preferred_languages AS preferred_languages_38, u1_.featured_magazines AS featured_magazines_39, u1_.show_profile_subscriptions AS show_profile_subscriptions_40, u1_.show_profile_followings AS show_profile_followings_41, u1_.notify_on_new_entry AS notify_on_new_entry_42, u1_.notify_on_new_entry_reply AS notify_on_new_entry_reply_43, u1_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_44, u1_.notify_on_new_post AS notify_on_new_post_45, u1_.notify_on_new_post_reply AS notify_on_new_post_reply_46, u1_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_47, u1_.add_mentions_entries AS add_mentions_entries_48, u1_.add_mentions_posts AS add_mentions_posts_49, u1_.is_banned AS is_banned_50, u1_.is_verified AS is_verified_51, u1_.is_deleted AS is_deleted_52, u1_.custom_css AS custom_css_53, u1_.ignore_magazines_custom_css AS ignore_magazines_custom_css_54, u1_.id AS id_55, u1_.password AS password_56, u1_.totp_secret AS totp_secret_57, u1_.totp_backup_codes AS totp_backup_codes_58, u1_.type AS type_59, u1_.ap_id AS ap_id_60, u1_.ap_profile_id AS ap_profile_id_61, u1_.ap_public_url AS ap_public_url_62, u1_.ap_followers_url AS ap_followers_url_63, u1_.ap_attributed_to_url AS ap_attributed_to_url_64, u1_.ap_featured_url AS ap_featured_url_65, u1_.ap_followers_count AS ap_followers_count_66, u1_.ap_inbox_url AS ap_inbox_url_67, u1_.ap_domain AS ap_domain_68, u1_.ap_preferred_username AS ap_preferred_username_69, u1_.ap_discoverable AS ap_discoverable_70, u1_.ap_manually_approves_followers AS ap_manually_approves_followers_71, u1_.private_key AS private_key_72, u1_.public_key AS public_key_73, u1_.ap_fetched_at AS ap_fetched_at_74, u1_.ap_deleted_at AS ap_deleted_at_75, u1_.ap_timeout_at AS ap_timeout_at_76, u1_.visibility AS visibility_77, u1_.created_at AS created_at_78 FROM entry_comment e0_ INNER JOIN "user" u1_ ON e0_.user_id = u1_.id INNER JOIN entry e2_ ON e0_.entry_id = e2_.id INNER JOIN entry e3_ ON e0_.entry_id = e3_.id INNER JOIN domain d4_ ON e3_.domain_id = d4_.id INNER JOIN magazine m5_ ON e2_.magazine_id = m5_.id WHERE e0_.visibility IN ($1, $2, $3) AND u1_.visibility IN ($4) AND e2_.visibility = $5 AND d4_.name = $6 AND e0_.parent_id IS NULL AND m5_.is_adult = $7 AND e2_.is_adult = $8 AND (NOT (EXISTS (SELECT h6_.id FROM hashtag_link h6_ INNER JOIN hashtag h7_ ON h6_.hashtag_id = h7_.id WHERE h7_.banned = true AND h6_.entry_comment_id = e0_.id))) ORDER BY e0_.up_votes + e0_.favourite_count - e0_.down_votes DESC, e0_.created_at DESC, e0_.id DESC) dctrn_result) dctrn_table

Runtimes:

SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_7 FROM (SELECT e0_.body AS body_0, e0_.lang AS lang_1, e0_.is_adult AS is_adult_2, e0_.favourite_count AS favourite_count_3, e0_.last_active AS last_active_4, e0_.ip AS ip_5, e0_.mentions AS mentions_6, e0_.id AS id_7, e0_.body_ts AS body_ts_8, e0_.up_votes AS up_votes_9, e0_.down_votes AS down_votes_10, e0_.ap_like_count AS ap_like_count_11, e0_.ap_dislike_count AS ap_dislike_count_12, e0_.ap_share_count AS ap_share_count_13, e0_.visibility AS visibility_14, e0_.ap_id AS ap_id_15, e0_.edited_at AS edited_at_16, e0_.created_at AS created_at_17, u1_.email AS email_18, u1_.username AS username_19, u1_.roles AS roles_20, u1_.followers_count AS followers_count_21, u1_.homepage AS homepage_22, u1_.about AS about_23, u1_.last_active AS last_active_24, u1_.marked_for_deletion_at AS marked_for_deletion_at_25, u1_.fields AS fields_26, u1_.oauth_azure_id AS oauth_azure_id_27, u1_.oauth_github_id AS oauth_github_id_28, u1_.oauth_google_id AS oauth_google_id_29, u1_.oauth_facebook_id AS oauth_facebook_id_30, u1_.oauth_privacyportal_id AS oauth_privacyportal_id_31, u1_.oauth_keycloak_id AS oauth_keycloak_id_32, u1_.oauth_simple_login_id AS oauth_simple_login_id_33, u1_.oauth_discord_id AS oauth_discord_id_34, u1_.oauth_zitadel_id AS oauth_zitadel_id_35, u1_.oauth_authentik_id AS oauth_authentik_id_36, u1_.hide_adult AS hide_adult_37, u1_.preferred_languages AS preferred_languages_38, u1_.featured_magazines AS featured_magazines_39, u1_.show_profile_subscriptions AS show_profile_subscriptions_40, u1_.show_profile_followings AS show_profile_followings_41, u1_.notify_on_new_entry AS notify_on_new_entry_42, u1_.notify_on_new_entry_reply AS notify_on_new_entry_reply_43, u1_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_44, u1_.notify_on_new_post AS notify_on_new_post_45, u1_.notify_on_new_post_reply AS notify_on_new_post_reply_46, u1_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_47, u1_.add_mentions_entries AS add_mentions_entries_48, u1_.add_mentions_posts AS add_mentions_posts_49, u1_.is_banned AS is_banned_50, u1_.is_verified AS is_verified_51, u1_.is_deleted AS is_deleted_52, u1_.custom_css AS custom_css_53, u1_.ignore_magazines_custom_css AS ignore_magazines_custom_css_54, u1_.id AS id_55, u1_.password AS password_56, u1_.totp_secret AS totp_secret_57, u1_.totp_backup_codes AS totp_backup_codes_58, u1_.type AS type_59, u1_.ap_id AS ap_id_60, u1_.ap_profile_id AS ap_profile_id_61, u1_.ap_public_url AS ap_public_url_62, u1_.ap_followers_url AS ap_followers_url_63, u1_.ap_attributed_to_url AS ap_attributed_to_url_64, u1_.ap_featured_url AS ap_featured_url_65, u1_.ap_followers_count AS ap_followers_count_66, u1_.ap_inbox_url AS ap_inbox_url_67, u1_.ap_domain AS ap_domain_68, u1_.ap_preferred_username AS ap_preferred_username_69, u1_.ap_discoverable AS ap_discoverable_70, u1_.ap_manually_approves_followers AS ap_manually_approves_followers_71, u1_.private_key AS private_key_72, u1_.public_key AS public_key_73, u1_.ap_fetched_at AS ap_fetched_at_74, u1_.ap_deleted_at AS ap_deleted_at_75, u1_.ap_timeout_at AS ap_timeout_at_76, u1_.visibility AS visibility_77, u1_.created_at AS created_at_78 FROM entry_comment e0_ INNER JOIN "user" u1_ ON e0_.user_id = u1_.id INNER JOIN entry e2_ ON e0_.entry_id = e2_.id AND (e2_.magazine_id = $1) INNER JOIN entry e3_ ON e0_.entry_id = e3_.id INNER JOIN magazine m4_ ON e2_.magazine_id = m4_.id WHERE e0_.visibility IN ($2, $3, $4) AND u1_.visibility IN ($5) AND e2_.visibility = $6 AND e0_.parent_id IS NULL AND m4_.is_adult = $7 AND e2_.is_adult = $8 AND (NOT (EXISTS (SELECT h5_.id FROM hashtag_link h5_ INNER JOIN hashtag h6_ ON h5_.hashtag_id = h6_.id WHERE h6_.banned = true AND h5_.entry_comment_id = e0_.id))) ORDER BY e0_.up_votes DESC, e0_.created_at DESC, e0_.id DESC) dctrn_result) dctrn_table

Runtimes:

SELECT e0_.body AS body_0, e0_.lang AS lang_1, e0_.is_adult AS is_adult_2, e0_.favourite_count AS favourite_count_3, e0_.last_active AS last_active_4, e0_.ip AS ip_5, e0_.mentions AS mentions_6, e0_.id AS id_7, e0_.body_ts AS body_ts_8, e0_.up_votes AS up_votes_9, e0_.down_votes AS down_votes_10, e0_.ap_like_count AS ap_like_count_11, e0_.ap_dislike_count AS ap_dislike_count_12, e0_.ap_share_count AS ap_share_count_13, e0_.visibility AS visibility_14, e0_.ap_id AS ap_id_15, e0_.edited_at AS edited_at_16, e0_.created_at AS created_at_17, u1_.email AS email_18, u1_.username AS username_19, u1_.roles AS roles_20, u1_.followers_count AS followers_count_21, u1_.homepage AS homepage_22, u1_.about AS about_23, u1_.last_active AS last_active_24, u1_.marked_for_deletion_at AS marked_for_deletion_at_25, u1_.fields AS fields_26, u1_.oauth_azure_id AS oauth_azure_id_27, u1_.oauth_github_id AS oauth_github_id_28, u1_.oauth_google_id AS oauth_google_id_29, u1_.oauth_facebook_id AS oauth_facebook_id_30, u1_.oauth_privacyportal_id AS oauth_privacyportal_id_31, u1_.oauth_keycloak_id AS oauth_keycloak_id_32, u1_.oauth_simple_login_id AS oauth_simple_login_id_33, u1_.oauth_discord_id AS oauth_discord_id_34, u1_.oauth_zitadel_id AS oauth_zitadel_id_35, u1_.oauth_authentik_id AS oauth_authentik_id_36, u1_.hide_adult AS hide_adult_37, u1_.preferred_languages AS preferred_languages_38, u1_.featured_magazines AS featured_magazines_39, u1_.show_profile_subscriptions AS show_profile_subscriptions_40, u1_.show_profile_followings AS show_profile_followings_41, u1_.notify_on_new_entry AS notify_on_new_entry_42, u1_.notify_on_new_entry_reply AS notify_on_new_entry_reply_43, u1_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_44, u1_.notify_on_new_post AS notify_on_new_post_45, u1_.notify_on_new_post_reply AS notify_on_new_post_reply_46, u1_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_47, u1_.add_mentions_entries AS add_mentions_entries_48, u1_.add_mentions_posts AS add_mentions_posts_49, u1_.is_banned AS is_banned_50, u1_.is_verified AS is_verified_51, u1_.is_deleted AS is_deleted_52, u1_.custom_css AS custom_css_53, u1_.ignore_magazines_custom_css AS ignore_magazines_custom_css_54, u1_.id AS id_55, u1_.password AS password_56, u1_.totp_secret AS totp_secret_57, u1_.totp_backup_codes AS totp_backup_codes_58, u1_.type AS type_59, u1_.ap_id AS ap_id_60, u1_.ap_profile_id AS ap_profile_id_61, u1_.ap_public_url AS ap_public_url_62, u1_.ap_followers_url AS ap_followers_url_63, u1_.ap_attributed_to_url AS ap_attributed_to_url_64, u1_.ap_featured_url AS ap_featured_url_65, u1_.ap_followers_count AS ap_followers_count_66, u1_.ap_inbox_url AS ap_inbox_url_67, u1_.ap_domain AS ap_domain_68, u1_.ap_preferred_username AS ap_preferred_username_69, u1_.ap_discoverable AS ap_discoverable_70, u1_.ap_manually_approves_followers AS ap_manually_approves_followers_71, u1_.private_key AS private_key_72, u1_.public_key AS public_key_73, u1_.ap_fetched_at AS ap_fetched_at_74, u1_.ap_deleted_at AS ap_deleted_at_75, u1_.ap_timeout_at AS ap_timeout_at_76, u1_.visibility AS visibility_77, u1_.created_at AS created_at_78, e0_.user_id AS user_id_79, e0_.entry_id AS entry_id_80, e0_.magazine_id AS magazine_id_81, e0_.image_id AS image_id_82, e0_.parent_id AS parent_id_83, e0_.root_id AS root_id_84, u1_.avatar_id AS avatar_id_85, u1_.cover_id AS cover_id_86 FROM entry_comment e0_ INNER JOIN "user" u1_ ON e0_.user_id = u1_.id INNER JOIN entry e2_ ON e0_.entry_id = e2_.id AND (e2_.magazine_id = $1) INNER JOIN entry e3_ ON e0_.entry_id = e3_.id INNER JOIN magazine m4_ ON e2_.magazine_id = m4_.id WHERE e0_.visibility IN ($2, $3, $4) AND u1_.visibility IN ($5) AND e2_.visibility = $6 AND e0_.parent_id IS NULL AND m4_.is_adult = $7 AND e2_.is_adult = $8 AND (NOT (EXISTS (SELECT h5_.id FROM hashtag_link h5_ INNER JOIN hashtag h6_ ON h5_.hashtag_id = h6_.id WHERE h6_.banned = true AND h5_.entry_comment_id = e0_.id))) ORDER BY e0_.created_at DESC, e0_.created_at DESC, e0_.id DESC LIMIT 15 OFFSET 40530

Runtimes

SELECT sess_data, sess_lifetime FROM sessions WHERE sess_id = $1 FOR UPDATE

Runtimes:

SELECT count(DISTINCT p0_.id) AS sclr_0 FROM post p0_ INNER JOIN magazine m1_ ON p0_.magazine_id = m1_.id INNER JOIN "user" u2_ ON p0_.user_id = u2_.id WHERE ((p0_.visibility = $1 AND m1_.visibility = $2 AND u2_.visibility = $3) OR (EXISTS (SELECT u3_.following_id AS sclr_1 FROM user_follow u3_ WHERE u3_.follower_id = $4 AND p0_.visibility = $5 AND u3_.following_id = p0_.user_id))) AND (EXISTS (SELECT m4_.magazine_id AS sclr_2 FROM magazine_subscription m4_ WHERE m4_.user_id = $6 AND m4_.magazine_id = p0_.magazine_id) OR EXISTS (SELECT u5_.following_id AS sclr_3 FROM user_follow u5_ WHERE u5_.follower_id = $7 AND u5_.following_id = p0_.user_id) OR p0_.user_id = $8) AND p0_.lang IN ($9, $10) AND (NOT EXISTS (SELECT u6_.blocked_id AS sclr_4 FROM user_block u6_ WHERE u6_.blocker_id = $11 AND u6_.blocked_id = p0_.user_id)) AND (NOT EXISTS (SELECT m7_.magazine_id AS sclr_5 FROM magazine_block m7_ WHERE m7_.user_id = $12 AND m7_.magazine_id = p0_.magazine_id)) AND (NOT (EXISTS (SELECT h8_.id FROM hashtag_link h8_ INNER JOIN hashtag h9_ ON h8_.hashtag_id = h9_.id WHERE h9_.banned = true AND h8_.post_id = p0_.id)))

Runtimes:

SELECT u0_.email AS email_0, u0_.username AS username_1, u0_.roles AS roles_2, u0_.followers_count AS followers_count_3, u0_.homepage AS homepage_4, u0_.about AS about_5, u0_.last_active AS last_active_6, u0_.marked_for_deletion_at AS marked_for_deletion_at_7, u0_.fields AS fields_8, u0_.oauth_azure_id AS oauth_azure_id_9, u0_.oauth_github_id AS oauth_github_id_10, u0_.oauth_google_id AS oauth_google_id_11, u0_.oauth_facebook_id AS oauth_facebook_id_12, u0_.oauth_privacyportal_id AS oauth_privacyportal_id_13, u0_.oauth_keycloak_id AS oauth_keycloak_id_14, u0_.oauth_simple_login_id AS oauth_simple_login_id_15, u0_.oauth_discord_id AS oauth_discord_id_16, u0_.oauth_zitadel_id AS oauth_zitadel_id_17, u0_.oauth_authentik_id AS oauth_authentik_id_18, u0_.hide_adult AS hide_adult_19, u0_.preferred_languages AS preferred_languages_20, u0_.featured_magazines AS featured_magazines_21, u0_.show_profile_subscriptions AS show_profile_subscriptions_22, u0_.show_profile_followings AS show_profile_followings_23, u0_.notify_on_new_entry AS notify_on_new_entry_24, u0_.notify_on_new_entry_reply AS notify_on_new_entry_reply_25, u0_.notify_on_new_entry_comment_reply AS notify_on_new_entry_comment_reply_26, u0_.notify_on_new_post AS notify_on_new_post_27, u0_.notify_on_new_post_reply AS notify_on_new_post_reply_28, u0_.notify_on_new_post_comment_reply AS notify_on_new_post_comment_reply_29, u0_.add_mentions_entries AS add_mentions_entries_30, u0_.add_mentions_posts AS add_mentions_posts_31, u0_.is_banned AS is_banned_32, u0_.is_verified AS is_verified_33, u0_.is_deleted AS is_deleted_34, u0_.custom_css AS custom_css_35, u0_.ignore_magazines_custom_css AS ignore_magazines_custom_css_36, u0_.id AS id_37, u0_.password AS password_38, u0_.totp_secret AS totp_secret_39, u0_.totp_backup_codes AS totp_backup_codes_40, u0_.type AS type_41, u0_.ap_id AS ap_id_42, u0_.ap_profile_id AS ap_profile_id_43, u0_.ap_public_url AS ap_public_url_44, u0_.ap_followers_url AS ap_followers_url_45, u0_.ap_attributed_to_url AS ap_attributed_to_url_46, u0_.ap_featured_url AS ap_featured_url_47, u0_.ap_followers_count AS ap_followers_count_48, u0_.ap_inbox_url AS ap_inbox_url_49, u0_.ap_domain AS ap_domain_50, u0_.ap_preferred_username AS ap_preferred_username_51, u0_.ap_discoverable AS ap_discoverable_52, u0_.ap_manually_approves_followers AS ap_manually_approves_followers_53, u0_.private_key AS private_key_54, u0_.public_key AS public_key_55, u0_.ap_fetched_at AS ap_fetched_at_56, u0_.ap_deleted_at AS ap_deleted_at_57, u0_.ap_timeout_at AS ap_timeout_at_58, u0_.visibility AS visibility_59, u0_.created_at AS created_at_60, u0_.avatar_id AS avatar_id_61, u0_.cover_id AS cover_id_62 FROM "user" u0_ INNER JOIN image i1_ ON u0_.avatar_id = i1_.id WHERE u0_.last_active >= $1 AND u0_.is_banned = false AND u0_.is_deleted = false AND u0_.visibility = $2 AND u0_.ap_deleted_at IS NULL AND u0_.ap_timeout_at IS NULL AND u0_.avatar_id IS NOT NULL ORDER BY u0_.last_active DESC LIMIT 35

Runtime: 1374.189 ms

Long updates, probably due to waiting for an exclusive lock:

User:

UPDATE "user" SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

Runtimes:

UPDATE "user" SET last_active = $1 WHERE id = $2

Runtimes:

UPDATE "user" SET followers_count = $1, ap_followers_count = $2, ap_fetched_at = $3 WHERE id = $4

Runtimes:

Magazine

UPDATE magazine SET ap_fetched_at = $1, created_at = $2 WHERE id = $3

Runtimes:

UPDATE magazine SET last_active = $1 WHERE id = $2

Runtimes:

UPDATE magazine SET subscriptions_count = $1, ap_followers_count = $2, ap_fetched_at = $3, created_at = $4 WHERE id = $5

Runtimes:

BentiGorlich commented 2 days ago

As I said for the update queries, I think the problem are locked rows and not missing indices, because they update by id and we have indices for that

melroy89 commented 2 days ago

As I said for the update queries, I think the problem are locked rows and not missing indices, because they update by id and we have indices for that

Ow you are 100% correct, I was not looking correctly to the query, yes we do a WHERE id = $2. Id has an index.

I now turned on mkne. Small info: commit delay is in microseconds, not milliseconds 😅

Hhaa oopsy! You are right once again.