1564566 - Cache, optimize, call less or use read-only for /api/project/{project}/jobs/

Status: NEW

(Tree Management :: Treeherder: API, enhancement, P3)

Description

[Armen [:armenzg]]

This is one of the APIs that is most affected when we have database slow downs (upgrading to M5 was not sufficient).

camd: sclements: I don't know Django or Treeherder enough to determine how to improve this. Let me know if you would like to just discuss it in here or jump on a call to troubleshoot together. Today we had multiple alerts.

See this NewRelic trace.

Almost 100% of it was blocked on DB access.

Under "Database queries" you will see this recommended optimizations [1].

 Query details
Close
Database instance 	treeherder-prod.cd3i3txkp6c6.us-east-1.rds.amazonaws.com:3306
Database name 	treeherder
Duration 	12,324 ms
Query analysis (show details)
Table 	Hint
job 	

    The table was retrieved with this index: job_last_modified_2486bd7e_uniq
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 2234 rows of this table were scanned.

job_group 	

    The table was retrieved with this index: PRIMARY
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 1 row of this table was scanned.

build_platform 	

    The table was retrieved with this index: PRIMARY
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 1 row of this table was scanned.

machine_platform 	

    The table was retrieved with this index: PRIMARY
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 1 row of this table was scanned.

job_type 	

    The table was retrieved with this index: PRIMARY
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 1 row of this table was scanned.

reference_data_signatures 	

    The table was retrieved with this index: PRIMARY
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 1 row of this table was scanned.

machine 	

    The table was retrieved with this index: PRIMARY
    You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
    Approximately 1 row of this table was scanned.

Stack trace
Traceback (most recent call last):
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/whitenoise/middleware.py", line 49, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/utils/deprecation.py", line 94, in __call__
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 1203, in _wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
File "/app/.heroku/python/lib/python3.7/site-packages/django/core/handlers/base.py", line 113, in _get_response
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/framework_django.py", line 544, in wrapper
File "/app/.heroku/python/lib/python3.7/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
File "/app/.heroku/python/lib/python3.7/site-packages/rest_framework/viewsets.py", line 116, in view
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/component_djangorestframework.py", line 46, in _nr_wrapper_APIView_dispatch_
File "/app/.heroku/python/lib/python3.7/site-packages/rest_framework/views.py", line 492, in dispatch
File "/app/treeherder/webapp/api/jobs.py", line 299, in list
File "/app/treeherder/webapp/api/jobs.py", line 167, in _get_job_list_response
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/models/query.py", line 274, in __iter__
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/models/query.py", line 1242, in _fetch_all
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/models/query.py", line 144, in __iter__
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1052, in results_iter
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1100, in execute_sql
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/backends/utils.py", line 67, in execute
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
File "/app/.heroku/python/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/hooks/database_dbapi2.py", line 25, in execute
File "/app/.heroku/python/lib/python3.7/site-packages/newrelic/api/time_trace.py", line 147, in __exit__

Comment 1

[Sarah Clements [:sclements]]

Are you talking about slowdowns from the log parser? Giving it a quick glance it seems like it's filtering on primary keys and the last_modified field has an index on it. I can look into it more when I switch to backend work/API improvements (unless you or :camd want to look into it sooner).

On a related note, I was looking at one of the database spikes today at 5:05pm PST. I know you're aware that the perf table is larger than it should be due to cycle_data having been disabled, but I'm wondering if reading the foreign-key indexes on repository, job and push tables during perf datum inserts would have an affect on get requests for those tables. It'd be interesting to see how optimizing that log parser insert task and reducing Perf datum table size would help with with these slowdowns (in addition to improvements to individual APIs).

FYI, these are the docs you'd want to look at regarding Django queries: https://docs.djangoproject.com/en/2.1/topics/db/queries/
and this: https://docs.djangoproject.com/en/2.2/topics/db/optimization/

Comment 2

[Armen [:armenzg]]

(In reply to Sarah Clements [:sclements] from comment #1)

Are you talking about slowdowns from the log parser? Giving it a quick glance it seems like it's filtering on primary keys and the last_modified field has an index on it. I can look into it more when I switch to backend work/API improvements (unless you or :camd want to look into it sooner).

It's a combination of both write operations (related to an increase number of logs to be parsed; lots of tasks finishing around the same time) and clients fetching more jobs data. This can wait until you're done.

On a related note, I was looking at one of the database spikes today at 5:05pm PST. I know you're aware that the perf table is larger than it should be due to cycle_data having been disabled, but I'm wondering if reading the foreign-key indexes on repository, job and push tables during perf datum inserts would have an affect on get requests for those tables. It'd be interesting to see how optimizing that log parser insert task and reducing Perf datum table size would help with with these slowdowns (in addition to improvements to individual APIs).

I would not be surprised at all.

FYI, these are the docs you'd want to look at regarding Django queries: https://docs.djangoproject.com/en/2.1/topics/db/queries/
and this: https://docs.djangoproject.com/en/2.2/topics/db/optimization/

Thank you so much!

Comment 3

[Armen [:armenzg]]

I believe that we could also make our APIs time out way sooner and make the UI snappier.

What is our current default?

Comment 4

[Armen [:armenzg]]

It seems that we keep hitting the backend every minute from every client.
https://github.com/mozilla/treeherder/blob/05e11a165e0a4fa558cc90887234dc3c54e7c756/ui/job-view/context/Pushes.jsx#L165

Comment 5

[Armen [:armenzg]]

We can try reading from the replica:
https://docs.djangoproject.com/en/2.2/topics/db/multi-db/#db_for_read