1304332 - Differences in the performance_signature table schema between environments

Comment hidden (obsolete)

Differences:
* The `performance_signature` table field `parent_signature_id` has a default of NULL on Heroku stage, but no default on all other environments.
* Charset/collation varies (bug 1303767)

# SCL3 prod / SCL3 stage:

CREATE TABLE `performance_signature` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `signature_hash` varchar(40) COLLATE utf8_bin NOT NULL,
  `suite` varchar(80) COLLATE utf8_bin NOT NULL,
  `test` varchar(80) COLLATE utf8_bin NOT NULL,
  `extra_properties` longtext COLLATE utf8_bin NOT NULL,
  `framework_id` int(11) NOT NULL,
  `option_collection_id` int(11) NOT NULL,
  `platform_id` int(11) NOT NULL,
  `last_updated` datetime(6) NOT NULL,
  `repository_id` int(11) NOT NULL,
  `lower_is_better` tinyint(1) NOT NULL,
  `parent_signature_id` int(11),
  `has_subtests` tinyint(1) NOT NULL,
  `alert_threshold` double,
  `fore_window` int(11),
  `max_back_window` int(11),
  `min_back_window` int(11),
  `should_alert` tinyint(1),
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`repository_id`,`framework_id`,`signature_hash`),
  KEY `<REMOVED>` (`framework_id`),
  KEY `<REMOVED>` (`option_collection_id`),
  KEY `<REMOVED>` (`platform_id`),
  KEY `<REMOVED>` (`last_updated`),
  KEY `<REMOVED>` (`repository_id`),
  KEY `<REMOVED>` (`parent_signature_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`parent_signature_id`) REFERENCES `performance_signature` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`option_collection_id`) REFERENCES `option_collection` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`framework_id`) REFERENCES `performance_framework` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`platform_id`) REFERENCES `machine_platform` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


# Vagrant / Heroku prototype:

CREATE TABLE `performance_signature` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `signature_hash` varchar(40) NOT NULL,
  `suite` varchar(80) NOT NULL,
  `test` varchar(80) NOT NULL,
  `extra_properties` longtext NOT NULL,
  `framework_id` int(11) NOT NULL,
  `option_collection_id` int(11) NOT NULL,
  `platform_id` int(11) NOT NULL,
  `last_updated` datetime(6) NOT NULL,
  `repository_id` int(11) NOT NULL,
  `lower_is_better` tinyint(1) NOT NULL,
  `parent_signature_id` int(11),
  `has_subtests` tinyint(1) NOT NULL,
  `alert_threshold` double,
  `fore_window` int(11),
  `max_back_window` int(11),
  `min_back_window` int(11),
  `should_alert` tinyint(1),
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`repository_id`,`framework_id`,`signature_hash`),
  KEY `<REMOVED>` (`framework_id`),
  KEY `<REMOVED>` (`option_collection_id`),
  KEY `<REMOVED>` (`platform_id`),
  KEY `<REMOVED>` (`last_updated`),
  KEY `<REMOVED>` (`repository_id`),
  KEY `<REMOVED>` (`parent_signature_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`parent_signature_id`) REFERENCES `performance_signature` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`option_collection_id`) REFERENCES `option_collection` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`framework_id`) REFERENCES `performance_framework` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`platform_id`) REFERENCES `machine_platform` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


# Heroku stage:

