Pruning farLog / refObjects

[Colin Jones]

Our farLog table is now exceeding 2.5 million rows, which is starting
to have a noticeable effect on access in the webtop with some queries
taking 2-3 seconds to run. A few questions...

Is it safe to "prune" farLog?
Can we just pick an arbitrary date and delete records from it before
that date?
Do we also need to delete associated refObjects entries for the farLog
data?
What are the conseuqences of doing this (simply removal of audit
data?)

Cheers,
Colin.

[Jeff Coughlin]

Yes it is safe to purge the log table. Some people like to purge it by a specific filter type, but I just purge it for anything more than 1m records (set it to whatever number you want). Feel free to use my code below. It's for MSSQL, but shouldn't be difficult to update as needed. I run it as a nightly scheduled task (in a file called [project]/system/dmCron/purgeFarLogTable.cfm).

When you first run it, I suggest running it with higher numbers and whittling it down until you get it near the number you want to do nightly.

<cfsetting enablecfoutputonly="true" requesttimeout="1800" />
<!--- @@displayname: Purge farLog Table (leaving only the last 1m records) --->
<!--- @@author: Jeff Coughlin (jeff [at] jeffcoughlin.dot.com) --->

<cftransaction>
<cfquery name="qPurgeFarLog" datasource="#application.dsn#">
delete from farLog
where ObjectID not in (
select top 1000000 ObjectID
from farLog
order by datetimecreated desc)
</cfquery>
</cftransaction>

<cfoutput>
<h2>Purge complete</h2>
</cfoutput>

<cfsetting enablecfoutputonly="false" />

--
Jeff Coughlin

> --
> You received this message cos you are subscribed to "farcry-dev" Google group.
> To post, email: farcr...@googlegroups.com
> To unsubscribe, email: farcry-dev+...@googlegroups.com
> For more options: http://groups.google.com/group/farcry-dev
> --------------------------------
> Follow us on Twitter: http://twitter.com/farcry

[Blair McKenzie]

Jeff is right about farLog - it's only functional use is to make sure bots can't brute force the webtop login, which means that it is safe to prune as much as is appropriate for your application.

Leaving refObjects records there won't break FarCry, but they can have a performance impact, so it's a good idea to clean refObjects after manually deleting records. Here's a quick-and-easy query:
delete from refObjects where typename='farLog' and objectid not in (select objectid from farLog);

Blair

[Sean Coyne]

farLog is set to bRefObjects="false" so there should be no refObjects records for that typename.

[Colin Jones]

Thanks for the replies guys - it is set to false, but we seem to have
1.2mill farlog intries in refObjects - perhaps they are a throwback
from a previous version, and have now stopped...

Will look at adding in the scheduled to task to keep it pruned.

Cheers!
Colin.

[AJ Mercer]

I was using set() and only passing stProperties

so bAudit defaulted to TRUE

I have gone through and added bAudit=0 and that has significantly reduce the number of log entries.

Thanks for the pruning code - I too now have a scheduled task that removes anything older than 24 hours

--
You received this message cos you are subscribed to "farcry-dev" Google group.
To post, email: farcr...@googlegroups.com
To unsubscribe, email: farcry-dev+...@googlegroups.com
For more options: http://groups.google.com/group/farcry-dev
--------------------------------
Follow us on Twitter: http://twitter.com/farcry

--

AJ Mercer
<webonix:net strength="Industrial" /> | <webonix:org community="Open" />
http://twitter.com/webonix

Railo Community Manager

[Colin Jones]

One thing we have noticed is that it does remove the "audit log" data
on objects (accessible in the webtop from the miscellaneous tab on the
overview page of an item), so if you want some audit data on who did
what to a content item you may want to make sure you set the deletion
period appropriately.

[Jeff Coughlin]

That's exactly why I set it to only purge going back 1m records.  It's not ideal (I'd like to have that audit log), but it's a necessary tradeoff.

--

Jeff Coughlin