946285 - balrog revisions pages never load sometimes

Status: RESOLVED FIXED

(Release Engineering Graveyard :: Applications: Balrog (frontend), defect)

Description

[[e-mail] (:bhearsum)]

Probably because the tables are huge and need indexes, if I had to guess. Eg: https://aus4-admin.mozilla.org/releases/Fennec-mozilla-central-nightly-latest/revisions/?page=20

Comment 1

[[e-mail] (:bhearsum)]

This will probably get less problematic when we start clearing out data regularly, too (bug 952135).

Comment 2

[[e-mail] (:bhearsum)]

Sheeri and I chatted about this today. The main query that's slow looks like:
select * from releases_history where name='Fennec-mozilla-central-nightly-latest' and data_version is null order by timestamp limit 10 offset 20;

She said that an index on the "name" column should speed this up (.8sec -> .02sec in her quick test). This will clearly help when we're querying the releases_history table, and it's probably worthwhile to add it to the releases table as well.

Sheeri, anytime after eastern lunch is usually free of writes to the releases & releases_history tables. Can we schedule this for next Tuesday at 1pm? I'll just have to verify that we don't have any jobs running that will write to those tables before we start.

Comment 3

[Sheeri Cabral [:sheeri]]

Tue, 1 pm Eastern is great. It's on my cal, I'll invite you.

Comment 4

[[e-mail] (:bhearsum)]

Attachment: add an index to releases_history.name

Turns out we did this previously for timestamp -- I just copied version 3 -> 6 and replaced the column names. Worked fine on my laptop, starting from a db that was at version 5 already.

Sheeri told me on IRC that she's happy to use the migrate script to do the upgrade.

Comment 5

[Nick Thomas [:nthomas] (UTC+12)]

Comment on attachment 8389415 [details] [diff] [review]
add an index to releases_history.name

Seems fine to me. Perhaps we should ask a DBA to do a more general sweep through our slow & common queries to look for other optimizations.

Comment 6

[[github robot]]

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

https://github.com/mozilla/balrog/commit/64dbb5a3855adfdd6a13b8a2d2ebf0c11711a4a4
bug 946285: balrog revisions pages never load sometimes - add an index to releases_history.name. r=nthomas

Comment 7

[Sheeri Cabral [:sheeri]]

Ben's trying on dev/stage and needs the Zeus timeout increased, I've asked Ludovic to increase dev/stage temporarily to 30 mins.

Comment 8

[Ludovic Hirlimann [:Usul]]

updated timeout for db-dev-rw pool from 300s to 1800s

left connect_timeout: to 10 seconds.

Comment 9

[[e-mail] (:bhearsum)]

(In reply to Ludovic Hirlimann [:Usul] from comment #8)
> updated timeout for db-dev-rw pool from 300s to 1800s
> 
> left connect_timeout: to 10 seconds.

Thanks Ludo!

When I tried to create the index mysql told me that it already existed -- I guess it finished creating even after I got disconnected. I dropped it and re-added to make sure everything was proper, and it happened very quickly (~20s) this time -- I guess the caches were warm.

I also upgraded the stage db, which went quickly since it's pretty much empty.

Comment 10

[[e-mail] (:bhearsum)]

Oh, I also meant to say that the index sped up pages like https://aus4-admin-dev.allizom.org/releases/Fennec-mozilla-central-nightly-latest/revisions/?page=20 a lot. They still take a few seconds to load, but we're far from hitting the zeus timeout now.

Comment 11

[Ludovic Hirlimann [:Usul]]

reverted the value to 300.

Comment 12

[Sheeri Cabral [:sheeri]]

logged into ausadm.private.phx1.mozilla.com

[[e-mail]1 ~]# git clone https://github.com/mozilla/balrog/
Cloning into 'balrog'...
remote: Reusing existing pack: 3310, done.
remote: Counting objects: 6, done.
remote: Compressing objects: 100% (6/6), done.
remote: Total 3316 (delta 0), reused 0 (delta 0)
Receiving objects: 100% (3316/3316), 7.20 MiB | 5.89 MiB/s, done.
Resolving deltas: 100% (1321/1321), done.
[[e-mail]1 ~]# 

Then I ran 
python scripts/manage-db.py -d mysql://user:password@host/db --version 6 upgrade
(where user, password, host and db were filled out with appropriate entries)

DEBUG:migrate.versioning.repository:Loading repository scripts/../auslib/migrate...
DEBUG:migrate.versioning.script.base:Loading script scripts/../auslib/migrate/versions/001_initial.py...
DEBUG:migrate.versioning.script.base:Script scripts/../auslib/migrate/versions/001_initial.py loaded successfully
DEBUG:migrate.versioning.script.base:Loading script scripts/../auslib/migrate/versions/002_rules_comments.py...
DEBUG:migrate.versioning.script.base:Script scripts/../auslib/migrate/versions/002_rules_comments.py loaded successfully
DEBUG:migrate.versioning.script.base:Loading script scripts/../auslib/migrate/versions/003_releases_history_index.py...
DEBUG:migrate.versioning.script.base:Script scripts/../auslib/migrate/versions/003_releases_history_index.py loaded successfully
DEBUG:migrate.versioning.script.base:Loading script scripts/../auslib/migrate/versions/004_rename_throttle.py...
DEBUG:migrate.versioning.script.base:Script scripts/../auslib/migrate/versions/004_rename_throttle.py loaded successfully
DEBUG:migrate.versioning.script.base:Loading script scripts/../auslib/migrate/versions/005_rules_longer_os_version.py...
DEBUG:migrate.versioning.script.base:Script scripts/../auslib/migrate/versions/005_rules_longer_os_version.py loaded successfully
DEBUG:migrate.versioning.script.base:Loading script scripts/../auslib/migrate/versions/006_releases_name_index.py...
DEBUG:migrate.versioning.script.base:Script scripts/../auslib/migrate/versions/006_releases_name_index.py loaded successfully
DEBUG:migrate.versioning.repository:Repository scripts/../auslib/migrate loaded successfully
DEBUG:migrate.versioning.repository:Config: {'db_settings': {'__name__': 'db_settings', 'required_dbs': '[]', 'version_table': 'migrate_version', 'repository_id': 'balrog'}}
DEBUG:AUSDatabase:migrating schema version 5 -> 6
[[e-mail]1 balrog]#

Comment 13

[Sheeri Cabral [:sheeri]]

And we're good!

mysql> select @@hostname;show create table releases_history\G
+----------------------------+
| @@hostname                 |
+----------------------------+
| aus4-1.db.phx1.mozilla.com |
+----------------------------+
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: releases_history
Create Table: CREATE TABLE `releases_history` (
  `change_id` int(11) NOT NULL AUTO_INCREMENT,
  `changed_by` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `product` varchar(15) DEFAULT NULL,
  `version` varchar(25) DEFAULT NULL,
  `data_version` int(11) DEFAULT NULL,
  `data` longtext,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY `timestamp` (`timestamp`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=301916 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Comment 14

[[e-mail] (:bhearsum)]

I verified that the index exists from a ro webhead, and pages like https://aus4-admin.mozilla.org/releases/Fennec-mozilla-central-nightly-latest/revisions/?page=21 load in 1-2s now, woohoo!

(In reply to Nick Thomas [:nthomas] from comment #5)
> Perhaps we should ask a DBA to do a more general sweep
> through our slow & common queries to look for other optimizations.

I've added this to my todo list for later.