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.

  • RoundSparrow @ BTOPM
    link
    fedilink
    11 year ago

    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?

  • RoundSparrow @ BTOPM
    link
    fedilink
    11 year ago

    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?

  • RoundSparrow @ BTOPM
    link
    fedilink
    11 year ago
                                                        "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
                                                          },
    
    
    • RoundSparrow @ BTOPM
      link
      fedilink
      11 year ago
      -- 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);
      
    • RoundSparrow @ BTOPM
      link
      fedilink
      1
      edit-2
      1 year ago
      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",
      
      
    • RoundSparrow
      link
      fedilink
      11 year ago

      So, I queried live instance and i find no index on post_aggregates community_id

                                                          "Filter": "(community_id = $14)",
                                                          "Rows Removed by Filter": 459490,
      
  • RoundSparrow @ BTOPM
    link
    fedilink
    11 year ago
                                                    "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,
    
    
    • RoundSparrow @ BTOPM
      link
      fedilink
      11 year ago

      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,

  • RoundSparrow @ BTOPM
    link
    fedilink
    11 year ago

    The sorting without post_id… do a subselect to get post_id. post_id in (select post_id from comment where id=comment_id)