It's also possible to figure out what your actual shared buffers needs are as well! #postgresql https://www.keithf4.com/a-small-database-does-not-mean-small-shared_buffers/ …https://twitter.com/AndresFreundTec/status/1178765225895399424 …
-
-
Replying to @keithf4
Unfortunately I think that methodology doesn't yield a meaningful result, except by accident. How many buffers are at what usagecount HUGELY depends on the size of shared buffers, because that influences the clock sweep rate. A usagecount < 3 doesn't mean the buffer is useless.
1 reply 0 retweets 1 like -
Replying to @AndresFreundTec @keithf4
If you have a workload not fitting in s_b, with a cache hit ratio of say 95% and an uneven distribution of those accesses (i.e. almost all realistic ones), there'll be a lot of buffers with low usagecounts. But they still can save a *LOT* of IO.
1 reply 0 retweets 0 likes -
Replying to @AndresFreundTec @keithf4
So unless you have a cache hit ratio very close to 100%, the usagecount analysis in that blog post doesn't tell whether specific shared buffers are useful or not. And in that case there's still no reason to use 3 as the cutoff (either 5 or 1 could make at least as much sense).
1 reply 0 retweets 0 likes -
Replying to @AndresFreundTec @keithf4
Pretty much by definition, as long as there some buffers need to be evicted to make room for others (i.e. workload doesn't fit into shared buffers), there will be buffers with usagecount <= 3 most of the time. The clock sweep logic *forces* that to be the case.
1 reply 0 retweets 1 like -
Replying to @AndresFreundTec @keithf4
Iteratively applying the approach presented, will almost always lead to a even smaller shared buffers recommendation in each further round. With a smaller s_b setting, there necessarily needs to be buffer replacement, which in turn means there'll be buffers with usagecount < 3.
2 replies 0 retweets 0 likes -
Replying to @AndresFreundTec
Also when you start looking at individual objects to see their demand, you can sometimes see the whole object being pegged at 5, so maybe it's better to look at individual object usage vs the usage count of everything?
1 reply 0 retweets 0 likes
Yea, you *sometimes* will be able to glean information that way. But very often not - there's often a time based component e.g. to indexes, leading to "one end" having decreasing usagecounts. Without that suggesting at all that s_b is too big.
Loading seems to be taking a while.
Twitter may be over capacity or experiencing a momentary hiccup. Try again or visit Twitter Status for more information.