Remove materialized views. by dessalines · Pull Request #908 · LemmyNet/lemmy
github.com
external-link
I wrote up a big thing on this, and then my computer crashed :( so here's the short version. Read speeds: ☭ ./generate_explain_reports.sh comment_fast_view.json: "Execution Time...

Credit to @eiknat@dev.lemmy.ml , and masterstur for their help with this effort.

Our postgres CPU usage has gone wayyyy down.

Dessalines
mod
admin
creator
link
fedilink
21
edit-2
2Y

woohoo :party popper:

@k_o_t
link
fedilink
42Y

bruh, that’s huge! what caused so many issues previously may i ask?

Dessalines
mod
admin
creator
link
fedilink
172Y

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.

@oriond
link
fedilink
42Y

Thank you so much for the hard work!

Dessalines
mod
admin
creator
link
fedilink
22Y

No probs :)

@k_o_t
link
fedilink
22Y

nice, thanks :)

@theafterman
link
fedilink
12Y

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.

Dessalines
mod
admin
creator
link
fedilink
22Y

It’s used elsewhere, but if you look up sql procedural functions or audit tables, you’ll find more.

@ksynwa
link
fedilink
32Y

how can it >100%? is that hyperthreading magic?

Dessalines
mod
admin
creator
link
fedilink
42Y

Multiple cores.

@Alex1138
link
fedilink
22Y

What program is this?

Dessalines
mod
admin
creator
link
fedilink
12Y

grafana.

I can feel the speed, it’s amazing now

Dessalines
mod
admin
creator
link
fedilink
212Y

@Doug
link
fedilink
82Y

Yeah I can actually browse the website instead of using it as a loading screen simulator

Dessalines
mod
admin
creator
link
fedilink
62Y

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.

@ster
link
fedilink
62Y

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…

Arden
link
fedilink
11
edit-2
2Y

Removed these lines? j/k

sleep(rand(1000)); // future performance tweaks

Seriously, nice work :)

Dessalines
mod
admin
creator
link
fedilink
2
edit-2
2Y

haha thx.

@snackwifi
link
fedilink
102Y

Wow it’s so much faster now. Great work!

Dessalines
mod
admin
creator
link
fedilink
52Y

Thank.

無門關
link
fedilink
9
edit-2
2Y

It appears to be fast indeed

EDIT: Oh god, it’s so good

Wow. Incredible work.

Dessalines
mod
admin
creator
link
fedilink
52Y

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

@theafterman
link
fedilink
5
edit-2
2Y

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.

@inwardpath
link
fedilink
4
edit-2
4M

deleted by creator

Dessalines
mod
admin
creator
link
fedilink
52Y

@theafterman@lemmy.ml @ConspicuouslyBland@lemmy.ml This is my next bug to fix. I’m pretty sure I know the culprit.

Dessalines
mod
admin
creator
link
fedilink
4
edit-2
2Y

Okay @theafterman@lemmy.ml @ConspicuouslyBland@lemmy.ml this should be fixed now, v0.7.13. You’ll probably need to refresh the page.

@theafterman
link
fedilink
42Y

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.

Dessalines
mod
admin
creator
link
fedilink
22Y

Ya this was a pretty bad bug.

@theafterman
link
fedilink
32Y

Was it #801?

Dessalines
mod
admin
creator
link
fedilink
32Y

Yep.

@inwardpath
link
fedilink
2
edit-2
4M

deleted by creator

wow this is ridiculously fast.

@schwartz
link
fedilink
32Y

How fast

@schwartz
link
fedilink
52Y

Oh fast enough to be usable. Fantistico

now the this website only has 80kb of content will finally be impressive ;)

IngrownMink4
link
fedilink
62Y

yep, i can confirm that it feels incredibly fast right now! ⚡

@wraptile
banned
link
fedilink
62Y

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!

adamsky
link
fedilink
62Y

the speed is strong with this one

@theafterman
link
fedilink
6
edit-2
2Y

Just posting so I can see if comments work faster now.

Edit: OMG YES! Thanks and keep up the good work!

Evan
link
fedilink
5
edit-2
2Y

wOW! NICE!

i LOVE IT

@nxlemmy
link
fedilink
52Y

Congrats! and thank you!

Dessalines
mod
admin
creator
link
fedilink
12Y

No probs :)

@DelishVeg
link
fedilink
42Y

Wow I feel it, nice work!

Lemmy Announcements


Feel free to announce new communities here.

Other than that, this is reserved for admin use only.

  • 0 users online
  • 4 users / day
  • 14 users / week
  • 42 users / month
  • 389 users / 6 months
  • 16.3K subscribers
  • 835 Posts
  • 3.97K Comments
  • Modlog