At Mon, 9 Dec 2019 10:56:40 -0500, Robert Haas <robertmhaas(at)gmail(dot)com> wrote in
> On Mon, Dec 9, 2019 at 4:04 AM Kyotaro Horiguchi
> <horikyota(dot)ntt(at)gmail(dot)com> wrote:
> > Yeah, only 0.5GB of shared_buffers makes the default value of
> > wal_buffers reach to the heaven. I think I can take numbers on that
> > condition. (I doubt that it's meaningful if I increase only
> > wal_buffers manually.)
>
> Heaven seems a bit exalted, but I think we really only have a formula
> because somebody might have really small shared_buffers for some
> reason and be unhappy about us gobbling up a comparatively large
> amount of memory for WAL buffers. The current limit means that normal
> installations get what they need without manual tuning, and small
> installations - where performance presumably sucks anyway for other
> reasons - keep a small memory footprint.
True. I meant the ceiling of defaultly tuned value, and the larger
value may work on the larger system.
Anyway, I ran the benchmark with
shared_buffers=1GB/wal_buffers=16MB(defalut). pgbench -s 20 uses 256MB
of storage so all of them can be loaded on shared memory.
The attached graph shows larger benefit in TPS drop and latency
increase for HDD. The DDL pages at the corsspoint between commit-FPW
and commit-sync moves from roughly 300 to 200 in TPS and latency, and
1000 to 600 in DDL runtime. If we can rely on the two graphs, 500 (or
512) pages seems to be the most promising candidate for the default
value of wal_skip_threshold.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
