Обсуждение: pg_stat_get_last_vacuum_time(): why non-FULL?

According to the manual (9.1), pg_stat_get_last_vacuum_time() returns

    timestamptz | Time of the last non-FULL vacuum initiated by the
                | user on this table

Why are full vacuums excluded from this statistic? It looks like there's
no way to get the date of the last manual vacuum, if only full vacuums
are performed.


regards,
crl

CR Lender <crlender@gmail.com> wrote:

> According to the manual (9.1), pg_stat_get_last_vacuum_time() returns
>
>     timestamptz | Time of the last non-FULL vacuum initiated by the
>                 | user on this table
>
> Why are full vacuums excluded from this statistic? It looks like there's
> no way to get the date of the last manual vacuum, if only full vacuums
> are performed.

Because FULL is a bit of a misnomer -- there are important things a
non-FULL vacuum does which a FULL vacuum does not.  In general, a
VACUUM FULL should be followed by a non-FULL vacuum to keep the
database in good shape.  Also, a VACUUM FULL is an extreme form of
maintenance which should rarely be needed; if you find that you
need to run VACUUM FULL, something is probably being done wrong
which should be fixed so that you don't need to continue to do such
extreme maintenance.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2013-03-26 19:28, Kevin Grittner wrote:
>> Why are full vacuums excluded from this statistic? It looks like there's
>> no way to get the date of the last manual vacuum, if only full vacuums
>> are performed.
>
> Because FULL is a bit of a misnomer -- there are important things a
> non-FULL vacuum does which a FULL vacuum does not.  In general, a
> VACUUM FULL should be followed by a non-FULL vacuum to keep the
> database in good shape.

Thank you, that's very helpful. I wasn't aware of that.

> Also, a VACUUM FULL is an extreme form of
> maintenance which should rarely be needed; if you find that you
> need to run VACUUM FULL, something is probably being done wrong
> which should be fixed so that you don't need to continue to do such
> extreme maintenance.

In this case I was only trying to make sense of an existing database
(8.3). The statistics in pg_stats were way off for some tables, so I
wanted to see if (auto)vacuum and (auto)analyze were being run.
pg_stat_all_tables() showed last_autoanalyze at >400 days for some of
the larger tables. There used to be a weekly cron job with VACUUM FULL
ANALYZE, and I was trying to find out if that cron job was still active.

Thanks,
crl

2013/3/27 CR Lender <crlender@gmail.com>:
>
>> Also, a VACUUM FULL is an extreme form of
>> maintenance which should rarely be needed; if you find that you
>> need to run VACUUM FULL, something is probably being done wrong
>> which should be fixed so that you don't need to continue to do such
>> extreme maintenance.
>
> In this case I was only trying to make sense of an existing database
> (8.3). The statistics in pg_stats were way off for some tables, so I
> wanted to see if (auto)vacuum and (auto)analyze were being run.
> pg_stat_all_tables() showed last_autoanalyze at >400 days for some of
> the larger tables. There used to be a weekly cron job with VACUUM FULL
> ANALYZE, and I was trying to find out if that cron job was still active.
>

What's your autovacuum configuration? autovacuum_vacuum_threshold?
autovacuum_analyze_threshold? autovacuum_vacuum_scale_factor?
autovacuum_analyze_scale_factor?

Related to your 400+ days not vacuumed tables, are you sure those
tables have data changes (INSERT/UPDATE/DELETE)? I have some static
tables with over a year of no vacuum (and autovacuum field never ran
on that relation).

What does n_dead_tup show?

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

