[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: How can i be certain autovacuum is causing reuse if table still grows
From:       Keith Fiske <keith.fiske () crunchydata ! com>
Date:       2020-08-05 14:25:40
Message-ID: CAODZiv4GE_en9Bbv-s8kx3u4QQ_CmV2WTh-=7r00evSKVqtuAw () mail ! gmail ! com
[Download RAW message or body]

On Wed, Aug 5, 2020 at 9:29 AM MichaelDBA <MichaelDBA@sqlexec.com> wrote:

> Hi Keith,
>
> I think statistics are also reset and not persistent through restarts when
> the pg_ctl option "-m immediate" is used when shutting down PG since it
> forces recovery mode when starting PG back up.  By the way, for PostgreSQL
> Aurora, statistics are reset automatically every time aurora forces PG to
> restart or reboot or the user selects that option manually.  I reported
> this as a bug with Aurora a few months ago.  They said it was not a bug,
> but a feature that the might implement in the future.  YUK.
>
> Regards,
> Michael Vitale
>
>

An "immediate" shutdown is often considered a crash to Postgres. So yes, it
likely will reset the statistics.



>
> Keith Fiske wrote on 8/4/2020 6:25 PM:
>
>
>
> On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe <scott_ribe@elevated-dev.com>
> wrote:
>
>> > On Aug 4, 2020, at 12:31 PM, Sidney Aloisio Ferreira Pryor <
>> sidney@tjrj.jus.br> wrote:
>> >
>> > Beside this, i saw on pg_stat_tables and the vacuum and autovacuum
>> count for this table is 0.
>>
>> Those values are not persistent; they are only since the last start of PG.
>>
>>
> This is not correct. Statistics are persistent through restarts. They are
> only reset during specific cases such as a crash or a major version
> upgrade. They can also be reset at any time manually.
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
>
>
>

-- 
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Wed, Aug 5, 2020 at 9:29 AM MichaelDBA &lt;<a \
href="mailto:MichaelDBA@sqlexec.com">MichaelDBA@sqlexec.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">

<div>Hi Keith,<br>
<br>
I think statistics are also reset and not persistent through restarts 
when the pg_ctl option &quot;-m immediate&quot; is used when shutting down PG 
since it forces recovery mode when starting PG back up.   By the way, for
 PostgreSQL Aurora, statistics are reset automatically every time aurora
 forces PG to restart or reboot or the user selects that option 
manually.   I reported this as a bug with Aurora a few months ago.   They 
said it was not a bug, but a feature that the might implement in the 
future.   YUK.<br>
<br>
Regards,<br>
Michael Vitale<br>
<br></div></blockquote><div><br></div><div><br></div><div>An &quot;immediate&quot; \
shutdown is often considered a crash to Postgres. So yes, it likely will reset the \
statistics.</div><div><br></div><div>  </div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div> <br>
<span>Keith Fiske wrote on 8/4/2020 6:25 PM:</span><br>
<blockquote type="cite">
  
  <div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Tue, Aug 4, 2020 at 3:54 PM Scott Ribe  &lt;<a \
href="mailto:scott_ribe@elevated-dev.com" \
target="_blank">scott_ribe@elevated-dev.com</a>&gt;  wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">&gt;  On Aug 4, 2020, at 12:31 PM, Sidney Aloisio \
Ferreira Pryor &lt;<a href="mailto:sidney@tjrj.jus.br" \
target="_blank">sidney@tjrj.jus.br</a>&gt;  wrote:<br>
&gt; <br>
&gt; Beside this, i saw on pg_stat_tables and the vacuum and autovacuum 
count for this table is 0.<br>
<br>
Those values are not persistent; they are only since the last start of 
PG.<br>
<br></blockquote></div><br clear="all"><div>This is not correct. 
Statistics are persistent through restarts. They are only reset during 
specific cases such as a crash or a major version upgrade. They can also
 be reset at any time manually.</div><div><br></div>-- <br><div dir="ltr"><div \
dir="ltr"><div><div dir="ltr">Keith  Fiske<br>Senior Database Engineer<br>Crunchy \
Data - <a href="http://crunchydata.com" \
target="_blank">http://crunchydata.com</a></div></div></div></div></div> \
</blockquote> <br>
</div>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" \
class="gmail_signature"><div dir="ltr"><div><div dir="ltr">Keith Fiske<br>Senior \
Database Engineer<br>Crunchy Data - <a href="http://crunchydata.com" \
target="_blank">http://crunchydata.com</a></div></div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]