From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Munro <thomas.munro@gmail.com>
cc: Andres Freund <andres@anarazel.de>, Stephen Frost <sfrost@snowman.net>,
        Alvaro Herrera <alvherre@2ndquadrant.com>,
        David Steele <david@pgmasters.net>,
        Dmitry Dolgov <9erthalion6@gmail.com>,
        Jakub Wartak <Jakub.Wartak@tomtom.com>,
        Tomas Vondra <tomas.vondra@enterprisedb.com>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: WAL prefetch (another approach)
In-reply-to: 
 <CA+hUKGJ03Q-TmE8-JzAm04_1maYus6_kX7XB--JBh1K63agSbQ@mail.gmail.com>
References: <8dd2dcc2-0b6b-a4ab-c1b3-032c0a5ab1c3@enterprisedb.com>
 <CA+hUKGKmkKfH1h9XG5ctYCgqeOHGdN5bHMoQMZLR=+kLY0PQzA@mail.gmail.com>
 <CA+hUKGLsF8sEE+n3qoaV6+XjmYEwYoFVDbLPbYTGz7oGkL=iZA@mail.gmail.com>
 <CA+hUKG+a4wn7B-qBu9SLc1HAbZRSKbp1Ep=rDeveM-UQz84+jg@mail.gmail.com>
 <773932.1619022622@sss.pgh.pa.us>
 <b971373e-5f05-e691-d83c-c72a4f67167b@enterprisedb.com>
 <CA+hUKGJKDibdw5HzOdgh+tFsA7=wwJ3O6p+qYwbgAD_xkLWA3Q@mail.gmail.com>
 <CAOuzzgrn7iKnFRsB4MHp3UisEQAGgZMbk_ViTN4HV4-Ksq8zCg@mail.gmail.com>
 <892344.1619054465@sss.pgh.pa.us>
 <CA+hUKGJKKh8TbA+6977zK5TKUSsTspBHsr0s4S42q7ynkxcPGQ@mail.gmail.com>
 <20210428163245.ff67pb2ii5irr5zr@alap3.anarazel.de>
 <2808045.1619628335@sss.pgh.pa.us>
 <CA+hUKGJ03Q-TmE8-JzAm04_1maYus6_kX7XB--JBh1K63agSbQ@mail.gmail.com>
Comments: In-reply-to Thomas Munro <thomas.munro@gmail.com>
	message dated "Thu, 29 Apr 2021 10:46:14 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2947528.1619652293.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 28 Apr 2021 19:24:53 -0400
Message-ID: <2947529.1619652293@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2947529.1619652293%40sss.pgh.pa.us>
Precedence: bulk

Thomas Munro <thomas.munro@gmail.com> writes:
> FWIW I also chewed through many megawatts trying to reproduce this on
> a PowerPC system in 64 bit big endian mode, with an emulator.  No
> cigar.  However, it's so slow that I didn't make it to 10 runs...

Speaking of megawatts ... my G4 has now finished about ten cycles of
installcheck-parallel without a failure, which isn't really enough
to draw any conclusions yet.  But I happened to notice the
accumulated CPU time for the background processes:

USER       PID  %CPU %MEM      VSZ    RSS   TT  STAT STARTED      TIME COM=
MAND
tgl      19048   0.0  4.4   229952  92196   ??  Ss    3:19PM  19:59.19 pos=
tgres: startup recovering 000000010000001400000022    =

tgl      19051   0.0  0.1   229656   1696   ??  Ss    3:19PM  27:09.14 pos=
tgres: walreceiver streaming 14/227D8F14    =

tgl      19052   0.0  0.1   229904   2516   ??  Ss    3:19PM  17:38.17 pos=
tgres: walsender tgl [local] streaming 14/227D8F14 =


IOW, we've spent over twice as many CPU cycles shipping data to the
standby as we did in applying the WAL on the standby.  Is this
expected?  I've got wal_consistency_checking =3D all, which is bloating
the WAL volume quite a bit, but still it seems like the walsender and
walreceiver have little excuse for spending more cycles per byte
than the startup process.

(This is testing b3ee4c503, so if Thomas' WAL changes improved
efficiency of the replay process at all, the discrepancy could be
even worse in HEAD.)

			regards, tom lane