search

LemmyNet / lemmy-ansible

A docker deploy for ansible
GNU Affero General Public License v3.0
248 stars 92 forks source link

Recommended/default config should include pg_stat_statements and auto_explain #211

Closed phiresky closed 4 months ago

phiresky commented 9 months ago

It's annoying to get vague reports about db perf issues without basic tools to get more info. I thought these were already part of the recommended installation but doesn't look like it

They don't affect performance (afaik) so shoudl be fine to always have active

ticoombs commented 9 months ago

I gather you mean these 3 configuration options? https://github.com/LemmyNet/lemmy/issues/4282#issuecomment-1860521011

From the auto_explain documentation; (Emphasis is mine)

More typical usage is to preload it into some or all sessions by including auto_explain in session_preload_libraries or shared_preload_libraries in postgresql.conf. Then you can track unexpectedly slow queries no matter when they happen. Of course there is a price in overhead for that.

I agree it would be a good idea, especially in helping to diagnose these issues everyone is happen. . What about if we have a commented debugging section in customPostgres.conf with sane-defaults so that admins can add / remove at will. Do you think that would be an acceptable middleground?

phiresky commented 9 months ago

According to the same docs, most of the overhead only happens if log_analyze is set to true:

Turning off auto_explain.log_timing ameliorates the performance cost, at the price of obtaining less information

It's hard to find a lot of specific information, but according to https://www.pgmustard.com/blog/auto-explain-overhead-with-timing the overhead may be in the order of 1% (with default settings). That sounds to me like a good cost for getting useful info out of instance admins