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
1Y

woohoo :party popper:

@k_o_t
admin
41Y

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

Dessalines
mod
admin
creator
171Y

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
41Y

Thank you so much for the hard work!

Dessalines
mod
admin
creator
21Y

No probs :)

@k_o_t
admin
21Y

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
21Y

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

@ksynwa
31Y

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

Dessalines
mod
admin
creator
41Y

Multiple cores.

@Alex1138
21Y

What program is this?

Dessalines
mod
admin
creator
11Y

grafana.

I can feel the speed, it’s amazing now

Dessalines
mod
admin
creator
211Y

@Doug
81Y

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

Dessalines
mod
admin
creator
61Y

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
61Y

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
1Y

Removed these lines? j/k

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

Seriously, nice work :)

Dessalines
mod
admin
creator
2
edit-2
1Y

haha thx.

@snackwifi
101Y

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

Dessalines
mod
admin
creator
51Y

Thank.

無門關
9
edit-2
1Y

It appears to be fast indeed

EDIT: Oh god, it’s so good

Wow. Incredible work.

Dessalines
mod
admin
creator
51Y

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
1Y

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
1Y

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
51Y

@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
4
edit-2
1Y

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
21Y

Ya this was a pretty bad bug.

Was it #801?

Dessalines
mod
admin
creator
31Y

Yep.

@inwardpath
21Y

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
31Y

How fast

@schwartz
51Y

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
61Y

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
61Y

the speed is strong with this one

@theafterman
6
edit-2
1Y

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
1Y

wOW! NICE!

i LOVE IT

@nxlemmy
51Y

Congrats! and thank you!

Dessalines
mod
admin
creator
11Y

No probs :)

@DelishVeg
41Y

Wow I feel it, nice work!

@ajz
mod
41Y

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

@Koa_lala
31Y

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
41Y

It was my scheme all along :smirking face:

@Restioson
21Y

test

@Restioson
21Y

nice

@diorama
21Y

cool

@penloy
21Y

Hell yeah!

@heqt1c
11Y

Still fast.

@developred
10
edit-2
5M

deleted by creator

Dessalines
mod
admin
creator
91Y

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

Dessalines
mod
admin
creator
61Y

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

@resynth1943
3
edit-2
5M

deleted by creator

@resynth1943
4
edit-2
5M

deleted by creator

@heqt1c
2
edit-2
1Y

How fast is it? edit: Very fast.

riccardo
2
edit-2
1Y

Is it? Edit: yes

@resynth1943
1
edit-2
5M

deleted by creator

@resynth1943
1
edit-2
5M

deleted by creator

@resynth1943
2
edit-2
5M

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
  • 18 users / week
  • 89 users / month
  • 333 users / 6 months
  • 10180 subscribers
  • 475 Posts
  • 2691 Comments
  • Modlog