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 1k0Aem-0006wY-Ue for pgsql-admin@arkaria.postgresql.org; Mon, 27 Jul 2020 21:27:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k0Ael-00024Y-Sb for pgsql-admin@arkaria.postgresql.org; Mon, 27 Jul 2020 21:27:35 +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 1k0Ael-00024R-HN for pgsql-admin@lists.postgresql.org; Mon, 27 Jul 2020 21:27:35 +0000 Received: from mail-oi1-x22d.google.com ([2607:f8b0:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1k0Aej-0000yo-IF for pgsql-admin@lists.postgresql.org; Mon, 27 Jul 2020 21:27:35 +0000 Received: by mail-oi1-x22d.google.com with SMTP id t4so15601015oij.9 for ; Mon, 27 Jul 2020 14:27:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=VFf22942JGCfgDInRDlRD7yuG4/l0oJsQHt1RIAbV00=; b=csGW/QHdHZ/MnCD5E1OmqCEQCGmUMbT9Z/yvunrU+xRZv0jW0+n1Hf+BII5WMK9r75 Q26wA9S5PMVSjM7J+JFJs4j4Okhk9OTqlS4yIir3oq670aQkQIhkGPi0S5EmhX+dq4k/ cj/+cOMRoSPfmeRxVSZ/Sl8b61g8OGhHPbG+WoMLUfyz4wJ363ZTfjee2zfg7nrAJsaj zN1oieDWrHyvXLG+MYUP550l7nooSGCITK8QBt7fUWnnYx5HjOrwm+pwR6rWVX/a3Rgz hF6aZ6JrQTcgN77TNH7Tju6srkUeoakCtiHyvgkZ59Hl4Pvlf1HTWmO39HUktxZU9tix 290g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=VFf22942JGCfgDInRDlRD7yuG4/l0oJsQHt1RIAbV00=; b=pzWV6PumRkkISXshmTg0hHcUFCzwU9ItY13tgHgrSmnxL+gwtEKK2l/m7qENuDPRN2 IqEzKsE7qcYeI7Lv7dVrF7/2ySpEMtabZGHWV18vYyf/Y0741kWO3mPu9Ydwgc5mosot umi8miWN0jseGxA6i+ryifXLw83+pvgby8bmQPHlN8nmFmHb/P0ZAplZpUGT4O5QT5MO oXxM9PCF2xOAT2p97btqMD5XvBDcmHenMMcOSXfkwa3qRsCd+P/A+Kr1MUJ21cDmOHCN UMujky8TspHYVDdFeiwEI/thZUQ7RR+OjffFS1n22VzXwL3s552XDG62asEO2T0HoeaH ViMg== X-Gm-Message-State: AOAM533hP3AGNi7PunPBJJ8X5KKQpCPFzJl+pHGvDOnv6IaicARZmCJv /3hicYhAvx09B4p8de3+9PwUNIU+/Hg= X-Google-Smtp-Source: ABdhPJziXeoBnZNz7Lm3yb5aULvZjZDiQs5ApUJs1Mp8l7rL3cJGHsUlS6vtOOOzpjwnsZMXNgE9jg== X-Received: by 2002:aca:6708:: with SMTP id z8mr1071206oix.96.1595885251478; Mon, 27 Jul 2020 14:27:31 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id l78sm2294686oih.29.2020.07.27.14.27.30 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 27 Jul 2020 14:27:30 -0700 (PDT) Subject: Re: How can i be certain autovacuum is causing reuse if table still grows To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: Date: Mon, 27 Jul 2020 16:27:30 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------1FF7188C1035562C39B3F4D4" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------1FF7188C1035562C39B3F4D4 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Another suggestion is to partition the table by a chronological key, so that only a fraction of the table needs to be vacuumed. On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote: > Thank you, David. > So can i assume that autovacuum only release dead tuples when it ends its > execution? > > Autovacuum is not finishing and is running for days. > We did not find a parameter to limit autovacuum executing time. > Do you have any recomendation? > > Thank you. > Sidney. > ---------------------------------------------------------------------------- > *De:* David G. Johnston > *Enviado:* segunda-feira, 27 de julho de 2020 17:17 > *Para:* Sidney Aloisio Ferreira Pryor > *Cc:* pgsql-admin@lists.postgresql.org > *Assunto:* Re: How can i be certain autovacuum is causing reuse if table > still grows > On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor > > wrote: > > Hi, we have a 9.6.5 postgres > > Autovacuum is set with default parameters and is running on this table > for days without naping or stopping. > >  3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM > ANALYZE public.flip_pagina_edicao (to prevent wraparound) > (1 rows) > > > Its probably best avoid drawing conclusions here until the anti-wraparound > vacuum has completed and you’ve updated to the latest minor version of > PostgreSQL. > > David J. > -- Angular momentum makes the world go 'round. --------------1FF7188C1035562C39B3F4D4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Another suggestion is to partition the table by a chronological key, so that only a fraction of the table needs to be vacuumed.

On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you, David.
So can i assume that autovacuum only release dead tuples when it ends its execution?

Autovacuum is not finishing and is running for days.
We did not find a parameter to limit autovacuum executing time.
Do you have any recomendation?

Thank you.
Sidney.
De: David G. Johnston <david.g.johnston@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
 
On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br> wrote:
Hi, we have a 9.6.5 postgres
 
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.

 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)


Its probably best avoid drawing conclusions here until the anti-wraparound vacuum has completed and you’ve updated to the latest minor version of PostgreSQL.

David J.


--
Angular momentum makes the world go 'round.
--------------1FF7188C1035562C39B3F4D4--