List:General Discussion« Previous MessageNext Message »
From:Rick James Date:October 25 2012 8:09pm
Subject:RE: Help with purging old logs for each customer ID
View as plain text  
Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows at a time,
DELETEing any that need to be purged.  I would use a Perl or PHP loop, or write a stored
procedure.  More discussion of "huge deletes" (which this _could_ be):
    http://mysql.rjweb.org/doc.php/deletebig
(PARTITIONing does not apply in your case, as I understand it.)

I like the loop:

    SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
    DELETE FROM customers_log
        WHERE customer_log_id >= @left_off AND customer_log_id < @z
          AND created_on < NOW() - INTERVAL 90 DAY;
    sleep a few seconds (to be a nice guy)

Plus code to take care of iterating and terminating.

That loop could be done continually.

It seems that customer_id is irrelevant??  

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Thursday, October 25, 2012 11:46 AM
> To: mysql@stripped
> Subject: Help with purging old logs for each customer ID
> 
> I have a customer log table that is starting to rapidly fill up (we
> have hundreds of thousands of users, but many are transient, and use
> the service for a few months, or use the free trial and quit, etc.)
> 
> CREATE TABLE `customers_log` (
>   `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
>   `customer_id` int(10) unsigned default '0',
>   `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
>   `type`
> enum('View','Action','Admin','Search','Login','Logout','Access','Genera
> l','A
> PI'),
>   `source` enum('web','mobile','system'),
>   `body` text,
>   PRIMARY KEY  (`customer_log_id`),
>   KEY `created_on` (`created_on`),
>   KEY `customers_id` (`customer_id`)
> ) ENGINE=InnoDB
> 
> What I'd like to do now is make a 'rolling log' in that I want to
> DELETE any entries older than 90 days for EACH `customer_id`.
> 
> I'm not sure how to do that in a query? I'd rather not iterate over
> each customer_id if I can help it.
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

Thread
Help with purging old logs for each customer IDDaevid Vincent25 Oct
  • RE: Help with purging old logs for each customer IDRick James25 Oct
    • RE: Help with purging old logs for each customer IDDaevid Vincent25 Oct
      • RE: Help with purging old logs for each customer IDRick James25 Oct
        • RE: Help with purging old logs for each customer IDDaevid Vincent25 Oct