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 1ow41s-0002ko-F3 for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 16:15:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow40t-0003eO-0S for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 16:14:47 +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 1ow40s-0003eF-Jq for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 16:14:46 +0000 Received: from mail-oa1-x31.google.com ([2001:4860:4864:20::31]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ow40m-0003dt-62 for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 16:14:45 +0000 Received: by mail-oa1-x31.google.com with SMTP id 586e51a60fabf-13bd19c3b68so6453046fac.7 for ; Fri, 18 Nov 2022 08:14:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=MnAcQToU4U92C/Y32OkRt7WA2qo90qEVmLTxykLLTnM=; b=pnFsDWeBeZmFwPA1bHR6YInj/Ccr1eME35s5Ox4vnpgwjEbwEyvZ3XX76+JgI6vI2/ NZ0uM9yriT5RYngCj/98tFjHtahPY2m1OfBPdlbG4W00C9UGTDeqg1yDVdpuHjYCZP97 1CM0Wdt3LO1fzQihWUjY3QPRof1miy+77AUhT8VU7L8BSRmSd1/XMzpkdyFxXQiXW/bu w7ERaL24N7CFHMaQTqMyhndW9JABWG5rah4xLdotiLPm1G7hsKTKplP7ZzWYjxsowndv vHUoUZl3nCEWYrzszvqsocwgYPV/lBvIUwKqPqeFD1KiFpTqZCpnBHkc9H7bNwJLsHSM dRVQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=MnAcQToU4U92C/Y32OkRt7WA2qo90qEVmLTxykLLTnM=; b=qOP2Vg8QWiNLfi4vcnFTRmBQ+Wuvy3ldTp+b64cxzJmudO3v4R7QRePn3YnRqxezzw 1NKlUeSbj1j3Ggpn+wm6jdzZ3yLxPO0iI//rKSo4xBbCxGxGYHi4264OK9RhUnQ1Cp2v iBp63oVQs977+Y8rqN/gSXhoHk3n/eAcE15wUypYvnIBxKpgzqGSAgH9pYhzUhPHcE+N yNEIhQ0kdIRjHz1IZdOV0SMCGjc3e8J84+leyvcmVKuc+runA/h41xfO+C0XDWms3Pxz p66CurpAKTnLy+VjWshHDecU255vZeD5j5zcTL5O3iTCH1Km90ftpNy1fGx3PnVOXdfw KaoA== X-Gm-Message-State: ANoB5plwRpwNMo1Acg9j2LriDMKzWc2CdlXahYPwNGQorVvPS/YuNv95 +I34igFlim9tePGsvXMZVT9zra+Ymo8= X-Google-Smtp-Source: AA0mqf5cda4xsyqH/+yCX4T63LNQZN0Jmjy6LHBXyAK7p83VUWwQIS3i0x+xXMtQ5ajO7QWmhZuk0w== X-Received: by 2002:a05:6870:2804:b0:13a:f2d0:e7b4 with SMTP id gz4-20020a056870280400b0013af2d0e7b4mr7257129oab.262.1668788079031; Fri, 18 Nov 2022 08:14:39 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id v12-20020a4ae6cc000000b0049fbd80e522sm1424712oot.23.2022.11.18.08.14.38 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 18 Nov 2022 08:14:38 -0800 (PST) Message-ID: <7d88dffa-b1ca-062f-8ff4-806f6c3a5578@gmail.com> Date: Fri, 18 Nov 2022 10:14:38 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Calculating average block write time Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <90374fa3-d750-e1ab-2fad-eaa7142f6b5a@gmx.net> <075d05c6d4cdc01cc2467faded785a1ff77b1395.camel@cybertec.at> From: Ron In-Reply-To: <075d05c6d4cdc01cc2467faded785a1ff77b1395.camel@cybertec.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/18/22 09:51, Laurenz Albe wrote: > 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. For a similar reason to why WAL files can't be be "single database" without significant rewrite? -- Angular momentum makes the world go 'round.