Re: Vacuuming strategy

On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango <elanelango(at)gmail(dot)com
> wrote:

>
>

>
Why do you have a 4 minute timeout? That seems counter-productive.
>
> Oh, Is it less or more?
>

I would not have timeouts on maintenance operations at all. To me a
statement timeout is a last ditch method to deal with a recalcitrant
application (or recalcitrant colleague) which occasionally does something
silly and which cannot be fixed.

>
> You delete a bunch of tuples every night, so of course a vacuum full
>> after that is going to return a lot of space. But that space is probably
>> just going to be needed again the next day. If you don't do the vacuum
>> full, does the *peak* space keep increasing, or does it stabilize?
>
> I haven't tested to see if the space keeps on increasing.
>
> I did pgstattupe() on one of the tables:
>
> managed_target_stats=# select * from pgstattuple('xyz');
>
> table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count
> | dead_tuple_len | dead_tuple_percent | free_space | free_percent
>
>
> ------------+-------------+------------+---------------+------------------+----------------+--------------------+------------+--------------
>
> 5642526720 | 18880283 | 4042874387 | 71.65 | 122037
> | 21495560 | 0.38 | 1239598044 | 21.97
>
> This is one of Tables1 tables and this is after running for a week or so
> with the default autovacuum settings. The dead_tuple_percent look good. But
> the free_percent looks high. Is this normal?
>

I don't think 21.97 percent free space is anything to worry about.
Especially since, if you have not done any successful VACUUM FULL or
CLUSTER recently, the observed value represents the effects of a long
history under different settings, so it means little for what to expect in
the future. You were doing vacuum full every night, but if it always timed
out then the old state of the table was retained.

> Also when I enabled autovacuum logs, I saw the autovacuums triggering very
> rarely. May be that's the reason for too much free space? I am going to try
> with the following settings:
>
> log_autovacuum_min_duration = 0
>
> autovacuum_vacuum_scale_factor = 0
>
> autovacuum_vacuum_threshold = 40000
>

I would not use that setting system wide, or any small tables you have
might bloat by a lot. The size lost to such bloating is not meaningful,
but the performance lost to it could be.

Indeed, I don't think you need to change these at all, or at least not
based on current evidence.

The only concrete problem you have is that you are doing vacuum fulls,
which are probably unnecessary, and they are hitting an arbitrary timeout,
which was also probably unnecessary. So just stop doing those two things,
and monitor the situation to see what happens.

Cheers,

Jeff