Note: There are a few cases of duplicates in user autocompletion which are being worked on.

balrog revisions pages never load sometimes

RESOLVED FIXED

Status

Release Engineering
Balrog: Frontend
RESOLVED FIXED
4 years ago
3 years ago

People

(Reporter: bhearsum, Unassigned)

Tracking

Firefox Tracking Flags

(Not tracked)

Details

Attachments

(1 attachment)

(Reporter)

Description

4 years ago
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
(Reporter)

Updated

4 years ago
Blocks: 933414
(Reporter)

Comment 1

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

Comment 2

3 years ago
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.
Tue, 1 pm Eastern is great. It's on my cal, I'll invite you.
(Reporter)

Comment 4

3 years ago
Created attachment 8389415 [details] [diff] [review]
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.
Attachment #8389415 - Flags: review?(nthomas)
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.
Attachment #8389415 - Flags: review?(nthomas) → review+

Comment 6

3 years ago
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
(Reporter)

Updated

3 years ago
Attachment #8389415 - Flags: checked-in+
Ben's trying on dev/stage and needs the Zeus timeout increased, I've asked Ludovic to increase dev/stage temporarily to 30 mins.
updated timeout for db-dev-rw pool from 300s to 1800s

left connect_timeout: to 10 seconds.
(Reporter)

Comment 9

3 years ago
(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.
(Reporter)

Comment 10

3 years ago
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.
reverted the value to 300.
logged into ausadm.private.phx1.mozilla.com

[root@ausadm.private.phx1 ~]# 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.
[root@ausadm.private.phx1 ~]# 

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
[root@ausadm.private.phx1 balrog]#
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)
(Reporter)

Comment 14

3 years ago
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.
Status: NEW → RESOLVED
Last Resolved: 3 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.