Lemmy.world shared a slow query, in detail! Yey!

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, 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 = ? . id ) ) INNER JOIN post ON ( post_aggregates . post_id = ? . id ) ) INNER JOIN community ON ( post_aggregates . community_id = ? . id ) ) LEFT OUTER JOIN community_person_ban ON ( ( post_aggregates . community_id = ? . community_id ) AND ( community_person_ban . person_id = ? . creator_id ) ) ) LEFT OUTER JOIN community_follower ON ( ( post_aggregates . community_id = ? . community_id ) AND ( community_follower . person_id = ? ) ) ) LEFT OUTER JOIN post_saved ON ( ( post_aggregates . post_id = ? . post_id ) AND ( post_saved . person_id = ? ) ) ) LEFT OUTER JOIN post_read ON ( ( post_aggregates . post_id = ? . post_id ) AND ( post_read . person_id = ? ) ) ) LEFT OUTER JOIN person_block ON ( ( post_aggregates . creator_id = ? . target_id ) AND ( person_block . person_id = ? ) ) ) LEFT OUTER JOIN community_block ON ( ( post_aggregates . community_id = ? . community_id ) AND ( community_block . person_id = ? ) ) ) LEFT OUTER JOIN post_like ON ( ( post_aggregates . post_id = ? . post_id ) AND ( post_like . person_id = ? ) ) ) LEFT OUTER JOIN person_post_aggregates ON ( ( post_aggregates . post_id = ? . post_id ) AND ( person_post_aggregates . person_id = ? ) ) ) LEFT OUTER JOIN local_user_language ON ( ( post . language_id = ? . language_id ) AND ( local_user_language . local_user_id = ? ) ) ) WHERE ( ( ( ( ( ( ( ( community . removed = ? ) AND ( community . deleted = ? ) ) AND ( post . removed = ? ) ) AND ( post . deleted = ? ) ) AND ( community . local = ? ) ) AND ( ( community . hidden = ? ) OR ( community_follower . person_id = ? ) ) ) AND ( post . nsfw = ? ) ) AND ( community . nsfw = ? ) ) ORDER BY post_aggregates . featured_local DESC, post_aggregates . hot_rank DESC, post_aggregates . published DESC LIMIT ? OFFSET ?

did a git pull request for new index on post searches by community

lemmy_server

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.

late May... what I saw was lemmy.ml turned off new user signup, community creation, and load-shed by letting the server error out.

now lemmy.world is bigger in content and activity but the drastic blocking of incoming links to comments is in place.

I think the number of active users has less to do with the performance problems than the amount of data in the database. The heavy use of JOIN and custom account-specific filters. An account with a large block list and a huge list of subscribed communities likely performs wildly different when listings posts than a non-logged in user.

Resorting to having nginx block comment links regardless of response time or rate-limits. No concurrency self-awareness in this approach.

self-awareness in the API

https://github.com/LemmyNet/lemmy/pull/3805

I've adjusted my attitude. Github issue 2910 has spelled it out to me how priorities work.

lemmy.world seems to have not sent any data to lemmy.ml for days it seems: https://lemmy.ml/post/2650814

Lemmy.world had to shut down the front page and put up a message about the load and a graph. They seem to chalk it down to the nature of social media sites to attract attacks.

I'd hack up the Rust code to have self-awareness of concurrency with PostgreSQL and return a new busy error.

Federation connections, RSS feed, API - and any other method that is hitting the database needs to have a concurrency count in the Rust code and an error message system for busy.

I'd probably build a a class to help with this and once concurrency for an API is over 5 mark the high water with a timestamp and start doing logic based on elapsed time. If > 5 and elapsed time exceeds a threshold (say 1 minute), then return the busy error.

is Prometheus the right way to expose these numbers for operators wanting to know about the thresholds.? I'd probably add a dedicated log file to track concurrency thresholds and busy errors.

the front-end apps also need to be caching "Trending communities", I think lemmy-ui is still pulling that live from PostgreSQL for every refresh of the page. I need to check if anyone has added that.

It was last Sunday, July 23 that the site_aggregates update of 1500 rows issue was discovered.

Lemmy.ca made a clone of their database.

The UPDATE of 1500 rows in site_aggregates is an example of what keeps slipping through the cracks.

