public inbox for [email protected]
help / color / mirror / Atom feedFrom: John Naylor <[email protected]>
To: Masahiko Sawada <[email protected]>
Cc: Nathan Bossart <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Matthias van de Meent <[email protected]>
Cc: Yura Sokolov <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: [PoC] Improve dead tuple storage for lazy vacuum
Date: Tue, 28 Feb 2023 00:07:23 +0700
Message-ID: <CAFBsxsHUxmXYy0y4RrhMcNe-R11Bm099Xe-wUdb78pOu0+PT2Q@mail.gmail.com> (raw)
In-Reply-To: <CAD21AoDAKmUG6LFvbzWqEgvXZBgYoap2yL74N8QkVgzg1cW-Tg@mail.gmail.com>
References: <CAFBsxsGW_aMSw0Odpd=T1UGRyfX2CdjdVqCNBMik-wfnGC0vAA@mail.gmail.com>
<CAD21AoAyxDaNrvWwSVfPerQkP034FG0PjYb=7j51QVTiPAtqgQ@mail.gmail.com>
<CAFBsxsFm1eNPv-_Z2-Gy0aFMMiqgvNym3ojwK0scbEaBbW4FHw@mail.gmail.com>
<CAD21AoB9M2Faw7K9CLON41iT6eXoC_YwTcKJ1V4Z33J0o0MySg@mail.gmail.com>
<CAD21AoA4QZ6uTksTzneO7WGXQ-0jnfv=4mGLfgF7V9tdNgV11Q@mail.gmail.com>
<CAFBsxsFV=-ER1GYzXkBnS68imeoAdAS69vEvMsrVpBhYFNopHg@mail.gmail.com>
<CAD21AoD3w76wERs_Lq7_uA6+gTaoOERPji+Yz8Ac6aui4JwvTg@mail.gmail.com>
<CAFBsxsHbvfdMe7qranpiEwaWPwTY97EgM9mXnCEj9gTo6BY=UA@mail.gmail.com>
<CAD21AoCCQ7T1XNCKtnNe+qiqsdgMsmZsugDpbYQdBVCAi5=zgg@mail.gmail.com>
<CAFBsxsGCW=CfgzatJOQ9iuz5UbrMaqPNpP7UZ-ON+2Pgh=zCwg@mail.gmail.com>
<20220921180126.GA453160@nathanxps13>
<CAFBsxsFk7btGg8G_Yjy76be8AJ9PKZH677zK5NKx4uL2EPRtcg@mail.gmail.com>
<CAFBsxsF6OT+4Hkjm3J1+qtE_OuegWO3D-UQ5hO0krTTrRZ=8hw@mail.gmail.com>
<CAD21AoBBF5Cr6Y+scEzoAcPQt6UQqwMaGnCODKvj+=CMUsY-Tw@mail.gmail.com>
<CAFBsxsG2SDMpmON_ovVY6S+oCT-Mn=9giMm4_YquHdkZfn7XBQ@mail.gmail.com>
<CAD21AoDg8S1QP-YR4132oPazPSvhdn-uArDF1AGEQiAYKhHfqA@mail.gmail.com>
<CAFBsxsFEVckVzsBsfgGzGR4Yz=Jp=UxOtjYvTjOz6fOoLXtOig@mail.gmail.com>
<CAFBsxsHDNrNSK4PDfKDArzVDEE2zozgMQZ0NbwMJmOfAC-xhbA@mail.gmail.com>
<CAFBsxsFPs258sP1E9oMT2EN3GJtMB7O8ZKrSDnsA0qdf1nFbcg@mail.gmail.com>
<CAD21AoB+z3+T2EnOBVXok2aeRywrDZDFkwwEaWKZSrFz9s1+mw@mail.gmail.com>
<CAFBsxsEunCjqXo6Q1rjw6fqmduh5DncTDaDw0NhLr2fYBQTj3w@mail.gmail.com>
<CAD21AoAT0p0crCjiY+UwXbkQG2bRNFbe0UF0Ba5EfC_w458JBQ@mail.gmail.com>
<CAFBsxsGV9ssgP-itGa_TVux0v=_B16-28ff+q_G2WLCgBstSLg@mail.gmail.com>
<CAD21AoBp2ErXCc01t0+jwBrNjRA=FkKPz1-yNNFcNC-PRsG5TA@mail.gmail.com>
<CAFBsxsG9NmS=XKtofS5sxddsA64RVLkYnQ4+8pbXkwADGDGjoQ@mail.gmail.com>
<CAD21AoB8oE4WtgXhs+YFwOH3ODAOvEN48afFLYL=vqMk0d25TA@mail.gmail.com>
<CAD21AoCLvVkvf=0Jyu+wV9=GZTcEva+R0q-FzMU9viT9asoevA@mail.gmail.com>
<CAFBsxsF67qpj16kRitgRLJFw=omWsWNwCkaJmNJM9PWvZ4zBLA@mail.gmail.com>
<CAFBsxsHgP5LP9q=Rq_3Q2S6Oyut67z+Vpemux+KseSPXhJF7sg@mail.gmail.com>
<CAFBsxsHdKnkSzyKwEgzL16-FQCEHuQ1jT5T6L=28DWT_sMfgPg@mail.gmail.com>
<CAD21AoDyJWxmPJ8cZj3Y8P1o6EEjjkbmgho4Jb2ABjfhc19Kkw@mail.gmail.com>
<CAFBsxsFSSXCyU1RtGi1EpONi+QRpurrgnwHR8mNK8Wgi=Stgvg@mail.gmail.com>
<CAFBsxsFMuyUd48SXy1VMFvpVBHh6B6300_UfTSiPckKJtcwWMA@mail.gmail.com>
<CAD21AoCe1Ch+y1_nDzTXF5w6VT=nnP33QXM5V9z-PE3-x0f_xw@mail.gmail.com>
<CAFBsxsF8_ce1Vc9HTqjdh4KknPDekjMJTW6JPq99U+GhdzxuqA@mail.gmail.com>
<CAD21AoA_VjvG6LDYvJyuezNPtH12bx0My5Ws88o1Gu4h9qX5GQ@mail.gmail.com>
<CAFBsxsG1Diefx7iUOW6Sgv+CGZSVc88tfQyhmsf93gU_C7N14g@mail.gmail.com>
<CAD21AoD3-bUAUPhZC9qnVY__fhNJSjHkxNxmzKmJwTuFvdDfrQ@mail.gmail.com>
<CAFBsxsEPQkCr50co+EA-+bvQNaVv-Cr99JxT-CMGBBqs0bGftQ@mail.gmail.com>
<CAD21AoALOcuJVgwn+5EFSQccMh_hQAfBtiC5hLDMgpRwOxY4rA@mail.gmail.com>
<CAFBsxsEdQPnvgyJRpVS+cWxQpe-Uq23ou0G0ukfK9-_pCxszZA@mail.gmail.com>
<CAD21AoA-=5rpABAGCu2tJ=YzfTtNgJ5WxkZR+4SS0W54ccN5Ww@mail.gmail.com>
<CAFBsxsF2-xJkUMoQCO0hda_SHKXsz2WLWbp8ep3J_bKFWiozkQ@mail.gmail.com>
<CAD21AoDqvkT7LjnjAeJ1urfRsmf0oeJkj5_=qKomFw1sYQ45mQ@mail.gmail.com>
<CAFBsxsGYGuEkKgF6h0BegtQoAkJ+B2enOMPB4u5T7RdVFAvQEQ@mail.gmail.com>
<CAD21AoBjmj0fDyTJCYvs3W2GQb9bUT5pwQLOU_=BUTOWhj9ToQ@mail.gmail.com>
<CAFBsxsG0wn9h7JJcMR03fdTmvwUaU0c9ps3_Bro98ndN7ysO=g@mail.gmail.com>
<CAD21AoAtR3NN2E1u5sv5RdZapDR07AmLQS2GseFMyvYeWDoBRg@mail.gmail.com>
<CAD21AoCSkEDYAqW3+JtSQQY+R2tDCrXA=PgNAUXUdUQ5gBOp0Q@mail.gmail.com>
<CAFBsxsHbBm_M22gLBO+AZT4mfMq3L_oX3wdKZxjeNnT7fHsYMQ@mail.gmail.com>
<CAD21AoDePt8x14Vrg+xLFd2akRAKHS_8j2uVmyoRVMYOze-VSw@mail.gmail.com>
<CAFBsxsFps1MReb3HQYEGc=M=1NXRjSu3TSaEVEXkVrCR3g-hxg@mail.gmail.com>
<CAD21AoDqO7v5cvFUyWD7_pwceJ1XfhA81FVLTspKn0sFxtqNPA@mail.gmail.com>
<CAFBsxsESfmZpWwk3XxWkpdg_DjawH2vizKW91fyawQ+wVFkOiQ@mail.gmail.com>
<CAD21AoD-sOZY5KFTQw1PQDvRExFGhda4SrW8C1tFr7jgJ_6ByQ@mail.gmail.com>
<CAFBsxsGCv8+v6d6Fgi+K0Re+CXsW_m9uTimZL5Kt7asUJKgz4A@mail.gmail.com>
<CAD21AoAFiw2DS07nPhiS14eVXpnF-bBmjvab4y+Ct8igU2z_8A@mail.gmail.com>
<CAD21AoDCuTO6_wKF9mPm2hb9Fo-dc9kwbec6iXpwptsYaoFdyA@mail.gmail.com>
<CAFBsxsGkhZ8YFdh0eg6ss3daGtekwjm2ZggS1=fewfkdStHeAA@mail.gmail.com>
<CAD21AoDYZjDm+gixXptOjWqZ7N7hkrh4r7YUPuqPJDoGR8r_5g@mail.gmail.com>
<CAFBsxsHLqMNnMFK7uirb-oAtJOtRhDUtz+ewiHsBLwdre6bjXQ@mail.gmail.com>
<CAFBsxsHrvTPUK=C1=xweJjGujja4Xjfgva3C8jnW3Shz6RBnFg@mail.gmail.com>
<CAD21AoDAKmUG6LFvbzWqEgvXZBgYoap2yL74N8QkVgzg1cW-Tg@mail.gmail.com>
On Fri, Feb 24, 2023 at 3:41 PM Masahiko Sawada <[email protected]>
wrote:
>
> In v29 vacuum took twice as long (286 s vs. 573 s)?
Not sure what happened there, and clearly I was looking at the wrong number
:/
I scripted the test for reproducibility and ran it three times. Also
included some variations (attached):
UUID times look comparable here, so no speedup or regression:
master:
system usage: CPU: user: 216.05 s, system: 35.81 s, elapsed: 634.22 s
system usage: CPU: user: 173.71 s, system: 31.24 s, elapsed: 599.04 s
system usage: CPU: user: 171.16 s, system: 30.21 s, elapsed: 583.21 s
v29:
system usage: CPU: user: 93.47 s, system: 40.92 s, elapsed: 594.10 s
system usage: CPU: user: 99.58 s, system: 44.73 s, elapsed: 606.80 s
system usage: CPU: user: 96.29 s, system: 42.74 s, elapsed: 600.10 s
Then, I tried sequential integers, which is a much more favorable access
pattern in general, and the new tid storage shows substantial improvement:
master:
system usage: CPU: user: 100.39 s, system: 7.79 s, elapsed: 121.57 s
system usage: CPU: user: 104.90 s, system: 8.81 s, elapsed: 124.24 s
system usage: CPU: user: 95.04 s, system: 7.55 s, elapsed: 116.44 s
v29:
system usage: CPU: user: 24.57 s, system: 8.53 s, elapsed: 61.07 s
system usage: CPU: user: 23.18 s, system: 8.25 s, elapsed: 58.99 s
system usage: CPU: user: 23.20 s, system: 8.98 s, elapsed: 66.86 s
That's fast enough that I thought an improvement would show up even with
standard WAL logging (no separate attachment, since it's a trivial change).
Seems a bit faster:
master:
system usage: CPU: user: 152.27 s, system: 11.76 s, elapsed: 216.86 s
system usage: CPU: user: 137.25 s, system: 11.07 s, elapsed: 213.62 s
system usage: CPU: user: 149.48 s, system: 12.15 s, elapsed: 220.96 s
v29:
system usage: CPU: user: 40.88 s, system: 15.99 s, elapsed: 170.98 s
system usage: CPU: user: 41.33 s, system: 15.45 s, elapsed: 166.75 s
system usage: CPU: user: 41.51 s, system: 18.20 s, elapsed: 203.94 s
There is more we could test here, but I feel better about these numbers.
In the next few days, I'll resume style review and list the remaining
issues we need to address.
--
John Naylor
EDB: http://www.enterprisedb.com
Attachments:
[application/sql] vacuum-test-lookup-int.sql (1.2K, ../CAFBsxsHUxmXYy0y4RrhMcNe-R11Bm099Xe-wUdb78pOu0+PT2Q@mail.gmail.com/3-vacuum-test-lookup-int.sql)
download
[application/sql] vacuum-test-lookup-uuid.sql (1.0K, ../CAFBsxsHUxmXYy0y4RrhMcNe-R11Bm099Xe-wUdb78pOu0+PT2Q@mail.gmail.com/4-vacuum-test-lookup-uuid.sql)
download
view thread (59+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PoC] Improve dead tuple storage for lazy vacuum
In-Reply-To: <CAFBsxsHUxmXYy0y4RrhMcNe-R11Bm099Xe-wUdb78pOu0+PT2Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox