4 ms·

We don't believe much in such static tuning recommendations. Our experience tells us that they can do as much good as bad. It is much more important to underst

We don't believe much in such static tuning recommendations. Our experience tells us that they can do as much good as bad. It is much more important to understand and learn a bit about how to tune them. You need to understand the workload, the usage pattern, to do a proper tuning. This site is a first step into this direction: provide guidance, centralize the available documentation, provide general recommendations. Other steps will follow suit, all focused on helping Postgres users tune the configuration better. But as of today, we know a lot of people using this site in their daily work, as a very convenient mechanism to check information you need to have handy when tuning Postgres. And/or use it to share stable and versioned URLs when you want to provide a link to reference what you are talking about, be it a blog post or a link in a document. All in all, we hope it can be useful as it is, and even more with the steps that will come after ;)

deleted 7y ago
[deleted]
wakatime 7y ago
> We don't believe much in such static tuning recommendations. Our experience tells us that they can do as much good as bad. PGTune just automates the RAM + Connections math you would normally do manually. PGTune is a good starting point, but you still need to know what each config does and configure beyond PGTune. Nobody is saying PGTune does your configs for you, it's just automating what we always did manually before.
- ahachete 7y ago
  But I mostly disagree with that math also ;) Mostly, because that math is subject to other parameters that are not easy to know statically --otherwise, why aren't them Postgres defaults? For example: shared_buffers is 1/4 of RAM and effective_cache_size 3/4. Well, several benchmarks have already pointed out that 1/4 is not necessarily a good number, and you need to benchmark your own workload. Similarly, effective_cache_size is slightly over dimensioned for dedicated servers and definitely too big for shared servers. Even more clearly, the max_connections recommendation may even become a significant problem for your database. You should almost always have a connection pooler in front of Postgres and have max_connections a small multiple of your cores. PgTune's recommendation is probably an order of magnitude higher than usual good values, which may lead to much worse performance. Another example: min_wal_size should be always a higher value than what is recommended if you have enough disk, and max_wal_size should definitely be something like significantly higher than what is recommended.
  - wakatime 7y ago
    You should blog about your learnings, would love to read more about "PG performance configs you should look into". We're probably leaving too many configs at default values just because we didn't know they were worth tweaking.
    - ahachete 7y ago
      Maybe I haven't blogged (yet!) about that, but definitely spoken about it: https://aht.es/#talks-postgresql_configuration_for_humans https://aht.es/#talks-postgresql_configuration_for_humans
thom 7y ago
I'm sure you've thought of this, but one thing you could do eventually is give a visitor a walkthrough, starting with the type of workload they expect, and highlight at each step what the most important levers available to them are. By all means stop short of calculating actual values etc, but just give people some entry point other than the full list of parameters, however nice the write-up under each parameter is (unless I'm missing something in the UI!)
- ahachete 7y ago
  Indeed. This is pretty much part of what it will be coming in the next iteration. What we're thinking is providing a set of "cards", where each card contains a theme: logging tuning, memory parameters, autovacuum, etc. And then, every card contains a recommended set of parameters to tune, and guidance about how to tune them. Thanks for the feedback :)

deleted 7y ago

[deleted]

wakatime 7y ago

> We don't believe much in such static tuning recommendations. Our experience tells us that they can do as much good as bad. PGTune just automates the RAM + Connections math you would normally do manually. PGTune is a good starting point, but you still need to know what each config does and configure beyond PGTune. Nobody is saying PGTune does your configs for you, it's just automating what we always did manually before.

ahachete 7y ago
But I mostly disagree with that math also ;) Mostly, because that math is subject to other parameters that are not easy to know statically --otherwise, why aren't them Postgres defaults? For example: shared_buffers is 1/4 of RAM and effective_cache_size 3/4. Well, several benchmarks have already pointed out that 1/4 is not necessarily a good number, and you need to benchmark your own workload. Similarly, effective_cache_size is slightly over dimensioned for dedicated servers and definitely too big for shared servers. Even more clearly, the max_connections recommendation may even become a significant problem for your database. You should almost always have a connection pooler in front of Postgres and have max_connections a small multiple of your cores. PgTune's recommendation is probably an order of magnitude higher than usual good values, which may lead to much worse performance. Another example: min_wal_size should be always a higher value than what is recommended if you have enough disk, and max_wal_size should definitely be something like significantly higher than what is recommended.
- wakatime 7y ago
  You should blog about your learnings, would love to read more about "PG performance configs you should look into". We're probably leaving too many configs at default values just because we didn't know they were worth tweaking.
  - ahachete 7y ago
    Maybe I haven't blogged (yet!) about that, but definitely spoken about it: https://aht.es/#talks-postgresql_configuration_for_humans https://aht.es/#talks-postgresql_configuration_for_humans

thom 7y ago

I'm sure you've thought of this, but one thing you could do eventually is give a visitor a walkthrough, starting with the type of workload they expect, and highlight at each step what the most important levers available to them are. By all means stop short of calculating actual values etc, but just give people some entry point other than the full list of parameters, however nice the write-up under each parameter is (unless I'm missing something in the UI!)

ahachete 7y ago
Indeed. This is pretty much part of what it will be coming in the next iteration. What we're thinking is providing a set of "cards", where each card contains a theme: logging tuning, memory parameters, autovacuum, etc. And then, every card contains a recommended set of parameters to tune, and guidance about how to tune them. Thanks for the feedback :)