On 2013-03-28 13:11, Martín Marqués wrote:
> 2013/3/27 CR Lender <crlender@gmail.com>:
>> In this case I was only trying to make sense of an existing database
>> (8.3). The statistics in pg_stats were way off for some tables, so I
>> wanted to see if (auto)vacuum and (auto)analyze were being run.
>> pg_stat_all_tables() showed last_autoanalyze at >400 days for some of
>> the larger tables. There used to be a weekly cron job with VACUUM FULL
>> ANALYZE, and I was trying to find out if that cron job was still active.
>
> What's your autovacuum configuration? autovacuum_vacuum_threshold?
> autovacuum_analyze_threshold? autovacuum_vacuum_scale_factor?
> autovacuum_analyze_scale_factor?

 autovacuum                      | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold    | 50
 autovacuum_freeze_max_age       | 200000000
 autovacuum_max_workers          | 3
 autovacuum_naptime              | 1min
 autovacuum_vacuum_cost_delay    | 20ms
 autovacuum_vacuum_cost_limit    | -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold     | 50

The database is running on PostgreSQL 8.3.6.

I don't maintain this server, and my knowledge about the autovacuum
feature and its settings are sketchy. The values above could be the
defaults for 8.3.6, or they may have been adjusted by the admin.

> Related to your 400+ days not vacuumed tables, are you sure those
> tables have data changes (INSERT/UPDATE/DELETE)? I have some static
> tables with over a year of no vacuum (and autovacuum field never ran
> on that relation).

Yes. Autovacuum and autoanalyze are active, and tables with frequent
DELETEs and UPDATEs appear to be vacuumed and analyzed at least once a
day. Other tables with relatively frequent INSERTs, but irregular
UPDATEs and rare DELETEs go without vacuum/analyze for long periods of
time. Static tables never get analyzed or vacuumed (as expected).

> What does n_dead_tup show?

Here are the statistics for three exemplary tables:

         relname: | r____      | oe____     | mv____
                  |            |            |
 n_tup_ins        |      35335 |     179507 |       9562
 n_tup_upd        |      46727 |     824898 |          0
 n_tup_del        |          0 |       9709 |       3567
 n_tup_hot_upd    |       2016 |     793169 |          0
 n_live_tup       |     206086 |    1132164 |      57964
 n_dead_tup       |      35583 |      46932 |       5436
 last_autovacuum  | 2011-05-25 |       NULL |       NULL
 last_autoanalyze | 2013-01-07 | 2012-12-27 | 2012-04-16

I'm not saying that autovacuum/autoanalyze aren't working as designed, I
was just surprised by the long delays.


Concerning the earlier reply to my question...

> On 2013-03-26 19:28, Kevin Grittner wrote:
>> Because FULL is a bit of a misnomer -- there are important things a
>> non-FULL vacuum does which a FULL vacuum does not.  In general, a
>> VACUUM FULL should be followed by a non-FULL vacuum to keep the
>> database in good shape.

I've read the manual more carefully now, and I can't see any mention of
what VACUUM does that VACUUM FULL does not. The point about extreme
maintainance is taken, but from what I read, VACUUM FULL should include
everything a normal VACUUM does.


Thanks,
crl

CR Lender <crlender@gmail.com> wrote:

> The database is running on PostgreSQL 8.3.6.

> I've read the manual more carefully now, and I can't see any mention of
> what VACUUM does that VACUUM FULL does not. The point about extreme
> maintainance is taken, but from what I read, VACUUM FULL should include
> everything a normal VACUUM does.

Prior to release 9.0 that is probably true.  Sorry for not asking
about the version first.  But you should read this page:

http://www.postgresql.org/support/versioning/

8.3 is out of support now.  Even for the 8.3 release, 8.3.6 is
missing over four years of fixes for bugs and security
vulnerabilities.  There is a very good chance that any problem you
see already fixed and you are just choosing to run without the fix.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2013/3/28 CR Lender <crlender@gmail.com>:
> On 2013-03-28 13:11, Martín Marqués wrote:
>> 2013/3/27 CR Lender <crlender@gmail.com>:
>>> In this case I was only trying to make sense of an existing database
>>> (8.3). The statistics in pg_stats were way off for some tables, so I
>>> wanted to see if (auto)vacuum and (auto)analyze were being run.
>>> pg_stat_all_tables() showed last_autoanalyze at >400 days for some of
>>> the larger tables. There used to be a weekly cron job with VACUUM FULL
>>> ANALYZE, and I was trying to find out if that cron job was still active.
>>
>> What's your autovacuum configuration? autovacuum_vacuum_threshold?
>> autovacuum_analyze_threshold? autovacuum_vacuum_scale_factor?
>> autovacuum_analyze_scale_factor?
>
>  autovacuum                      | on
>  autovacuum_analyze_scale_factor | 0.1
>  autovacuum_analyze_threshold    | 50
>  autovacuum_freeze_max_age       | 200000000
>  autovacuum_max_workers          | 3
>  autovacuum_naptime              | 1min
>  autovacuum_vacuum_cost_delay    | 20ms
>  autovacuum_vacuum_cost_limit    | -1
>  autovacuum_vacuum_scale_factor  | 0.2
>  autovacuum_vacuum_threshold     | 50
>
> The database is running on PostgreSQL 8.3.6.

