Date: Wed, 13 Oct 2021 16:57:33 -0300
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>
Subject: Re: prevent immature WAL streaming
Message-ID: <202110131957.cjm4ftp4dtdm@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20211013191345.gqgsih7gq6jn5cxo@alap3.anarazel.de>
Archived-At: 
 <https://www.postgresql.org/message-id/202110131957.cjm4ftp4dtdm%40alvherre.pgsql>
Precedence: bulk

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