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 1nyHpl-0002v1-Nv for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Jun 2022 18:52:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nyHpk-0008Ag-BF for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Jun 2022 18:52:12 +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 1nyHpk-0008AX-1W for pgsql-hackers@lists.postgresql.org; Mon, 06 Jun 2022 18:52:12 +0000 Received: from tamriel.snowman.net ([70.109.60.50]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nyHpd-0001PI-FY for pgsql-hackers@postgresql.org; Mon, 06 Jun 2022 18:52:11 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 7C60E5F79F; Mon, 6 Jun 2022 14:52:03 -0400 (EDT) Date: Mon, 6 Jun 2022 14:52:03 -0400 From: Stephen Frost To: Andres Freund Cc: pgsql-hackers@postgresql.org Subject: Re: pgsql: Use pre-fetching for ANALYZE Message-ID: <20220606185203.GZ9030@tamriel.snowman.net> References: <20220603023016.rlht3r7fmkksw5z7@alap3.anarazel.de> <20220603023511.ug6nkj2oyho7lovc@alap3.anarazel.de> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="0k2ZE0YWsaeoufzj" Content-Disposition: inline In-Reply-To: <20220603023511.ug6nkj2oyho7lovc@alap3.anarazel.de> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0k2ZE0YWsaeoufzj Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Andres Freund (andres@anarazel.de) wrote: > On 2022-06-02 19:30:16 -0700, Andres Freund wrote: > > On 2021-03-16 18:48:08 +0000, Stephen Frost wrote: > > > Use pre-fetching for ANALYZE > > >=20 > > > When we have posix_fadvise() available, we can improve the performance > > > of an ANALYZE by quite a bit by using it to inform the kernel of the > > > blocks that we're going to be asking for. Similar to bitmap index > > > scans, the number of buffers pre-fetched is based off of the > > > maintenance_io_concurrency setting (for the particular tablespace or, > > > if not set, globally, via get_tablespace_maintenance_io_concurrency()= ). > >=20 > > I just looked at this as part of debugging a crash / hang in the AIO pa= tch. > >=20 > > The code does: > >=20 > > block_accepted =3D table_scan_analyze_next_block(scan, targblock, vac= _strategy); > >=20 > > #ifdef USE_PREFETCH > >=20 > > /* > > * When pre-fetching, after we get a block, tell the kernel about the > > * next one we will want, if there's any left. > > * > > * We want to do this even if the table_scan_analyze_next_block() call > > * above decides against analyzing the block it picked. > > */ > > if (prefetch_maximum && prefetch_targblock !=3D InvalidBlockNumber) > > PrefetchBuffer(scan->rs_rd, MAIN_FORKNUM, prefetch_targblock); > > #endif > >=20 > > I.e. we lock a buffer and *then* we prefetch another buffer. That seems= like a > > quite bad idea to me. Why are we doing IO while holding a content lock,= if we > > can avoid it? At the end, we're doing a posix_fadvise() which is a kernel call but hopefully wouldn't do actual IO when we call it. Still, agreed that it'd be better to do that without holding locks and no objection to making such a change. > It also seems decidedly not great from a layering POV to do the IO in > analyze.c. There's no guarantee that the tableam maps blocks in a way tha= t's > compatible with PrefetchBuffer(). Yes, the bitmap heap scan code does > something similar, but a) that is opt in by the AM, b) there's a comment > saying it's quite crufty and should be fixed. Certainly open to suggestions. Are you thinking it'd make sense to add a 'prefetch_block' method to TableAmRoutine? Or did you have another thought? Thanks! Stephen --0k2ZE0YWsaeoufzj Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJinkzTAAoJEO1sijiDR2RVPhMP/AwrDbbkYP867yQSPhmaWJ/G xOoYwI4TwH5CoD1ZPAIKss9u7WJUcgJVqCRO3KVG+XQMb/eoPbhgZFvLZ2diRMb9 QGlCgYZbkye8aaHCwVqXARW6RCjIsfbNZ3KZtGIF9dfqTU76ee4kttBRL8ZCB0ya Ao42ussvZMbD0Lkunui9Vp+d0giBYHYJzvvKmfjZVuXECQ4bpgdCrn9wY3NkGv2w ukyh7ONpc5duVjbXGUBTqLpcTTnSJP5otUzyAIPzUib6j6x12cYNFtq8iVzkrWUe 09T3y+9W8fDenylPcybXSD3IBGvwvrQrJbmetOue//PTUKEP4c7BOXBW29qt7Yx4 lOhOZ0gHXZrbIXS7IuPHZ+aHLOmNNu2tLu4r1RU5gMmpRHPVlekoUNUA1F55nLxV dc/m2NE3KA5ypdtilb17cTU8nRAhr8DENF04F4Sk6giiBzknvSDf1F8HUd6rFtct 7fUKHP/OM+OCkpTzm9LawZ+Dm6bcMN59PVQmg3DsBHE5MckNYmQUmnZX+1qjhHhi BWtReBLLnlBkEGlqqJob8Q5KhNE0YvA75e/kAksM6apBhvC53PHDDoRoLAeX7/Sf nM4ScKj/bow/hGlFUUyLznkO42N/Pr6y+mgEqXo2Z40LAMalFMj/WSRsHf8+6i6a YC33cos9dx6mlffP6PKU =o8w1 -----END PGP SIGNATURE----- --0k2ZE0YWsaeoufzj--