1293500 - ALTER TABLE treeherder.job_details to fix column width and add unique index

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Pythian Team73]

As per request from [e-mail] in #data room.

The treeherder job_detail table needs two columns to be shrunk in width and a unique index added afterwards.

ALTER TABLE job_detail MODIFY COLUMN title varchar(70), MODIFY COLUMN value varchar(125);
ALTER TABLE job_detail ADD UNIQUE KEY `title_value_job_uniq` (`title`,`value`,`job_id`);

The preference would be to use pt-online-schema-change to do both ALTER TABLE commands to limit the impact on the database due to locking.

Comment 1

[Pythian Team73]

Here are the suggested commands to run for pt-osc (percona online-schema-change).

Assumptions: 
1. Production server has pt-tools installed
2. mysql database user root has necessary privileges to connect to slaves to track lag.
3. Foreign key on job table with be auto-handled by pt-osc.
4. Production database name is treeherder.

Here is some more detail

-- Changes required

(09:34:08) moen: (08:47:36) camd: but I still need to modify the field lengths on title and value
(09:34:08) moen: (08:47:42) camd: title to 70 and value to 125
(09:34:08) moen: (08:47:50) camd: then the index will be title, value and job_id

ALTER TABLE job_detail MODIFY COLUMN title varchar(70), MODIFY COLUMN value varchar(125);
ALTER TABLE job_detail ADD UNIQUE KEY `title_value_job_uniq` (`title`,`value`,`job_id`);


-- table on production from Cam's post.

*************************** 1. row ***************************
       Table: job_detail
Create Table: CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(512) COLLATE utf8_bin NOT NULL,
  `url` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `job_detail_d697ea38` (`job_id`),
  CONSTRAINT `job_detail_job_id_4e70fcf2620116d_fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=193416958 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

-- table on stage already changed.

mysql> use treeherder_stage;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table job_detail\G
*************************** 1. row ***************************
       Table: job_detail
Create Table: CREATE TABLE `job_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(125) COLLATE utf8_bin DEFAULT NULL,
  `url` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `job_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title_value_job_uniq` (`title`,`value`,`job_id`),
  KEY `job_detail_d697ea38` (`job_id`),
  KEY `idx_title` (`title`),
  KEY `idx_value` (`value`),
  CONSTRAINT `job_detail_job_id_4e70fcf2620116d_fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=166141567 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

-- suggested pt-osc commands

-- ALTER TABLE shrink columns

time pt-online-schema-change  h=localhost,D=treeherder,t=job_detail,u=root --dry-run --alter "MODIFY COLUMN title varchar(70), MODIFY COLUMN value varchar(125)" --max-lag 2 --check-interval 5 --critical-load Threads_running=50,Threads_connected=50 --max-load Threads_connected=50,Threads_running=50 

time pt-online-schema-change  h=localhost,D=treeherder,t=job_detail,u=root --execute --alter "MODIFY COLUMN title varchar(70), MODIFY COLUMN value varchar(125)" --max-lag 2 --check-interval 5 --critical-load Threads_running=50,Threads_connected=50 --max-load Threads_connected=50,Threads_running=50  

-- ALTER TABLE add unique key

time pt-online-schema-change  h=localhost,D=treeherder,t=job_detail,u=root --dry-run --alter " ADD UNIQUE KEY title_value_job_uniq (title,value,job_id)" --max-lag 2 --check-interval 5 --critical-load Threads_running=50,Threads_connected=50 --max-load Threads_connected=50,Threads_running=50 

time pt-online-schema-change  h=localhost,D=treeherder,t=job_detail,u=root --execute --alter " ADD UNIQUE KEY title_value_job_uniq (title,value,job_id)" --max-lag 2 --check-interval 5 --critical-load Threads_running=50,Threads_connected=50 --max-load Threads_connected=50,Threads_running=50

Comment 2

[Sheeri Cabral [:sheeri]]

Pythian:

The outages with this are a great illustration of why, even though pt-online-schema-change can be helpful, we do changes as much as possible one server at a time with a failover. This change could have been done as such, with a 5 minute outage for failing over.

Please add to your Mozilla information, that we prefer doing maintenance one server at a time as opposed to using pt-osc, because "online" isn't always what it's cracked up to be, and in this case, affected production for over 9 hours.

Comment 3

[Pythian Team73]

[beebe.paul]
Thanks Sheeri, I will update the team to reiterate this information

Is this resolved? Can it be closed or is there further assistance needed?

Comment 4

[Sheeri Cabral [:sheeri]]

Resolving.