lemmy.ml as a long-running server may have data patterns in it that a fresh-install server does not easily match.

Lemmy.ca cloning a live database to run EXPLAIN was exactly the kind of thing I wanted to see from lemmy.ml 2 months ago.

the open source cross-application platforms like Kbin or Lemmy, it's been really hard for me to adjust to the chaos of it all and the server crashes causing so many people problems.

I clearly have failed to understand how integral to the social identity of the Lemmy community server crashes have become. The "little guy taking on Reddit and Twitter" for 4 years.

I have personal DB2 and PostgreSQL experience in production "mission critical" applications. I should have seen that for 4 years the developers had all the social experience of social -- like Hollywood films or Music Industry labels.

My brain damaged mind was WORRIED about the June 4, 2023 GitHub Issue 2910 so much that I started organizing [email protected] on June 13 after lemmy.ml upgraded hardware and I saw more crashes.

It's cultural. I should not have looked into the kitchen and let my personal DB2 and PostgreSQL memories haunt me that this was "production" system crashing.

I fucked up, and I've wasted 2 months in WORRY about the Reddit API deadline the first month, and now the Elon Musk X rename, and I admit - I didn't get the social experience the developers have for 4 years working "social communities".

I study religions, I should have interpreted it using that hat instead of "production PostgreSQL or DB2".

It's been a bad year for me, and I've made several big mistakes. I had to drink to clear my mind for the first time this year since New Years. Those memories of running "mission critical" servers had to be shut off, stop applying them to socially-driven Lemmy culture. The developers have been running this site for 4 years, they know modern audiences and what works - I've just been pissing into the wind with the wrong interpretation :(

Perhaps I've had the wrong attitude about the server needing to be reliable, data loss importance, and crashes causing all the front-ends to look bad, etc

When I step back and study the history of Microsoft vs. Apple. the "open hardware" approach of Microsoft proved to be abandoned.

Apple always had the best loyalty by NOT offering hardware choice to their customers. and Linux, which offered the most hardware choices of all, was even less popular.

Apple knew people didn't like to pay money for software, so they were an operating system company who packaged it with hardware.

I know project developers like Lemmy do not get good money. Maybe I have been far too harsh on them ignoring PostgreSQL crashes and should just face up that the Lemmy community, the users of Lemmy, think it is all perfectly fine.

32 cores of CPU to run a server without all that much content, it bothered me a lot to see the money being spent like that and to witness server crashing. But there are people here who seem to actually enjoy all of it. It makes the fight against Big Reddit and Big Twitter seem more dramatic.

If you look at how Hollywood productions are run in terms of expenses for sets, clothes, shooting locations - it can be millions of dollars for 90 seconds of a movie. Because it's in the sphere of social media, films are social.

Perhaps I've been the wrong kind of fool and their approach to just allow crashes and not treat it like an important production server - is actually intuitive to how social projects of "taking on the big guys" works.

If it was closed-source paid-for software, crashes would be an issue to challenge. But with open source, they seem to have taken the attitude that anyone can fix it - so not their problem. They really emphasized to me what they mean by "supported". And if lemmy.ml is their idea of a "supported" server, I should take it as reality and give up any idea I have that easily-fixed crashes are important. It's product defining.

It was hard for me to come to terms with.

I should have stuck to topics like ChatGPT, Neil Postman, Marshall McLuhan.

Hate of Twitter, hate of Reddit seems to have been the primary motivation for many people to join Lemmy.

In the past 2 months, I have seen many people requesting feature in apps to block entire instances of Lemmy or enhance other blocking features.

Bashing on Facebook was often a thing you saw on Reddit, bashing end-users.

Are people largely attracted to this?

I'm mostly just saying it out loud. These trends like what Elon Musk has done to Twitter, or ChatGPT becoming mainstream in 2023 wit all it's misinformation creation abilities... are happening. Regardless of the anything going on with Lemmy.

ChatGPT is probably the bigger real change. The news/world news on Lemmy isn't really that different than Reddit from my 60+ days of reading. Same general style of comments, reactions, and flow of postings.

1. When I personally create tests to validate "full delete", it fails, with a 4-year old bragging claim on GitHub project page

