EDR: Large storefile and binarystatus Postgres Tables

Products

• Carbon Black EDR (formerly Cb Response)

Issue/Introduction

Performance and/or loss of retention

• Large storefiles and binarystatus tables upwards of 10GB or more each
• /var/cb/data/modulestore is talking up most of the partition space

Environment

• EDR Server: All Versions

Cause

• Not sharing binaries (All or specific groups)
• Environment has many unique binaries
• Network issues connecting to Alliance
• KeepAllModuleFiles=1 in /etc/cb/cb.conf

Resolution

1. On the Primary server, check the oldest storefiles entries

   psql -p 5002 -d cb -c "select * from storefiles order by timestamp asc limit 20;"

2. Get a count of how many entries will be removed. Example is set to 60 days.

   psql -p 5002 -d cb -c "select count(*) from storefiles where timestamp <= current_timestamp - interval '60' day;"

3. Delete the entries older than the set days

   psql -p 5002 -d cb -c "delete from storefiles where timestamp <= current_timestamp - interval '60' day;"

4. Repeat steps 1-3 for binary_status

   psql -p 5002 -d cb -c "select * from binary_status order by date_added asc limit 20;" 
   psql -p 5002 -d cb -c "select count(*) from binary_status where date_added <= current_timestamp - interval '60' day;" 
   psql -p 5002 -d cb -c "delete from binary_status where date_added <= current_timestamp - interval '60' day;"

5. Vacuum the Postgres tables
   1. Stop services CB Response: How to restart server services
   2. Start only postgres

      CentOS6:
      sudo services cb-pgsql start
      
      CentOS7-8:
      sudo systemctl start cb-pgsql

   3. Vacuum the tables with these commands

      psql -p 5002 -d cb -c "vacuum (full, analyze, verbose) sensor_registrations;" 
      psql -p 5002 -d cb -c "vacuum (full, analyze, verbose) binary_status;"
      psql -p 5002 -d cb -c "vacuum (full, analyze, verbose) storefiles;"

   4. Stop pgsql

      CentOS6:
      sudo services cb-pgsql stop
      
      CentOS7-8:
      sudo systemctl stop cb-pgsql

   5. Start all services CB Response: How to restart server services
6. Delete the physical binaries using a cron. This should be added to Primary and Minions if clustered (see Additional Notes to make this change persistent).
   • Edit /etc/cron.d/cb by adding this line, mtime is set to +60 days for this example running at 2am once a day

     0 2 * * * root find /var/cb/data/modulestore/ -type f -mtime +60 -delete >> /var/log/cb/job-runner/startup.out 2>&1

   • Note: The cron should be added anywhere in the /etc/cron.d/cb file above this line

     # Cron requires that each entry in a crontab end in a newline. This comment is included to ensure that's always the case.

   • Run df -h to confirm partition space is being gained back. 

Additional Information

• To make step 6 permanent, add the cron to /etc/cb/cron/cb.cron.template. If services are restarted, the /etc/cron.d/cb file will be overwritten with the template.
• These steps remove only the physical binary files that are uploaded to the server. This will affect the binary download from the binary info page, but will not affect the binary metadata in the binary search page
• To view how much space is being taken by the postgres tables before and after run

  psql -p 5002 -d cb -c "SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" 
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') 
  ORDER BY pg_relation_size(C.oid) DESC;"