3 ms·

> 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 + Co

> 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

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