Re: Vacuuming strategy

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Wed, Apr 30, 2014 at 10:40 AM, Elanchezhiyan Elango <elanelango@xxxxxxxxx> 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