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 1pSgQr-00041f-PA for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 15:44:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSgQq-0003eG-9z for pgsql-general@arkaria.postgresql.org; Thu, 16 Feb 2023 15:44:24 +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 1pSgQo-0003e6-J4 for pgsql-general@lists.postgresql.org; Thu, 16 Feb 2023 15:44:23 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSgQk-0002Lb-Dw for pgsql-general@postgresql.org; Thu, 16 Feb 2023 15:44:22 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 6247E5C0239; Thu, 16 Feb 2023 10:44:13 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Thu, 16 Feb 2023 10:44:13 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding: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=fm3; t=1676562253; x= 1676648653; bh=v0a3qpBsZBfd9849fcPY0qniC72nncr8SlMEeZb5Fwg=; b=T 9DDJdoZFWbbKm9OBrQUgi7uct3nEuy/vFepEw4OpdjhrHKq6FJeduNH/f2648Jno qWBL+YJM8Rp7WRYSFCo6+ssIa/zSZ/a/VpVud4JjbcRT9TWy3ST7GjKvN/EsKlSU aw4/BjJp2MhyfYZTltHF2sATEdL2LtO6hn39MYr1RzX1Bwo5AR5+qNw61UopA3uT vBRslChQzLBhW/8j2PH+EH2ZK+Ioebk4mSdWNG6D8ZHkBXoxaUgaKSMkCixtDzEi zedVFKy1DXO/SefiWXrfUeYoL2D9FNE8pAHZ1r9V3D5DdM29fAF5LmDD3vUV5wWT R0FsIxk083UHZqNDPyGKQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding: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=1676562253; x=1676648653; bh=v 0a3qpBsZBfd9849fcPY0qniC72nncr8SlMEeZb5Fwg=; b=ZO2tQmpxgMTluw0uJ LT/BGE1v/ZhSz6ln8mLz63CLmXTOjmXpPQT4TllFguDw/Lm2qoLjxUNMMg6i/41V uwsESlwKTY7SsWqrZzBOVdS7SlKJncb3mGxD8DbHJniJ5zeRkuy89Y4N775oScNc NdkjXLILXSmeIqZbQwY3A0NKlncPxlD6dJo5eRaN+pdvk/GNLBBeOXm7nekIvT9t /dlmHGwfe0VBw+2bjk4BzKhNDDCNZA+Xb/HmVO//+EFV4/GjO9OyIZsm2Th6idnP Pin8g5NGD/dLO2y8EXaVZTjYijOyKBkS92yKw1SMXwvrT7s56cJYQhgtZdvjBtzw eqbpA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudeijedgjeekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeelkeegjefhkeetheefudevteehiedvudehkeduleet fedvffeuhedtvdegtdeffeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 16 Feb 2023 10:44:12 -0500 (EST) Message-ID: <9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com> Date: Thu, 16 Feb 2023 07:44:11 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.7.2 Subject: Re: Automatic aggressive vacuum on almost frozen table takes too long To: Mikhail Balayan , pgsql-general@postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/15/23 22:57, Mikhail Balayan wrote: > Hello, > > I have a big table in the actively working system, in which nothing is > written for a long time, and nothing is read from it. Table size is 15GB > (data only), indexes 150GB. > Since the table does not change, after a while it crosses the > autovacuum_freeze_max_age and an aggressive vacuum is triggered. And it > would be OK, but vacuuming of the table takes a long time, despite the > fact that exactly the same scan was made a few days before and almost > all pages are marked as frozen, which is confirmed by data from the log: >         automatic aggressive vacuum of table > "appdbname.appschemaname.applications": index scans: 1 >             pages: 0 removed, 2013128 remain, 0 skipped due to pins, > 2008230 skipped frozen >             tuples: 2120 removed, 32616340 remain, 0 are dead but not > yet removable, oldest xmin: 4111875427 >             buffer usage: 2005318781 hits, 19536511 misses, 23903 dirtied >             avg read rate: 4.621 MB/s, avg write rate: 0.006 MB/s >             system usage: CPU: user: 26398.27 s, system: 335.27 s, > elapsed: 33029.00 s > > That is, if I understand it correctly, it says that there were (and > actually are) 2013128 pages of which 2008230 were skipped, which leaves > 4898 blocks to be scanned. I.e. it seems that the allocated 1GB > (autovacuum_work_mem) should be enough to handle that amount of blocks > and to avoid multiple scans of the indexes. > But, based on buffer usage, one can see that a huge amount of data is > read, greatly exceeding not only the number of remaining unfrozen > blocks, but also the size of the table and indexes taken together: 2 > billion blocks, more than 15TB. > > Is this a bug in Postgresql or am I interpreting the log data wrong? Not sure if this applies but from: https://www.postgresql.org/docs/11/release-11-18.html Release 11.18 Avoid long-term memory leakage in the autovacuum launcher process (Reid Thompson) The lack of field reports suggests that this problem is only latent in pre-v15 branches; but it's not very clear why, so back-patch the fix anyway. > > Just in case, I'm using Postgresql version: 11.11. Besides the above you are missing 8 releases of other fixes. > autovacuum_vacuum_cost_delay: 2ms > autovacuum_vacuum_cost_limit: 8000 > > Thank you. > > BR, > Mikhael -- Adrian Klaver adrian.klaver@aklaver.com