Horton

Re: standby recovery fails (tablespace related) (tentative patch and discussion)

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-08-04T02:54:41Z
Lists: pgsql-hackers 
On Sun, Jul 31, 2022 at 11:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thomas Munro <thomas.munro@gmail.com> writes:
> > I noticed this is a 32 bit FBSD system.  Is it running on UFS, perhaps
> > on slow storage?  Are soft updates enabled (visible as options in
> > output of "mount")?
>
> It's an ancient (2006) mac mini with 5400RPM spinning rust.
> "mount" says
>
> /dev/ada0s2a on / (ufs, local, soft-updates, journaled soft-updates)
> devfs on /dev (devfs)

I don't have all the details and I may be way off here but I have the
impression that when you create and then unlink trees of files
quickly, sometimes soft-updates are flushed synchronously, which turns
into many 5400 RPM seeks; dtrace could be used to check, but some
clues in your numbers would be some kind of correlation between time
and number of clusters that are set up and torn down by each test.
Without soft-updates, it'd be much worse, because then many more
things become synchronous I/O.  Even with write caching enabled,
soft-updates flush the drive cache when there's a barrier needed for
crash safety.  It may also be that there is something strange about
Apple hardware that makes it extra slow at full-cache-flush operations
(cf unexplainable excess slowness of F_FULLFSYNC under macOS including
old spinning rust systems and current flash systems, and complaints
about this general area on current Apple hardware from the Asahi
Linux/M1 port people, though how relevant that is to 2006 spinning
rust I dunno).  It would be nice to look into how to tune, fix or work
around all of that, as it also affects CI which has a IO limits
(though admittedly a couple of orders of mag higher IOPS than 5400
RPM).

Commits

  1. Improve recently-added test reliability

  2. Fix new recovery test for log_error_verbosity=verbose case

  3. Fix test instability

  4. Fix replay of create database records on standby

  5. Allow "in place" tablespaces.

  6. Fix get_dirent_type() for Windows junction points.

  7. Revert "Fix replay of create database records on standby"

  8. Add end-to-end testing of pg_basebackup's tar-format output.

  9. Make DROP DATABASE command generate less WAL records.

  10. Consolidate methods for translating a Perl path to a Windows path.