Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYoVZ-002nlE-BD for pgsql-general@arkaria.postgresql.org; Wed, 23 Aug 2023 14:06:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qYoVY-00DMDO-7L for pgsql-general@arkaria.postgresql.org; Wed, 23 Aug 2023 14:06:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYoVX-00DMDA-RZ for pgsql-general@lists.postgresql.org; Wed, 23 Aug 2023 14:06:51 +0000 Received: from mail-lj1-x22e.google.com ([2a00:1450:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qYoVU-000a6o-Ss for pgsql-general@postgresql.org; Wed, 23 Aug 2023 14:06:51 +0000 Received: by mail-lj1-x22e.google.com with SMTP id 38308e7fff4ca-2b703a0453fso94444681fa.3 for ; Wed, 23 Aug 2023 07:06:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692799607; x=1693404407; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=RpNBuOwUf5sDw3NA2vqR/Cd4wLLyMi6VC4S0phWQo6s=; b=cmUh7e5CQDSB8f6WxuM41kPgyWlQMiEcWqDpduumdhvLO08nIGd1O7JpwjsMltlzlq SEPoZbAYIkY/TFAHCapKfcyw7vlqKeNKbypJzxSwif75+42IC6MS824Bop6St2c67g58 srwP3Eyoh4UZU3gQpm1ZKl87hr8nWwGlIATwdIjRtkSeJ2n323S/6/bixln6oBhGEmIQ d+e+yZu3Sjad8DSTmEHgu7FnY4mlFvOiZLaTpQqDEVGUsD0Ant+GY01iTFw0/WovfcBx Lp+Fv25J+MIvn6cwGtftUeD4VaKcMIVMUKb0OWvI42OLUA5rp3+/mEWpRs5hGlgmwn1c pSYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692799607; x=1693404407; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=RpNBuOwUf5sDw3NA2vqR/Cd4wLLyMi6VC4S0phWQo6s=; b=W6zuRbwEvsBoqG9BvdDQWzwx2OAxGPFlhC2NAJL8ap2AUsealb/aMQexdw0PKNtVIf PbBhAU1VEs2GecNaNEsQ5lghocYfi3Zwm6LFJj36zTnfK3vd186PCZ6qyS5FFrBEydgM ZsAy1l+oTj3K6gyUnH93NGZphoLSm6UMOw4Xhdnt3HlJ5WJXy2r/ECNLDVlZA75IRcjP L2StC478AJiCgud0wJj4OBuQKuRzNA2+C85UfieVCFcEjZbzZtHWC5r0TkZYV7cXFSaZ LcfrCPKmVEc0A4Ib1z4dle4tBWJHy8ckfYutG1kFbKsjNyzg+a1Z57UL0yoL2W/Z7ZKn Y6nQ== X-Gm-Message-State: AOJu0Yz53033QTnP8ZpzDCyZeiwzjAzE9V+NGFNeL2ZwBg8QCdGI/rkk KPcySN2D1cUflmhIQEC6DP/6F2PRtbQ= X-Google-Smtp-Source: AGHT+IG6FUOLUHm+6M3xQQHj4YEAggRCPJCNKf2fx8hlCaS1TlGC/dqXcHlOSUUeUBJqm3jQiyxYrg== X-Received: by 2002:a2e:8796:0:b0:2bc:cff6:f506 with SMTP id n22-20020a2e8796000000b002bccff6f506mr4122202lji.0.1692799606444; Wed, 23 Aug 2023 07:06:46 -0700 (PDT) Received: from [192.168.0.254] ([5.134.59.90]) by smtp.gmail.com with ESMTPSA id z7-20020a2e8e87000000b002b9f0fb5f20sm3276198ljk.72.2023.08.23.07.06.45 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 23 Aug 2023 07:06:45 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------RFUIvtOlqY8fa09MdKvIdxZd" Message-ID: <3fafd9d5-ba37-8abf-ba46-ca51b6f706c9@gmail.com> Date: Wed, 23 Aug 2023 18:06:43 +0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.14.0 Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? To: Adrian Klaver , pgsql-general General References: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru> <18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com> <9440ab9c-4ee1-7668-5476-dc28e8086524@aklaver.com> <918f94be-41b9-e684-7679-b6bd819f693c@gmail.com> <9fed2b70-cc36-1239-d5b6-7a81afeafb82@aklaver.com> <329abf6c-2165-f410-717f-c20b3dd0a3d7@gmail.com> <0ebcc8ea-7f5a-d014-d53f-e078622be35d@aklaver.com> Content-Language: en-US From: Rihad In-Reply-To: <0ebcc8ea-7f5a-d014-d53f-e078622be35d@aklaver.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------RFUIvtOlqY8fa09MdKvIdxZd Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 8/21/23 20:50, Adrian Klaver wrote: > On 8/21/23 09:31, Rihad wrote: >> On 8/21/23 20:17, Adrian Klaver wrote: >>> On 8/21/23 09:09, Rihad wrote: >>>> On 8/21/23 20:00, Adrian Klaver wrote: >>> > >> >> Sorry, they are all as per default, commented out in the config. >> >> There are no long running queries, otherwise they wouldn't be >> vacuumed/analyzed in due time after running first manual analyze, >> which updates n_live_tup to match reltuples. >> > > My only remaining suggestion is to closely monitor the Postgres log > and see if provides a clue. > I'm awfully sorry, I read the autovacuum manual carefully, it isn't n_live_tup, but reltuples that is taken into account during the calculation. vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples where the vacuum base threshold is autovacuum_vacuum_threshold , the vacuum scale factor is autovacuum_vacuum_scale_factor , *and the number of tuples is **|pg_class|**.**|reltuples|**.* Your first suggestion was to RTFM. --------------RFUIvtOlqY8fa09MdKvIdxZd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
On 8/21/23 20:50, Adrian Klaver wrote:
On 8/21/23 09:31, Rihad wrote:
On 8/21/23 20:17, Adrian Klaver wrote:
On 8/21/23 09:09, Rihad wrote:
On 8/21/23 20:00, Adrian Klaver wrote:



Sorry, they are all as per default, commented out in the config.

There are no long running queries, otherwise they wouldn't be vacuumed/analyzed in due time after running first manual analyze, which updates n_live_tup to match reltuples.


My only remaining suggestion is to closely monitor the Postgres log and see if provides a clue.

I'm awfully sorry, I read the autovacuum manual carefully, it isn't n_live_tup, but reltuples that is taken into account during the calculation.


vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples.


Your first suggestion was to RTFM.

--------------RFUIvtOlqY8fa09MdKvIdxZd--