Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Using Perl to clean up DBs?

by jimbus (Friar)
on Jul 13, 2005 at 19:08 UTC ( [id://474656]=perlquestion: print w/replies, xml ) Need Help??

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"
    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.
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

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";
      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.

        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";

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://474656]
Approved by gellyfish
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others sharing their wisdom with the Monastery: (5)
As of 2024-03-29 11:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found