Re: pgstattuple free_percent to high - Mailing list pgsql-general
| From | Nicola Contu |
|---|---|
| Subject | Re: pgstattuple free_percent to high |
| Date | |
| Msg-id | CAMTZZh08UX+=LpY_a4HjAj=Gqa6n_Xhxf0wYgP9Fmegc=W1KgQ@mail.gmail.com Whole thread Raw |
| In response to | Re: pgstattuple free_percent to high (Rene Romero Benavides <rene.romero.b@gmail.com>) |
| Responses |
Re: pgstattuple free_percent to high
|
| List | pgsql-general |
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.romero.b@gmail.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.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
pgsql-general by date: