Re: pgstattuple free_percent to high

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.contu@gmail.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/