Refreshing comments on an active post is one of the hardest-hitting requests for the server performance-wise in 0.18.1 era.

see comments

  • RoundSparrowOP
    1·
    1 year ago

    Here is an overview of loading 3 comments specific to a posting, a typical refresh an end-user does while reading:

    node lemmyhelper_commandline.js rawpost --server https://bulletintree.com/ --postid 200
api/v3/comment/list?post_id=200&type_=All&limit=300&sort=New
timeConnect 0.33 timeParse 0.007 server https://bulletintree.com/

API JSON results:

Comment count 3
{
  comments: [
    {
      comment: [Object],
      creator: [Object],
      post: [Object],
      community: [Object],
      counts: [Object],
      creator_banned_from_community: false,
      subscribed: 'NotSubscribed',
      saved: false,
      creator_blocked: false
    },
    {
      comment: [Object],
      creator: [Object],
      post: [Object],
      community: [Object],
      counts: [Object],
      creator_banned_from_community: false,
      subscribed: 'NotSubscribed',
      saved: false,
      creator_blocked: false
    },
    {
      comment: [Object],
      creator: [Object],
      post: [Object],
      community: [Object],
      counts: [Object],
      creator_banned_from_community: false,
      subscribed: 'NotSubscribed',
      saved: false,
      creator_blocked: false
    }
  ]
}
    • RoundSparrowOP
      1·
      1 year ago

      That API call is generating this SQL query to PostgreSQL back-end:

      SELECT "comment"."id", "comment"."creator_id", "comment"."post_id", "comment"."content", "comment"."removed", "comment"."published", "comment"."updated", "comment"."deleted", "comment"."ap_id", "comment"."local", "comment"."path", "comment"."distinguished", "comment"."language_id", "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", "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", "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", "comment_aggregates"."id", "comment_aggregates"."comment_id", "comment_aggregates"."score", "comment_aggregates"."upvotes", "comment_aggregates"."downvotes", "comment_aggregates"."published", "comment_aggregates"."child_count", "comment_aggregates"."hot_rank", "community_person_ban"."id", "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires", "community_follower"."id", "community_follower"."community_id", "community_follower"."person_id", "community_follower"."published", "community_follower"."pending", "comment_saved"."id", "comment_saved"."comment_id", "comment_saved"."person_id", "comment_saved"."published", "person_block"."id", "person_block"."person_id", "person_block"."target_id", "person_block"."published", "comment_like"."score"

FROM ((((((((((("comment"
INNER JOIN "person" ON ("comment"."creator_id" = "person"."id"))
INNER JOIN "post" ON ("comment"."post_id" = "post"."id"))
INNER JOIN "community" ON ("post"."community_id" = "community"."id"))
INNER JOIN "comment_aggregates" ON ("comment_aggregates"."comment_id" = "comment"."id"))
LEFT OUTER JOIN "community_person_ban" ON (("community"."id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "comment"."creator_id")))
LEFT OUTER JOIN "community_follower" ON (("post"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $1)))
LEFT OUTER JOIN "comment_saved" ON (("comment"."id" = "comment_saved"."comment_id") AND ("comment_saved"."person_id" = $2)))
LEFT OUTER JOIN "person_block" ON (("comment"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $3)))
LEFT OUTER JOIN "community_block" ON (("community"."id" = "community_block"."community_id") AND ("community_block"."person_id" = $4)))
LEFT OUTER JOIN "comment_like" ON (("comment"."id" = "comment_like"."comment_id") AND ("comment_like"."person_id" = $5)))
LEFT OUTER JOIN "local_user_language" ON (("comment"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $6)))

WHERE (("comment"."post_id" = $7) AND (("community"."hidden" = $8) OR ("community_follower"."person_id" = $9))) ORDER BY "comment"."published" DESC LIMIT $10 OFFSET $11
      • RoundSparrowOP
        1·
        1 year ago

        I propose that /fastAPI/v1/ path does not use this backend Query and focuses more on performance.

        JOINS:
        community-blocking. If a user is banned, can’t they just create another account or use anonymous?
        comment_like - in high performance, knowing if the person reading has liked which comments on this posting could be cached independent. Another API call to get comment_like for an individual user and posting comments.
        local_user_language - let clients get the language preferences for an end-user and take this off the query for every single load of comments off the server.
        post - do not JOIN on post, require another API call and the client application use that data. Don’t return it on every single comment.
        community - same as post.
        person - return the raw creator_id and an API to fetch all the creator_id mappings for the comments. Allow the client to cache and merge those manually.

        INTERNAL CACHING:

        The API should use a cache and not require the database to reference ("community"."hidden" = $8) in the WHERE clause. The number of communities is in the order of 100,000 or less, and caching if those are hidden in RAM (or SQLite database) wold be of value because you would never execute this entire query if the post was in a hidden community.

        Which raises another issue about the Lemmy API and concepts. Comment links are orphan, they are not tied to a community. But in practice, they are tied to a post, but the URL links that end-users see on lemmy-ui (and search engines) do not have the post or the community in mind. Should the community_id or name be required to load comments, so that posting ID is no longer system-wide?

  • RoundSparrowOP
    1·
    1 year ago

    API for performance vote/like

    Bulk voting that client sends to servers. These are the comments and posts that I would like to upvote, list.

    Bulk vote downloads: These are the postings I want the latest vote count on. These are the comments I want vote counts on. list.

    An API to query comments and posts and get returned only the post_id/comment_id and counts, with limits up to 2500 rows returned. Very little data per row, many rows, bulk refresh so that a client can find out about interesting comments and posts and then request them from the API.