Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lsrjP-0000aM-BG for pgsql-hackers@arkaria.postgresql.org; Mon, 14 Jun 2021 18:54:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lsrjO-0001q0-7V for pgsql-hackers@arkaria.postgresql.org; Mon, 14 Jun 2021 18:54:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lsqZq-00062d-DK for pgsql-hackers@lists.postgresql.org; Mon, 14 Jun 2021 17:40:46 +0000 Received: from mail-lj1-x236.google.com ([2a00:1450:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lsqZo-0001b8-4i for pgsql-hackers@postgresql.org; Mon, 14 Jun 2021 17:40:45 +0000 Received: by mail-lj1-x236.google.com with SMTP id d2so21204570ljj.11 for ; Mon, 14 Jun 2021 10:40:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=QRSlup+Sf9ZOTd05yImL0g72M102fPJvu8LGng9mR80=; b=r4cPosJrLHUffFOr03+Mbc78gLEI1Mg9vvgQ88ZI3nge7UE56P8NZrth4L5zR8zNYU +v6xdyZx0+Z8kCtLOgwKqQ5b8xzzIOX7R2ujsGK8kCQ0C0hmrlVimQYp/0kwQe6Vlz3A wID0HiYOizEe2f58ef5qkPItmtNvSHjOEiokrOIptlfO8lp1z8fLMUDiRKt6H9asXX5y mwReEJKx2KM2ddyTExOoYX8fX3WTZ4KiksaRuoHNm5m97RDRxQMXiZYoArvirxqscQe4 ek9QKwZdE2AEad7O06Ts0FDb+A29zvuuZ3JHqqeqIaFhOBy6G+YhXaifxIgbTf4hQTUx qowA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=QRSlup+Sf9ZOTd05yImL0g72M102fPJvu8LGng9mR80=; b=OHnAynzDLZbYPT/p8eHQOBTT5mZGesaECdTeuz9BtgZ5qd7Qz96FwZECWeynMhaa8t URrZGf778R30ps2xPGCFa+ZHgwociepQVoKbkmBMBDdgAXCySP4cyGjtP0GZLIdcX9bQ jrpsH5DDUjm9LFyAzAY5Z+5Qwq5z6yIwABSvj6HVezL5x4xNzpnUizEkp6RQ70xhtjGc ZGgVy3Sg/hjGn3EUvby81tKNf1AjJl3vMZl0x+9Zbs0DCstgCmdvcCCv97Zp20xfbzEy Q9GSO2/LM2YMR0tl5MNbQkVtrU9mjzj0e/zQK9Oah3v76FydocYnCZW2G34T2a8muQzO NOUQ== X-Gm-Message-State: AOAM530FsZnmBbI0SWKpdORmKQurAX9mEsMvHmgJLnoJL4kpCF16Mnm5 02LLmyby8nk+kQs8bICtlekejgqKtaE8wr80igo= X-Google-Smtp-Source: ABdhPJwbgSSPlNDgnykVJ+mFuVpYM7CnaY1kpqPZFT3xIJDglim/ljoQmssuDa8jI8yKt0uId2eKRpwp0gDhs3uOYBo= X-Received: by 2002:a2e:509:: with SMTP id 9mr15103045ljf.6.1623692442714; Mon, 14 Jun 2021 10:40:42 -0700 (PDT) MIME-Version: 1.0 References: <20201028013651.de5cj2xadgmba5nf@development> <13D8FD63-559A-4737-B7FD-05288D1CEF8B@yandex-team.ru> <20201028233243.ygm6yqlynkqpzekr@development> <43F3DE92-F236-4EA5-B4D6-39BEF6BD849D@yandex-team.ru> <20201029134933.xd4mh2cofuf6tdfz@development> <65C1B4BA-D16F-4939-978B-AC8F370F5A5E@yandex-team.ru> <9b4d17df-b811-8323-16be-3cab913216d1@enterprisedb.com> <35862787-8b4d-a290-789e-6e12dc6527e8@enterprisedb.com> <13F86913-C01B-4983-AE2E-493F5A028280@yandex-team.ru> <6ba7eae2-8b0c-0690-11a5-e921e6586180@darold.net> <3F5E23A3-6250-486E-9CCA-E2A5B6A28B4F@yandex-team.ru> <636f694a-4d69-d6d9-78d1-c88714f3e2e0@darold.net> <3319917a-679e-b07d-b194-473552b72082@darold.net> <1E3DD5BF-A54E-4203-86C5-94C9EFA0E095@yandex-team.ru> <04ae150b-6442-148a-bb23-77082f6fca1d@darold.net> <3768803E-4794-409D-88D2-C3EF7CA1B7FC@yandex-team.ru> <75B95FDD-2BE0-4525-8BDD-FC67C8BCBD53@yandex-team.ru> <88CB7042-6181-4EF4-8B25-61EC5E5725D4@yandex-team.ru> <018EF641-FC9D-4BA9-A458-6842E27E91C2@yandex-team.ru> <27D90FFC-B897-4BC5-B4F5-9047B9886AA3@yandex-team.ru> <15CA416B-3446-462D-902B-4883DD3ADEE6@yandex-team.ru> <494C5E7F-E410-48FA-A93E-F7723D859561@yandex-team.ru> In-Reply-To: <494C5E7F-E410-48FA-A93E-F7723D859561@yandex-team.ru> From: =?UTF-8?B?0JLQsNGB0LjQu9GM0LXQsiDQlNC80LjRgtGA0LjQuQ==?= Date: Mon, 14 Jun 2021 20:40:16 +0300 Message-ID: Subject: Re: MultiXact\SLRU buffers configuration To: Andrey Borodin Cc: Thomas Munro , Gilles Darold , Tomas Vondra , Tomas Vondra , Alexander Korotkov , Anastasia Lubennikova , Daniel Gustafsson , Kyotaro Horiguchi , pgsql-hackers Content-Type: multipart/alternative; boundary="00000000000045c53d05c4bd5b37" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000045c53d05c4bd5b37 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =D0=BF=D0=BD, 14 =D0=B8=D1=8E=D0=BD. 2021 =D0=B3. =D0=B2 15:07, Andrey Boro= din : > PFA patch implementing this idea. > I'm benchmarked v17 patches. Testing was done on a 96-core machine, with PGDATA completely placed in tmpfs. PostgreSQL was built with CFLAGS -O2. for-update PgBench script: \set aid random_zipfian(1, 100, 2) begin; select :aid from pgbench_accounts where aid =3D :aid for update; update pgbench_accounts set abalance =3D abalance + 1 where aid =3D :aid; update pgbench_accounts set abalance =3D abalance * 2 where aid =3D :aid; update pgbench_accounts set abalance =3D abalance - 2 where aid =3D :aid; end; Before each test sample data was filled with "pgbench -i -s 100", testing was performed 3 times for 1 hour each test. The benchmark results are presented with changing multi_xact_members_buffers and multicast_offsets_buffers (1:2 respectively)= : settings tps multixact_members_buffers_64Kb 693.2 multixact_members_buffers_128Kb 691.4 multixact_members_buffers_192Kb 696.3 multixact_members_buffers_256Kb 694.4 multixact_members_buffers_320Kb 692.3 multixact_members_buffers_448Kb 693.7 multixact_members_buffers_512Kb 693.3 vanilla 676.1 Best regards, Dmitry Vasiliev. --00000000000045c53d05c4bd5b37 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=D0=BF=D0=BD, 14 =D0=B8=D1=8E=D0=BD. 2021 =D0=B3. =D0=B2 15:07, Andr= ey Borodin <x4= mmm@yandex-team.ru>:
PFA patch implementing this idea.

I&= #39;m benchmarked v17 patches.
Testing was done on a 96-core machin= e, with PGDATA completely placed in tmpfs.
PostgreSQL was built wit= h CFLAGS -O2.

for-update PgBench script:
\= set aid random_zipfian(1, 100, 2)
begin;
select :aid from pgbench_acc= ounts where aid =3D :aid for update;
update pgbench_accounts set abalanc= e =3D abalance + 1 where aid =3D :aid;
update pgbench_accounts set abala= nce =3D abalance * 2 where aid =3D :aid;
update pgbench_accounts set aba= lance =3D abalance - 2 where aid =3D :aid;
end;

Bef= ore each test sample data was filled with "pgbench -i -s 100", te= sting was performed 3 times for 1 hour each test.
The benchmar= k results are presented with changing multi_xact_members_buffers and multic= ast_offsets_buffers (1:2 respectively):
settings =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0tp= s
multixact_members_buffers_64Kb =C2=A0=C2=A0693.2
multixact_members_= buffers_128Kb =C2=A0691.4
multixact_members_buffers_192Kb =C2=A0696.3multixact_members_buffers_256Kb =C2=A0694.4
multixact_members_buffers_3= 20Kb =C2=A0692.3
multixact_members_buffers_448Kb =C2=A0693.7
multixac= t_members_buffers_512Kb =C2=A0693.3
vanilla =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0676.1

Best regards, Dmitry Vasiliev.
--00000000000045c53d05c4bd5b37--