Horton

Re: [HACKERS] WAL logging problem in 9.4.3?

Kyotaro Horiguchi <horikyota.ntt@gmail.com>

View thread View on postgr.es
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
To: robertmhaas@gmail.com
Cc: noah@leadboat.com, pgsql-hackers@postgresql.org, 9erthalion6@gmail.com, andrew.dunstan@2ndquadrant.com, hlinnaka@iki.fi, michael@paquier.xyz
Date: 2019-12-10T07:59:25Z
Lists: pgsql-hackers

Attachments

At Mon, 9 Dec 2019 10:56:40 -0500, Robert Haas <robertmhaas@gmail.com> wrote in 
> On Mon, Dec 9, 2019 at 4:04 AM Kyotaro Horiguchi
> <horikyota.ntt@gmail.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

Commits

  1. Add perl2host call missing from a new test file.

  2. Skip WAL for new relfilenodes, under wal_level=minimal.

  3. Revert "Skip WAL for new relfilenodes, under wal_level=minimal."

  4. Back-patch log_newpage_range().

  5. During heap rebuild, lock any TOAST index until end of transaction.

  6. In log_newpage_range(), heed forkNum and page_std arguments.

  7. Back-patch src/test/recovery and PostgresNode from 9.6 to 9.5.

  8. Reduce pg_ctl's reaction time when waiting for postmaster start/stop.

  9. Accelerate end-of-transaction dropping of relations

  10. Redesign the planner's handling of index-descent cost estimation.

  11. Make TRUNCATE do truncate-in-place when processing a relation that was created