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....
this post was submitted on 05 Aug 2023
1 points (100.0% liked)
Lemmy Project Priorities Observations
5 readers
1 users here now
I've raised my voice loudly on meta communities, github, and created new [email protected] and [email protected] communities.
I feel like the performance problems are being ignored for over 30 days when there are a half-dozen solutions that could be coded in 5 to 10 hours of labor by one person.
I've been developing client/server messaging apps professionally since 1984, and I firmly believe that Lemmy is currently suffering from a lack of testing by the developers and lack of concern for data loss. A basic e-mail MTA in 1993 would send a "did not deliver" message back to message sender, but Lemmy just drops delivery and there is no mention of this in the release notes//introduction on GitHub. I also find that the Lemmy developers do not like to "eat their own dog food" and actually use Lemmy's communities to discuss the ongoing development and priorities of Lemmy coding. They are not testing the code and sampling the data very much, and I am posting here, using Lemmy code, as part of my personal testing! I spent over 100 hours in June 2023 testing Lemmy technical problems, especially with performance and lost data delivery.
I'll toss it into this echo chamber.
founded 1 year ago
MODERATORS
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
},
So, I queried live instance and i find no index on post_aggregates community_id
"Filter": "(community_id = $14)",
"Rows Removed by Filter": 459490,-- 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",
"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,
The sorting without post_id... do a subselect to get post_id. post_id in (select post_id from comment where id=comment_id)