Bug #14336
closed
Rudder database grows with time because of defaults values for autovacuum that don't fit our needs
Description
default postgresql default on autovacuum are pretty concervative:
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
It means that autovacuum will be triggered only when 50 lines + 20% of the total of the table are removed. Which is pretty low by all standart
in our test rudder, the results are
select relid, relname, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count from pg_stat_all_tables where relname not like 'pg_%'; relid | relname | n_dead_tup | last_vacuum | last_autovacuum | vacuum_count ---------+----------------------------+------------+-------------------------------+-------------------------------+-------------- 16519 | rulesdirectivesjoin | 0 | | | 0 1004365 | nodeconfigurations2 | 0 | | | 0 27768 | globalschedule | 0 | | | 0 16532 | nodes | 945 | | 2019-01-03 02:05:51.58561+01 | 0 16486 | directives | 23954 | | 2019-01-27 02:03:00.586128+01 | 0 16447 | eventlog | 0 | | | 0 16574 | changerequest | 0 | | | 0 16461 | groups | 3786 | | 2018-12-28 02:06:21.745887+01 | 0 11527 | sql_languages | 0 | | | 0 187263 | datasources | 0 | | | 0 16413 | ruddersysevents | 0 | 2019-02-18 00:00:53.306166+01 | 2019-02-18 00:05:40.079255+01 | 192 11547 | sql_sizing_profiles | 0 | | | 0 16603 | reportsexecution | 63 | 2019-02-18 00:00:55.103523+01 | 2019-02-18 10:18:19.276842+01 | 96 11532 | sql_packages | 0 | | | 0 159819 | archivednodecompliance | 383 | | 2019-02-16 00:01:34.310626+01 | 0 16432 | archivedruddersysevents | 63293 | | 2019-02-13 00:02:59.897577+01 | 0 11542 | sql_sizing | 0 | | | 0 16473 | groupsnodesjoin | 0 | | | 0 52443 | nodes_info | 23 | | 2019-02-10 00:01:46.880864+01 | 0 16500 | rules | 9747 | | 2019-02-11 12:51:34.945122+01 | 0 724962 | plugin_reporting | 0 | | | 0 19505 | rulesgroupjoin | 0 | | | 0 16584 | workflow | 0 | | | 0 11517 | sql_features | 0 | | | 0 136138 | archivednodeconfigurations | 5 | | 2019-02-12 00:02:42.436093+01 | 0 16556 | rudderproperties | 66 | | 2019-02-18 07:34:59.06583+01 | 0 159804 | nodecompliance | 0 | | 2019-02-18 00:03:46.231303+01 | 0 758128 | nodecompliancelevels | 0 | 2019-02-18 11:51:45.404387+01 | 2019-02-16 00:05:01.78318+01 | 4 11522 | sql_implementation_info | 0 | | | 0 136124 | nodeconfigurations | 47 | | 2019-02-15 00:02:01.526796+01 | 0 11537 | sql_parts | 0 | | | 0 16595 | statusupdate | 791 | | 2019-02-18 11:09:05.868524+01 | 0 16547 | migrationeventlog | 0 | | | 0 16564 | gitcommit | 0 | | | 0
yes, that's nearly never an autovacuum
More info on the threadsholds here
https://blog.2ndquadrant.com/autovacuum-tuning-basics/
we can change it on a table basis, or on a postgresql basis
Files
Updated by Nicolas CHARLES almost 6 years ago
- File image.png image.png added
- File monitoring.png monitoring.png added
adding stats about a user usage
Updated by Nicolas CHARLES almost 6 years ago
the table by table settings can be found in
```select relname, reloptions
from pg_class where relname='ruddersysevents';
relname | reloptions
-----------------+---------------------------------------
ruddersysevents | {autovacuum_vacuum_scale_factor=0.01}
```
Updated by Nicolas CHARLES almost 6 years ago
tables most likely to benefit from this are
nodecompliance
nodecompliancelevels
statusupdate
reportsexecution
rudderproperties
groups
directives
nodes
rules
archivednodeconfigurations
archivednodecompliance
archivedruddersysevents
ruddersysevents
nodeconfigurations
Updated by Nicolas CHARLES almost 6 years ago
- Status changed from New to In progress
- Assignee set to Nicolas CHARLES
Updated by Nicolas CHARLES almost 6 years ago
- Status changed from In progress to Pending technical review
- Assignee changed from Nicolas CHARLES to François ARMAND
- Pull Request set to https://github.com/Normation/rudder/pull/2142
Updated by Nicolas CHARLES almost 6 years ago
- Status changed from Pending technical review to Pending release
Applied in changeset rudder|d7fbdf3041bcded8f7d113b16ac0ccdef87a57ee.
Updated by François ARMAND over 5 years ago
- Status changed from Pending release to Released
This bug has been fixed in Rudder 5.0.7 which was released today.
Updated by Nicolas CHARLES over 5 years ago
- Related to Bug #15413: Autovacuum may never finish, or take more than one day, on busy Rudder instance, because of resources attrition added