Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jENNI-0008F2-K6 for pgsql-admin@arkaria.postgresql.org; Wed, 18 Mar 2020 01:20:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jENNH-0004vB-90 for pgsql-admin@arkaria.postgresql.org; Wed, 18 Mar 2020 01:19:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jENNG-0004v3-VI for pgsql-admin@lists.postgresql.org; Wed, 18 Mar 2020 01:19:59 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jENND-0003vk-Pk for pgsql-admin@lists.postgresql.org; Wed, 18 Mar 2020 01:19:58 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 34D2C5C02FC; Tue, 17 Mar 2020 21:19:54 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Tue, 17 Mar 2020 21:19:54 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm1; bh=Wp9eFowXVMe6ufiliF0Y9vKb0Es z8D8vM/4G2gsu86w=; b=amEDEpfRiXuVm7gqJWoT6zo1rSK56jAleQEBYBaf60c zghVC/thfN2SmGAJMlbx5LloLcYjeaI+DtzLTWI5arOA4IqrrMLd6jTFcG1G5ZNe JqUotsdkp1cMsOYi9APv6S0eO4Ng2NxWk34yZf48kMySLmo09i2+qhyrGVa3j+ur eI0Gfx4rX6vhFF2x1pC9waSiczAzEdqpptzkdWCq2MzUpVgvfQpgUDrBFKjbscJM xCR/0BTMOkGdtwTsJ3cQDbKAHdxIii64Ruu0USDkm2qsUtjjgcvVUkMLJICgTJLI EOlH7M3bvqZCtEaz9zmOlvdiLI7pU5Err8qmQSLKUKg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; bh=Wp9eFo wXVMe6ufiliF0Y9vKb0Esz8D8vM/4G2gsu86w=; b=wu6X9L1g1A7MwJBNaryZGW B2PBryAKYd8houu9lWztFa5/lxOeY5NqoY2ynZf3aHIBrPVcPmJ7xRU4wRRo2pwF rZia6Lsmh8orr8dg6ewAL6G1jbsIymDJdBfpHa5mYspeNz8zkQhRfRiZMPvVIMUY IfAPKNC0jKkWuXcSG6eVcF8b8BXWpfrTwQub01CBtAYrqH5MaEOsDfrmTiggAZsy E3Mb9ba5PHEihhZJc6inDGxiYSkWxL4tSs5IiDqCW9bYdlO+uryshJlquLoPf04d AkKn7lmkUxlRxTxWVlVZSuS967pzw+1eAGJ7sl1+NvtnIFN1r56zT8F1bhmSi32w == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedugedrudefiedgfedtucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvffukfhfgggtuggjsehttdertddttddvnecuhfhrohhmpeetnhgurhgv shcuhfhrvghunhguuceorghnughrvghssegrnhgrrhgriigvlhdruggvqeenucfkphepie ejrdduiedtrddvudejrddvhedtnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghm pehmrghilhhfrhhomheprghnughrvghssegrnhgrrhgriigvlhdruggv X-ME-Proxy: Received: from intern.anarazel.de (c-67-160-217-250.hsd1.ca.comcast.net [67.160.217.250]) by mail.messagingengine.com (Postfix) with ESMTPA id 85EFB30618B7; Tue, 17 Mar 2020 21:19:53 -0400 (EDT) Date: Tue, 17 Mar 2020 18:19:52 -0700 From: Andres Freund To: Justin King Cc: pgsql-admin@lists.postgresql.org Subject: Re: PG12 autovac issues Message-ID: <20200318011952.caitfhlslpq6zype@alap3.anarazel.de> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2020-03-17 17:18:57 -0500, Justin King wrote: > As you can see in this table, there are only ~80K rows, but billions > of updates. What we have observed is that the frozenxid reaches the > 200M mark fairly quickly because of the amount of activity. And each of those updates is in a separate transaction? Is that required? I.e. any chance to perform multiple of those updates in one transaction? Have you considered just increasing the vacuum limit? It's mostly there because it can increase space usage a bit, but given today's systems its not a usually a problem unless you have hundreds of postgres instances on a single system. It's pretty common to just increase that to 1.8 billion or so.