Re: pgstattuple free_percent to high

Hi Rene,
thanks for you reply.

I think tuning the autovacuum settings may increase performances and remove
dead_tuples but as far as I know, the autovacuum runs a vacuum analyze.
The vacuum analyze won't touch the free_percent of the table.

So I'm trying to find a way to adjust the free percent for some tables
without doing a manually full vacuum.
We are now monitoring the free percent, so we may find the part of the code
that can increase that value, but was wondering if there is anything on the
postgres side to resolve this problem.

Thanks,
Nicola

2017-12-14 0:16 GMT+01:00 Rene Romero Benavides <rene(dot)romero(dot)b(at)gmail(dot)com>:

> Check for long running transactions modifying (update, insert) on those
> tables ,using pg_stat_activity.
>
> Tweak these storage parameters for such tables:
> autovacuum_vacuum_cost_delay : decrease it (the autovacuum daemon goes to
> sleep less often )
> autovacuum_vacuum_threshold : decrease it (to trigger more frequent
> autovacuum activations )
> autovacuum_vacuum_cost_limit : increase it (to allow the autovacuum daemon
> to work for longer periods)
> autovacuum_vacuum_scale_factor : decrease it (to trigger more autovacuum
> activations when this percentage of a table has been modified)
>
> For example I've set these parameters for one table experiencing long
> running transactions, and for its access patterns have worked:
>
> autovacuum_vacuum_cost_delay=5, autovacuum_vacuum_threshold=
> 50,autovacuum_vacuum_cost_limit=3000, autovacuum_vacuum_scale_factor=0.01
> but these settings are very particular for each usage pattern.
>
> Take into account that more activity from autovacuum means more IO, more
> CPU usage, you might also benefit from setting autovacuum_work_mem to a
> higher setting if the available RAM allows it, to give more RAM to the
> autovacuum daemon.
>
>
>
> 2017-12-13 9:49 GMT-06:00 Nicola Contu <nicola(dot)contu(at)gmail(dot)com>:
>
>> Hello,
>> We are running postgres 9.6.6 on centos 7.
>>
>> We have a large DB (180GB) with about 1200 tables.
>>
>> We have autovacuum set with default values and we are seeing that for
>> some tables the free percent goes really high (51%) and we need to daily
>> full vacuum those tables.
>>
>> dbanme=# SELECT * FROM pgstattuple('tablename');
>> table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count
>> | dead_tuple_len | dead_tuple_percent | free_space | free_percent
>> ------------+-------------+------------+---------------+----
>> --------------+----------------+--------------------+-------
>> -----+--------------
>> 2119548928 | 526658 | 1023569149 | 48.29 | 0
>> | 0 | 0 | 1083485292 | 51.12
>> (1 row)
>>
>> I guess this is because of long queries but I'm not really sure.
>> Do you know how to avoid this problem and what can cause it?
>>
>> Do you think that increasing the autovacuum settings for those tables
>> would alleviate the issue?
>>
>> Thanks,
>> Nicola
>>
>
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>