/
Auto Vacuum Performance Impact Testing

Auto Vacuum Performance Impact Testing

Owned by Toine Siebelink
Mar 26, 2025
3 min read

Postgress AutoVacuum Impacts

Test Configurations

#

Environment/Workload

Description

#

Environment/Workload

Description

1

Tested on 

laptop :

Processor :          11th Gen Intel(R) Core(TM) i5-1135G7 @ 2.40GHz                                      2.42 GHz

Installed RAM :   40.0 GB (39.7 GB usable)

Edition :               Windows 11 Business

2

Docker-compose 

Docker-compose file: https://gerrit.onap.org/r/gitweb?p=cps.git;a=blob_plain;f=docker-compose/docker-compose.yml;hb=6d68fd5becf3dc0f3266aa452650332f24929b32

3

JMeter

4

Number of CPS Instance

1

5

Number of Cm Handles

~20,000

6

Number Modules per Cm Handle

10

 

#

Component

Configuration Parameters

Test 1

Test 2

#

Component

Configuration Parameters

Test 1

Test 2

1

CPS

notifactions:enabled

false

false

2

CPS

config.additional.notification.data-updated.filters.dataspaces.enabled-dataspaces

N/A

N/A

3

NCMP

modules-sync-watchdog:async-executor:parallelism-level

10

10

4

Postgress

Autovacuum (SQL CMD : show autovacuum; )

ON  

OFF*

*Note, AutoVacuum need to be turned off per table

# SQL Query to turn off autovacuum : ALTER TABLE anchor  SET (autovacuum_enabled = false); ALTER TABLE fragment  SET (autovacuum_enabled = false); ALTER TABLE schema_set  SET (autovacuum_enabled = false); ALTER TABLE schema_set_yang_resources  SET (autovacuum_enabled = false); #Verify if autovacuum is turned off: SELECT reloptions FROM pg_class WHERE relname = 'schema_set_yang_resources';

 

Test Results

#

Description

Synchronized CM Handles

Total Time

Vacuuming Time

Orphans (temp) table cleanup

CM handles/sec

#

Description

Synchronized CM Handles

Total Time

Vacuuming Time

Orphans (temp) table cleanup

CM handles/sec

1

AutoVacuum On

19,625

34min

~406 sec (20%)

~206 sec (10%)

9.7

2

AutoVacuum Off

19,530 

40min

0

~206 sec (8%)

8.1

Test 1, Auto Vacuum Statistic 

Query used:

SELECT schemaname, relname, last_autovacuum, autovacuum_count, n_tup_ins as "inserts", n_tup_upd as "updates", n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples" FROM pg_stat_user_tables where schemaname ='public';

 

Entity Name

Autovacuum @Time 

#CM Handles 

processed

End of Test Stats

Inserts

Updates

Deletes

Live_tuples

Dead_tuples

fragment

1:26 

2:26 

3:26  

4:26  

5:26  

6:26  

8:27 

10:26  

14: 26 

18:27  

29:28  

0

1054

1617

2586

3118

3987

5601

7227

8965

10380

14106

98,473

100,841

26

98,444

16,436

schema_set

03:26  

05:26  

08:26  

12:26  

19:26  

23:27  

33:27  

1617

3118

5601

8000

10380

13420

16544

23,456

0

3,830

19,628

803

anchor

03:26  

05:26  

08:26  

12:26  

19:27  

23:27  

33:27  

1617

3118

5601

8000

10380

13420

16544

23,451

0

3,827

19,629

800

schema_set_yang_resources

02:26  

03:26  

04:26  

05:26  

06:27  

08:27  

10:27  

13:27  

19:27  

22:27  

31:27  

37:27 

1054

1617

2586

3118

3987

5601

7227

8663

10380

12544

14544

19544

257,982

0

42,134

215,901

3,850

TOTAL

19,625 CM Handles

 

403,362

100,841

49,817

353,602

21,889

Docker stats (test #1)

CONTAINER ID 

NAME

CPU %

MEM USAGE / LIMIT

MEM % 

NET I/O

BLOCK I/O

PIDS

CONTAINER ID 

NAME

CPU %

MEM USAGE / LIMIT

MEM % 

NET I/O

BLOCK I/O

PIDS

93ce7ae1c5e0   

wiremock 

0.08%

1.054GiB / 19.37GiB

5.44% 

44.9MB / 778MB

 0B / 0B

36

bbc5a9ffe470

netconf-simulator

3.06%

37.07MiB / 19.37GiB

0.19%

82.9kB / 344kB

0B / 0B

18

52a732c4141e

sdnc

0.65%

2.26GiB / 19.37GiB

11.66%

1.2MB / 20.5MB

0B / 0B

157

4d1f36646f9d

sdnc_mariadb_1

0.01%

107.3MiB / 19.37GiB

0.54%

20.4MB / 911kB

0B / 0B

18

a9594101ae0f

kafka

0.38%

 340.2MiB / 19.37GiB

1.71%

76.1kB / 74kB

0B / 0B

80

395a66a6b1ec

cps-and-ncmp

2.02%

1.517GiB / 19.37GiB

7.83%

1.9GB / 342MB

0B / 0B

275

082a8a190743

zookeeper

0.11%

88.35MiB / 19.37GiB

0.45%

103kB / 46.9kB

0B / 0B

59

35ebe13dcfef

dbpostgresql

0.16%

 502.7MiB / 19.37GiB 

2.53%

329MB / 1.01GB

0B / 0B

17

 

Test 2, Housekeeping Statistic

Entity Name

Total Autovacuum Count

End of Test Stats

Inserts

Updates

Deletes

Live_tuples

Dead_tuples

fragment

0

98,136

101,826

200

97,924

96,592

schema_set

0

23,548

0

4,015

19,532

4,016

anchor

0

23,546

0

4,014

19,532

4,014

schema_set_yang_resources

0

259,012

0

44,169

214,834

44,178

Total

0

404,242

0

52,398

351,822

148,800

Details log as zip:

References