RoundSparrow

I heard that they gave out a bunch of free .ml domains and those are the ones they aren't allowing for free any more...

I already feel like I have to keep sticking my neck out to get them to question if using the ORM and a dozen JOIN statements isn't a problem.... but I guess I'll link it: https://github.com/LemmyNet/lemmy/pull/3900

As stated on my Lemmy user profile, I'm "RocketDerp" on GitHiub.

Honestly, the reason I keep making noise is because I'm sick of Lemmy crashing all the time when I come to use it... and I am on many servers that this happens. I really am not trying to piss off the developers, I even said I felt like I am being hazed, and I feel like hazing in general might explain what is going on with how much they are avoiding the elephant in the ROOM that ORM and a dozen JOIN might be the cause! Let alone the lack of Redis or Memcached addition being avoided, that's a second elephant on the second floor tap-dancing.... GitHub Issue 2910 was the straw that broke my back weeks ago, it took months for them to address it when it could be fixed in a couple hours (and it was weeks before the Reddti API deadline at the end of June.... and issue 2910 was neglected). The whole thing was a nightmare for me to watch...

First optimization is to not fetch every field and prune it down. For example, it gets public key and private key for every community and user account - then does nothing with them. That's just pushing data between Rust and PostgreSQL for no reason. That kind of thing is pretty obvious.. the huge number of things listed after "SELECT".

The whole approach is what I recently described as: make a JOIN fusion implosion bomb, then wait for null columns to fall out

There are short-term and long-term solutions. Right now there is already a new feature that will add one more JOIN that is pending merge.... "instance blocking" by each single user.

Based on the server overloads and resulting crashes, I think some obvious solutions would be to remove post_aggregates table entirely and just throw more columns on the post table... I've seen people do stuff like that. But really you have to have a concept of core foundation.

To me the core foundation of Lemmy data is that people want fresh meat, when world events get into a frenzy, they want to F5 and get the LATEST post and the LATEST comments. Data should have a big wall between the most recent 5 days and everything else. It's the heart of the beast of human events and a platform like this.

From that perspective, that fresh posts and fresh comments mean everything, you can optimize by just doing a INNER SELECT before any JOIN... or partition the database TABLE into recent and non-recent, or some out-of-band steps to prepare recent data before this SELECT even comes up from an API call... and not let PostgreSQL do so much heavy lifting each page refresh.

I've largely given up on pull requests.... for sake of sanity. But I waded back in...

I made a pull request today... and I very strategically choose to do it with minimal of features so that it would just go through... and I got lectured that JOIN is never a concern and that filtering based on the core function of the site (presenting fresh meat to readers) was a bad use of the database. I've never seen hazing on a project like this. Memcached and Redis should be discussed every day as "why are we not doing what every website does?", but mum is the word.

Community stuff can work well if done right. For example you don’t see Debian repositories constantly crashing.

I don't follow your comment, are you suggesting I said something negative about open source project communities? I was talking about the Lemmy social media communities who actually comment and fund the 64-core server upgrades without asking why the site crashes with only 57K users.... the people who comment and post on Lemmy.... not the "open source" programmer community, but the social media community of Lemmy.

If anyone bothered to actually look at the SQL SELECT that Lemmy uses to list posts every time you hit refresh it would be blindingly obvious how convoluted it is. yet the community does not talk about the programming issues and instead keeps raising money for 64 core hardware upgrades without recognizing just how tiny Lemmy's database really is and how 57K users is not a large number at all!

your original one, friend. I wouldn’t have argued this point if you had started here.

