1

I'm having a tough time deciding whether or not to partition my table based on a unix timestamp or by a descriptor attribute. It's just over a billion rows currently.

My table basically contains:

  • BIGINT id
  • DOUBLE value
  • SMALLINT device_id
  • BIGINT unixtime
  • (and others...)

I can either choose to partition it based on the device_id or some modulus value on the unixtime field. On all of the examples I've seen on partitioning, everybody seems to do it on a timestamp field.

Right now, I'm constantly doing searches based on the device_id and a range of unixtime values: a user chooses a device, then picks out a time frame that they want to view (most of the time, it's the past 24 hours).

Would it even make sense to partition it based on the device_id column? Or would using the unixtime field be the way to go?

Improve this question
1
  • Just a warning: partitioning key has to be part of any unique (even primary) key you will ever have on that table - that means that partitioned table will allow the same id (if id was primary before partitioning applied) repeated for each partition if I understand it correctly. That may or may not be a problem for your case.
    – jkavalik
    Commented Jun 9, 2015 at 6:11

2 Answers 2

Reset to default
3

By all means, partition by unixtime. Why ???

  • You can separate data by day
  • You can archive the data of the oldest partition
  • This can be scheduled in the MySQL event scheduler for
    • adding new partitions
    • deleting old partitions
  • You drop the data of the oldest partition by DDL (ALTER TABLE tbalename DROP PARTITION partitionname) instead of DML (DELETE FROM tablename WHERE unixtime ...). See my answer to this post Partitioning and freeing disk space
  • If you are using MySQL 5.6, you can query against the specific partition instead of whole table.

Here is what I am going to do for you

Let say your table is as you gave as an example

USE mydatabase
CREATE TABLE mytable
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    value DOUBLE NOT NULL,
    device_id SMALLINT NOT NULL,
    unixtime INT UNSIGNED,
    PRIMARY KEY (id,unixtime),
    KEY device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(unixtime))
