More posts = more slow, so we're temporarily hiding older posts to make the bear run faster.
Our devs continue to work on getting in a long-term fix for the performance issues we've seen as the site as grown. The performance issues are due to a couple slow database views.
The bigger these views are, the slower things get. We're trying to replace the views altogether, but in the meantime, we get a significant performance boost by filtering down to only posts and comments from the past couple months.
We're leaving this in place as a workaround until the long-term fix is done for a few reasons:
consistency; you don't have to wonder whether or not the site is in "albanian bunker mode" at any given point
the alternative: the site performance problems have been getting worse as the tables get bigger. having hexbear be frequently unavailable altogether seems worse than limiting functionality
the trade-offs. our experience has been that hexbear has been significantly faster the past couple weeks. of course, individual needs will vary, but on the whole, we expect that most users are ok with the tradeoff of hiding older posts for a smooth site experience--particularly given that this is not intended as a long term state of affairs.
If you have any feedback or concerns, you can comment on this post, or email Joseph Biden at joe@whitehouse.gov . If you know Rust or are familiar with the Diesel library, and want to help us get the long-term fix in, please reach out to:
Layla via Matrix or me via Matrix
DMs to our hexbear accounts are fine as well, @CARCOSA @layla
I saw this and for half a second before I read the rest I thought we were going WW3 :posadist-nuke:
Hexchat sounds like a place witches hang out. Snapbear sounds like something that will maul your face off if you step on a twig.
The old posts aren’t gone, they’re just archived and resting right?
that’s where club penguin and all the neopets are, right?
:anarcho-bottom:
They’re always in your heart, listen to you heart, simbaaaaaaa
yeah. I just literally changed the sql query to be <original query> + WHERE post.id > something somewhat recent.
it's currently set to 170000 iirc. I kept playing around with it until performance hit a reasonable level. I tried 100000 first, for example.
oh ok i see. thank you for your service. literally braver and more productive than the tepops
:rat-salute:
edit: troops. what the fuck are tepops
Maybe change it to be
<query> + WHERE post.id > (latest post.id - <performance number>)
So you don't have to keep changing it.
And if possible allow user overrides so the archive posts can be accessed still, but aren't presented unless a radio button for <check archive> is toggled in the search dialogue?
Upshot of this is that if we ever upgrade hardware, that performance number can be updated to something that matches the servers capabilities (or if we introduce new options, we could have a recent history to updates tradeoff).
I'll have to take a peek at the code later and see if I can draft up an actual contribution instead of just hypothesizing though lol
It's slow for a few reasons. I've included the view definition for posts here, but the one for comments is basically the same. (note that when I say "we", I really mean lemmy, as this was just part of our original fork from them.)
The biggest reason by far is the cross join. Doing a cross join on user_ means that we have a row for every post, FOR EVERY USER. :ohnoes: So our 300k posts ends up being a stupid view with 300k x 20k = 6 billion rows. really.
with comments in the millions, it's even worse.
it's kind of a testament to postgres that this site was even mostly functional in the first place.
after that, the next-worst offender is the UNION ALL to account for logged out users or the possibility that you haven't interacted with a given post (which one depends a bit on context).
and basically all of this is so that we can do a "select where user = blah" reading across this insane master view, and get the users likes (which is why disabling upbears was the first version of bunker mode. I should have thought to just limit post recency from the get-go.)
The view definition:
spoiler
SELECT pav.id, pav.name, pav.url, pav.body, pav.creator_id, pav.community_id, pav.removed, pav.locked, pav.published, pav.updated, pav.deleted, pav.nsfw, pav.stickied, pav.featured, pav.embed_title, pav.embed_description, pav.embed_html, pav.thumbnail_url, pav.ap_id, pav.local, pav.creator_actor_id, pav.creator_local, pav.creator_name, pav.creator_preferred_username, pav.creator_published, pav.creator_avatar, pav.creator_tags, pav.creator_community_tags, pav.banned, pav.banned_from_community, pav.community_actor_id, pav.community_local, pav.community_name, pav.community_icon, pav.community_removed, pav.community_deleted, pav.community_nsfw, pav.community_hide_from_all, pav.number_of_comments, pav.score, pav.upvotes, pav.downvotes, pav.hot_rank, pav.hot_rank_active, pav.newest_activity_time, pav.user_id, pav.my_vote, pav.subscribed::boolean AS subscribed, pav.read::boolean AS read, pav.saved::boolean AS saved FROM ( SELECT p.id, p.name, p.url, p.body, p.creator_id, p.community_id, p.removed, p.locked, p.published, p.updated, p.deleted, p.nsfw, p.stickied, p.featured, p.embed_title, p.embed_description, p.embed_html, p.thumbnail_url, p.ap_id, p.local, u.actor_id AS creator_actor_id, u.local AS creator_local, u.name AS creator_name, u.preferred_username AS creator_preferred_username, u.published AS creator_published, u.avatar AS creator_avatar, ut.tags AS creator_tags, cut.tags AS creator_community_tags, u.banned, cb.id::boolean AS banned_from_community, c.actor_id AS community_actor_id, c.local AS community_local, c.name AS community_name, c.icon AS community_icon, c.removed AS community_removed, c.deleted AS community_deleted, c.nsfw AS community_nsfw, cs.hide_from_all AS community_hide_from_all, COALESCE(cps.number_of_comments, 0)::bigint AS number_of_comments, COALESCE(cps.score, 0)::bigint AS score, COALESCE(cps.upvotes, 0)::bigint AS upvotes, COALESCE(cps.downvotes, 0)::bigint AS downvotes, COALESCE(cps.hot_rank, 0) AS hot_rank, COALESCE(cps.hot_rank_active, 0) AS hot_rank_active, COALESCE(cps.newest_activity_time, p.published) AS newest_activity_time, me.id AS user_id, COALESCE(cf.community_id, 0) AS subscribed, COALESCE(pr.post_id, 0) AS read, COALESCE(ps.post_id, 0) AS saved, COALESCE(pl.score::integer, 0) AS my_vote FROM post p LEFT JOIN user_ u ON p.creator_id = u.id LEFT JOIN user_tag ut ON p.creator_id = ut.user_id LEFT JOIN community_user_tag cut ON p.creator_id = cut.user_id AND p.community_id = cut.community_id LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id AND p.community_id = cb.community_id LEFT JOIN community_settings cs ON p.community_id = cs.id LEFT JOIN community c ON p.community_id = c.id LEFT JOIN hexbear.post_stat cps ON cps.post_id = p.id CROSS JOIN user_ me LEFT JOIN community_follower cf ON me.id = cf.user_id AND cf.community_id = p.community_id LEFT JOIN post_read pr ON me.id = pr.user_id AND pr.post_id = p.id LEFT JOIN post_saved ps ON me.id = ps.user_id AND ps.post_id = p.id LEFT JOIN post_like pl ON me.id = pl.user_id AND p.id = pl.post_id) pav UNION ALL SELECT p.id, p.name, p.url, p.body, p.creator_id, p.community_id, p.removed, p.locked, p.published, p.updated, p.deleted, p.nsfw, p.stickied, p.featured, p.embed_title, p.embed_description, p.embed_html, p.thumbnail_url, p.ap_id, p.local, u.actor_id AS creator_actor_id, u.local AS creator_local, u.name AS creator_name, u.preferred_username AS creator_preferred_username, u.published AS creator_published, u.avatar AS creator_avatar, ut.tags AS creator_tags, cut.tags AS creator_community_tags, u.banned, cb.id::boolean AS banned_from_community, c.actor_id AS community_actor_id, c.local AS community_local, c.name AS community_name, c.icon AS community_icon, c.removed AS community_removed, c.deleted AS community_deleted, c.nsfw AS community_nsfw, cs.hide_from_all AS community_hide_from_all, COALESCE(cps.number_of_comments, 0)::bigint AS number_of_comments, COALESCE(cps.score, 0)::bigint AS score, COALESCE(cps.upvotes, 0)::bigint AS upvotes, COALESCE(cps.downvotes, 0)::bigint AS downvotes, COALESCE(cps.hot_rank, 0) AS hot_rank, COALESCE(cps.hot_rank_active, 0) AS hot_rank_active, COALESCE(cps.newest_activity_time, p.published) AS newest_activity_time, NULL::integer AS user_id, NULL::integer AS my_vote, NULL::boolean AS subscribed, NULL::boolean AS read, NULL::boolean AS saved FROM post p LEFT JOIN user_ u ON p.creator_id = u.id LEFT JOIN user_tag ut ON p.creator_id = ut.user_id LEFT JOIN community_user_tag cut ON p.creator_id = cut.user_id AND p.community_id = cut.community_id LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id AND p.community_id = cb.community_id LEFT JOIN community c ON p.community_id = c.id LEFT JOIN community_settings cs ON p.community_id = cs.id LEFT JOIN hexbear.post_stat cps ON cps.post_id = p.id;
and so what we're going to do instead is something like:
spoiler
select comment.id, comment.creator_id, post_id, parent_id, content, comment.removed, read, comment.published, comment.updated, comment.deleted, comment.ap_id, comment.local, post.name, post.community_id, community.actor_id AS community_actor_id, community.local AS community_local, community.name AS community_name, community.icon AS community_icon, community_settings.hide_from_all as community_hide_from_all, u.banned, COALESCE(community_user_ban.id, 0)::boolean AS banned_from_community, u.actor_id AS creator_actor_id, u.local AS creator_local, u.name AS creator_name, u.preferred_username AS creator_preferred_username, u.published AS creator_published, u.avatar AS creator_avatar, ut.tags AS creator_tags, cut.tags AS creator_community_tags, COALESCE(ccs.score, 0)::bigint AS score, COALESCE(ccs.upvotes, 0)::bigint AS upvotes, COALESCE(ccs.downvotes, 0)::bigint AS downvotes, COALESCE(ccs.hot_rank, 0) AS hot_rank, COALESCE(ccs.hot_rank_active, 0) AS hot_rank_active, c.* FROM comment left join post on comment.post_id = post.id left join community on post.community_id = community.id left join community_settings on community_settings.id = community.id left join user_ u on u.id=comment.creator_id left join community_user_ban on u.id = community_user_ban.user_id AND community_user_ban.community_id = community.id LEFT JOIN user_tag ut ON u.id = ut.user_id LEFT JOIN community_user_tag cut ON u.id = cut.user_id AND community.id = cut.community_id LEFT JOIN community_user_ban cb ON u.id = cb.user_id AND community.id = cb.community_id LEFT JOIN hexbear.comment_stat ccs ON ccs.comment_id = comment.id left join (select user_id,comment_id,score as my_vote from comment_like where user_id = $1) c on c.comment_id = comment.id;
to achieve basically the exact same result.the reason this wasn't done back in like january is because lemmy is written in rust, which none of us are super familiar with, and uses diesel for queries, which is kind of limited. so figuring out how to translate the query to rust and diesel is what we're working on. I think we're actually getting close. but we've all been really busy, so progress has been small bursts here and there.
Oof that's rough. I have to manage a 100gig oracle database at work and this is giving me flashbacks, I wish I had time to help.
Also I don't get why lemmy devs decided to use a query builder. They just made queries much harder to optimise for absolutely no reason. What's wrong with SQL?
no clue, haha. I guess it's just one of those things where different people have different schools of thought. I'm also very anti Spring for similar reasons.
But this comment will get very long if I get on my soapbox about the trend towards factories and builders and libraries and generics and everything else that stands between you and what your code is actually doing, haha.
Oh dang I've written a lot of Rust and used Diesel in deployed projects before, I'll needa have a look when I got a working dev environment again.
Dont worry, the fbi has them all safely archived, im sure they would let us look at them if we ask nicely
:albania: This post has been fact checked by True Albanian Patriots :albania:
:hoxha-turt: ✅ ＴＲＵＥ ✅ :hoxha-turt:
Hexbear admins right now:
:geordi-no: :purge-1:
:geordi-yes: :purge-2:
It's good to know we're posting so hard that the website is slowing down. We love our beautiful posters, don't we folks
:posting:
We do folks, we do. Some would say, our beautiful posters, they would say "they are good and beautiful." But I wouldn't say that. No, I would say THEY ARE THE GREATEST POSTERS
If there were a way to just preserve useful effortposts etc. and delete everything else after a period of time, that honestly wouldn't be bad.
maybe just delete the megathread posts?
(this post brought to you by doesn't-post-in-the-megathread-gang)
my advice: always save effortposts in text files before posting them
The problem with this is that useful comments appear in the strangest places and the comment threads they exist in are usually full of useful tidbits. I have a shit load of threads I reference from time to time for things, usually using the search feature to find them again later. Sometimes a meme thread might be the place where some comrade dumps a load of information on a topic, you never know.
Solely preserve the posts that went down in flames. Like every feminism post that made men here uncomfortable, people having breakdowns in real time for not being allowed to claim racial oppression while white, or so on.
Socialism is when no posts
(Site's working great now tho thank you admins and devs)
