1

I've recently inherited a MySQL database that is growing. It's just over a billion records and the performance of one of the tables is abysmal. I would like to change databases eventually, but I do not have the time to do so right now.

The creator of the database decided to use BIGINTs in place of TINYINTs or SMALLINTs, chose poor indices, didn't partition the table (when it could really use one). Unfortunately, every time I try to ALTER this table, it locks it and the alter could run for days, which is something that I can't really have, because the table is constantly growing and needs to be accessible.

I'm using MySQL 5.7.5 and even if I choose an 'In Place' algorithm or no lock, it still seems to affect the performance OR it will run and never finish, like it times-out or something.

Is there a way to quickly alter a table without having much downtime?

Here is the CREATE TABLE:

CREATE TABLE `devicelog` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` bigint(20) unsigned NOT NULL,
  `system_id` bigint(20) unsigned NOT NULL,
  `device_id` bigint(20) unsigned NOT NULL,
  `field_id` bigint(20) unsigned NOT NULL,
  `val` double DEFAULT NULL,
  `valid` tinyint(1) NOT NULL,
  `timestmp` datetime NOT NULL,
  `unixtime` bigint(20) unsigned DEFAULT NULL,
  `interval5` bigint(20) unsigned DEFAULT NULL,
  `interval15` bigint(20) unsigned DEFAULT NULL,
  `interval60` bigint(20) unsigned DEFAULT NULL,
  `interval1440` bigint(20) unsigned DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `site_id` (`site_id`),
  KEY `system_id` (`system_id`),
  KEY `device_id` (`device_id`),
  KEY `field_id` (`field_id`,`device_id`,`system_id`,`site_id`,`valid`),
  KEY `unixtime` (`unixtime`),
  KEY `created_timestmp` (`created`),
  KEY `timestmp` (`timestmp`)
) ENGINE=InnoDB AUTO_INCREMENT=1012220164 DEFAULT CHARSET=utf8 COMMENT='Device log.'
  • Please run the SHOW CREATE TABLE mytable\G. I hope it has a TIMESTAMP or DATETIME in it. I think I can help you on this one. – RolandoMySQLDBA May 18 '15 at 20:12
  • @RolandoMySQLDBA I've edited the post to show the CREATE TABLE. It does have a DATETIME, so hopefully that'll help. – Kevin Evans May 18 '15 at 20:18
  • If you wanted this partitioned, which datetime would you want to partition by, created or timestmp ? – RolandoMySQLDBA May 18 '15 at 20:23
  • I'd like to use timestmp to partition it. – Kevin Evans May 18 '15 at 20:23
  • Question: What is the unixtime field ??? – RolandoMySQLDBA May 18 '15 at 20:40
3

There are two tools you can use to do this.

1) Percona's PT-ONLINE-SCHEMA-CHANGE which can be found here (Percona is a very big hitter in the MySQL world).

and there is also

2) Shlomi Noach's oak-online-alter-table which can be found here. Noach's c.v. is also impressive.

Public awards Oracle Technologist of the Year: Developer award Oracle ACE MySQL Community Member of the Year, 2009 award.

Obviously partitioning could also help your speeds, but if you can't afford to partition now, these tools may be of assistance to you.

1

ALTER TABLE to partition the table is out of the question here. What you really need is a set of stored procedures that will migrate the data in a new table with minimal downtime (less than 10 seconds).

CREATE NEW PARTITION TABLE

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_DeviceLog_Create` $$
CREATE PROCEDURE `sp_DeviceLog_Create`(
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64),
  DaysBack INT,
  DaysPast INT
)
ThisStoredProcedure:BEGIN

  DECLARE Target_DBTB,tag1,tag2,comma VARCHAR(1024);
  DECLARE today,first_date,last_date DATE;

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

  SET today = DATE(NOW());
  SET first_date = today - INTERVAL DaysBack DAY;
  SET last_date  = today + INTERVAL DaysPast DAY;

  SET @sql = CONCAT('DROP TABLE IF EXISTS ',Target_DBTB);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ',Target_DBTB,' (');
  SET @sql = CONCAT(@sql,'`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,');
  SET @sql = CONCAT(@sql,'`site_id` bigint(20) unsigned NOT NULL,');
  SET @sql = CONCAT(@sql,'`system_id` bigint(20) unsigned NOT NULL,');
  SET @sql = CONCAT(@sql,'`device_id` bigint(20) unsigned NOT NULL,');
  SET @sql = CONCAT(@sql,'`field_id` bigint(20) unsigned NOT NULL,');
  SET @sql = CONCAT(@sql,'`val` double DEFAULT NULL,');
  SET @sql = CONCAT(@sql,'`valid` tinyint(1) NOT NULL,');
  SET @sql = CONCAT(@sql,'`timestmp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,');
  SET @sql = CONCAT(@sql,'`unixtime` bigint(20) unsigned DEFAULT NULL,');
  SET @sql = CONCAT(@sql,'`interval5` bigint(20) unsigned DEFAULT NULL,');
  SET @sql = CONCAT(@sql,'`interval15` bigint(20) unsigned DEFAULT NULL,');
  SET @sql = CONCAT(@sql,'`interval60` bigint(20) unsigned DEFAULT NULL,');
  SET @sql = CONCAT(@sql,'`interval1440` bigint(20) unsigned DEFAULT NULL,');
  SET @sql = CONCAT(@sql,'`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,');
  SET @sql = CONCAT(@sql,'PRIMARY KEY (`id`,`timestmp`),');
  SET @sql = CONCAT(@sql,'KEY `site_id` (`site_id`),');
  SET @sql = CONCAT(@sql,'KEY `system_id` (`system_id`),');
  SET @sql = CONCAT(@sql,'KEY `device_id` (`device_id`),');
  SET @sql = CONCAT(@sql,'KEY `field_id` (`field_id`,`device_id`,`system_id`,`site_id`,`valid`),');
  SET @sql = CONCAT(@sql,'KEY `unixtime` (`unixtime`),');
  SET @sql = CONCAT(@sql,'KEY `created_timestmp` (`created`),');
  SET @sql = CONCAT(@sql,'KEY `timestmp` (`timestmp`)');
  SET @sql = CONCAT(@sql,') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''Device log.''');
  SET @sql = CONCAT(@sql,' PARTITION BY RANGE (UNIX_TIMESTAMP(timestmp)) (');

  SET comma = '';
  SET @dt1 = first_date;
  WHILE @dt1 <= last_date DO
    SET @dt2 = @dt1 + INTERVAL 1 DAY;
    SET tag1 = CONCAT('p',LCASE(LEFT(DATE_FORMAT(@dt1,'%Y_%m_%d'),11)));
    SET tag2 = DATE_FORMAT(@dt2,'%Y-%m-%d 00:00:00');
    SET @sql = CONCAT(@sql,comma,'PARTITION ',tag1,' VALUES LESS THAN ( UNIX_TIMESTAMP(',QUOTE(tag2),') ) ENGINE=InnoDB');
    SET comma = ',';
    SET @dt1 = @dt1 + INTERVAL 1 DAY;
  END WHILE;
  SET @sql = CONCAT(@sql,',PARTITION p99999999999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB)');

  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

APPEND NEW PARTITION

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_DeviceLog_Append` $$
CREATE DEFINER=`root`@`localhost` 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 p99999999999');
  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 p99999999999 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 ;

REMOVE OLD PARTITION

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_DeviceLog_Rotate` $$
CREATE DEFINER=`root`@`localhost` 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 ;

COPY DATA FROM LIVE TABLE

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_DeviceLog_Copy` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Partition_Table_Copy`(
  SourceDB VARCHAR(64),
  SourceTB VARCHAR(64),
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64),
  ChunkSize INT
)
ThisStoredProcedure:BEGIN

  DECLARE Source_DBTB,Target_DBTB,SQLStmt VARCHAR(1024);
  DECLARE first,final,lastidtocopy INT;

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

  SET @flush_sql = CONCAT('FLUSH TABLES ',Target_DBTB);

  SET @sql = CONCAT('SELECT id INTO @first FROM ',Source_DBTB,' ORDER BY id LIMIT 1');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = CONCAT('SELECT id INTO @lastidtocopy FROM ',Source_DBTB,' ORDER BY id DESC LIMIT 1');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @final = @first + ChunkSize;
  SET first = @first;
  SET final = @final;
  SET lastidtocopy = @lastidtocopy;
  SET @flush_count = 0;
  SET @flush_limit = 25;

  WHILE first < lastidtocopy DO
    SET @sql = CONCAT('INSERT INTO ',Target_DBTB);
    SET @sql = CONCAT(@sql,' SELECT * FROM ',Source_DBTB);
    SET @sql = CONCAT(@sql,' WHERE id >= ',first,' AND id < ',final);
    SET @sql = CONCAT(@sql,' AND timestmp IS NOT NULL');
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    SET first = final;
    SET final = first + ChunkSize;
    SET @flush_count = @flush_count + 1;
    IF @flush_count = @flush_limit THEN
      PREPARE s FROM @flush_sql; EXECUTE s; DEALLOCATE PREPARE s;
      SET @flush_count = 0;
    END IF;
  END WHILE;
  IF @flush_count > 0 THEN
    PREPARE s FROM @flush_sql; EXECUTE s; DEALLOCATE PREPARE s;
  END IF;

