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.

  • RoundSparrowOPM
    2·
    2 years ago

    Damn it - I made a mistake

    Ok, re-reading the documentation again, I made a major error interpreting these results.

    mean_exec_time double precision: Mean time spent executing the statement, in milliseconds

    All my statements about INSERT on Upvotes taking 1/3 of a second are wrong, it’s less than 1 millisecond. Although it sure doesn’t feel that fast when you are interactively using Lemmy 0.18 and pressing the Vote buttons, it seems rather sluggish. I’ve almost never seen fractions of a milliseconds, but here it is.