Optimize database size and performance
When optimizing for database size and performance, database vacuuming can be utilized in addition to retention settings.
Retention days setting
The audit_retention_days configuration parameter in the properties.yml file specifies the number of days to retain data in the audit log database and exec_history log database. Records older than the specified number of days will be deleted.
For example, if the retention days is set to 30, data created on the first day will be deleted on the 31st day.
Managing unused space and reuse
Assume the database size reaches 90GB in 30 days, with a daily addition of 3GB of data.
On the 31st day, 3GB of data is removed. The database size will not shrink; instead, its associated pages are added to the free list, marking them as available for future use. Therefore, if new records are added at the same rate as deletions, the database size will remain constant, never exceeding 90GB.
Vacuuming
If you change the retention days to a smaller number but maintain the same addition and deletion rate, and you want the database size to be smaller or are experiencing performance issues due to database size, you can vacuum the database.
To vacuum a database, run the following SQLite command in your container:
Vacuuming can take a while, depending on the size of your database. You will see performance impacts during vacuuming in which reads could be slowed and writes could be blocked. 1.5 to 2 times the size of the original database is required in free disk space to perform a vacuum. It is advised to vacuum during a maintenance period while systems are offline.
For more information on vacuuming, refer to the SQLite documentation.
