Re: prevent immature WAL streaming

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, "Bossart, Nathan" <bossartn(at)amazon(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, "masao(dot)fujii(at)oss(dot)nttdata(dot)com" <masao(dot)fujii(at)oss(dot)nttdata(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "mengjuan(dot)cmj(at)alibaba-inc(dot)com" <mengjuan(dot)cmj(at)alibaba-inc(dot)com>, "Jakub(dot)Wartak(at)tomtom(dot)com" <Jakub(dot)Wartak(at)tomtom(dot)com>, Ryo Matsumura <matsumura(dot)ryo(at)fujitsu(dot)com>
Subject: Re: prevent immature WAL streaming
Date: 2021-10-13 19:57:33
Message-ID: 202110131957.cjm4ftp4dtdm@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

create table filler (a int, b text);
do $$
wal_segsize int := setting::int from pg_settings where name = 'wal_segment_size';
remain int;
iters int := 0;
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
end if;
iters := iters + 1;
end loop;
$$ ;

(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 —
"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)

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2021-10-13 20:06:32 Re: [RFC] building postgres with meson
Previous Message Tom Lane 2021-10-13 19:49:41 Re: AW: VS2022: Support Visual Studio 2022 on Windows