How to Minimize Downtime for an ALTER in MySQL

alter-tableMySQL

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.'

Best Answer

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.

Related Question