What you have to look at is autovacuum_analyze_scale_factor which in
your case is 10% of the total tuples of the relation. So when 50
tuples (the threshold) over the 10% of the total tuples are
analyzable, autovacuum analyzes the relation.

Same thing with vacuum, but with 20% in that case.

If you want autovacuum to really clean and analize more, you will have
to lower autovacuum_analyze_scale_factor and
autovacuum_vacuum_scale_factor.

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

On 2013-03-28 20:44, Kevin Grittner wrote:
> CR Lender <crlender@gmail.com> wrote:
>
>> The database is running on PostgreSQL 8.3.6.
>
>> I've read the manual more carefully now, and I can't see any mention of
>> what VACUUM does that VACUUM FULL does not. The point about extreme
>> maintainance is taken, but from what I read, VACUUM FULL should include
>> everything a normal VACUUM does.
>
> Prior to release 9.0 that is probably true.

Hm, I can't find it, even in the manual for 9.2.
http://www.postgresql.org/docs/current/static/sql-vacuum.html

If VACUUM FULL is just a more aggressive VACCUM (including writing new
data files), then I don't understand the "non-FULL" restriction in
pg_stat_get_last_vacuum_time()... unless that information is somehow
lost when table files are rewritten.

> 8.3 is out of support now.  Even for the 8.3 release, 8.3.6 is
> missing over four years of fixes for bugs and security
> vulnerabilities.  There is a very good chance that any problem you
> see already fixed and you are just choosing to run without the fix.

