Project

General

Profile

Bug #14336

Rudder database grows with time because of defaults values for autovacuum that don't fit our needs

Added by Nicolas CHARLES 11 months ago. Updated 11 months ago.

Status:
Released
Priority:
N/A
Category:
System integration
Target version:
Severity:
User visibility:
Effort required:
Priority:
0

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

image.png (90.9 KB) image.png Nicolas CHARLES, 2019-02-18 12:29
monitoring.png (75.2 KB) monitoring.png Nicolas CHARLES, 2019-02-18 12:29

Subtasks

Bug #14338: Migration script for defaults values for autovacuumReleasedNicolas CHARLESActions

Related issues

Related to Rudder - Bug #15413: Autovacuum may never finish, or take more than one day, on busy Rudder instance, because of resources attritionReleasedActions

Associated revisions

Revision d7fbdf30 (diff)
Added by Nicolas CHARLES 11 months ago

Fixes #14336: Rudder database grows with time because of defaults values for autovacuum that don't fit our needs

History

#1

Updated by Nicolas CHARLES 11 months ago

adding stats about a user usage

#2

Updated by Nicolas CHARLES 11 months 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}
```

#3

Updated by Nicolas CHARLES 11 months ago

tables most likely to benefit from this are

nodecompliance
nodecompliancelevels
statusupdate
reportsexecution
rudderproperties

groups
directives
nodes
rules

archivednodeconfigurations
archivednodecompliance
archivedruddersysevents

ruddersysevents
nodeconfigurations

#4

Updated by Nicolas CHARLES 11 months ago

  • Status changed from New to In progress
  • Assignee set to Nicolas CHARLES
#5

Updated by Nicolas CHARLES 11 months 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
#6

Updated by Nicolas CHARLES 11 months ago

  • Status changed from Pending technical review to Pending release
#7

Updated by François ARMAND 11 months ago

  • Status changed from Pending release to Released

This bug has been fixed in Rudder 5.0.7 which was released today.

#8

Updated by Nicolas CHARLES 6 months ago

  • Related to Bug #15413: Autovacuum may never finish, or take more than one day, on busy Rudder instance, because of resources attrition added

Also available in: Atom PDF