Remove materialized views. by dessalines · Pull Request #908 · LemmyNet/lemmy
github.com
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.

    • k_o_t
      4·
      4 years ago

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

      • DessalinesOPMA
        17·
        4 years ago

        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.

        • theafterman
          1·
          4 years ago

          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.

          • DessalinesOPMA
            2·
            4 years ago

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

    • ksynwa
      3·
      4 years ago

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

    • Doug
      8·
      4 years ago

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

      • DessalinesOPMA
        6·
        4 years ago

        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
          6·
          4 years ago

          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·
    4 years ago

    Removed these lines? j/k

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

    Seriously, nice work :)

    • DessalinesOPMA
      9·
      4 years ago

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

    • DessalinesOPMA
      6·
      4 years ago

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

  • 無門關
    9·
    4 years ago

    It appears to be fast indeed

    EDIT: Oh god, it’s so good

  • ConspicuouslyBland
    8·
    4 years ago

    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

  • wraptile
    6·
    4 years ago

    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!

  • theafterman
    6·
    4 years ago

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

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

  • IngrownMink4
    6·
    4 years ago

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