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 5 months ago. Updated 5 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

Associated revisions

Revision d7fbdf30 (diff)
Added by Nicolas CHARLES 5 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 5 months ago

adding stats about a user usage

#2

Updated by Nicolas CHARLES 5 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 5 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 5 months ago

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

Updated by Nicolas CHARLES 5 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 5 months ago

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

Updated by François ARMAND 5 months ago

  • Status changed from Pending release to Released

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

Also available in: Atom PDF