/storage/seat is massive

This partition getting too big causes issues. Seat Stands for

  • S – stats
  • E – Events
  • A – alarms
  • T – Tasks

For this article we’re interested in when the growth is in events. In the example below we see that the eventtblsp is 54GB. this is the largest directory in /storage/seat

du -sh /storage/seat/vpostgres/*
1.3M alarmtblsp
54G eventtblsp
2.9G hs1tblsp
900M hs2tblsp
308M hs3tblsp
400M hs4tblsp
2.3G tasktblsp

Firstly you need to identify what records are filling the database

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres 

SELECT COUNT(0),
event_type
FROM vpxv_event_all
GROUP BY event_type
ORDER BY 1 DESC LIMIT 5;

The output in this case is

16297499 | vim.event.UserLoginSessionEvent
16295260 | vim.event.UserLogoutSessionEvent
2690546  | DatastoreFileDownloadEvent
1187037  | vim.event.TaskEvent
486828   | esx.problem.visorfs.ramdisk.full

You should then investigate using the vsphere client to see what’s issuing all these events.

Below here we can should investigate what dcui@127.0.0.1 is doing and try and stop it

Often it’s not so simple to stop these events. They’re often coming from backup software which vmware admins have no control over. In this instance we’ve used a workaround. This creates a procedure in the database which removes these unwanted events regularly.

Please change the procedure as appropriate to your issue

ldt_keep_from - this can be changed to keep any amount of days of the event. In our example we're keeping 10 days of information.

lv_event_type1 and lv_event_type2 - these are the 2 events we have an issue with in this example

If you've only 1 event then you'll need to change the code (event_type = '''||
           lv_event_type1||''' or event_type = '''||lv_event_type2||''')
to
 (event_type = '''|| lv_event_type1||''')

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

CREATE OR REPLACE FUNCTION remove_some_Events ()
returns VARCHAR as $$
DECLARE
event_partition VARCHAR (2000);
lv_event_type1 VARCHAR (200);
lv_event_type2 VARCHAR (200);
ldt_keep_from date;
ln_count_rows_removed_event int;
ln_count_rows_removed_event_arg int;
ln_rowcount int;
DECLARE return_count VARCHAR := 'DEFAULT';
BEGIN
 ln_count_rows_removed_event = 0;
 ln_count_rows_removed_event_arg = 0;
 -- this is configuring the amount of this data you want to keep.
 -- Change this as you feel comfortable.
 ldt_keep_from = timezone('UTC', now())  - (10 || ' days')::interval;
 --- These are the events we're going to remove for issue 1
 lv_event_type1 = 'vim.event.UserLoginSessionEvent';
 lv_event_type2 = 'vim.event.UserLogoutSessionEvent';
  FOR part IN 1..92  LOOP
 event_partition = 'delete  from VPX_EVENT_ARG_' || CAST(part AS TEXT)
            || ' where event_id in (select event_id from  VPX_EVENT_' || CAST(part AS TEXT)
            || ' where create_time < '''||ldt_keep_from ||''' and ((event_type = '''||
           lv_event_type1||''' or event_type = '''||lv_event_type2||''')))';
         EXECUTE event_partition;                     get diagnostics ln_rowcount =  row_count;                     ln_count_rows_removed_event_arg = ln_count_rows_removed_event_arg + ln_rowcount;                     ln_rowcount = 0;
 event_partition = 'delete  from VPX_EVENT_' || CAST(part AS TEXT)
           || ' where (event_type = '''||lv_event_type1||''' or event_type = '''||lv_event_type2||''')';
             EXECUTE event_partition;
                         get diagnostics ln_rowcount =  row_count;
                         ln_count_rows_removed_event = ln_count_rows_removed_event + ln_rowcount;
                         ln_rowcount = 0;
 END LOOP;             return_count = 'vpx_events_removed=' || ln_count_rows_removed_event || ',vpx_event_arguments_removed=' || ln_count_rows_removed_event_arg ;             RETURN return_count;
 END
 $$language plpgsql;

this can be run manually the first time. it may take a long time depending on the amount of data which need to be removed. It can be executed with all other services running.

VCDB=# select remove_some_Events();

vpx_events_removed=1988331,vpx_event_arguments_removed=78653978
(1 row)

once your happy you can package it in a simple shell script.

vi /storage/updatemgr/run_cleanup.sh

#!/bin/sh
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "SELECT * FROM clean_some_Events();"
:wq! 
chmod 777 /storage/updatemgr/run_cleanup.sh
./storage/updatemgr/run_cleanup.sh

Now it’s best to schedule this to run every night.

crontab -e
0  2 * * * /storage/updatemgr/run_cleanup.sh
:wq!

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s