Credit to @eiknat@dev.lemmy.ml , and masterstur for their help with this effort.
Our postgres CPU usage has gone wayyyy down.
woohoo :party popper:
bruh, that’s huge! what caused so many issues previously may i ask?
So the early days of lemmy, we didn’t use any caching, but as we grew, in order to speed up read speeds, I added a SQL caching technique called materialized views. This instantly helped, and made lemmy a lot faster.
Then we grew some more, and started to get a lot of active users, doing actions at the same time. Unfortunately, the only way you can update data in materialized views, is to refresh the entire table, which at first was extremely fast, but as we grew, started to take at least a second every time. And even worse, it locked out all reads on the table. So reads when they could occur, were very fast, but had to wait for all the locking writes to finish.
In this user explosion last week, this became unbearable and I made it my top priority to fix this. Now Lemmy uses a different caching technique called fast tables, where updates to core data tables run SQL triggers, which update only the specifically changed data on the fast tables. This makes locking writes extremely fast and far less common.
There’s still room for improvement of course, and luckily this last PR was the first time I’ve ever gotten another set of eyes looking at the DB besides myself. But yeah, that’s the long version.
Thank you so much for the hard work!
No probs :)
nice, thanks :)
Interesting. I’m a bit ignorant when it comes to db administration, but is this fast tables technique something you developed on your own or is it a known documented thing? I can’t seem to find anything.
It’s used elsewhere, but if you look up sql procedural functions or audit tables, you’ll find more.
how can it >100%? is that hyperthreading magic?
Multiple cores.
What program is this?
grafana.
I can feel the speed, it’s amazing now
Yeah I can actually browse the website instead of using it as a loading screen simulator
Hopefully eventually we can get some more professional DB admins to look ours over, but yeah this one was entirely my fault, I’m awful at DB optimization.
Still some improvements to be made, but well done, I don’t have to have other tabs open so I don’t get bored during the loading…
Removed these lines? j/k
sleep(rand(1000)); // future performance tweaks
Seriously, nice work :)
haha thx.
Wow it’s so much faster now. Great work!
Thank.
deleted by creator
Long story short… do not use materialized views if you need a lot of concurrent reads / writes.
Nice, ya you can see why I’ve been really antsy to deploy this for a few days.
It appears to be fast indeed
EDIT: Oh god, it’s so good
Wow. Incredible work.
Thank
When a topic loads, it’s fast indeed. But my visit this evening started with topics not loading at all (kept seeing a circling circle in each of my tabs in which I opened topics). Upon reloading the page, the topics actually loaded.
I’m running Firefox 77.0.1 on Windows 10
This happens to me too, since before the fixes though. I always thought it was related to my shitty internet connection, and never looked further into it.
deleted by creator
@theafterman@lemmy.ml @ConspicuouslyBland@lemmy.ml This is my next bug to fix. I’m pretty sure I know the culprit.
Okay @theafterman@lemmy.ml @ConspicuouslyBland@lemmy.ml this should be fixed now,
v0.7.13. You’ll probably need to refresh the page.I can definitely notice the improvement. I tested it in Firefox 68 ESR on my PC and Firefox Nightly on my mobile. Great work again, and quick too.
Ya this was a pretty bad bug.
Was it #801?
Yep.
deleted by creator
wow this is ridiculously fast.
How fast
Oh fast enough to be usable. Fantistico
now the this website only has 80kb of content will finally be impressive ;)
yep, i can confirm that it feels incredibly fast right now! ⚡
the speed is strong with this one
Just posting so I can see if comments work faster now.
Edit: OMG YES! Thanks and keep up the good work!
Really nice! I’ve actually refrained from using Lemmy for the past few months because of how slow it was on my end. I assume it was because I’m on the other end of the world to the servers but now it’s much more usable experience!
Congrats! and thank you!
No probs :)
wOW! NICE!
i LOVE IT
