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 1nwx9i-0008Mu-Vo for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Jun 2022 02:35:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nwx9h-0006RJ-Fy for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Jun 2022 02:35:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nwx9h-0006RA-6Y for pgsql-hackers@lists.postgresql.org; Fri, 03 Jun 2022 02:35:17 +0000 Received: from wout1-smtp.messagingengine.com ([64.147.123.24]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nwx9e-0002vi-S0 for pgsql-hackers@postgresql.org; Fri, 03 Jun 2022 02:35:16 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.west.internal (Postfix) with ESMTP id 8116932007E8; Thu, 2 Jun 2022 22:35:13 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Thu, 02 Jun 2022 22:35:13 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm1; t=1654223713; x=1654310113; bh=ckdllcsnKG dtzj6V16cAQcwfJfiakLkAl+py1u3+Ab0=; b=OBxYezfQiA0bg/yyWBLbY/pGt1 X/X/AwbYvyV2YMX7jqWlqe39V9wCw2X2h6WNXHs8tkp7suAaRnGX877LEQkWepaA u2HfhNIss+QzBZ2DlXzvif8IqN7UV1oyVEu9jgrzJNQeIlJNv2oF/2MyVGWGSm31 YiueZT8npXrSlQV+XMzJ/9D2mQNUCwpaFpOxCD1j65u3oCo+pSsXfONmjn9v18pG 9mklyjXDoEPygI25s5ka0S6bJdldYT87kvdyP+Xfezf+K5cQ+5rVXaobPG2CTvVN vChPywqhRTPv89zI5t0g5eJcdFNjPN7++FG8N7Kzt03TnCEaHjuFe+jaoXHw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:date:feedback-id :feedback-id:from:from:in-reply-to:in-reply-to:message-id :mime-version:references:reply-to:sender:subject:subject:to:to :x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1654223713; x=1654310113; bh=ckdllcsnKGdtzj6V16cAQcwfJfia kLkAl+py1u3+Ab0=; b=NDupc9+XFmNvDwYFymqye/mVg24gGYke6/dPxSJrGhkX dh5DABIOcwcFN8Jg+PL73tWl+de1X9s3ONNAxBRPxe56+rU+u4tDfQV4u6wdoRHo 38HcLJJnR3sbNCMvWgFGy2R8IDuid56ml17uTYHwliD3hprvYPJeoFwGuN0A5DWD HkfTQI/2F6MgOsmHAVl2nibGSNSvlEl2H4Sf3Qv7LQRvo/AeJRvw0jCzdOgKlKqI 4kadf0S7z3X+vj5A+a+LNlsLzuEhSHR8LRTIUeLBYfWQb/UzYukQ7HY8UD6uUANB I9iLV9UOsLZgU08dXEFZ0CQ10csDKUX0tndZvUkFMw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvfedrleehgdehkecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvuffkfhggtggujgesthdtredttddtvdenucfhrhhomheptehnughrvghs ucfhrhgvuhhnugcuoegrnhgurhgvshesrghnrghrrgiivghlrdguvgeqnecuggftrfgrth htvghrnhepudekhfekleeugeevteehleffffejgeelueduleeffeeutdelffeujeffhfeu ffdunecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprg hnughrvghssegrnhgrrhgriigvlhdruggv X-ME-Proxy: Feedback-ID: id4a34324:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 2 Jun 2022 22:35:12 -0400 (EDT) Date: Thu, 2 Jun 2022 19:35:11 -0700 From: Andres Freund To: Stephen Frost , pgsql-hackers@postgresql.org Subject: Re: pgsql: Use pre-fetching for ANALYZE Message-ID: <20220603023511.ug6nkj2oyho7lovc@alap3.anarazel.de> References: <20220603023016.rlht3r7fmkksw5z7@alap3.anarazel.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20220603023016.rlht3r7fmkksw5z7@alap3.anarazel.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, 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 > > > > 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()). > > I just looked at this as part of debugging a crash / hang in the AIO patch. > > The code does: > > block_accepted = table_scan_analyze_next_block(scan, targblock, vac_strategy); > > #ifdef USE_PREFETCH > > /* > * 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 != InvalidBlockNumber) > PrefetchBuffer(scan->rs_rd, MAIN_FORKNUM, prefetch_targblock); > #endif > > 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? 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 that'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. Greetings, Andres Freund