It’s been a week since I’ve had to do a major attitude adjustment.
I’ve created tons of local comments on this server to try and figure out what is going on with performance that lemmy.world is still blocking all comment links: https://lemmy.world/post/2697806
I really wish developer would have rushed in a fix for the ampersand munging in URL and other content… a lot of links now on Lemmy are broken because of the damage to the URL.
The sorting without post_id… do a subselect to get post_id. post_id in (select post_id from comment where id=comment_id)
ok, so there is a new bug in the attempt to fix things, and there is a code comment about “denial of service” related to loading comments…
Ok, here is the original comment… https://github.com/LemmyNet/lemmy/commit/3b09d8c882c2effcfad9fda4ae06350dd20c0e5f
so, et me think about this, lemmy.world has blocked comment only, where no post id. Is it possible they are fetching a huge number of comments, ordering, then filtering?
What I need to see is the SQL statement before and after this commit… and try to find a pattern of data where this goes slow. OK< “If post_id filter is missing, getting comments ordered by path causes a sequence scan on the whole comments table.” - that is what I need to try and reproduce.
I’m thinking an SQL in with the post = comment id.
As the whole thing is that lemmy.world is blocking https://bulletintree.com/comment/1479898 style comment link, so there can’t be any reason to load comments for any post other than the one being read.
Idea to try later today…
Study the params of lemmy-ui for fetch on a comment link… also how “view context” changes those form variables.
Also manually do the form WITHOUT max-depth set at all, see how lemmy_server behaves with that sorting.
How exactly was an index scan being hit without post id that is now being undone?
about rate limiting… in lemmy and nginx
Does it consider login session required for some API paths?
Creating comments and posts could be rate-limited based on karma of account. Is voting rate-limited?
"Plans": [ { "Node Type": "Index Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Scan Direction": "Forward", "Index Name": "idx_post_aggregates_featured_community_active", "Relation Name": "post_aggregates", "Alias": "post_aggregates", "Startup Cost": 0.42, "Total Cost": 41907.69, "Plan Rows": 93, "Plan Width": 90, "Actual Startup Time": 153.975, "Actual Total Time": 327.882, "Actual Rows": 9, "Actual Loops": 1, "Filter": "(community_id = $14)", "Rows Removed by Filter": 459490, "Shared Hit Blocks": 434687, "Shared Read Blocks": 10015, "Shared Dirtied Blocks": 47, "Shared Written Blocks": 2950, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0 },-- Add missing most comments index create index idx_post_aggregates_featured_local_most_comments on post_aggregates (featured_local desc, comments desc, published desc); create index idx_post_aggregates_featured_community_most_comments on post_aggregates (featured_community desc, comments desc, published desc); -- featured_local create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc, published desc); create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc, published desc); create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc, published desc); -- featured_community create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc, published desc); create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc, published desc); create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc, published desc); -- Fixing some comment aggregates ones create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc, published desc); create index idx_comment_aggregates_score on comment_aggregates (score desc, published desc);duration: 329.450 ms plan: { "Query Text": "SELECT \"post\".\"id\", \"post\".\"name\", \"post\".\"url\", \"post\".\"body\", \"post\".\"creator_id\", \"post\".\"community_id\", \"post\".\"removed\", \"post\".\"locked\", \"post\".\"published\", \"post\".\"updated\", \"post\".\"deleted\", \"post\".\"nsfw\", \"post\".\"embed_title\", \"post\".\"embed_description\", \"post\".\"thumbnail_url\", \"post\".\"ap_id\", \"post\".\"local\", \"post\".\"embed_video_url\", \"post\".\"language_id\", \"post\".\"featured_community\", \"post\".\"featured_local\", \"person\".\"id\", \"person\".\"name\", \"person\".\"display_name\", \"person\".\"avatar\", \"person\".\"banned\", \"person\".\"published\", \"person\".\"updated\", \"person\".\"actor_id\", \"person\".\"bio\", \"person\".\"local\", \"person\".\"private_key\", \"person\".\"public_key\", \"person\".\"last_refreshed_at\", \"person\".\"banner\", \"person\".\"deleted\", \"person\".\"inbox_url\", \"person\".\"shared_inbox_url\", \"person\".\"matrix_user_id\", \"person\".\"admin\", \"person\".\"bot_account\", \"person\".\"ban_expires\", \"person\".\"instance_id\", \"community\".\"id\", \"community\".\"name\", \"community\".\"title\", \"community\".\"description\", \"community\".\"removed\", \"community\".\"published\", \"community\".\"updated\", \"community\".\"deleted\", \"community\".\"nsfw\", \"community\".\"actor_id\", \"community\".\"local\", \"community\".\"private_key\", \"community\".\"public_key\", \"community\".\"last_refreshed_at\", \"community\".\"icon\", \"community\".\"banner\", \"community\".\"followers_url\", \"community\".\"inbox_url\", \"community\".\"shared_inbox_url\", \"community\".\"hidden\", \"community\".\"posting_restricted_to_mods\", \"community\".\"instance_id\", \"community\".\"moderators_url\", \"community\".\"featured_url\", \"community_person_ban\".\"id\", \"community_person_ban\".\"community_id\", \"community_person_ban\".\"person_id\", \"community_person_ban\".\"published\", \"community_person_ban\".\"expires\", \"post_aggregates\".\"id\", \"post_aggregates\".\"post_id\", \"post_aggregates\".\"comments\", \"post_aggregates\".\"score\", \"post_aggregates\".\"upvotes\", \"post_aggregates\".\"downvotes\", \"post_aggregates\".\"published\", \"post_aggregates\".\"newest_comment_time_necro\", \"post_aggregates\".\"newest_comment_time\", \"post_aggregates\".\"featured_community\", \"post_aggregates\".\"featured_local\", \"post_aggregates\".\"hot_rank\", \"post_aggregates\".\"hot_rank_active\", \"post_aggregates\".\"community_id\", \"post_aggregates\".\"creator_id\", \"post_aggregates\".\"controversy_rank\", \"community_follower\".\"id\", \"community_follower\".\"community_id\", \"community_follower\".\"person_id\", \"community_follower\".\"published\", \"community_follower\".\"pending\", \"post_saved\".\"id\", \"post_saved\".\"post_id\", \"post_saved\".\"person_id\", \"post_saved\".\"published\", \"post_read\".\"id\", \"post_read\".\"post_id\", \"post_read\".\"person_id\", \"post_read\".\"published\", \"person_block\".\"id\", \"person_block\".\"person_id\", \"person_block\".\"target_id\", \"person_block\".\"published\", \"post_like\".\"score\", coalesce((\"post_aggregates\".\"comments\" - \"person_post_aggregates\".\"read_comments\"), \"post_aggregates\".\"comments\") FROM (((((((((((((\"post_aggregates\" INNER JOIN \"person\" ON (\"post_aggregates\".\"creator_id\" = \"person\".\"id\")) INNER JOIN \"community\" ON (\"post_aggregates\".\"community_id\" = \"community\".\"id\")) LEFT OUTER JOIN \"community_person_ban\" ON ((\"post_aggregates\".\"community_id\" = \"community_person_ban\".\"community_id\") AND (\"community_person_ban\".\"person_id\" = \"post_aggregates\".\"creator_id\"))) INNER JOIN \"post\" ON (\"post_aggregates\".\"post_id\" = \"post\".\"id\")) LEFT OUTER JOIN \"community_follower\" ON ((\"post_aggregates\".\"community_id\" = \"community_follower\".\"community_id\") AND (\"community_follower\".\"person_id\" = $1))) LEFT OUTER JOIN \"community_moderator\" ON ((\"post\".\"community_id\" = \"community_moderator\".\"community_id\") AND (\"community_moderator\".\"person_id\" = $2))) LEFT OUTER JOIN \"post_saved\" ON ((\"post_aggregates\".\"post_id\" = \"post_saved\".\"post_id\") AND (\"post_saved\".\"person_id\" = $3))) LEFT OUTER JOIN \"post_read\" ON ((\"post_aggregates\".\"post_id\" = \"post_read\".\"post_id\") AND (\"post_read\".\"person_id\" = $4))) LEFT OUTER JOIN \"person_block\" ON ((\"post_aggregates\".\"creator_id\" = \"person_block\".\"target_id\") AND (\"person_block\".\"person_id\" = $5))) LEFT OUTER JOIN \"post_like\" ON ((\"post_aggregates\".\"post_id\" = \"post_like\".\"post_id\") AND (\"post_like\".\"person_id\" = $6))) LEFT OUTER JOIN \"person_post_aggregates\" ON ((\"post_aggregates\".\"post_id\" = \"person_post_aggregates\".\"post_id\") AND (\"person_post_aggregates\".\"person_id\" = $7))) LEFT OUTER JOIN \"community_block\" ON ((\"post_aggregates\".\"community_id\" = \"community_block\".\"community_id\") AND (\"community_block\".\"person_id\" = $8))) LEFT OUTER JOIN \"local_user_language\" ON ((\"post\".\"language_id\" = \"local_user_language\".\"language_id\") AND (\"local_user_language\".\"local_user_id\" = $9))) WHERE ((((((((\"community\".\"deleted\" = $10) AND (\"post\".\"deleted\" = $11)) AND (\"community\".\"removed\" = $12)) AND (\"post\".\"removed\" = $13)) AND (\"post_aggregates\".\"community_id\" = $14)) AND ((\"community\".\"hidden\" = $15) OR (\"community_follower\".\"person_id\" = $16))) AND (\"post\".\"nsfw\" = $17)) AND (\"community\".\"nsfw\" = $18)) ORDER BY \"post_aggregates\".\"featured_community\" DESC , \"post_aggregates\".\"hot_rank_active\" DESC , \"post_aggregates\".\"published\" DESC LIMIT $19 OFFSET $20",So, I queried live instance and i find no index on post_aggregates community_id
"Filter": "(community_id = $14)", "Rows Removed by Filter": 459490,
"Plans": [ { "Node Type": "Nested Loop", "Parent Relationship": "Outer", "Parallel Aware": false, "Async Capable": false, "Join Type": "Left", "Startup Cost": 10.18, "Total Cost": 1208.32, "Plan Rows": 57, "Plan Width": 3099, "Actual Startup Time": 0.189, "Actual Total Time": 16.959, "Actual Rows": 606, "Actual Loops": 1, "Inner Unique": true, "Join Filter": "((post_aggregates.community_id = community_person_ban.community_id) AND (community_person_ban.person_id = post_aggregates.creator_id))", "Rows Removed by Join Filter": 2424, "Shared Hit Blocks": 3507, "Shared Read Blocks": 2126, "Shared Dirtied Blocks": 11, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0,Join Filter": “((post_aggregates.community_id = community_person_ban.community_id) AND (community_person_ban.person_id = post_aggregates.creator_id))”,
“Rows Removed by Join Filter”: 2424,