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.
Running for the past 24 hours, my server is almost all federation activity, subscribed to the most active communities I could find on other instances to get some data in my database. My signup is closed and I’m the only interactive user, doing mostly read-only operations.
So far with federation and reading of content being the only activity, 313 SQL statements have been identified. I’m running on a Oracle Cloud Ubuntu 22.04 system with 24GB of RAM and 200GB of block storage with 4x2Ghz Ampere Arm cores.
LIKES / UPVOTES: I’m seeing that upvotes on both the comment and post are taking over 1/3 of a second each to INSERT, 0.37 and 0.35 seconds mean average to do a single insert from incoming federation activity. with 24GB of RAM, the index should be in cache/RAM, but locking and I/O to write and log is taking significant time. That’s with only 304319 rows in my comment_like table / 124218 in post_like, not nearly the amount of rows I expect Beehaw, Lemmy.ml and other long-running sites have.