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 1pa4F3-00050y-00 for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Mar 2023 00:34:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pa4F1-0006HV-Nr for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Mar 2023 00:34:43 +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 1pa4F1-0006HJ-ED for pgsql-hackers@lists.postgresql.org; Thu, 09 Mar 2023 00:34:43 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pa4Ey-0006sd-Vh for pgsql-hackers@postgresql.org; Thu, 09 Mar 2023 00:34:42 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 6CA555C0178; Wed, 8 Mar 2023 19:34:40 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Wed, 08 Mar 2023 19:34:40 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:cc:content-type: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=1678322080; x=1678408480; bh=Ke 96sbuCPenYAd2G42ePV94CkLZyItCDm2yeckHXNsk=; b=djWejLM+nkuCRCn9Ex PP1FobrS5Etbb4W20hkmRcxsvqCrDOPrDyNCj/jVWAAUO094Dfx6tHs8KqGeWmEg N5aDIxEh1aCDA2mv6JjkZ3BsdAf0HFWFHPYFN8NtMk3dgI8T4Jy+zdOpIOB2qMq7 Bj1i1FH6cKldYBO2dCzlIFq80/S+c8WuMbSseie5GxvVIHaACiR7KwOXeisnREc0 vSvuOhpYCGoehUmP+/V5kMs1czVhSIJ0w5sHap5TMk1c4oAjpma9B+1VN670jyGq Z+36CbQ9XCz8GNpksHqOrcA0zP3Mheh7PP1jZqv3vNGAJo9F/4xhT2981Q7IKN5v bhvg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type: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=1678322080; x=1678408480; bh=Ke96sbuCPenYA d2G42ePV94CkLZyItCDm2yeckHXNsk=; b=RjIVSQbkS+gJ32imd7IbXCL7zxuhk Cf0Qfz1YUtuIvjXc7xvl+T9jvJpHyZN7yixZ17l7baSaoDGYICP4wRkxEbmLp3x6 BnlGPvxT6ZTGo/ZSKCboqpyEtUIS39IuuPr4lPpyXFU0DKbwq5SGVDJA957rriLG kQfeKEZCKWIi3WXbEvDe4Qnn0x3Ho24ei3NfNI+QRms31ye+yzSLEXNnxqnnyvjV Wp8jWCtCEQGJ6MZgn8vJ1IY28kCec0GjVFDgltWmxjpFU7ohYcXFsD9OtU60wlt1 T2MAt/RMMdbuozUiV9gPBr/Nqn3+Stc8OUT+dULB8sgGuK8zGd4hXamWA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvddugedgvdduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkfhggtggujgesthdtredttddtvdenucfhrhhomheptehnughr vghsucfhrhgvuhhnugcuoegrnhgurhgvshesrghnrghrrgiivghlrdguvgeqnecuggftrf grthhtvghrnhepvdfffeevhfetveffgeeiteefhfdtvdffjeevhfeuteegleduheetvedu ieettddunecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomh eprghnughrvghssegrnhgrrhgriigvlhdruggv X-ME-Proxy: Feedback-ID: id4a34324:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 8 Mar 2023 19:34:39 -0500 (EST) Date: Wed, 8 Mar 2023 16:34:38 -0800 From: Andres Freund To: "Drouvot, Bertrand" Cc: Melanie Plageman , Pg Hackers , Maciek Sakrejda , Justin Pryzby , smilingsamay@gmail.com, Kyotaro Horiguchi , Lukas Fittl , Magnus Hagander , Alvaro Herrera Subject: Re: Track IO times in pg_stat_io Message-ID: <20230309003438.rectf7xo7pw5t5cj@awork3.anarazel.de> References: <20230307184751.jdokdy2xmx3xpaua@awork3.anarazel.de> <7b450eef-8717-ce4a-a9c8-ccab24ecd89a@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <7b450eef-8717-ce4a-a9c8-ccab24ecd89a@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On 2023-03-08 12:55:34 +0100, Drouvot, Bertrand wrote: > On 3/7/23 7:47 PM, Andres Freund wrote: > > On 2023-03-07 13:43:28 -0500, Melanie Plageman wrote: > > > > Now I've a second thought: what do you think about resetting the related number > > > > of operations and *_time fields when enabling/disabling track_io_timing? (And mention it in the doc). > > > > > > > > That way it'd prevent bad interpretation (at least as far the time per operation metrics are concerned). > > > > > > > > Thinking that way as we'd loose some (most?) benefits of the new *_time columns > > > > if one can't "trust" their related operations and/or one is not sampling pg_stat_io frequently enough (to discard the samples > > > > where the track_io_timing changes occur). > > > > > > > > But well, resetting the operations could also lead to bad interpretation about the operations... > > > > > > > > Not sure about which approach I like the most yet, what do you think? > > > > > > Oh, this is an interesting idea. I think you are right about the > > > synchronization issues making the statistics untrustworthy and, thus, > > > unuseable. > > > > No, I don't think we can do that. It can be enabled on a per-session basis. > > Oh right. So it's even less clear to me to get how one would make use of those new *_time fields, given that: > > - pg_stat_io is "global" across all sessions. So, even if one session is doing some "testing" and needs to turn track_io_timing on, then it > is even not sure it's only reflecting its own testing (as other sessions may have turned it on too). I think for 17 we should provide access to per-existing-connection pg_stat_io stats, and also provide a database aggregated version. Neither should be particularly hard. > - There is the risk mentioned above of bad interpretations for the "time per operation" metrics. > > - Even if there is frequent enough sampling of it pg_stat_io, one does not know which samples contain track_io_timing changes (at the cluster or session level). You'd just make the same use of them you do with pg_stat_database.blks_read etc today. I don't think it's particularly useful to use the time to calculate "per IO" costs - they can vary *drastically* due to kernel level buffering. The point of having the time available is that it provides information that the number of operations doesn't provide. > > I think we simply shouldn't do anything here. This is a pre-existing issue. > > Oh, never thought about it. You mean like for pg_stat_database.blks_read and pg_stat_database.blk_read_time for example? Yes. Greetings, Andres Freund