1330775 - The fetch-runnablejobs task takes 10 mins and makes 140,000 MySQL requests

Status: RESOLVED WONTFIX

(Tree Management :: Treeherder: Data Ingestion, defect, P1)

Description

[Ed Morley [:emorley]]

For example:
https://rpm.newrelic.com/accounts/677903/applications/14179733/transactions?tw%5Bend%5D=1484251707&tw%5Bstart%5D=1484165307#id=5b224f746865725472616e73616374696f6e2f43656c6572792f66657463682d616c6c7468657468696e6773222c22225d

A single run of the task takes on average 540 seconds and makes 141,300 MySQL requests.

MySQL breakdown:

MySQL commit:    15700
MySQL runnable_job select:    15700
MySQL build_platform select:    15700
MySQL machine_platform select:    15700
MySQL option_collection select:    15700
MySQL job_type select:    15700
MySQL runnable_job update:    15700
MySQL job_group select:    15700
MySQL option select:    15700

Comment 1

[Ed Morley [:emorley]]

So the problem is that currently update_runnable_jobs_table() loops through allthethings.json
and for each entry:
* does a get_or_create() on each of:
  - BuildPlatform
  - MachinePlatform
  - JobGroup
  - JobType
  - OptionCollection
* Then using those IDs created the RunnableJob object

Instead it should do some aggregation on the reference data.

Comment 2

[Ed Morley [:emorley]]

This task now only takes 5 mins and makes a third of the DB requests - presumably since we run fewer jobs on buildbot (the taskcluster jobs have their data fetched another way). As such let's just wontfix this given buildbot going away.