Lemmy Server Performance

420 readers

1 users here now

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.

founded 1 year ago

MODERATORS

[email protected]

Lemmy Server and language choices on every individual comment, many rows in the database per-community, per-site, etc. Overheard of a comment INSERT SQL (lemmy.ml)

submitted 1 year ago* (last edited 1 year ago) by [email protected] to c/[email protected]

8 comments fedilink hide all child comments

Over a short period of time, this is my incoming federation activity for new comments. pg_stat_statements output being show. It is interesting to note these two INSERT statements on comments differ only in the DEFAULT value of language column. Also note the average execution times is way higher (4.3 vs. 1.28) when the language value is set, I assume due to INDEX updates on the column? Or possibly a TRIGGER?

About half of the comments coming in from other servers have default value.

WRITES are heavy, even if it is an INDEX that has to be revised. So INSERT and UPDATE statements are important to scrutinize.

top 4 comments

sorted by: hot top controversial new old

[–] [email protected] 3 points 1 year ago* (last edited 1 year ago) (1 children)

Speaking of language overhead, pg_stat_statements shows ~~this particular SELECT statement returning almost 2000 rows per execution (if I'm interpreting the row count correctly).~~

EDIT, NEVERMIND: if I actually divided in my head, it is 184 rows per query, which I think is the default number of languages installed on a lemmy instance. so 184 rows isn't the wild 2000 number I thoughtlessly estimated.

The statement has been executed 1313 times, that number is far lower than the 3000 comment INSERT statements, so it isn't per comment. I have 803 + 703 INSERT into the post table, so that doesn't match.

Oh, ok, 186 times a similar SELECT is being run that has a high number of rows returned per execution:

1313 + 186 = 1499. 803 + 703 = 1506 post inserts (there may be more variations of each statement). It could be once per post INSERT?

I suspect these two SQL statements are supposed to have a WHERE site_id clause on them. Can someone help identify in the Rust code why incoming federation of comments/posts is running this query over and over, but not per-comment or per-post, but something less?

[–] [email protected] 2 points 1 year ago* (last edited 1 year ago)

I haven't found where 1499 executions in the time period would be accounted for, but I did notice some suspect logic in the Rust code regarding edit/creation of a community: https://github.com/LemmyNet/lemmy/blob/main/crates/api_crud/src/community/update.rs#L44

This seems to not specify a site_id when loading the languages, that would return all rows in the table.

[–] [email protected] 3 points 1 year ago (1 children)

Are those times milliseconds?

[–] [email protected] 1 points 1 year ago* (last edited 1 year ago)

I got burned by thinking it was seconds weeks back. Documentation:

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

load more comments