Re: Best way to clear stale data from very active audit tables

bobdurie_at_gmail.com wrote:
> Hi there, i have a java application using oracle's jdbc driver that
> has a bunch of audit style tables. We're experiencing significant
> issues with our application when the data within these tables is being
> purged, and i need to know a better method for designing the schema
> (or application) to better handle this situation.
>
> Typically:
> - our "audit" table is created with a bunch of fields, one being
> "auditdate" which is a timestamp with a typical index on it.
> - this table is continually insert'ed into
> - select queries are run against this table on a regular basis
> - once an hour our "purge" job runs that does:
> DELETE FROM audits WHERE auditdate < (CURRENT_TIMESTAMP + INTERVAL
> '-7' DAY(5))
>
> When this table gets upwards of 20million+ records and many inserts
> are happenning concurrently, the purge job hammers the system, and
> causes the subsequent inserts to slow to a crawl, not to mention the
> DELETE command above to start taking upwards of 10+ minutes to run.
>
> I've debated doing a SELECT auditID with the where above and deleting
> the records one by one, but that seems horribly inefficient. Stopping
> the inserts altogether during the purge is not really an option. Can
> someone suggest an alternate approach or design for this scenario?
>
> A few other points:
> - our application has several different "audit" style tables, each of
> them has similar requirements, some get purged every week, some every
> 2 hours. I'm hoping this approach is universal for all because all
> are experiencing similar problems.
> - our application can also run against sql server, which while fast,
> occasionally has deadlock issues doing the above purging, inserting,
> and selecting concurrently... but it is fast. This also means we aim
> to have db agnostic code but i realize this is not always possible.
>
> Thank you for any advice you can offer!!
>
> Bob

Hi Bob,

What version are you running?

Regards,

Steve Received on Mon Jul 23 2007 - 10:53:34 CDT