(PARTITION p2015_05_09 VALUES LESS THAN (1431230400) ENGINE = InnoDB,
 PARTITION p2015_05_10 VALUES LESS THAN (1431316800) ENGINE = InnoDB,
 PARTITION p2015_05_11 VALUES LESS THAN (1431403200) ENGINE = InnoDB,
 PARTITION p2015_05_12 VALUES LESS THAN (1431489600) ENGINE = InnoDB,
 PARTITION p2015_05_13 VALUES LESS THAN (1431576000) ENGINE = InnoDB,
 PARTITION p2015_05_14 VALUES LESS THAN (1431662400) ENGINE = InnoDB,
 PARTITION p2015_05_15 VALUES LESS THAN (1431748800) ENGINE = InnoDB,
 PARTITION p2015_05_16 VALUES LESS THAN (1431835200) ENGINE = InnoDB,
 PARTITION p2015_05_17 VALUES LESS THAN (1431921600) ENGINE = InnoDB,
 PARTITION p2015_05_18 VALUES LESS THAN (1432008000) ENGINE = InnoDB,
 PARTITION p2015_05_19 VALUES LESS THAN (1432094400) ENGINE = InnoDB,
 PARTITION p2015_05_20 VALUES LESS THAN (1432180800) ENGINE = InnoDB,
 PARTITION p2015_05_21 VALUES LESS THAN (1432267200) ENGINE = InnoDB,
 PARTITION p2015_05_22 VALUES LESS THAN (1432353600) ENGINE = InnoDB,
 PARTITION p2015_05_23 VALUES LESS THAN (1432440000) ENGINE = InnoDB,
 PARTITION p2015_05_24 VALUES LESS THAN (1432526400) ENGINE = InnoDB,
 PARTITION p2015_05_25 VALUES LESS THAN (1432612800) ENGINE = InnoDB,
 PARTITION p2015_05_26 VALUES LESS THAN (1432699200) ENGINE = InnoDB,
 PARTITION p2015_05_27 VALUES LESS THAN (1432785600) ENGINE = InnoDB,
 PARTITION p2015_05_28 VALUES LESS THAN (1432872000) ENGINE = InnoDB,
 PARTITION p2015_05_29 VALUES LESS THAN (1432958400) ENGINE = InnoDB,
 PARTITION p2015_05_30 VALUES LESS THAN (1433044800) ENGINE = InnoDB,
 PARTITION p2015_05_31 VALUES LESS THAN (1433131200) ENGINE = InnoDB,
 PARTITION p2015_06_01 VALUES LESS THAN (1433217600) ENGINE = InnoDB,
 PARTITION p2015_06_02 VALUES LESS THAN (1433304000) ENGINE = InnoDB,
 PARTITION p2015_06_03 VALUES LESS THAN (1433390400) ENGINE = InnoDB,
 PARTITION p2015_06_04 VALUES LESS THAN (1433476800) ENGINE = InnoDB,
 PARTITION p2015_06_05 VALUES LESS THAN (1433563200) ENGINE = InnoDB,
 PARTITION p2015_06_06 VALUES LESS THAN (1433649600) ENGINE = InnoDB,
 PARTITION p2015_06_07 VALUES LESS THAN (1433736000) ENGINE = InnoDB,
 PARTITION p2015_06_08 VALUES LESS THAN (1433822400) ENGINE = InnoDB,
 PARTITION p2015_06_09 VALUES LESS THAN (1433908800) ENGINE = InnoDB,
 PARTITION p2015_06_10 VALUES LESS THAN (1433995200) ENGINE = InnoDB,
 PARTITION p2015_06_11 VALUES LESS THAN (1434081600) ENGINE = InnoDB,
 PARTITION p2015_06_12 VALUES LESS THAN (1434168000) ENGINE = InnoDB,
 PARTITION p2015_06_13 VALUES LESS THAN (1434254400) ENGINE = InnoDB,
 PARTITION p2015_06_14 VALUES LESS THAN (1434340800) ENGINE = InnoDB,
 PARTITION p2015_06_15 VALUES LESS THAN (1434427200) ENGINE = InnoDB,
 PARTITION p2015_06_16 VALUES LESS THAN (1434513600) ENGINE = InnoDB,
 PARTITION p2015_06_17 VALUES LESS THAN (1434600000) ENGINE = InnoDB,
 PARTITION p2015_06_18 VALUES LESS THAN (1434686400) ENGINE = InnoDB,
 PARTITION p9999999999 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Please note that

  • unixtime must be part of the PRIMARY KEY.
  • unixtime should be INT UNSIGNED instead of BIGINT

This will make your table 30 days back and 10 days ahead. Of course, those extra 10 days should be empty all the time.

If you run the following code, this will create the stored procedures and events that will run every night

USE yourdatabase

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Partition_Table_Append` $$
CREATE PROCEDURE `sp_Partition_Table_Append`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64)
)
BEGIN

  DECLARE Target_DBTB,pname VARCHAR(1024);

  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);

  SET @dt1 = DATE(NOW()) - INTERVAL 7 DAY;

  SET @looking_for_opendate = 1;
  WHILE @looking_for_opendate = 1 DO
    SET @dt1 = @dt1 + INTERVAL 1 DAY;
    SET @dt2 = @dt1 + INTERVAL 1 DAY;
    SET pname = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@dt1,'%Y_%m_%d'),11)));
    SELECT COUNT(1) INTO @looking_for_opendate
    FROM information_schema.partitions
    WHERE table_schema   = TargetDB
    AND   table_name     = TargetTB
    AND   partition_name = pname;
  END WHILE;

  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' DROP PARTITION p9999999999');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' ADD PARTITION (PARTITION ',pname,' VALUES LESS THAN ( UNIX_TIMESTAMP(',QUOTE(@dt2),') ) ENGINE=InnoDB)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' ADD PARTITION (PARTITION p9999999999 VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB)');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
  SET @sql = CONCAT('FLUSH TABLES ',Target_DBTB);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Partition_Table_Rotate` $$
CREATE PROCEDURE `sp_Partition_Table_Rotate`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64),
  DaysToKeep INT
)
ThisStoredProcedure:BEGIN

  DECLARE Target_DBTB,OldPartitionToKeep,partitions_to_drop VARCHAR(1024);
  DECLARE FirstOfTheMonth,OldestMonthToKeep DATE;

  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);

  SET @today  = DATE(NOW());
  SET @oldest = @today - INTERVAL DaysToKeep DAY;
  SET OldPartitionToKeep = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@oldest,'%Y_%m_%d'),11)));

  SELECT COUNT(1) INTO @zapcount
  FROM information_schema.partitions
  WHERE table_schema   = TargetDB
  AND   table_name     = TargetTB
  AND   partition_name < OldPartitionToKeep;

  IF @zapcount = 0 THEN LEAVE ThisStoredProcedure; END IF;

  SELECT GROUP_CONCAT(partition_name) INTO partitions_to_drop
  FROM information_schema.partitions
  WHERE table_schema   = TargetDB
  AND   table_name     = TargetTB
  AND   partition_name < OldPartitionToKeep;

  SET @sql = CONCAT('ALTER TABLE ',Target_DBTB,' DROP PARTITION ',partitions_to_drop);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

DELIMITER $$

DROP EVENT IF EXISTS Append_Daily_Partitions $$
CREATE EVENT Append_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) - INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
ThisScheduledEvent:BEGIN
  DECLARE srcdb,srctb VARCHAR(64);
  DECLARE FoundCount INT;

  SET srcdb = DATABASE();

  DROP TABLE IF EXISTS PartitionedTablesToRotate;
  CREATE TABLE PartitionedTablesToRotate
  (
    id INT NOT NULL AUTO_INCREMENT,
    table_schema VARCHAR(64) NOT NULL,
    table_name VARCHAR(64) NOT NULL,
    days_to_retain INT NOT NULL DEFAULT 30,
    PRIMARY KEY (id),
    UNIQUE KEY DB_TBL_INDEX (table_schema,table_name)
  );
  INSERT INTO PartitionedTablesToRotate
  (table_schema,table_name)
  SELECT table_schema,table_name
  FROM information_schema.partitions
  WHERE table_schema=srcdb
  AND partition_name='p9999999999';

  SET @ndx = 0;
  SELECT COUNT(1) INTO @lastndx FROM PartitionedTablesToRotate;
  WHILE @ndx < @lastndx DO
    SET @ndx = @ndx + 1;

    SELECT table_name INTO srctb
    FROM PartitionedTablesToRotate WHERE id = @ndx;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.partitions
    WHERE table_schema = srcdb
    AND table_name = srctb
    AND partition_name = 'p9999999999';

    IF FoundCount = 1 THEN
      CALL sp_Partition_Table_Append(srcdb,srctb);
    END IF;
  END WHILE;