CREATE TABLE `performance_signature` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `signature_hash` varchar(40) COLLATE utf8_bin NOT NULL,
  `suite` varchar(80) COLLATE utf8_bin NOT NULL,
  `test` varchar(80) COLLATE utf8_bin NOT NULL,
  `extra_properties` longtext COLLATE utf8_bin NOT NULL,
  `framework_id` int(11) NOT NULL,
  `option_collection_id` int(11) NOT NULL,
  `platform_id` int(11) NOT NULL,
  `last_updated` datetime(6) NOT NULL,
  `repository_id` int(11) NOT NULL,
  `lower_is_better` tinyint(1) NOT NULL,
  `parent_signature_id` int(11) DEFAULT NULL,
  `has_subtests` tinyint(1) NOT NULL,
  `alert_threshold` double,
  `fore_window` int(11),
  `max_back_window` int(11),
  `min_back_window` int(11),
  `should_alert` tinyint(1),
  PRIMARY KEY (`id`),
  UNIQUE KEY `<REMOVED>` (`repository_id`,`framework_id`,`signature_hash`),
  KEY `<REMOVED>` (`framework_id`),
  KEY `<REMOVED>` (`option_collection_id`),
  KEY `<REMOVED>` (`platform_id`),
  KEY `<REMOVED>` (`last_updated`),
  KEY `<REMOVED>` (`repository_id`),
  KEY `<REMOVED>` (`parent_signature_id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`parent_signature_id`) REFERENCES `performance_signature` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`option_collection_id`) REFERENCES `option_collection` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`framework_id`) REFERENCES `performance_framework` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`platform_id`) REFERENCES `machine_platform` (`id`),
  CONSTRAINT `<REMOVED>` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Looks like the field was added in:
https://github.com/mozilla/treeherder/commit/edb99f0401ad381447d1165e8f0b5c28c3a7ecab

Perhaps the initial version that landed on master didn't have the `null=True`? Or else were manual changes made?

Ed Morley [:emorley]

Reporter

Updated

•

8 years ago

Blocks: 1226693

Comment hidden (obsolete)

Now that the other environments have been recreated, the differences are:

--- vagrant.sql	2016-11-25 17:22:53.439965200 +0000
+++ prod.sql	2016-11-25 17:31:49.775087600 +0000
...
--
 -- Table structure for table `performance_signature`
 --
 
 DROP TABLE IF EXISTS `performance_signature`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `performance_signature` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `signature_hash` varchar(40) COLLATE utf8_bin NOT NULL,
   `suite` varchar(80) COLLATE utf8_bin NOT NULL,
   `test` varchar(80) COLLATE utf8_bin NOT NULL,
   `extra_properties` longtext COLLATE utf8_bin NOT NULL,
   `framework_id` int(11) NOT NULL,
   `option_collection_id` int(11) NOT NULL,
   `platform_id` int(11) NOT NULL,
   `last_updated` datetime(6) NOT NULL,
   `repository_id` int(11) NOT NULL,
   `lower_is_better` tinyint(1) NOT NULL,
-  `parent_signature_id` int(11),
+  `parent_signature_id` int(11) DEFAULT NULL,
   `has_subtests` tinyint(1) NOT NULL,
-  `alert_threshold` double,
-  `fore_window` int(11),
-  `max_back_window` int(11),
-  `min_back_window` int(11),
-  `should_alert` tinyint(1),
+  `alert_threshold` double DEFAULT NULL,
+  `fore_window` int(11) DEFAULT NULL,
+  `max_back_window` int(11) DEFAULT NULL,
+  `min_back_window` int(11) DEFAULT NULL,
+  `should_alert` tinyint(1) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `<INDEX_NAME>` (`repository_id`,`framework_id`,`signature_hash`),
   KEY `<INDEX_NAME>` (`framework_id`),
   KEY `<INDEX_NAME>` (`option_collection_id`),
   KEY `<INDEX_NAME>` (`platform_id`),
   KEY `<INDEX_NAME>` (`last_updated`),
   KEY `<INDEX_NAME>` (`repository_id`),
   KEY `<INDEX_NAME>` (`parent_signature_id`),
+  KEY `<INDEX_NAME>` (`suite`,`test`,`lower_is_better`,`repository_id`,`framework_id`,`platform_id`,`option_collection_id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`parent_signature_id`) REFERENCES `performance_signature` (`id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`option_collection_id`) REFERENCES `option_collection` (`id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`framework_id`) REFERENCES `performance_framework` (`id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`platform_id`) REFERENCES `machine_platform` (`id`),
   CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 /*!40101 SET character_set_client = @saved_cs_client */;


Since comment 0 there's a new difference: Prod has an index that Vagrant doesn't.

Ed Morley [:emorley]

Reporter

Comment 2

•

7 years ago

Updated diff:

--- schema-vagrant.sql
+++ schema-prod.sql
...
 CREATE TABLE `performance_signature` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `signature_hash` varchar(40) COLLATE utf8_bin NOT NULL,
   `suite` varchar(80) COLLATE utf8_bin NOT NULL,
   `test` varchar(80) COLLATE utf8_bin NOT NULL,
   `lower_is_better` tinyint(1) NOT NULL,
   `last_updated` datetime(6) NOT NULL,
   `has_subtests` tinyint(1) NOT NULL,
   `extra_options` varchar(60) COLLATE utf8_bin NOT NULL,
   `should_alert` tinyint(1) DEFAULT NULL,
   `alert_threshold` double DEFAULT NULL,
   `min_back_window` int(11) DEFAULT NULL,
   `max_back_window` int(11) DEFAULT NULL,
   `fore_window` int(11) DEFAULT NULL,
   `framework_id` int(11) NOT NULL,
   `option_collection_id` int(11) NOT NULL,
   `parent_signature_id` int(11) DEFAULT NULL,
   `platform_id` int(11) NOT NULL,
   `repository_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `<NAME>` (`repository_id`,`framework_id`,`signature_hash`),
   KEY `<NAME>` (`framework_id`),
   KEY `<NAME>` (`option_collection_id`),
   KEY `<NAME>` (`parent_signature_id`),
   KEY `<NAME>` (`platform_id`),
-  KEY `<NAME>` (`signature_hash`),
   KEY `<NAME>` (`last_updated`),
+  KEY `<NAME>` (`suite`,`test`,`lower_is_better`,`repository_id`,`framework_id`,`platform_id`,`option_collection_id`),
   CONSTRAINT `<NAME>` FOREIGN KEY (`parent_signature_id`) REFERENCES `performance_signature` (`id`),
   CONSTRAINT `<NAME>` FOREIGN KEY (`option_collection_id`) REFERENCES `option_collection` (`id`),
   CONSTRAINT `<NAME>` FOREIGN KEY (`framework_id`) REFERENCES `performance_framework` (`id`),
   CONSTRAINT `<NAME>` FOREIGN KEY (`platform_id`) REFERENCES `machine_platform` (`id`),
   CONSTRAINT `<NAME>` FOREIGN KEY (`repository_id`) REFERENCES `repository` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Ed Morley [:emorley]

Reporter

Comment 3

•

7 years ago

Couple of questions:
* Prod is missing the index on `signature_hash` - I'm presuming we want that index (so should add it instead of adjusting the model)
* Prod has an extra composite index not in the model - do we need it (and so should add it to the model)? The index:
(`suite`,`test`,`lower_is_better`,`repository_id`,`framework_id`,`platform_id`,`option_collection_id`)

Flags: needinfo?(wlachance)

William Lachance (:wlach)

Assignee

Comment 4

•

7 years ago

Hmm, to be honest I can't remember making these changes. :)

I would be inclined to reset things to match the vagrant instance on stage and validate that things are still reasonably fast. If there are issues (i.e. we were actually depending on the index), we can add it to the model. I'll do this sometime this week, leaving NI open so I don't forget.

Ed Morley [:emorley]

Reporter

Comment 5

•

7 years ago

It it helps, I've just checked stage and for indexes it's the same as prod (additional composite index, and missing the `signature_hash` index), albeit the column definitions vary slightly.

William Lachance (:wlach)

Assignee

Comment 6

•

7 years ago

This fell down in my priority list, but I will pick it up next week, promise.

GitHub Autolander Bot

Comment 7

•

7 years ago

Attached file [treeherder] wlach:1304332 > mozilla:master — Details

William Lachance (:wlach)

Assignee

Updated

•

7 years ago

Assignee: nobody → wlachance

Flags: needinfo?(wlachance)

William Lachance (:wlach)

Assignee

Comment 8

•

7 years ago

Comment on attachment 8836753 [details] [review]
[treeherder] wlach:1304332 > mozilla:master

Let's just do the simplest thing possible here to synchronize the two. I very much doubt the performance signature table is a bottleneck for anything (it's small).

