The often repeated recommendation to not exceed 8GB/25% of RAM for shared buffers in postgres is wrong. For pgbench scale 1500, on laptop with fast SSD and 32GB of RAM, parallelism of 16. s_b of 1GB, 8GB, 16GB, using huge pages. r/o: 115k, 100k, 185k r/w: 16300, 15100, 21500
-
Show this thread
-
Replying to @AndresFreundTec
Thanks for the results. A few questions: 1) what OS? If Linux: 2) is any of this a result of kernel VM improvements? 3) for r/w is there more response time variance with 16gb because of writeback storms?
1 reply 0 retweets 0 likes -
Replying to @MarkCallaghanDB
It's indeed linux. And I don't think it's really VM improvement depandant - I think this has been true for a long time. The response time for 24GB (see copy/paste correction upthread) is vastly better than for the other shared buffer settings, as there is no buffer replacement.
2 replies 0 retweets 1 like -
Replying to @AndresFreundTec @MarkCallaghanDB
Which means all the writes happen in a sorted manner (albeit with some gaps depending on write patterns). These days we, by default, prevent writeback storms caused by checkpointing, by issuing sync_file_range(SYNC_FILE_RANGE_WRITE) to control outstanding writes.
2 replies 0 retweets 2 likes -
Replying to @AndresFreundTec
I was curious if the work by
@axboe helped Postgreshttps://lwn.net/Articles/682582/ …1 reply 0 retweets 0 likes -
Replying to @MarkCallaghanDB @axboe
It reduces some latency spikes in the cases where our own writeback control is not enabled for the sources of dirty data (i.e. backend writes by default, or another *_flush_after setting disabled). It also costs a bit of throughput in some cases.
1 reply 0 retweets 1 like
IIRC the bigger improvements was the rate limiting of page cache dirtying a few years ago. Doesn't make a meaningful difference for the cases where our flushing logic is enabled, but when not it is a huge improvement. IIRC that was done after our own writeback control.
-
-
There were production cases were we saw stalls of *single* writes in the 1000s range. On rotational disks, obviously. Not fun for a database workload.
0 replies 0 retweets 1 likeThanks. 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.