1185901 - Convert the bugscache table from MyISAM to InnoDB

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P3)

Description

[Ed Morley [:emorley]]

The RDS logs are complaining:

DB Instance treeherder-heroku contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#MySQL.CommonDBATasks.Tables

The bugscache table is the only one using MYISAM:
https://github.com/mozilla/treeherder/search?q=MyISAM

It was switched to MyISAM with no reason given, in:
https://github.com/mozilla/treeherder/commit/60c5cc8cfc47c0dad937c726fb4b4450a013b474

...however I presume it was due to us wanting full text searching on that table.

However as of MySQL 5.6, InnoDB supports full text searching:
http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

Comment 1

[Sheeri Cabral [:sheeri]]

This is a small table on both stage and prod:

On prod:
9:00 AM <sheeri> -rw-rw---- 1 mysql mysql 1.7M Sep 18 12:03 treeherder/bugscache.MYD
9:00 AM <sheeri> -rw-rw---- 1 mysql mysql 3.5M Sep 18 12:03 treeherder/bugscache.MYI
9:00 AM <sheeri> So like 5.2M
9:01 AM <sheeri> mdoglio: and when I say "not long" I mean, like 5-10 seconds
9:01 AM <sheeri> could do it live
9:01 AM <sheeri> similar size on stage:
9:01 AM <sheeri> -rw-rw---- 1 mysql mysql 1.7M Sep 18 12:03 treeherder_stage/bugscache.MYD
9:01 AM <sheeri> -rw-rw---- 1 mysql mysql 3.9M Sep 18 12:03 treeherder_stage/bugscache.MYI
9:01 AM <sheeri> so we can do stage first, then test a bit, then prod
9:05 AM  → ahal joined  ↔ treeherder-production and treeherder-stage nipped out  
9:06 AM <•mdoglio> sheeri: feel free to do it whenever you want then
9:06 AM <sheeri> OK, will do on test and make a bug
9:06 AM <sheeri> mysql> alter table treeherder_stage.bugscache engine=innodb;
9:06 AM <sheeri> Query OK, 8939 rows affected (1.18 sec)
9:06 AM <sheeri> Records: 8939  Duplicates: 0  Warnings: 0
9:06 AM <sheeri> that was fast :D 

So, this is complete on stage, let's let it run for a bit and then convert on prod.

Comment 2

[Ed Morley [:emorley]]

TODO: Change the type in treeherder_reference_1.sql.tmpl + update the table on Heroku too.

Comment 3

[Ed Morley [:emorley]]

Heroku updated:

00:35:44	ALTER TABLE bugscache ENGINE=InnoDB	9033 row(s) affected Records: 9033  Duplicates: 0  Warnings: 0	2.437 sec

Comment 4

[Ed Morley [:emorley]]

Attachment: Convert the bugscache table from MyISAM to InnoDB

Comment 5

[Treeherder GitHub Bugbot]

Commit pushed to master at https://github.com/mozilla/treeherder

https://github.com/mozilla/treeherder/commit/9e1896f5a028393a45673e3b520557fa5cceb32b
Bug 1185901 - Convert the bugscache table from MyISAM to InnoDB

MySQL 5.6 supports full text searching, so we no longer need to use
MyISAM. Django doesn't let us mix and match engines, so we need to pick
one and stick with it, for bug 1193836.

The bugscache table has already been updated manually on stage/prod and
Heroku; this switches it for Travis/the local Vagrant environment.

Comment 6

[Ed Morley [:emorley]]

Updated on prod too (stage done in comment 1):

mysql> SHOW CREATE TABLE treeherder.bugscache \G
*************************** 1. row ***************************
       Table: bugscache
Create Table: CREATE TABLE `bugscache` (
  `id` int(11) NOT NULL DEFAULT '0',
  `status` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `resolution` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `summary` varchar(255) COLLATE utf8_bin NOT NULL,
  `crash_signature` mediumtext COLLATE utf8_bin,
  `keywords` mediumtext COLLATE utf8_bin,
  `os` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_resolution` (`resolution`),
  FULLTEXT KEY `idx_summary` (`summary`),
  FULLTEXT KEY `idx_crash_signature` (`crash_signature`),
  FULLTEXT KEY `idx_keywords` (`keywords`),
  FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE bugscache ENGINE=InnoDB;
Query OK, 9049 rows affected (1.21 sec)
Records: 9049  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE treeherder.bugscache \G
*************************** 1. row ***************************
       Table: bugscache
Create Table: CREATE TABLE `bugscache` (
  `id` int(11) NOT NULL DEFAULT '0',
  `status` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `resolution` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `summary` varchar(255) COLLATE utf8_bin NOT NULL,
  `crash_signature` mediumtext COLLATE utf8_bin,
  `keywords` mediumtext COLLATE utf8_bin,
  `os` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_resolution` (`resolution`),
  FULLTEXT KEY `idx_summary` (`summary`),
  FULLTEXT KEY `idx_crash_signature` (`crash_signature`),
  FULLTEXT KEY `idx_keywords` (`keywords`),
  FULLTEXT KEY `idx_all_full_text` (`summary`,`crash_signature`,`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)