* I have manually removed the extra index from production/stage. 
* Here's a migration to remove the signature_hash index from vagrant.

Attachment #8836753 - Flags: review?(emorley)

Ed Morley [:emorley]

Reporter

Comment 9

•

7 years ago

Comment on attachment 8836753 [details] [review]
[treeherder] wlach:1304332 > mozilla:master

> * I have manually removed the extra index from production/stage. 

Prototype too? :-)

Attachment #8836753 - Flags: review?(emorley) → review+

Ed Morley [:emorley]

Reporter

Comment 10

•

7 years ago

Thank you for looking at this :-)

Treeherder GitHub Bugbot

Comment 11

•

7 years ago

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

https://github.com/mozilla/treeherder/commit/0d55215d73806d7d4a3297c00ada63787ae1d979
Bug 1304332 - Remove index on signature_hash (#2160)

We've been happily running without it in production for a while, so
let's just remove it.

William Lachance (:wlach)

Assignee

Comment 12

•

7 years ago

(In reply to Ed Morley [:emorley] from comment #9)
> Comment on attachment 8836753 [details] [review]
> [treeherder] wlach:1304332 > mozilla:master
> 
> > * I have manually removed the extra index from production/stage. 
> 
> Prototype too? :-)

No, but just took care of it!

Ed Morley [:emorley]

Reporter

Comment 13

•

7 years ago

:-D

Ed Morley [:emorley]

Reporter

Comment 14

•

7 years ago

The migration deployed fine on prod.

Status: NEW → RESOLVED

Closed: 7 years ago

Resolution: --- → FIXED

Bugzilla

Quick Search

Differences in the performance_signature table schema between environments

Categories

(Tree Management :: Perfherder, defect, P2)

Tracking

(Not tracked)

People

(Reporter: emorley, Assigned: wlach)

References

Details

Crash Data

Security

(public)

User Story

Attachments

(1 file)

Description

Updated

Comment 1

Comment 2

Comment 3

Comment 4

Comment 5

Comment 6

Comment 7

Updated

Comment 8

Comment 9

Comment 10

Comment 11

Comment 12

Comment 13

Comment 14

Attachment

General

Description

File Name

Content Type