You're right of course, the PostgreSQL version on the server is rather
old. We're redesigning the whole application, and migrating to 9.2 will
be part of the process (I'm running 9.1 locally).

Thanks,
crl

On 2013-03-31 18:31, CR Lender wrote:
> On 2013-03-28 20:44, Kevin Grittner wrote:
>> CR Lender <crlender@gmail.com> wrote:
>>> I've read the manual more carefully now, and I can't see any mention of
>>> what VACUUM does that VACUUM FULL does not. The point about extreme
>>> maintainance is taken, but from what I read, VACUUM FULL should include
>>> everything a normal VACUUM does.
>>
>> Prior to release 9.0 that is probably true.
>
> Hm, I can't find it, even in the manual for 9.2.
> http://www.postgresql.org/docs/current/static/sql-vacuum.html
>
> If VACUUM FULL is just a more aggressive VACCUM (including writing new
> data files), then I don't understand the "non-FULL" restriction in
> pg_stat_get_last_vacuum_time()... unless that information is somehow
> lost when table files are rewritten.

I don't mean to be pushy, but I have a meeting with the admin of that
database tomorrow, and it would be nice if I had something concrete to
tell him. I still don't know what it is that VACCUM does but VACUUM full
doesn't do. There's nothing in the manual about that.

Thanks,
crl

On Monday, April 08, 2013 4:40 AM CR Lender wrote:
> On 2013-03-31 18:31, CR Lender wrote:
> > On 2013-03-28 20:44, Kevin Grittner wrote:
> >> CR Lender <crlender@gmail.com> wrote:
> >>> I've read the manual more carefully now, and I can't see any
> mention of
> >>> what VACUUM does that VACUUM FULL does not. The point about extreme
> >>> maintainance is taken, but from what I read, VACUUM FULL should
> include
> >>> everything a normal VACUUM does.
> >>
> >> Prior to release 9.0 that is probably true.
> >
> > Hm, I can't find it, even in the manual for 9.2.
> > http://www.postgresql.org/docs/current/static/sql-vacuum.html
> >
> > If VACUUM FULL is just a more aggressive VACCUM (including writing
> new
> > data files), then I don't understand the "non-FULL" restriction in
> > pg_stat_get_last_vacuum_time()... unless that information is somehow
> > lost when table files are rewritten.
>
> I don't mean to be pushy, but I have a meeting with the admin of that
> database tomorrow, and it would be nice if I had something concrete to
> tell him. I still don't know what it is that VACCUM does but VACUUM
> full
> doesn't do. There's nothing in the manual about that.

One of the important difference is that during the time VACUUM FULL is operating on a relation,
no other operations will be allowed on that relation. Most of admin care about this point, because
they don't want to stop operations for background garbage collect.
VACUUM FULL is only done in rare cases when the relation size has grown too bigger than it's actual
Contents.


With Regards,
Amit Kapila.

Jeff Janes <jeff.janes@gmail.com> wrote:
> Amit Kapila <amit.kapila@huawei.com> wrote:

>> One of the important difference is that during the time VACUUM
>> FULL is operating on a relation, no other operations will be
>> allowed on that relation. Most of admin care about this point,
>> because they don't want to stop operations for background
>> garbage collect.

> While that is true, it is not a reason not to update
> pg_stat_get_last_vacuum_time.  I'm having a hard time coming up
> with a reason not to update pg_stat_get_last_vacuum_time with a
> full vacuum.
>
> On version 8.4 and below, you could justify it by saying that
> VACUUM FULL bloated the indexes and then left them that way, and
> so we shouldn't update the time field.  But that is no longer the
> case.  And even then, doing a ordinary vacuum afterwards isn't
> going to fix the index bloat, so even that argument is a bit
> sketchy.

I'm not sure that what we're doing now is correct, but updating
things as if a normal vacuum had been done would *not* be the thing
to do.  For starters, VACUUM FULL blows away the free space map and
visibility map for a table.  Among other things, that means that
index-only scans will cease to work until the table has a normal
vacuum.  A normal vacuum (or autovacuum) will restore those, so a
VACUUM FULL should probably set things up to show that the table is
in need of a vacuum soon.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Jeff Janes <jeff.janes@gmail.com> wrote:
> Amit Kapila <amit.kapila@huawei.com> wrote:

>> One of the important difference is that during the time VACUUM
>> FULL is operating on a relation, no other operations will be
>> allowed on that relation. Most of admin care about this point,
>> because they don't want to stop operations for background
>> garbage collect.

> While that is true, it is not a reason not to update
> pg_stat_get_last_vacuum_time.  I'm having a hard time coming up
> with a reason not to update pg_stat_get_last_vacuum_time with a
> full vacuum.
>
> On version 8.4 and below, you could justify it by saying that
> VACUUM FULL bloated the indexes and then left them that way, and
> so we shouldn't update the time field.  But that is no longer the
> case.  And even then, doing a ordinary vacuum afterwards isn't
> going to fix the index bloat, so even that argument is a bit
> sketchy.

I'm not sure that what we're doing now is correct, but updating
things as if a normal vacuum had been done would *not* be the thing
to do.  For starters, VACUUM FULL blows away the free space map and
visibility map for a table. 

On 2013-04-09 00:09, Kevin Grittner wrote:
> I'm not sure that what we're doing now is correct, but updating
> things as if a normal vacuum had been done would *not* be the thing
> to do.  For starters, VACUUM FULL blows away the free space map and
> visibility map for a table.  Among other things, that means that
> index-only scans will cease to work until the table has a normal
> vacuum.

Ah, now it makes sense. Thank you, that's what I was looking for.

And I agree with Jeff that this could be documented in more detail.

Thanks,
crl