Vacuum the VCAC asd_event_processing_data Table - vRO 7.6

Mindwatering Incorporated

Author: Tripp W Black

Created: 05/17/2022 at 10:40 PM

 

Category:
VMWare

We were told by VMware to run a DELETE for the asd_event_processing_data table because it was huge and it contained a lot of orphaned entries. See KB 75168.

Warning:
Back-up. Take a COLD snapshot. Use these at your own risk. Make sure VMware told you to also follow the KB.

We noticed we didn't have the purge.run.requests.older lines in our vcac.properties file from the KB article. The VMware tech instructed us not to add the new entries to vcac.properties because those were the lines that didn't work. It is supposed to be just automatic. We need to just do the DELETE and vacuum in our next maintenance window.

Below are our instructions around the KBs instructions:

1. Login to the master appliance:
$ ssh root@vraappliance.mindwatering.net
[master] vraappliance:~ # su postgres -

postgres@vraappliance > cd /opt/vmware/vpostgres/current/bin/
postgres@vraappliance > psql vcac

Now run the purge:
vcac=# DELETE FROM asd_event_processing_data epd WHERE epd.id NOT IN (SELECT CAST (eventprocessingdataid AS UUID) FROM asd_workflowrunrequest req where eventprocessingdataid is not null);

<wait - this will take a really long time - 15 GB will take ~36 hours>

Then perform the vacuum to reclaim the space:
> vacuum (VERBOSE, FULL);


Exit the postgres command terminal with \q
vcac=# \q




________________________________________

Steps to Check Table Count:
# su postgres -
# psql vcac
vcac=# \d asd_event_processing_data;
Table "public.asd_event_processing_data"
Column | Type | Modifiers
id | uuid | not null
event_id | character varying(40) | not null
subscription_id | character varying(40) | not null
data | text | not null
timestamp | timestamp without time zone | not null
Indexes:
"asd_event_processing_data_pkey" PRIMARY KEY, btree (id)
"idx_asd_event_processing_data" btree (event_id, subscription_id)

vcac=# SELECT COUNT(id) FROM asd_event_processing_data;
count
--------
54321
(1 row)


Steps to Check Table Sizes:
vcac=# SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 30;
relation | total_size
------------------------------------------+------------
public.asd_event_processing_data | 16543 MB
public.asd_workflowrunrequest | 5432 MB
public.o11n_workflowrunstate | 4321 MB
...


Evidence of Smaller Storage folder:
# df -h /storage/db/pgdata
Filesystem Size Used Use% Mounted on
/dev/sdi1 300G 50G 25% /storage/db




previous page