The Puppet Labs Issue Tracker has Moved: https://tickets.puppetlabs.com

Bug #14664

Option for MySQL partitioning to make cleanup faster/more efficient; index on "time"

Added by Sheeri Cabral over 2 years ago. Updated over 2 years ago.

Status:Needs DecisionStart date:05/23/2012
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:-
Target version:-
Keywords: Affected URL:
Branch: Affected Dashboard version:

Description

I understand that not everyone is using MySQL….however, for those that are….the current procedure to clean up the database is to run commands that DELETE rows, and then run OPTIMIZE to defragment.

There is a better way. In MySQL 5.1 and up, you can use partitioning. What partitioning does is creates one physical file per partition, and it means you can easily do a DROP PARTITION statement and it takes only the time it takes to delete the file (MySQL does this for you), and there’s no defragmentation (or lengthy locking!) to deal with.

Basically I’d recommend making one partition per day, and then the cleanup script can do a DROP partition.


Even if you don’t go this route, I noticed that there’s no appropriate index. This command takes a LONG time:

DELETE FROM reports WHERE (time < ‘2012-05-19 13:54:11’)

Because there’s no index on time. So at the very least, there should be an index on time.

Here’s and example of partitioning:

mysql> select TO_DAYS(‘2012-05-23’); +———————————–+ | TO_DAYS(‘2012-05-23’) | +———————————–+ | 735011 | +———————————–+ 1 row in set (0.31 sec)

ALTER TABLE reports

 PARTITION BY RANGE( TO_DAYS(`time`) ) (
     PARTITION p20120522 VALUES LESS THAN (735011),
     PARTITION p20120523 VALUES LESS THAN (735012),
     PARTITION p20120524 VALUES LESS THAN (735013),
     PARTITION p20120525 VALUES LESS THAN (735014)
 );

And then when you want to cleanup the 5/22 partition, you do ALTER TABLE reports DROP PARTITION p20120522;

To add new partitions, do: ALTER TABLE ADD PARTITION (PARTITION p20120526 VALUES LESS THAN (735015));

I would add cron scripts or MySQL events to do the adding of partitions.

History

#1 Updated by Kelsey Hightower over 2 years ago

  • Status changed from Unreviewed to Needs Decision

Sheeri,

Thanks for taking the time to write this up! I’m going to change the status of this ticket to “Needs Decision” so that we can use the info provided to see how we can improve the Dashboard.

#2 Updated by Sheeri Cabral over 2 years ago

There are some gotchas with MySQL partitioning that I’m running into, but in the end, there’s a problem, because of the foreign key from resource_statuses to reports – MySQL doesn’t yet support partitioning and foreign keys on the same table. :(

But it’s something to keep an eye out for…..and I’d definitely put an index on time.

Also available in: Atom PDF