jimbus has asked for the wisdom of the Perl Monks concerning the following question:

I've got several tables that are essentially log files who's data is irrelevant after a week or two, and I'm wondering if there is a "best practice" for cleaning up databases? I'm using a lot of Perl at the moment and am leaning toward throwing a script in cron that uses DBI to delete any thing older than X days or weeks old. But I wondered if there was some facility in mysql or something I was missing that was more appropriate. Thanks, Jimbus
Never moon a werewolf!

Replies are listed 'Best First'.
Re: Using Perl to clean up DBs?
by davidrw (Prior) on Jul 13, 2005 at 19:15 UTC
    if it's just a partial truncation, you can just do it directly (no perl) from cron with the mysql cmdline utility. I don't know the syntax offhand so i'll use postgres as an example cron entry: 
    0 1 * * * psql -U username dbname -c "delete from yourtable where now(
+) - logdate > 8"

    [download]
    As for how to whack logs, you might consider just dumping last week's to a temp table.. or maybe dump to disk before you delete .. depends on your needs/resources (disk space).

    Also, if there needs to be more logic involved, SQL::Abstract or Class::DBI could be big assests for the task.
[reply]
[d/l]
Re: Using Perl to clean up DBs?
by CountZero (Bishop) on Jul 13, 2005 at 20:05 UTC
    Have a look at rotatelog. It probably does all you want and it is written in Perl too!

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
Remember to re-org
by mugwumpjism (Hermit) on Jul 14, 2005 at 04:30 UTC

    Whichever approach you take, you need to make sure that the table is "re-organised" on a regular basis as well; otherwise those deleted rows may still consume space!

    See your DB manual for more. 

    $h=$ENV{HOME};my@q=split/\n\n/,`cat $h/.quotes`;$s="$h/."
."signature";$t=`cat $s`;print$t,"\n",$q[rand($#q)],"\n";

    [download]
[reply]
[d/l]
      On the other hand, they are log tables, the empty space will be reused to store new rows, so the space does not really go wasted. Reorganizing would just take space away from a table whose natural growth will see it extend itself again soon.
[reply]

        It really depends on the RDBMS and the type of the table. There is no absolute "this is what happens to space freed by deleted rows". It's something that you need to find out about, preferably before all of your table space is gone and you don't know why. 

        $h=$ENV{HOME};my@q=split/\n\n/,`cat $h/.quotes`;$s="$h/."
."signature";$t=`cat $s`;print$t,"\n",$q[rand($#q)],"\n";

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom