Sentinel data directory is consuminga lot of disk space

  • 7024073
  • 20-Aug-2019
  • 09-Sep-2019

Environment

Sentinel 8.2 Sentinel Database Manager

Situation

Despite maximum disk space being set to 90%, it was observed that the disk partition hosting the database was over 95% full.  In reviewing the /var/opt/novell/sentinel/3rdparty/postgresql/data/pg_tblspc/#####/PG_9.5_201510051/##### (where ##### is a number), we found a number to have multiple files beginning with a specific number (viz. 19773-19773.3 and overhead files of 19773_fsm & 19773_vm).

Resolution

Good disk usage queries are available on the postgresql.org wiki.  The "General Table Size Information" section is helpful for identifying the size of all of the tables (including separate indexes) while the "Finding the largest databases in your cluster" can be a quick hit.  Note: the queries on the wiki need to be contained within double quotes (" ") and on one line; as well as passed to the db.sh script (along with the database name and user -- see examples below).
  1. Login as the database user or switch (su) to that user (which is typically 'novell').
  2. Run the query from "Finding the Largest Databases in your Cluster"
    This will return the top 20 tables in descending order.

    Note: the preface of "evt_rpt_" is an indication of a report that is having data synchronized.

  3. Further information, such as number of records, table name identifier # (OID), etc. can be found in the "General Table Size Information" report.

    The OID number will be start of the filename used by a given table.  The more files starting with that is a confirmation that the report is consuming more space.

  4. Identify if this # is being used by a report, and which one.

    - login into the Sentinel UI at https://dns_name:8443/
    - navigate to "Storage" -> "Data Synchronization" and edit each report, looking for the same table name under the blue FIELD MAPPING title (below connections).

  5. After identifying the report in question, click the "Resynchronize" link (after the report).
    NOTE: RECORDS WILL BE REMOVED FROM THE DATABASE PERMANENTLY.  If you need them retained, you may want to engage Technical Support or increase the size of the disk that is holding the database.

    You will see the options that can be changed:
    - Synchronize data from:  
    If you don't need the data to be stored for reporting, you can set this to yesterday or a few days in the past.  By default, it sets it to the earliest time in the database.

    - Check to Truncate Data:
    This is checked by default.  If set to the previous day, only one day's worth of data will be retained.


  6. Once the data is resync'd and truncated, "vacuum" the database by running:
    /opt/novell/sentinel/3rdparty/postgrespl/bin/vacuumdb -U dbauser -w<DataBase_Password> -h 127.0.0.1 -d SIEM -t <theTableName--public.evt_rpt_##########> -v -f

    where:
    - <DataBase_Password> is the actual password for the database user
    - <theTableName> is the space hogging table identified in #5
    - -v is verbose
    - -f is full vacuum

    (note: if you don't want the password on the command line, use -W instead of "-w <DataBase_Password>".  You will be prompted to enter the password each time this is run but it keeps the password more secure.

  7. You may not see a reduction for a period of time -- depending on how large the table is and how much free space is available on the disk.  So be patient.  If you desire, you can re-run step #6 again.