Lemmy Server Performance
Lemmy Server Performance
lemmy_server uses the Diesel ORM that automatically generates SQL statements. There are serious performance problems in June and July 2023 preventing Lemmy from scaling. Topics include caching, PostgreSQL extensions for troubleshooting, Client/Server Code/SQL Data/server operator apps/sever operator API (performance and storage monitoring), etc.
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
@[email protected] 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