It is probably due to a number of people stopping using their alts after some instance hopping.
Also a few people who came to see how it was, and weren’t attracted enough to become regular visitors.
Curious to see at which number we’ll stabilize.
Next peak will probably happen after either major features release (e.g. exhaustive mod tools allowing reluctant communities to move from Reddit) or the next Reddit fuck up (e.g. removing old.reddit)
Stats on each server: https://lemmy.fediverse.observer/list
may I voice my opinion on the exchange? This is coming from a place of trying to help, since I really do appreciate all the work you’ve put in and are putting in, and the fediverse can really use your talents, so I hope I don’t offend you.
Can you explain to me why it isn’t social hazing?
it didn’t appear that you were being ignored/hazed
Do you know how to read a SQL statement? I just can’t grasp how it isn’t social hazing. I’ve been reading SQL statements for decades, this is obviously a problematic one.
Can you offer alternate explanations of how 3 people could think that SQL statement isn’t … poor performing and gong to cause problems? And how an SQL statement without a WHERE clause took them months to discover and fix?
Extreme hazing is my best answer. I just can’t accept that the SQL statements don’t speak for themselves along with the server crashes. 57K users for 1300 servers is very… taking several seconds to load 10 posts…
Look at the date… May… this has been going on since May. If it isn’t social hazing … what is it? I keep asking myself that.
Can you explain to me why it isn’t social hazing?
Like I said, this was my interpretation based on reading that exchange. It’s difficult to convey tone or intention with text, but I didn’t detect hostility from the devs, but I did sense that they were frustrated that process wasn’t being followed. Perhaps they should not have gotten hung up on that, but it didn’t appear to be malicious.
Do you know how to read a SQL statement? I just can’t grasp how it isn’t social hazing. I’ve been reading SQL statements for decades, this is obviously a problematic one.
I do, and your arguments about the joins being problematic seemed solid. From having worked on systems with huge scale, I also agree that Lemmy doesn’t seem to be big enough to be brought to its knees by the volume of posts it’s processing. However, I’m far from an expert, so I don’t want to suggest any certainty about the root causes, especially as I don’t have the energy or inclination to dig as deep into it as I would to form that opinion.
I don’t know why they weren’t receptive, but perhaps they themselves felt attacked. I know that wasn’t your intention, but misunderstanding happen, especially over text.
Here, you can dig into what posted days before the pull request you read:
https://github.com/LemmyNet/lemmy/issues/2877#issuecomment-1685314733
June 4:
joins are better than
inqueries with potentially thousands of inserted IDs.
Given that more than 8 JOIN statements is something PostgreSQL specifically concerns itself with (join_collapse_limit). I hand-edit the query with a single IN clause and the performance problem disappears. 8 full seconds becomes less than 200ms against 5,431,043 posts. And that 200ms is still high, as I was extremely over-reaching with “LIMIT 1000” in case the end-user went wild with blocking lists or some other filtering before reaching the final “LIMIT 10”. When I change it to “LIMIT 20” in the subquery, it drops almost in half to 115ms… still meeting the needs of the outer “LIMIT 10” by double. More of the core query filtering can be put into the IN subquery, as we aren’t dealing with more than 500 length pages (currently limited to 50).
SELECT
"post"."id" AS post_id, "post"."name" AS post_title,
-- "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" AS p_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" AS p_inst,
"community"."id" AS c_id, "community"."name" AS 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" AS c_inst,
-- "community"."moderators_url", "community"."featured_url",
("community_person_ban"."id" IS NOT NULL) AS ban,
-- "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"."pending",
("post_saved"."id" IS NOT NULL) AS save,
("post_read"."id" IS NOT NULL) AS read,
("person_block"."id" IS NOT NULL) as block,
"post_like"."score",
coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments") AS unread
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" = 3))
)
LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = 3))
)
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" = 3))
)
LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = 3))
)
LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = 3))
)
LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = 3))
)
LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = 3)))
LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = 3))
)
WHERE
post_aggregates.id IN (
SELECT id FROM post_aggregates
WHERE "post_aggregates"."creator_id" = 3
ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
LIMIT 1000
)
AND
(((((((
(
(("community"."deleted" = false) AND ("post"."deleted" = false))
AND ("community"."removed" = false))
AND ("post"."removed" = false)
)
AND ("post_aggregates"."creator_id" = 3)
)
AND ("post"."nsfw" = false))
AND ("community"."nsfw" = false)
)
AND ("local_user_language"."language_id" IS NOT NULL)
)
AND ("community_block"."person_id" IS NULL)
)
AND ("person_block"."person_id" IS NULL)
)
ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
LIMIT 10
OFFSET 0
;
If it isn’t social hazing, then what is going on here? Why has this issue gone on since May and servers are crashing every day?
. However, I’m far from an expert,
Funny, because I’m a published author and expert on messaging systems… like Lemmy. Iv’e been building them since 1986 professionally.
There was a massive thread I posted dozens of comments on that came before today’s pull request… I suggest you read that too.
Did you notice them even acknowledge server crashes are happening? Do you think developers ever suggest Memcache or Redis? Or discuss how Reddit solved their scaling in 2010 with PostgreSQL?
but perhaps they themselves felt attacked. I know that wasn’t your intention, but misunderstanding happen, especially over text.
I don’t have any trouble understanding a bad SQL statement that has 14 JOINs and being told “JOIN is a distraction” after posting tons of examples.
Do we really need to spoon fed the stuff I did post?
Have you never seen social hazing in action? is it possible that I might be on to something going on psychologically besides my autism?
I can’t believe anyone thinks a server should be crashing with 1 user on it.
Have you never seen social hazing in action? is it possible that I might be on to something going on psychologically besides my autism?
Okay, I can’t speak to whether social hazing happened or not, but I can tell you that you’re making me extremely uncomfortable.
I started a dialogue, but at this point you’re now sending multiple messages for each of my replies, and asking a lot from me in terms of attention. I do not wish to continue this conversation, but I wish you all the best.