END $$

DELIMITER ;

DELIMITER $$

DROP EVENT IF EXISTS Rotate_Daily_Partitions $$
CREATE EVENT Rotate_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) + INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
ThisScheduledEvent:BEGIN
  DECLARE srcdb,srctb VARCHAR(64);
  DECLARE FoundCount,DaysToKeep INT;

  SET srcdb = DATABASE();

  SELECT COUNT(1) INTO FoundCount FROM information_schema.tables
  WHERE table_schema=srcdb AND table_name='PartitionedTablesToRotate';
  IF FoundCount = 0 THEN
    LEAVE ThisScheduledEvent;
  END IF;

  SET @ndx = 0;
  SELECT COUNT(1) INTO @lastndx FROM PartitionedTablesToRotate;
  WHILE @ndx < @lastndx DO
    SET @ndx = @ndx + 1;

    SELECT table_name,days_to_retain INTO srctb,DaysToKeep
    FROM PartitionedTablesToRotate WHERE id = @ndx;

    SELECT COUNT(1) INTO FoundCount
    FROM information_schema.partitions
    WHERE table_schema = srcdb
    AND table_name = srctb
    AND partition_name = 'p9999999999';

    IF FoundCount = 1 THEN
      CALL sp_Partition_Table_Rotate(srcdb,srctb,DaysToKeep);
    END IF;
  END WHILE;

END $$

DELIMITER ;

DELIMITER $$

SET GLOBAL event_scheduler = 'ON';

The events will do the following:

  • Append_Daily_Partitions will add new partition at 11:45 PM
  • Rotate_Daily_Partitions will delete old partition at 12:15 AM

Please install this on a Dev or Staging Server and verify to works for you.

GIVE IT A TRY !!!

Improve this answer
9
  • If I need data available on-hand for a year, should I just adjust days_to_retain to 365 for that table? Is that a bad idea having 365 partitions?
    – Kevin Evans
    Commented Jun 8, 2015 at 22:35
  • MySQL 5.6 can handle up to 8192 partitions, so it is possible. The tradeoff is that 8192 file handles must be open (For those using MyISAM that would double (16384)). You are probably better of doing monthly partitions, whihc is only 12 file handles per year, and have the rotation events launch at midnight on the first of the month.
    – RolandoMySQLDBA
    Commented Jun 9, 2015 at 0:03
  • Do you think 52 weekly rotations per year would be alright? What would change in the code, is it just the scheduling stuff?
    – Kevin Evans
    Commented Jun 9, 2015 at 17:28
  • Not only would you change the scheduling stuff (say run the event midnight Monday morning), but you need to make sure each partition name matches the timestamp for each Monday. In my answer, I have midnight 2015-05-10 in the p2015_05_09 partition. In the case of doing Mondays, each partition for Monday would actually be last Monday by name. EXAMPLE: Partition p2015_06_08 (1433736000) actually holds Jun 01 - Jun 7 data
    – RolandoMySQLDBA
    Commented Jun 9, 2015 at 17:30
  • CORRECTION on my example: To have Mondays, Partition p2015_06_01 would be defined as LESS THAN (1433736000) for the week of Jun 01 - Jun 07 because 1433736000 is the timestamp for midnight Jun 08.
    – RolandoMySQLDBA
    Commented Jun 9, 2015 at 17:38
0

Have a partition named future that can collect any future data. But try to build the "tomorrow" partition just before midnight. (More in a minute.)

Don't DROP future and then rebuild two partitions; instead do it all in one atomic REORGANIZE PARTITION to convert one partition (future) into pnnnnnnnn (tomorrow) and new copy of future (VALUES LESS THAN MAXVALUE).

In the same script decide if it is time to drop the oldest partition.

Do not have more than one partition in the future -- some operations open all partitions (unnecessarily). Just one will suffice, and the REORGANIZE will repair the structure if you are late running the script. Similarly, don't have more than, say, 50 partitions. Make them weekly or monthly instead of daily, if necessary.

I cover the details in my blog.

For the one query you mentioned, have

KEY device_id (device_id, unixtime)

But if you are expecting a performance improvement, you may be disappointed. (device_id, unixtime) for a lookup works just as well on a non-partitioned table as the partitioned one being discussed.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.