2. High performance. Constant server crashes from site_aggregates table getting EVERY row modified via badly-written 2022 SQL TRIGGER when a single comment or post is inserted.

I am not ignorant of the techniques of post-2013 Surkov IRA in Saint Pete Russia psychology, and post-2013 Psychology of Cambridge Analytica.

My bran damage is unable to create English prose able to convince developers to listen to and face June 4 issue 2910 in GitHub.

I tried to put compassion, kindness, love, humanism first with creation of June 13 [email protected]

I am a worthless failure :( Over 50 days of effort since June 4 issue 2910 on Github, and I failed to get them to understand the end-user experience of endless crashes on lemmy.ml and all servers with TRIGGER UPDATE statements

June 24, a full week before the Reddit API delay and my June 13 post in [email protected] about paying more attention to the PostgreSQL code they had put into Lemmy Server that was causing constant crashes as called out June 4 by GitHub issue 2910

The effort developers have made to DISCOURAGE focus attention on the PostgreSQL TRIGGER code: https://github.com/LemmyNet/lemmy-docs/issues/233

Removing "emergency" in the fix first-thing Monday after not working on a weekend was how out-of-the-way the project leaders have been to AVOID the TRIGGER code getting scrutiny and fixed since June 4 call-out in issue 2910

Just like they do not work on weekends, and first thing Monday their priority on GitHub was to EDIT a pull request to remove the word "EMERGENCY" (server crashes) and then go on to add low-importance new features all week.

PostgreSQL related crashes and mistakes in the Lemmy Project back-end are a pattern, as June 4 2023 GitHub issue 2910 clearly documents - which sat ignored for months.

As fast as possible, closing and brushing under the rug GitHub Issue 3756 that was field-testing of the Lemmy 0.18.3 release (there was also NO CALL or Announcement by the project leaders to test 0.18.2 or 0.18.3 - there is no quality assurance on the project from all that I have seen).

If the developers of the program do not grasp PostgreSQL - why constantly cover it up? Why brush SQL problems under the rug? It's the core of Lemmy, where all the data is stored.

I have some new books on order to better understand social hazing.

From what I can piece together, there were some smaller flocks of Reddit users coming to Lemmy before I started using Lemmy hours each day - 64 days ago on May 25.

May 25, every Lemmy instance on the recommend list was crashing for me. Very obvious signs of PostgreSQL performance problems in the code. Refresh of the listing would fail 1 out of 5 times, often as much as half. Beehaw, Lemmy.ml, Lemmy.world and I visted the non-English instances too - but I really did not see anything of what I would consider significant post and comment activity.

May 29, I am searching and reading Lemmy content now for 4 days (between constant crashing). I can nod find the developers sharing their PostgreSQL problems in communities and trying to fix crashes. They seem to be avoiding using Lemmy, why? I don't understand. But I keep reading.

June 1, it was clear that server crashing was everywhere and nothing was being done about it. I start reading GitHub issues and pull requests multiple times a day, and trying to understand the priorities of the project since I can find no Lemmy community for discussing the ongoing server overload and performance problems in lemmy_server - it must be somewhere? Discussions are free to host on GitHub, but they are disabled by the project leaders. They are not using Github discussions, they are not using Lemmy communities. I'm perplexed.

June 2, I find out that the project leaders run lemmy.ml - so I focus on hanging out there to witness change management. I finally managed to get an account created on lemmy.ml past all the crashing.

June 4, GitHub Issue 2910 is opened. The PostgreSQL TRIGGER is directly identified as causing servers to "blow up". This is a very easy fix, it can even be done without having to recompile lemmy_server and make a back-end release. A bash script or even just a web page of PostgreSQL steps (like the existing Lemmy "from scratch install" has) would provide huge and immediate relief.

June 4 was a Sunday. I watch project leaders who do not work on weekends come in Monday on June 5, Tuesday June 6, Wednesday June 7, etc and ignore dramatic Issue about PostgreSQL, issue 2910. I am still looking over lemmy.ml between constant server crashes trying to find evidence that the project leaders of Lemmy actually ask for help in [email protected] from the Lemmy community. Crickets, they don't use Lemmy to discuss and seek help regarding the crashes of Lemmy. I am wildly perplexed and I do not understand the social hazing yet.

Another weekend the project leaders aren't around. Monday June 10 comes. Issue 2910 and the constant server crashes are not discussed on Lemmy. The whole Lemmy community is abuzz that in 3 weeks Reddit is shutting down the API and something must be done to prepare.

June 13

June 13. With social hazing as the main leadership priority, promoting Matrix chat instead of Lemmy communities such as [email protected] , the project leaders encourage big server hardware upgrades and upgrade Lemmy.ml https://lemmy.ml/post/1234235 - but there is no significant improvement and crashing are still constantly happening because of PostgreSQL and issue 2910 about PostgreSQL being ignored now for 9 days.

June 13, I know the problem is not hardware. It's obviously PostgreSQL code being fed by lemmy_serve.r I am dumbfounded. Why aren't project leaders asking in [email protected] using Lemmy platform? I created a community [email protected] and posted https://lemmy.ml/post/1237418 about PosgreSQL and developer basics, 101. The scope of the social hazing against he USA media environments is not yet clear to me. "eating your own dog food" as developers, and Matrix Chat as part of the social hazing not fully appreciated by me at that time. Only in retrospection can I see what was going on June 13.

June 15 - again, the project leaders are sending all kinds of social signals that they are going to ignore server crashes as a topic. I opened one of several postings on Lemmy.ml calling out the constant crashes, despite the June 13 hardware upgrade. https://lemmy.ml/comment/948282

June 19

June 19, I am well into a campaign of pleading for developers to install pg_stat_statements and to use Lemmy itself to see the scope of the Lemmy.ml and other servers crashing constantly. https://lemmy.ml/post/1361757

June 30

reddit flocks to Lemmy again as had been happening all month, to find all Lemmy servers constantly crashing as I had personally seen every day since June 25. Ignoring GitHub Issue 2910 for several weeks and avoidng Lemmy [email protected] and other communities is no accident, I now see social hazing was the primary project leadership concern.

July 22 - Saturday

Lemmy.ca staff downloads a clone of their own database and runs PostgreSQL EXPLAIN again identifies the TRIGGER logic is a bomb within the code, blowing up servers, and it still hasn't gotten any attention.

I knowing avoiding the issue on GitHub has been the social hazing game since June 4, but I still make as much noise as posible on GitHub and create a pull rquest labeled "EMERGENCY" on July 23, Sunday.

Monday July 24

The very first priority of the project leaders on GitHub is to edit my pull request title to remove the word "emergency" about the TRIGGER fix. At this point, I have no other explanation of what I have witnessed since May 25 than social hazing on a mythological scale I have never personally witnessed before. No postings are made by developers on Lemmy, and they continue to hang out on Matrix Chat as part of their social hazing rituals.

Friday July 28

Issue 2910 isn't even mentioned in the release notes of 0.18.3 - that are created today. Sine June 4 I've witnessed it be deliberaetly ignored just as I have seen the avoidance of using Lemmy communities like [email protected] to discuss how easy it is to notice o lemmy.ml that TRIGGER statements are crashing the server constantly.

It's still nearly impossible for me to describe the scale of this social hazing against all users of Twitter, Reddit, the World Wide Web / Internet. 1) don't eat your own dogfood, avoid using Lemmy to discuss Lemmy, 2) avoid GitHub issues and discussions about server crashes and PostgreSQL TRIGGER topis. 3) Say the problem is scaling via hardware, and do avoid admitting that hardware upgrades were the wrong answer on June 13.

THE. MOST. SOPHISTICATED. SOCIAL HAZING. I have ever witnessed. June 4, GitHub Issue 2910. Elon Musk rebranding of "Twitter" to "X' went on, Reddit API change, and everything possible was done to avoid posting in Lemmy [email protected] that CPU was at 100% for PostgreSQL. Wild ride.

The fix of comment delete not making it to 1498 subscribed servers out of 1500 went in. I'm working on testing to confirm the same for moderator remove and other actions (testing did get several merges this week, not sure where problems like feature/sticky in a community for all 1500 servers go).

I am contemplating if some kind of existing-data cleanup could be done for all the comment deletes that never got sent out. If an admin API could be added to trigger a undelete then delete again if comment = local. Crawl them. Probably too many other fixes.

Will the server-crashing fix go in before the weekend?

June 4 GitHub issue:

This has to be social hazing.