END $$

DELIMITER ;

SWAP IN NEW TABLE

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_DeviceLog_Swap` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Partition_Table_Swap`(
  SourceDB VARCHAR(64),
  SourceTB VARCHAR(64),
  TargetDB VARCHAR(64),
  TargetTB VARCHAR(64)
)
ThisStoredProcedure:BEGIN

  DECLARE Source_DBTB,Switch_DBTB,Target_DBTB VARCHAR(1024);

  SET Source_DBTB = CONCAT(SourceDB,'.',SourceTB);
  SET Target_DBTB = CONCAT(TargetDB,'.',TargetTB);
  SET Switch_DBTB = CONCAT(SourceDB,'.',SourceTB,'_offline');

  SET @maxid = 0;
  SET @sql = CONCAT('SELECT id INTO @maxid FROM ',Target_DBTB,' ORDER BY id DESC LIMIT 1');
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = CONCAT('RENAME TABLE ',Source_DBTB,' TO ',Switch_DBTB);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = CONCAT('INSERT INTO ',Target_DBTB,' SELECT * FROM ',Switch_DBTB,' WHERE id > ',@maxid);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @sql = CONCAT('RENAME TABLE ',Target_DBTB,' TO ',Source_DBTB);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

END $$

DELIMITER ;

EXECUTING EVERYTHING

