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
21
edit-2
9M

woohoo :party popper:

@k_o_t
admin
49M

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

Dessalines
mod
admin
creator
179M

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
49M

Thank you so much for the hard work!

Dessalines
mod
admin
creator
29M

No probs :)

@k_o_t
admin
29M

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.

Dessalines
mod
admin
creator
29M

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

@ksynwa
39M

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

Dessalines
mod
admin
creator
49M

Multiple cores.

@Alex1138
29M

What program is this?

Dessalines
mod
admin
creator
19M

grafana.

I can feel the speed, it’s amazing now

Dessalines
mod
admin
creator
219M

@Doug
89M

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

Dessalines
mod
admin
creator
69M

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
69M

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
11
edit-2
9M

Removed these lines? j/k

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

Seriously, nice work :)

Dessalines
mod
admin
creator
2
edit-2
9M

haha thx.

@snackwifi
109M

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

Dessalines
mod
admin
creator
59M

Thank.

@felipeforte
9
edit-2
9M

It appears to be fast indeed

EDIT: Oh god, it’s so good

Wow. Incredible work.

Dessalines
mod
admin
creator
59M

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
5
edit-2
9M

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
4
edit-2
9M

I also still get this issue. Firefox 78.0.1 on macOS 10.15.5

For instance, I have opened tabs to both c/buddhism and c/zen and neither one is loading, even after refreshes. Just spinning circle.

After more testing I think it only happens when opening links to lemmy pages in new tabs. Because in those dead tabs- if I navigate to another link and back, then it works.

Dessalines
mod
admin
creator
59M

@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
3
edit-2
9M

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.

Dessalines
mod
admin
creator
29M

Ya this was a pretty bad bug.

Was it #801?

Dessalines
mod
admin
creator
39M

Yep.

@inwardpath
29M

Wow, that was fast! 😀 Can confirm here that it’s working great now- haven’t had a single spinning circle and have opened many new tabs. 👍

wow this is ridiculously fast.

@schwartz
39M

How fast

@schwartz
59M

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! ⚡

@wraptile
banned
69M

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
69M

the speed is strong with this one

@theafterman
6
edit-2
9M

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

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

@Cloak
5
edit-2
9M

wOW! NICE!

i LOVE IT

@nxlemmy
59M

Congrats! and thank you!

Dessalines
mod
admin
creator
19M

No probs :)

@DelishVeg
49M

Wow I feel it, nice work!

@ajz
mod
49M

ho ho ho ! :party popper: :sparkler: :sparkles:

@Koa_lala
39M

Yes hello this is test.

By making it slower at first, we now appreciate the speed.

Such is the way of the Buddha.

Dessalines
mod
admin
creator
49M

It was my scheme all along :smirking face:

@Restioson
29M

test

@Restioson
29M

nice

@diorama
29M

cool

@penloy
29M

Hell yeah!

@heqt1c
19M

Still fast.

@developred
10
edit-2
3M

deleted by creator

Dessalines
mod
admin
creator
99M

Long story short… do not use materialized views if you need a lot of concurrent reads / writes.

Dessalines
mod
admin
creator
69M

Nice, ya you can see why I’ve been really antsy to deploy this for a few days.

@resynth1943
3
edit-2
3M

deleted by creator

@resynth1943
4
edit-2
3M

deleted by creator

@heqt1c
2
edit-2
9M

How fast is it? edit: Very fast.

riccardo
2
edit-2
9M

Is it? Edit: yes

@resynth1943
1
edit-2
3M

deleted by creator

@resynth1943
1
edit-2
3M

deleted by creator

@resynth1943
2
edit-2
3M

deleted by creator

Lemmy Announcements


Feel free to announce new communities here.

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

  • 0 users online
  • 3 users / day
  • 37 users / week
  • 66 users / month
  • 309 users / 6 months
  • 9032 subscribers
  • 384 Posts
  • 2427 Comments
  • Modlog