Horton

Re: prevent immature WAL streaming

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew@dunslane.net>, "Bossart, Nathan" <bossartn@amazon.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, "masao.fujii@oss.nttdata.com" <masao.fujii@oss.nttdata.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, "mengjuan.cmj@alibaba-inc.com" <mengjuan.cmj@alibaba-inc.com>, "Jakub.Wartak@tomtom.com" <Jakub.Wartak@tomtom.com>, Ryo Matsumura <matsumura.ryo@fujitsu.com>
Date: 2021-10-13T19:57:33Z
Lists: pgsql-hackers 
On 2021-Oct-13, Andres Freund wrote:

> > > Another thing: filling a segment by inserting lots of very tiny rows is pretty
> > > expensive. Can't we use something a bit wider? Perhaps even emit_message?
> 
> FWIW, the count of inserted rows is something like 171985 ;)

This does ~1600 iterations to fill one segment, 10 rows per iteration,
row size is variable; exits when two BLCKSZ remain to complete the WAL
segment:

create table filler (a int, b text);
do $$
declare
        wal_segsize int := setting::int from pg_settings where name = 'wal_segment_size';
        remain int;
        iters int := 0;
begin
        loop
                insert into filler
                select g, repeat(md5(g::text), (random() * 60 + 1)::int)
                from generate_series(1, 10) g;

                remain := wal_segsize - (pg_current_wal_insert_lsn() - '0/0') % wal_segsize;
                raise notice '(%) remain: %', iters, remain;
                if remain < 2 * setting::int from pg_settings where name = 'block_size' then
                        exit;
                end if;
                iters := iters + 1;
        end loop;
end
$$ ;

(Of course, I'm not proposing that the 'raise notice' be there in the
committed form.)

If I enlarge the 'repeat' count, it gets worse (more iterations
required) because a lot of the rows become toasted and thus subject to
compression.  If I do 20 rows per iteration rather than 10, the risk is
that we'll do too many near the end of the segment and we'll have to
continue running until completing the next one.

So, this seems good enough.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL.  This is by far the most pleasant management experience of
any database I've worked on."                             (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php

Commits

  1. Fix determination of broken LSN in OVERWRITTEN_CONTRECORD

  2. Fix instability in 026_overwrite_contrecord.pl test.

  3. Fix typo

  4. Change recently added test code for stability

  5. Remove unstable, unnecessary test; fix typo

  6. Repair two portability oversights of new test

  7. Fix WAL replay in presence of an incomplete record

  8. Document XLOG_INCLUDE_XID a little better

  9. Revert "Avoid creating archive status ".ready" files too early"

  10. Avoid creating archive status ".ready" files too early

  11. Fix scenario where streaming standby gets stuck at a continuation record.

  12. Teach xlogreader to follow timeline switches

  13. Forbid using pg_xlogfile_name() and pg_xlogfile_name_offset() during