USE mydata
CALL sp_DeviceLog_Create(database(),'newdevicelog',35,1);
SHOW CREATE TABLE newdevicelog\G
CALL sp_DeviceLog_Copy(database(),'devicelog',database(),'newdevicelog',25000);
CALL sp_DeviceLog_Swap(database(),'devicelog',database(),'newdevicelog');

EPILOGUE

While running sp_DeviceLog_Copy, new rows of data have been added to your DeviceLog table. When you run sp_DeviceLog_Swap, it gets those extra rows, adds them, and swaps them. Your downtime should be like 5-10 seconds.

When done, your new table will be live. The old table will be called devicelog_offline. When your app is writing data cleanly to the new devicelog table for a couple of days, you can run DROP TABLE devicelog_offline;

GIVE IT A TRY !!!

NOTE: You will need an event to auto rotate old partitions every day

DELIMITER $$

CREATE EVENT Rotate_Daily_Partitions
  ON SCHEDULE
  EVERY 1 DAY
  STARTS DATE(NOW() + INTERVAL 1 DAY) - INTERVAL 15 MINUTE
  ON COMPLETION PRESERVE
DO
BEGIN

  DECLARE srcdb,srctb VARCHAR(64);

  SET srcdb = 'mydata';
  SET srctb = 'devicelog';
  CALL sp_DeviceLog_Append(srcdb,srctb);
  CALL sp_DeviceLog_Rotate(srcdb,srctb,35);

END $$

DELIMITER ;

SET GLOBAL event_scheduler = 'ON';

Remember to add this to my.cnf

[mysqld]
event_scheduler = 'ON';
  • 1
    Please test this on a Dev or Staging Server. Do everything but the swap. – RolandoMySQLDBA May 18 '15 at 21:09
  • The event will kick off at 11:45 PM every night. – RolandoMySQLDBA May 18 '15 at 21:09
  • If you want the change the BIGINTs into INTs, you do that in sp_DeviceLog_Create – RolandoMySQLDBA May 18 '15 at 21:12
  • Please note that I changed timpstmp from DATETIME to TIMESTAMP – RolandoMySQLDBA May 18 '15 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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