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 1nwx53-0008Ac-3o for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Jun 2022 02:30:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nwx51-0002zI-Vj for pgsql-hackers@arkaria.postgresql.org; Fri, 03 Jun 2022 02:30:27 +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 1nwx50-0002z9-9p for pgsql-hackers@lists.postgresql.org; Fri, 03 Jun 2022 02:30:27 +0000 Received: from wout1-smtp.messagingengine.com ([64.147.123.24]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nwx4w-0006jj-9H for pgsql-hackers@postgresql.org; Fri, 03 Jun 2022 02:30:25 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id C895C3200912; Thu, 2 Jun 2022 22:30:18 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Thu, 02 Jun 2022 22:30:19 -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=1654223418; x=1654309818; bh=KHBa1BbaSl ahg4+E46SYMy8Y2LgerLiStUr/Zwdq87k=; b=XozYm7MiTVHO3lNUQXLwjg8cuH BKdwlMyhk3zyLybotPAunXen2dfRCUfjMkU5ZGRSt4qRv6DRur6e2lVtJu2GOl98 4jjWM5jxeuTsdEza0NaNSdjskACAV4POAadx+7ln9Tk8TA6PwrFcSL9Q4d6Du4Qn e0Iw0up1gEe9WhTu+1ykPDEOLkXcOy0u5w5UJA5YJ5Wg4nkZOoCViP/f9Bgm25Ab RmWOs2uKq4/pC407K1Ayr8u4N6byP+Pe2MVaj2uunCY+lCMCNgNg+PhO71DFKNDe MWqEWzZqwkEGN4c3TXp89o538EKA2BunecHZRZ1Fx/E0VFkrXg3ulDkbe9PQ== 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=1654223418; x=1654309818; bh=KHBa1BbaSlahg4+E46SYMy8Y2Lge rLiStUr/Zwdq87k=; b=ksXx4kAH/mTgAXY6WfcAqMxxVH9XxWpAaqBtx4HPsbQi 0dsC0w0s3Jwkk5B2rzIjNH6ZaMF75nda8+ae4b1v42oltMcP9YAH9RP5fPemqn74 huoo1CzzamACBn3RZeIzpwVnnvBj/jqa6hFnQx9iy0WlG2FPQbJXHXKuYHHU+zxO H9UceoHJ0VpFECZnaEUv/gfxgKcGEbGB27EW2/hBpjQ+zNRJgK35CbVY7liPLCL+ NAH/6xMG/gGUt/IbspkyUpUziGCB09FQ1n9cgUusmbAkiSEvhriAIHSP5qabxC5d C5pCCqt9aogVgbdhnkXfF05pxGN6Wb+7ZjEOzJi1qA== 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:30:18 -0400 (EDT) Date: Thu, 2 Jun 2022 19:30:16 -0700 From: Andres Freund To: Stephen Frost , pgsql-hackers@postgresql.org Subject: Re: pgsql: Use pre-fetching for ANALYZE Message-ID: <20220603023016.rlht3r7fmkksw5z7@alap3.anarazel.de> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, 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? Greetings, Andres Freund