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 1ow5CC-0007IT-89 for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 17:30:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow5C9-0004Zw-2b for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 17:30:29 +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 1ow5C8-0004Zf-NH for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 17:30:28 +0000 Received: from mout.gmx.net ([212.227.17.20]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow5Bz-0004L6-LI for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 17:30:25 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1668792616; bh=pQFyC9AaPLHGEEOzKWJTt21NUvTx9K2rwVPG4360s+Q=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=PWepiktRIH7nzBOxRf1poLNXNSKdTa3ylR+B7214hyPQ+BBDtHccp9Wg3a2wrBzz/ VpsiJ8I4Q0+yxlKp1aXvbRGrp969VsLVhbhjBk9kkQJvqrVRemqJ4yyDMOtbknVVz6 yZHifX4EH+zHYaPUoHOAnEVOgHlBoyBKvutnv60H+6UzPr0HJEXupW+iLP/NHq85RA ohfAH9yIC24WxQYgqAyAC5U7BPWcY29UFGHkp+2mBhV7EBsczvuIxzxu0hkJcxxkyP W+VKCbY7CXyl6S+2L8mdTk/muUY4JSolqI/rLNB8v2bnSCHvJ5BZBKIx6dR1HERw7c LA7fx5RFxGPMQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [192.168.178.20] ([83.171.173.43]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MHXFx-1oievB2N7p-00DUhV for ; Fri, 18 Nov 2022 18:30:16 +0100 Subject: Re: Calculating average block write time To: pgsql-general@lists.postgresql.org References: <90374fa3-d750-e1ab-2fad-eaa7142f6b5a@gmx.net> <075d05c6d4cdc01cc2467faded785a1ff77b1395.camel@cybertec.at> From: Thomas Kellerer Message-ID: <694aa102-2155-4d67-304e-c29fbd30d865@gmx.net> Date: Fri, 18 Nov 2022 18:30:15 +0100 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666 MIME-Version: 1.0 In-Reply-To: <075d05c6d4cdc01cc2467faded785a1ff77b1395.camel@cybertec.at> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: de-DE Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:4vNsKlDkmFfQ1RDL24/X1DyQnqRLg9TnOmZSaCtRgQUGxQPMSDb zDKTVL3EslQFO3domb1iwxelnAcNJNEFEF+vXfjB6NG3UP4g9sSQau2OyLyp47z9nQWVsUF QaD52rm3D+6KjE2Me8wn0A5SJ9mFt8pCK7f/j0pg+fT+GWVAOuStvaoUu4+oNUY+S8hmRvC MerP3xODAHa5XeiMQfOeA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:x0EI1uzHqLU=;bBRenJUXpYbDQOP4zkJX4ZkjqSq jXHR/hGkECIm97AL+4JiqrPkHM9Vlj/slDcZLyi+rSzIAtXOnMrt6m7KP7NV+NjhsPczcJJdk MLw5sso2j7AQvHgc4k82YgpjPY6oj+lI69lTG2RNBmY9wLCIf0E2a1muwwp8YnHuXFflGHgRY p/6WQXeLwmOWc4/psD1A80lfXH9YcyP0hfmmnkz7dKX0/IDYrtCanoHRtFv7heoNUS+wJO358 j/2HqEhFkMCFntjDwGMV95lY8dfOyFj5UJlf0wO6umLj7pTft0kCB2IbzFQS1Awiq6F/+E3DJ oXe4fyW4FXhyLAd+AXqI8WmZEJxZXBAdM+qLie2mJU/TRdVev4opvAgkk/7oNDFQzZIf/sdAf bJM42SWsseak96Q2uTDeYFSK9Zuk8NJgJudXa0IK/aEW4JrqJxDI2fVVqtN1STyZm7u2qCL6F W8S+biKoC8FVdWSzExhjNXXAiuLaBpcYJQpN7aetzCYBkMTAM8+o20LLbdyRqJ2U3Y87oWdmK pL7+Hmd7sF1ScOSq6kerjjDdtfePqV7w1Kac12uavSp6GGaRz/ycONPgjK41I26goZhicYtoE MduHATQCyfA/ZZI+3elsj6EtHKBdgEyRdDMvdZMuYMB+bZZ5CbGiLHG75nrf3chBd618VRVUL bDv2mI5sCmruEe+5NHOKDDP7z3CwVP1pxXdw02zQWr6Si6bXUj4/sZr/xO9ENiYBZitr+gVhA U2Q482pYvRpBiR3kcCMY3Pyw70rnpJv74TyoL2jNMFMCkSAh5LEBtUPIovNVcZr6BK9PAXRMh oRwFC4cJs4M4/aOr4abFsE5w43QMdKQTWxq0auud1slZiOcPR4rjb7AGfOLVvkmFAWQaASmtv FzmFoNxhsBp99tondBgIef5rARJGxjHJ3QN2NTT0QUYPISGOPMjolLo8sW0PWIfeqYP1ajTvN Mx6+Mw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Laurenz Albe schrieb am 18.11.2022 um 16:51: >>> 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) f= or 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 resul= t of that wouldn't be accurate (too low). > > You cannot get that, because most writes are done by the checkpointer, a= nd > that does not distinguish between databases. Ah, OK. Makes sense. Thanks