MySQL Partitioning – Choosing Table Fields to Partition On

MySQLpartitioning

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?

Best Answer

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.

MySQL Partitioning – Choosing Table Fields to Partition On

Best Answer

GIVE IT A TRY !!!

Related Question

Best Answer

GIVE IT A TRY !!!

Related Solutions

Sql-server – partitioning by datetime vs date vs int performance

Mysql – Table partition should not be selected

Related Question