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
If it's an accident, it sure has worked out well for several years now. And iteratively applying it certainly has not resulted in smaller values all the time. I've watched it fluctuate up and down wildly, depending on the sampling rate.
1 reply 0 retweets 0 likes -
Replying to @keithf4
If you use the approach on a pgbench workload, it'll yield a lower s_b, even though, as my numbers above indicate, a larger s_b is the solution. At 20GB, close 23GB (when the working sets fits into memory), it suggests ~7.6GB on average. Even though 23GB is 30% faster.
2 replies 0 retweets 0 likes -
Replying to @AndresFreundTec
Interesting. Would be interesting to chat with you sometime for what your effective means to determining a good size s_b is and determing if it's too large or small.
1 reply 0 retweets 2 likes -
Replying to @keithf4 @AndresFreundTec
I'm inviting myself to that conversation.
1 reply 0 retweets 1 like
Are you both going to be at http://pgconf.eu ?
-
-
Replying to @AndresFreundTec @pg_xocolatl
Unfortunately I will not be there
0 replies 0 retweets 0 likesThanks. Twitter will use this to make your timeline better. UndoUndo
-
-
-
Thanks. Twitter will use this to make your timeline better. UndoUndo
-
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.