Closed
Bug 1293500
Opened 8 years ago
Closed 8 years ago
ALTER TABLE treeherder.job_details to fix column width and add unique index
Categories
(Data & BI Services Team :: DB: MySQL, task)
Data & BI Services Team
DB: MySQL
Tracking
(Not tracked)
RESOLVED
FIXED
Due Date:
People
(Reporter: team73, Assigned: team73)
References
Details
(Keywords: treeherder)
As per request from cdawson@mozilla.com 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.
Assignee | ||
Comment 1•8 years ago
|
||
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
Updated•8 years ago
|
Group: metrics-private
Keywords: treeherder
Comment 2•8 years ago
|
||
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.
Assignee | ||
Comment 3•8 years ago
|
||
[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?
You need to log in
before you can comment on or make changes to this bug.
Description
•