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 1ow3el-0000z6-Ll for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 15:51:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow3ei-00087l-Ky for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 15:51:52 +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 1ow3ei-00087c-6r for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 15:51:52 +0000 Received: from mail-ed1-x536.google.com ([2a00:1450:4864:20::536]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ow3ef-0003Rm-86 for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 15:51:51 +0000 Received: by mail-ed1-x536.google.com with SMTP id y24so2692398edi.10 for ; Fri, 18 Nov 2022 07:51:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=aGtR17iAh3773quqmieeb3f1sGy0FK7OsASjM3TJjtE=; b=ayQh9AwWLRYibV5xkahAtRIknMcSWIO/Je3LNdvBlFXfktZPdjQg5FDwkgPJCjnRNU GuH0MeceOU86+dw6qU5Ehdg3LP0OIs3Z85RiRpP7vjm60t6UA9zhEpNrrMH1eLHh9yFe uwXnTr9A0cEpUNJcFppZcU1m1va4GLeCW0V4IN38ydhSSinPewqsIFUuX/de/CBO4IYN eJHgdx1znK8Ee2HFq5ouad4UrPZZ0TpzzEXdB5TF7YTda2Oebm/IGtieqOSmEllM2RCH ezBUpTtmzRMlPAxxPNycCSQCXr14g26VXooLN07KDEDuYOdzkmCxWg9SI6oaCZPtMROb LQrw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=aGtR17iAh3773quqmieeb3f1sGy0FK7OsASjM3TJjtE=; b=n24q1m4EbSyc0bngNQUEbwjFdG0ftbIiTeG1KBVcGZ0hyDBF20orX2wf2bWkWnl58s s4FsBTtfFfWNvVQwpSyvVmBvU0xolMKa4V74BO65rWFXC8r2ja57RFNmuqTeZc+l58St No1XjqfQxx7v978Sv/YCM7sHayiJJsLBXjZvl2F5lm6wIH5RnH0c3pHwH/uUJoWXyadI 6jtL9C9685InenoLXALS+l07Gue9nERwE7H91yMnoCUQge+bEASii3Xu//fUPeh8w4YV 5bgkaS7hGD3FU/SJ93UplwSR3iuWu4sOEacOSkGN98WvgjoB2tlb0xTrp+smO/coPLg5 Ri7w== X-Gm-Message-State: ANoB5plr2BjJUM5pI64ZwYIXWJLbVtl5rujYoUmD+Kr4NYU6obyJRv7e 31gbf5h2DZcIfG/lH+FBOPCBJxBvPV6eHw== X-Google-Smtp-Source: AA0mqf6N2XhnPn0cwKYskbfdVojsWoAnUtQNoSUjyBLesFotuUUiq5ruvxe347oPoPGE4mBnEgQOcA== X-Received: by 2002:a05:6402:43cb:b0:468:f0f5:72c1 with SMTP id p11-20020a05640243cb00b00468f0f572c1mr5592105edc.391.1668786706544; Fri, 18 Nov 2022 07:51:46 -0800 (PST) Received: from localhost.localdomain ([217.116.180.4]) by smtp.gmail.com with ESMTPSA id mb1-20020a170906eb0100b0077016f4c6d4sm1849419ejb.55.2022.11.18.07.51.45 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 18 Nov 2022 07:51:46 -0800 (PST) Message-ID: <075d05c6d4cdc01cc2467faded785a1ff77b1395.camel@cybertec.at> Subject: Re: Calculating average block write time From: Laurenz Albe To: Thomas Kellerer , pgsql-general@lists.postgresql.org Date: Fri, 18 Nov 2022 16:51:45 +0100 In-Reply-To: References: <90374fa3-d750-e1ab-2fad-eaa7142f6b5a@gmx.net> Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2022-11-18 at 11:11 +0100, Thomas Kellerer wrote: > Thomas Kellerer schrieb am 04.11.2022 um 10:19: > > I can easily calculate the average block read time using > > pg_stat_database and divide blk_read_time by blks_read. > > > > While there is a column blk_write_time, it seems that there is no > > cummulative measure for the total number of blocks written. > > > > Any ideas on how to get the equivalent of (blk_read_time / blks_read) for blk_write_time? > > I thought about using tup_updated + tup_inserted + tup_deleted because each tuple change would require at least one block written. > However, a single block write could update multiple tuples so the result of that wouldn't be accurate (too low). You cannot get that, because most writes are done by the checkpointer, and that does not distinguish between databases. Yours, Laurenz Albe -- +43-670-6056265 CYBERTEC PostgreSQL International GmbH Römerstraße 19, 2752 Wöllersdorf Web: https://www.cybertec-postgresql.com