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 1k0GMO-0002NR-F4 for pgsql-admin@arkaria.postgresql.org; Tue, 28 Jul 2020 03:33:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k0GMN-0003sY-Bx for pgsql-admin@arkaria.postgresql.org; Tue, 28 Jul 2020 03:32:59 +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 1k0GMN-0003sR-0q for pgsql-admin@lists.postgresql.org; Tue, 28 Jul 2020 03:32:59 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1k0GMG-0004I4-6G for pgsql-admin@lists.postgresql.org; Tue, 28 Jul 2020 03:32:58 +0000 Received: by mail-ot1-x32a.google.com with SMTP id 93so3747877otx.2 for ; Mon, 27 Jul 2020 20:32:52 -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=2W5TqD8JOjWhuCHh7Xx+U4OwtazDpm01VTybiyFqLKk=; b=aFw2jFrgJMQI5/Cb31OdaeX0v/BArIEdja+qh4RgHh265Gw/CSfC7eDxSBO9Zucuta UqcgaySUudfj9xGyfbYJArHzs5QGIb6anSusmSBqD6Rbek6QFwLYOCvSW+g9TyPHsoye U+/HOrqDjno6MnhkJTO3tvGasCSryy1Hg6AmRHPqLjbgQa50WeXLfMVkAe8t81OczdwX axaflSMbBR/mRzeUY3JXOkdz0tRcJp4ws1Z7LpIrK8TwL5qhUQWcfUaclGkuI2jstsva Sj3mhGYZ25JBv5MQPQTjPbOQfM7uvtg5drgIx7lEtHe2MIJ+n78atGeYuY2Bo9GDH1j0 YZnA== 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=2W5TqD8JOjWhuCHh7Xx+U4OwtazDpm01VTybiyFqLKk=; b=MXk7qjHnrVOTxdV7NrAT60AmtNSvzFGE6nAhujrL1VhAtdsyDzApATJhIyq3jNGDE3 ihRMbPpAlr8k0n9OWn6PeT7waNDXwuW0x7qpWCTlTW2wvdoxJEd7pKk7Ycznn/By6VqJ CM9lgJqvSt2nx4o/e1lZDU9msUgU94FMcfBhcFyh0xSfVqy/t0wv0m/t+RxTNPxtZ6H6 /dlqytl6JYTs8weAy3kjqneR43QNEZHv9Ciztq803puquDV+nufipii7c4DMF7mk2Aa/ h/ltMpVL6tRnwhlGR59HlK9Uj6tpDqudxAgsQurJeKJ8rMFL+bdu0NlsKHadUNZHS3NX I8yQ== X-Gm-Message-State: AOAM530DJ2aE6KBOjCdOnyptaLZTpOk/95Y7ovXh+bMac4DntawtlkTB TMOn/6ohq1OorWB0S3Y3LhNOD5wBxM0= X-Google-Smtp-Source: ABdhPJxPxHckGLAski4lIYG1IsAzi93lVTMFMq18gckeEzIYkvrBzdocD2ihrS9gOLDrbut9s10ufQ== X-Received: by 2002:a9d:7545:: with SMTP id b5mr22420320otl.19.1595907170148; Mon, 27 Jul 2020 20:32:50 -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 y26sm2787187ooq.3.2020.07.27.20.32.48 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 27 Jul 2020 20:32:49 -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 22:32:48 -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="------------570F19E0AC962A953C591B70" 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. --------------570F19E0AC962A953C591B70 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Any key that "increases linearly over time" is a chronological key; sequences are a perfect example. On 7/27/20 4:38 PM, Sidney Aloisio Ferreira Pryor wrote: > > Thank you Ron but this table has no date column. > > It has one line for each page of each scanned document. > > ---------------------------------------------------------------------------- > *De:* Ron > *Enviado:* segunda-feira, 27 de julho de 2020 18:27 > *Para:* pgsql-admin@lists.postgresql.org > *Assunto:* Re: How can i be certain autovacuum is causing reuse if table > still grows > 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. -- Angular momentum makes the world go 'round. --------------570F19E0AC962A953C591B70 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Any key that "increases linearly over time" is a chronological key; sequences are a perfect example.

On 7/27/20 4:38 PM, Sidney Aloisio Ferreira Pryor wrote:

Thank you Ron but this table has no date column.

It has one line for each page of each scanned document.

De: Ron <ronljohnsonjr@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 18:27
Para: 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
 
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.

--
Angular momentum makes the world go 'round.
--------------570F19E0AC962A953C591B70--