Database compaction increases disk usage

Products

• CA Privileged Access Manager (PAM)

Issue/Introduction

We have compacted the PAM database following instructions on page https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/privileged-access-manager/4-0/administrating/maintenance/configuration-and-database-backups/compact-the-database-to-regain-storage-space.html. But when we checked on disk usage right after the compaction, we found in fact it had increased substantially. Why is that so? Does it mean that the compaction failed and we actually increased the database size?

Environment

Release : 3.4

Component : PRIVILEGED ACCESS MANAGEMENT

Cause

Database compaction works as follows:

1) Save the current data in the database.

2) Drop the PAM databases.

3) Stop mysql, remove any remaining files associated with the PAM databases and restart.

4) Load the PAM data saved in step 1.

Step 4 implies that the PAM database schema is created again and all saved data inserted into the tables. This creates a large amount of transaction logs that will be purged only after X hours, where X is the "Duration to Preserve MySQL Binary Logs (hours)" cluster tuning setting, see page https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/privileged-access-manager/4-0/deploying/set-up-a-cluster/configure-a-cluster.html#concept.dita_30f62b5da45eda51a0c59e3b30699fbe1d7588aa_ClusterTuningClusterTuning.

Resolution

Wait for the "Duration to Preserve MySQL Binary Logs (hours)" time interval before checking the disk space again. Only once the transaction logs written during the database compaction have been purged can you draw any conclusions about how much disk space you may have gained.

The transaction logs are not sent to other cluster nodes on cluster startup in the latest PAM releases. Therefore cluster startup can benefit from a compacted database on the primary cluster node right after it completed. You do not have to wait for the binary logs to be removed.

Note that when PAM compacts the database, no PAM data is removed. This activity is most valuable after reducing retention periods of data, e.g. lowering the archive age for metric and/or auditlog data on the Settings > Credential Manager > Auto-Archive page. This would cause PAM to delete a large amount of historic data within the next 24 hrs, but mysql would not shrink the size of the file containing the data. Only database compaction will do so. If you run with the same settings, and roughly the same amount of data in the PAM database, for extended periods of time, database compaction cannot be expected to shrink the database size significantly, and any modest reduction may be short-lived.