From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bossart, Nathan <bossartn@amazon.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: masao.fujii@oss.nttdata.com <masao.fujii@oss.nttdata.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: mengjuan.cmj@alibaba-inc.com <mengjuan.cmj@alibaba-inc.com>
Cc: Jakub.Wartak@tomtom.com <Jakub.Wartak@tomtom.com>
Cc: Ryo Matsumura <matsumura.ryo@fujitsu.com>
Subject: Re: prevent immature WAL streaming
Date: Wed, 13 Oct 2021 16:57:33 -0300
Message-ID: <202110131957.cjm4ftp4dtdm@alvherre.pgsql> (raw)
In-Reply-To: <20211013191345.gqgsih7gq6jn5cxo@alap3.anarazel.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, andres@anarazel.de, tgl@sss.pgh.pa.us, andrew@dunslane.net, bossartn@amazon.com, horikyota.ntt@gmail.com, masao.fujii@oss.nttdata.com, pgsql-hackers@lists.postgresql.org, mengjuan.cmj@alibaba-inc.com, Jakub.Wartak@tomtom.com, matsumura.ryo@fujitsu.com
Subject: Re: prevent immature WAL streaming
In-Reply-To: <202110131957.cjm4ftp4dtdm@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox