7
Lemmy server admins, please enable pg_stat_statements on your PostgreSQL server so the Lemmy community can better identify the performance problems on the more active sites (lemmy.ml)
submitted 3 years ago* (last edited 3 years ago) by [M] to c/lemmyperformance@lemmy.ml
6 comments fedilink hide all child comments
 

I have been working with pg_stat_statements extension to PG and it give us a way to see the actual SQL statements being executed by lemmy_server and the number of times they are being called.

This has less overhead than cranking up logging and several cloud computing services enable it by default (example) - so I don't believe it will have a significant slow down of the server.

A DATABASE RESTART WILL BE REQUIRED

It does require that PostgreSQL be restarted. Which can take 10 or 15 seconds, typically.

Debian / Ubuntu install steps

https://pganalyze.com/docs/install/self_managed/02_enable_pg_stat_statements_deb

Following the conventions of "Lemmy from Scratch" server install commands:

sudo -iu postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"

Followed by a restart of the PostgreSQL service.

top 3 comments

sorted by: hot top controversial new old
[–] [S] 2 points 3 years ago

source: https://github.com/LemmyNet/lemmy/pull/3482

  • permalink
  • fedilink
  • source
    •
    [–] [S] 2 points 3 years ago* (last edited 3 years ago)

    Update July 24, 2023

    Some major stored procedure SQL problems were overlooked until 2 days ago. I submitted revised statements to fix a massive write operation on every single post and comment creation on a local site. The site_aggregates table... every row modified instead of a single row. https://github.com/LemmyNet/lemmy/pull/3704

    I was curious why pg_stat_statements didn't draw more attention to the INSERT statements hitting so many rows, and I found out that by default it does not take into account stored procedure execution! https://stackoverflow.com/questions/56741860/pg-stat-activity-how-to-see-current-activity-inside-a-running-stored-procedure

    pg_stat_statements.track = all

    Now I'm seeing far more activity than I have been looking at for the past 2 months... the stored procedure statements are showing up!

    Install steps: https://gist.github.com/rcanepa/535163dc249539912c25

  • permalink
  • fedilink
  • source
    •
    [–] 1 point 3 years ago

    @roundsparrow@lemmy.ml If you find queries that are slow could you post the EXPLAIN ANALYZE here or send them to me? You can do it manually or maybe use https://www.postgresql.org/docs/current/auto-explain.html

  • permalink
  • fedilink
  • source
    •
    load more comments
    view more: next ›