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 1ow4AR-0003Qp-NW for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 16:24:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow4AP-0000rr-9b for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 16:24:37 +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 1ow4AO-0000ra-VM for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 16:24:36 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow4AM-0008J4-QM for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 16:24:36 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 2AIGOV7n2234522; Fri, 18 Nov 2022 11:24:31 -0500 From: Tom Lane To: Ron cc: pgsql-general@lists.postgresql.org Subject: Re: Calculating average block write time In-reply-to: <7d88dffa-b1ca-062f-8ff4-806f6c3a5578@gmail.com> References: <90374fa3-d750-e1ab-2fad-eaa7142f6b5a@gmx.net> <075d05c6d4cdc01cc2467faded785a1ff77b1395.camel@cybertec.at> <7d88dffa-b1ca-062f-8ff4-806f6c3a5578@gmail.com> Comments: In-reply-to Ron message dated "Fri, 18 Nov 2022 10:14:38 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2234520.1668788671.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 18 Nov 2022 11:24:31 -0500 Message-ID: <2234521.1668788671@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ron writes: > On 11/18/22 09:51, Laurenz Albe wrote: >> On Fri, 2022-11-18 at 11:11 +0100, Thomas Kellerer wrote: >>> Any ideas on how to get the equivalent of (blk_read_time / blks_read) = for blk_write_time? >> You cannot get that, because most writes are done by the checkpointer, = and >> that does not distinguish between databases. > For a similar reason to why WAL files can't be be "single database" with= out = > significant rewrite? Right, because we define a checkpoint as cluster-wide. Another problem with trying to measure this is that we can only know how long it took to shove the block out to kernel space. It's highly unlikely that the physical write has happened by the time write() returns, and we don't know (nor care that much) exactly when it does happen. So it's not clear that the measurement would have much real-world significance if we did calculate it. You could argue that read() is also squishy since it might be satisfied from kernel buffers rather than actually getting bits off the disk. However, there is some physical reality there: before read() we did not have the data, and afterwards we do. regards, tom lane