Audited Table Size Reporting

KMoody

Member
Our auditing tables take up almost 10GB, twice as large as the rest of our databases. I want to figure out where all this data is coming from.

Is there a way to find out how much space it takes to store auditing information for tables and their fields? For example, if I audit twenty tables, can I figure out how much space it costs to audit each table?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
There is no simple formula for determining the storage cost of auditing. There are a couple of reasons for this. First, there are options available to you when creating audit policies and the choices you make will affect how much data is stored for a given audit event. Second, the amount of audit data you write isn't a function of something you can pre-calculate like your schema size or database size. Rather it is a function of the activity on the objects or events that are being audited. You can try to get some idea of that by looking at your current run-time metrics, but of course past activity doesn't necessarily predict future activity.

If you decide to use OpenEdge auditing, planning is key. That planning needs to include the creation of a data-retention policy and the use of an audit archive database. The data-retention policy will dictate, among other things, how long the audit data remains in your application database(s) before you archive it and how long archived audit data will remain in the audit archive database until deletion.

I recommend that you run proutil auditarchive on your application databases at least daily and deactivate non-essential audit indexes in those databases (see Knowledge Article for details). Do your audit data reporting only against the audit archive database, where all indexes are active . These practices will minimize the performance and storage impact of enabling auditing in production.

One other piece of advice: start small. Audit one thing or a few and determine the effect on performance and storage. If you're not sure whether you want to audit a table or event, don't. There is no point in capturing data that no one will read.
 

TomBascom

Curmudgeon
Rob's advice is excellent.

The two biggest mistakes that people make when they implement auditing are:

1) They audit everything. Literally. Just in case.

2) They do not immediately begin automatically archiving the audit data.

Either mistake leads to the situation where the audit data is, by far, the largest component of disk usage. And backups. And the time to make backups.

Deciding to start archiving the audit data months (or years) down the road because it is now *huge* and someone finally noticed (perhaps because your db crashed when you ran out of disk space) is very, very painful.

On the bright side it is possible to archive a little bit at a time. Which means that you can do it during off hours to minimize the impact on users.

On the not so bright side it is very, very slow. I once spent a couple of months running audit archive processes for a customer every night. I automated it of course but it still took a lot of monitoring and tweaking to optimize the nightly runs based on how far it got the previous night.

In another situation it was easier to dump & reload the non-audit data into a new database and leave the old audit data behind. Nobody was ever looking at it anyway.
 
Top