1496859 - Firefox Monitor: add index to subscribers.sha1 column in stage, then prod

Status: VERIFIED FIXED

(Cloud Services :: Operations: Deployment Requests - DEPRECATED, task)

Description

[Luke Crouch [:groovecoder]]

We are receiving a spike of HIBP callback requests which look up our subscribers by sha1 hash. The RDS node is maxing on CPU. :(

Adding an index to the sha1 column should help this.

create index on subscribers (sha1);

Comment 1

[Bob Micheletto [:bobm]]

Index created in stage, please verify.

Comment 2

[Bob Micheletto [:bobm]]

(In reply to Bob Micheletto [:bobm] from comment #1)
> Index created in stage, please verify.

Verification done by :groovecoder via IRC:
groovecoder> bobm: stage DB good - works for subscribing, verifying, and notifying

Production index has been added.

Comment 3

[Luke Crouch [:groovecoder]]

Note: I've created a PR[1] to retro-actively add a migration for this DB change to our code-base for future environments. But since the index already exists in stage & prod, we should run this SQL to "fake" the migration that records this DB change:

INSERT INTO knex_migrations (name, batch, migration_time) values ('20181007085241_add_sha1_index.js', 4, '2018-10-07 08:52:42.000-05');

[1] https://github.com/mozilla/blurts-server/pull/520

Comment 4

[Bob Micheletto [:bobm]]

Closing this bug.