I mentioned "ORM" right in my first comment.

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 (((((((
  ((("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
;

the people who run Lemmy don’t have the money to support a fleet of failover servers that take over when the main server goes offline.

That has nothing to do with the issue I'm talking about. Every server with the amount of data in them would fail. Doesn't matter if you had 100 servers on standby.

The Rust logic for database access and PostgreSQL logic in lemmy is unoptimized and there is a serious lack of Diesel programming skills. site_aggregates table had a mistake where 1500 rows were updated for every single new comment and post - and it only got noticed when lemmy.ca was crashing so hard they made a complete copy of the data and studied what was gong on.

Throwing hardware at it, as you describe, has been the other thing... massive numbers of CPU cores. What's needed is to learn what Reddit did before 2010 with PostgreSQL.... as Reddit also used PostgreSQL (and is open source).

That’s basically the only reason you don’t see lots of downtime from major corporations: investment in redundancy,

Downtime because you avoid using Redis or Memcached caching at all costs in your project isn't common to see in major corporations. But Lemmy avoids caching any data from PostgreSQL at all costs. Been that way for several years. May 17, 2010: "Lesson 5: Memcache;"

As I said in my very first comment, server crashing as a way to scale is a very interesting approach.

EDIT: Freudian slip, "memecached" instead of Memcached

It’s not

It's really odd how many people around here think the server crashes are perfectly normal and are glad to see newcomers driven away.

Only way to solve this (imho) is to reinstall Lemmy BUT use another subdomain.

I wold agree that this is worth considering as an approach to not clash identity and get into custom SQL or Rust programming. But there isn't even really a procedure in place to decommission the old lemmy entity... so another damned if you do, damned if you don't in 0.18.4 era.

I'm a little surprised that the federation private key/public key signing doesn't get upset about all new keys appearing on the same domain name. I've tried to get details of exactly how a server joins the Lemmy network and gets discovered over on !lemmyfederation@lemmy.ml but haven't gotten any actually discussion on the details.

What do you think? Will this work?

I've seen people nuke and start-over their database from empty several times while having problems setting up NGinx and Docker... or whatever part.

I'm glancing at the list of SEQUENCE in Lemmy....

CREATE SEQUENCE public.admin_purge_comment_id_seq
CREATE SEQUENCE public.admin_purge_community_id_seq
CREATE SEQUENCE public.admin_purge_person_id_seq
CREATE SEQUENCE public.admin_purge_post_id_seq
CREATE SEQUENCE public.captcha_answer_id_seq
CREATE SEQUENCE public.comment_aggregates_id_seq
CREATE SEQUENCE public.comment_id_seq
CREATE SEQUENCE public.comment_like_id_seq
CREATE SEQUENCE public.comment_reply_id_seq
CREATE SEQUENCE public.comment_report_id_seq
CREATE SEQUENCE public.comment_saved_id_seq
CREATE SEQUENCE public.community_aggregates_id_seq
CREATE SEQUENCE public.community_block_id_seq
CREATE SEQUENCE public.community_follower_id_seq
CREATE SEQUENCE public.community_id_seq
CREATE SEQUENCE public.community_language_id_seq
CREATE SEQUENCE public.community_moderator_id_seq
CREATE SEQUENCE public.community_person_ban_id_seq
CREATE SEQUENCE public.custom_emoji_id_seq
CREATE SEQUENCE public.custom_emoji_keyword_id_seq
CREATE SEQUENCE public.email_verification_id_seq
CREATE SEQUENCE public.federation_allowlist_id_seq
CREATE SEQUENCE public.federation_blocklist_id_seq
CREATE SEQUENCE public.instance_id_seq
CREATE SEQUENCE public.language_id_seq
CREATE SEQUENCE public.local_site_id_seq
CREATE SEQUENCE public.local_site_rate_limit_id_seq
CREATE SEQUENCE public.local_user_id_seq
CREATE SEQUENCE public.local_user_language_id_seq
CREATE SEQUENCE public.mod_add_community_id_seq
CREATE SEQUENCE public.mod_add_id_seq
CREATE SEQUENCE public.mod_ban_from_community_id_seq
CREATE SEQUENCE public.mod_ban_id_seq
CREATE SEQUENCE public.mod_hide_community_id_seq
CREATE SEQUENCE public.mod_lock_post_id_seq
CREATE SEQUENCE public.mod_remove_comment_id_seq
CREATE SEQUENCE public.mod_remove_community_id_seq
CREATE SEQUENCE public.mod_remove_post_id_seq
CREATE SEQUENCE public.mod_sticky_post_id_seq
CREATE SEQUENCE public.mod_transfer_community_id_seq
CREATE SEQUENCE public.password_reset_request_id_seq
CREATE SEQUENCE public.person_aggregates_id_seq
CREATE SEQUENCE public.person_ban_id_seq
CREATE SEQUENCE public.person_block_id_seq
CREATE SEQUENCE public.person_follower_id_seq
CREATE SEQUENCE public.person_id_seq
CREATE SEQUENCE public.person_mention_id_seq
CREATE SEQUENCE public.person_post_aggregates_id_seq
CREATE SEQUENCE public.post_aggregates_id_seq
CREATE SEQUENCE public.post_id_seq
CREATE SEQUENCE public.post_like_id_seq
CREATE SEQUENCE public.post_read_id_seq
CREATE SEQUENCE public.post_report_id_seq
CREATE SEQUENCE public.post_saved_id_seq
CREATE SEQUENCE public.private_message_id_seq
CREATE SEQUENCE public.private_message_report_id_seq
CREATE SEQUENCE public.received_activity_id_seq
CREATE SEQUENCE public.registration_application_id_seq
CREATE SEQUENCE public.secret_id_seq
CREATE SEQUENCE public.sent_activity_id_seq
CREATE SEQUENCE public.site_aggregates_id_seq
CREATE SEQUENCE public.site_id_seq
CREATE SEQUENCE public.site_language_id_seq
CREATE SEQUENCE public.tagline_id_seq
CREATE SEQUENCE utils.deps_saved_ddl_id_seq

Let the servers keep crashing, tell everyone to add new instances to help with performance, which puts 1500 rows into the database tables that used to have 50 rows and invokes a massive federation 1-vote-1-https overhead... causing more crashing... all the while ignoring the SQL design of machine-generated ORM statements and counting logic hidden in the background triggers.

... keep users off your sever as a method of scaling by crashing. It's one of the more interesting experiences I've had this year! And I spent all of February and March with the release of GPT-4... which was also interesting!

That feature you linked to is to flair users.... there is a different issue to flair posts: https://github.com/LemmyNet/lemmy/issues/317

It is complicated. It's surely a damned-if-do damned-if-don't situation. It doesn't sound like you had all that much in terms of local users, communities, posts, comments - so at least that's in your favor.