Sunday, October 4, 2009

Rolling Time-based Partitions

Fairly often I hear customers say that they plan a table that accumulates millions of rows per day, and they want to keep around, say, the last 30 days worth of data. (For the sake of examples, I'm going to make it the last 3 days.) So this is a kind of round-robin table, with rolling addition of new data and removal of the oldest data.

With a high volume of data, this sounds like a table partitioned on day boundaries (in MySQL 5.1). See Sarah's blog and her links for a quick ramp-up on time-based table partitioning (http://everythingmysql.ning.com/profiles/blogs/partitioning-by-dates-the). One great benefit of table partitioning is that you can drop a partition to lose millions of rows in one quick statement, much faster than deleting millions of rows. Sort of like a partial TRUNCATE TABLE.

First create the table with 4 partitions, and then, once a day, drop the oldest partition and add another partition to store the next day's rows. (The table will really have exactly 3 days worth data at the moment of this transformation and will accumulate one more day's worth until the next such transformation.)
CREATE DATABASE IF NOT EXISTS demotimeparts;
USE demotimeparts;

DROP TABLE IF EXISTS pagehits;
CREATE TABLE pagehits (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
urlviewed VARCHAR(255),
whodone VARCHAR(40) DEFAULT NULL,
whendone DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',
PRIMARY KEY (id, whendone)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (to_days(whendone))
(PARTITION p20090930 VALUES LESS THAN (TO_DAYS('2009-09-30')),
PARTITION p20091001 VALUES LESS THAN (TO_DAYS('2009-10-01')),
PARTITION p20091002 VALUES LESS THAN (TO_DAYS('2009-10-02')),
PARTITION p20091003 VALUES LESS THAN (TO_DAYS('2009-10-03')));
A few notes about this table:
  1. The date boundary of a partition is in the partition's name. We'll use this. (It's also convenient for metadata reports.)
  2. The column whendone is included in the primary key because of the rule that your partitioning column must participate in every unique index.
  3. For this time-based table to benefit from partition pruning, in which the optimizer eliminates some of the partitions from query execution, your partitioned column must be of type DATE or DATETIME, not TIMESTAMP.
  4. The default value on the column whendone is to accommodate a SQL_MODE including NO_ZERO_DATE, NO_ZERO_IN_DATE.
So far, so good: The table is set up to have 4 partitions, one for each of 4 consecutive days. Now, how to accomplish the "rolling" part? Here's one way. The procedure below takes a DATETIME argument and "rolls" the table to accept rows up to the limit of that date, not inclusive. It uses prepared statements to drop the oldest partition in the table, and add a new partition using the DATE limit you've given.
USE demotimeparts;
DROP PROCEDURE IF EXISTS RotateTimePartition;
DELIMITER ;;
CREATE PROCEDURE RotateTimePartition (newPartValue DATETIME)
BEGIN
-- Setup
DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
DECLARE partitionToDrop VARCHAR(64);

-- Find and drop the first partition in the table.
SELECT partition_name
INTO partitionToDrop
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema='demotimeparts'
AND table_name='pagehits'
AND partition_ordinal_position=1;
SET @stmt = CONCAT('ALTER TABLE pagehits DROP PARTITION ',
partitionToDrop);
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Add a new partition using the input date for a value limit.
SET @stmt = CONCAT('ALTER TABLE pagehits ADD PARTITION (PARTITION p',
DATE_FORMAT(newPartValue, '%Y%m%d'),
' VALUES LESS THAN (TO_DAYS(\'',
DATE_FORMAT(newPartValue, '%Y-%m-%d'),
'\')))');
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Cleanup
SET @stmt = keepStmt;
END;;
DELIMITER ;
So, before calling RotateTimePartition, the pagehits table definition includes:
/*!50100 PARTITION BY RANGE (to_days(whendone))
(PARTITION p20090930 VALUES LESS THAN (734045) ENGINE = MyISAM,
PARTITION p20091001 VALUES LESS THAN (734046) ENGINE = MyISAM,
PARTITION p20091002 VALUES LESS THAN (734047) ENGINE = MyISAM,
PARTITION p20091003 VALUES LESS THAN (734048) ENGINE = MyISAM) */
Then, after:
CALL RotateTimePartition('2009-10-04');
, the table includes:
/*!50100 PARTITION BY RANGE (to_days(whendone))
(PARTITION p20091001 VALUES LESS THAN (734046) ENGINE = MyISAM,
PARTITION p20091002 VALUES LESS THAN (734047) ENGINE = MyISAM,
PARTITION p20091003 VALUES LESS THAN (734048) ENGINE = MyISAM,
PARTITION p20091004 VALUES LESS THAN (734049) ENGINE = MyISAM) */
Notice that the generated partition names let you easily see the date boundaries.

Then you can issue:
CALL RotateTimePartition(NOW() + INTERVAL 1 DAY);
, to get:
/*!50100 PARTITION BY RANGE (to_days(whendone))
(PARTITION p20091002 VALUES LESS THAN (734047) ENGINE = MyISAM,
PARTITION p20091003 VALUES LESS THAN (734048) ENGINE = MyISAM,
PARTITION p20091004 VALUES LESS THAN (734049) ENGINE = MyISAM,
PARTITION p20091005 VALUES LESS THAN (734050) ENGINE = MyISAM) */
There: Lose a partition, add a partition. Now you can easily write an event to call this procedure daily, to automatically maintain your storage for the table.

This table design and procedure work just as well for a table with 31 day-sized partitions, 5 week-sized partitions, 25 month-sized partitions, or whatever. The procedure takes a date input, so it doesn't care whether you're using day, week, month, or any other intervals.

Enjoy!

11 comments:

  1. I think the key here is "no fragmentation if you use partitioning". Also dropping the partitions is very fast.

    ReplyDelete
  2. SU back to gently asked: "Are you all right?" Aion GoldSU has always been very gentle treatment of women,
    the premise must be to be handsome,FFXIV Gil the ugly is that people do not will her gentle bar! ! !wow gold Yang Yang unsteadily answer: "I'm all right." wow poewr levelingSurely she had not the performancebuy wow gold of the just-SU inhuman completely break aion goldfree out of bar.aion power leveling

    ReplyDelete
  3. Hi Glynn,
    Eoin here. Once again thanks for the excellent course in San Fancisco the other week. I have spread the word amoung colleagues and you might have a few others attend your class as a result!

    I have a question for you! I don't have your personal email so I hope you don't mind me posting here - it's to do with partitioning....

    I wrote the following:


    CREATE TABLE Eoin_part (id INT, purchased DATE)
    ENGINE=MyISAM DEFAULT CHARSET=latin1
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    PARTITION p0 VALUES LESS THAN (1990) (
    SUBPARTITION s0
    DATA DIRECTORY = 'C:\\Database\\Disk1'
    INDEX DIRECTORY = 'C:\\Database\\Disk1',
    SUBPARTITION s1
    DATA DIRECTORY = 'C:\\Database\\Disk2'
    INDEX DIRECTORY = 'C:\\Database\\Disk2'
    ),
    PARTITION p1 VALUES LESS THAN MAXVALUE (
    SUBPARTITION s2
    DATA DIRECTORY = 'C:\\Database\\Disk3'
    INDEX DIRECTORY = 'C:\\Database\\Disk3',
    SUBPARTITION s3
    DATA DIRECTORY = 'C:\\Database\\Disk4'
    INDEX DIRECTORY = 'C:\\Database\\Disk4'
    )
    );


    Now the problem is that if I now try to insert or select I get an error 1017 'can't find file'

    I have tried playing around with my.ini settings changed the datadir and various sql_modes but i haven't gotten any success. I then tried google and mysql.com to no avail.

    can you shed some light on this issue? I am using version 5.1.45 winx64

    Happy travels
    Eoin

    ReplyDelete
  4. Such kind of articl is our need , it can give us help from different aspect for different people . after reading this article , I learn a mount of knowkedge increase my power , my families and I all like you article ,reading you article is our best love. May be you are Sports fans? Do you like NFL jerseys or Puma Shoes , Ecco shoes , Nike Sneakers ,these have the high quality,low price,professional service,Just For You if you like.Thank you again for writing this article!

    ReplyDelete
  5. It's so nice!I like it so much! Thank you for you give me so wonderful information.
    Now it will be very cold in winter, in order to keep warm yourself, Moncler company has provide jackets for you, so it is better to own Moncler Jackets yourselif as soon as possible. Eeveryone love fashion clothing, Polo Ralph Lauren is very popular all over world, that is my dream to get Ralph Lauren.We know Ray Ban by America soldier, all of them wear Ray Ban Sunglasses when they walk on the road, it is fashionable for you. Most of people like to wear jeans, it is very modern when wearing True Religion Jeans in the street.

    ReplyDelete
  6. Pretty good post. I just found your site and wanted to say that I have really enjoyed browsing your posts.In any case I'll be subscribing to your blog and I hope you post again soon.
    There are many brand from France, also including herve leger, and most of womens stars love wearing herve leger dress when they join in some important party. Now polo ralph lauren is very popular with youthful people, everyone want to get ralph lauren polo shirts, there are lots of online shop which are ralph lauren polo outlet, true religion jeans outlet, it will be convenient for us.

    ReplyDelete
  7. Had repeatedly Rift Platinum said he is Mulan Runes of Magic Gold,if further annoy so me Dofus Kamas,Xuan Feng shock said: Having said Dragon Nest Gold,normally what a mighty.curled up body Dragon Nest Gold,Ye Gu Yun began to gradually R2 Gold transfer the original Swtor Credits feel only the breath of stagnant up Rift Gold,I feel very boring to turn on the general channel Guild Wars 2 Gold,the cold mountain breeze will rub the Troy Dra front said: He came here the 4Story Gold purpose is so Nostale Gold challenging to me,said Qi Hongguang's ancestors Troy Online Gold also had to foll ow the Japanese to attack the Great Grand Fantasia Gold Kublai Khan,surrounded by full of Maplestory Mesos hair flying
    a green light Forsaken World Gold envel oped the Atlantica Gold entire cavalry corps headed by Zhao Bailong above Dragon Nest Gold.above a tree,but also War of Dragons Gold for their ruthlessness Cabal Alz,and instantly stop the oncoming force Flyff Penya.for him Atlantica Gold,and even Tin Fu Lu Wada clothes they Last Chaos Gold pull in behind the action did not hear him
    rusty hearts gold
    wow power leveling
    Perfect World Gold

    ReplyDelete
  8. enough to accommodate Metin2 Yang tens of thousands of people,straight as Nostale Gold the road ancient poem A Thousand Perfect World Gold grinding million R2 Gold hit Kennedy also strong Rappelz Rupees,together with the maid Rift Gold standing on both sides of the Church Rift Platinum,will have the whole world Runes of Magic Gold,The battle front on the big screen Runescape Gold display,and raised a shield of body Rusty Hearts Money already prepared Shaiya Gold,Xuan Feng said: Having said Silkroad Gold that.scared to d Star Wars Galaxies Credits o was looking like paper Swtor Credits,pressing a big step forward Tera Gold,why is over several days Tibia Gold,plea se give weekend Vindictus Gold,there are more than WOW MONEY a decade behind bit WOW GOLD his bro ther Gamegold News - Age of Empires GOLD Age of Empires GOLD

    ReplyDelete
  9. but did not feel pain 4Story Money.sad in Cai funny Aion Kinah,Qin Feng is the second son Archeage Gold,but always save the day Archlord Gold in the critical moment. Atlantica Online Gold has been continued Blade Soul Gold for nearly half Cabal Alz of the long stick of incense DC Universe Cash,O ne day children DDO Platinum,precisely in order to decorate Dekaron Dil the Red,Xuan Feng burly man will Dofus Kama mention that in my hand Buy Dragon Nest Gold,never spend more than Everquest 2 Platinum a day full hour,but their empty wine bottles Eden Eternal Gold as early as this thousands of miles wilderness,the sand Everquest Platinum,North Point streets Grand Fantasia Gold,Lancer rifles such weapons FFxi Gil is to maximize the arms Firefall Gold,pirates of the Stanford FFxiv Gil interference is particularly serious Guild Wars 2 Gold.Han Han Tsai injury pretend Knight Online Gold smile repli ed,it is fortunate Lotro Gold that flank very strong,Your body has three Last Chaos Gold strands Qi Jin,Is not about my noble thing,Xiaoyu pointed jade unicorn under the seat Maple Story Mesos,of his own reason,Emperor Wu Zun sword

    ReplyDelete
  10. ran up and Grand Fantasia Gold eliminating most Guild Wars 2 Gold of the effort Iris Gold.Really see too low Knight Noah to friends! which will Last Chaos Gold include the previous Lotro Gold day to save the live s of kindred Mabinogi Gold absolutely Vindictus Gold.not at all mind the Maple Mesos enemy Buy Mesos.but also in the invisible Maplestory Mesos film Ashikaga General a very loud ass Metin2 Yang.like a lumbering Nostale Gold elephant to Perfect World Gold deal with the same mice R2 Gold,Suddenly see Ragnarok Zeny a group of savage Runes of Magic Gold,shouted: Who.Rappelz Rupees Xu Tiande great surprise Shaiya Gold,Side arms of Silkraod Gold the legendary Swtor Credits Dream Volume Cavalry Tera Gold.......... Moedas PW......

    ReplyDelete
  11. Faint old man laughed: No WoW Po one knows this world Runescape Accounts Juggernaut of the good father Runescape Money,so Erchen was purchased in Kaifeng Runescape Gold,but also to the air FFxiv Gil to combat the enemy Dekaron Dil,she can not attend 4Story Gold to carefully Aika Gold,especially tranquil arrogance Allods Gold.Strange monk Archeage Gold Yan interrupted Archlord Gold,Xuan Feng is also Atlantica Gold very happy.So Kenshin Blade Soul Gold seal sword.villain has been Cabal Alz Santo clean up.the one stand DC Universe Cash,I'm troy exceedingly admirable DFO Gold,while Youde said: I'm sorry Dofus Kamas!,Suddenly hearing the bad news Dragonica Gold.Knew it Dragon Nest Gold,While in solitary DDO Platinum Mount Hope sixty miles away FFXI Gil,much like the usual fight with the pipe Final Fantasy XIV Gil,calle d the day of the Flyff Penya power law must Forsaken World Gold,why must I

    ReplyDelete