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 Server and language choices on every individual comment, many rows in the database per-community, per-site, etc. Overheard of a comment INSERT SQL
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.
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?
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.
Are those times milliseconds?
I got burned by thinking it was seconds weeks back. Documentation:
mean_exec_time double precision
Mean time spent executing the statement, in milliseconds