Optimize repeated auth_user MySQL queries

RESOLVED FIXED

Status

RESOLVED FIXED
3 years ago
3 years ago

People

(Reporter: peterbe, Assigned: peterbe)

Tracking

Version 2

Details

Attachments

(4 attachments)

(Assignee)

Description

3 years ago
Created attachment 8606407 [details]
Screenshot 2015-05-15 11.23.39.png

The, by far, most time consuming MySQL query is the auth_user query. 

We could replace this with a cache call. We did this successfully in Air Mozilla here https://bugzilla.mozilla.org/show_bug.cgi?id=1033047
(Assignee)

Updated

3 years ago
Assignee: nobody → peterbe
(Assignee)

Comment 1

3 years ago
Turns out the bad queries are not related to the auth. It's to do with the drop-downs used for the pjax tables. That stuff can be optimized.
(Assignee)

Comment 3

3 years ago
Sheeri, Jakem, Stephen,

Don't worry about the details of this PR but we're proud to say that we've found a massive gaping optimization hole which can quite easily be fixed. 

It turns out that if you go to a page like this: https://moztrap.mozilla.org/results/runs/ 
The drop-down choices visible when you click the big "Advanced Filtering" button are all generated with limitless and condition-less queries against like 7 different database tables. 

Then if you use the links in the footer to go to page 2 or page 3, a piece of AJAX reloads the table and AGAIN it loads those expensive big fat queries. Every time. 

The benchmarks we've run only makes those pages go from about 2.5s to 1.7s but the much less SQL we have to do the less likely it is that one rotten query will pile up and make matters worse for everything.
Great news! Please keep us in the loop on when that optimization will be put in place.

Does this help with the actual problem, which was the rendering? The slower mysql was only about 5% of the problem, whereas the rendering was a lot more (like 75% or more of the time spent).
(Assignee)

Comment 5

3 years ago
It's not MySQL's fault at all. What happened was that on every request, it needed to do about 7(?) queries to MySQL all of which were unbounded `SELECT ... FORM table;` and for each Django would convert the row into an ORM object instance. I estimate about 3-4,000 of these iterations per page and if you have 10 requests per minutes that's going to be 30-40,000 iterations per minute. With my patch, that's going to go down to about 75-100,000 iterations per **day** instead. If it works out we can half that number by upping the cache timeout from 1h to 2h.
Awesome work on https://github.com/mozilla/moztrap/pull/96 - now that it's merged, and pushed to production, is there anything specific left to do, here?
(Assignee)

Comment 7

3 years ago
Seems the bugcloser hook isn't hooked up for moztrap :(

For the record: https://github.com/mozilla/moztrap/commit/0469661d3f482585c10a33071fa878626ef4bb6d
Status: NEW → RESOLVED
Last Resolved: 3 years ago
Resolution: --- → FIXED
(In reply to Peter Bengtsson [:peterbe] from comment #5)
> It's not MySQL's fault at all. What happened was that on every request, it
> needed to do about 7(?) queries to MySQL all of which were unbounded `SELECT
> ... FORM table;` and for each Django would convert the row into an ORM
> object instance. I estimate about 3-4,000 of these iterations per page and
> if you have 10 requests per minutes that's going to be 30-40,000 iterations
> per minute. With my patch, that's going to go down to about 75-100,000
> iterations per **day** instead. If it works out we can half that number by
> upping the cache timeout from 1h to 2h.

Yikes! Glad you found that though.

Comment 9

3 years ago
Created attachment 8612360 [details]
NR graphs after change

Awesome.

Comment 10

3 years ago
Created attachment 8612363 [details]
NR graphs after change - apdex and overall response time

Maybe even more impressive, this change was enough that the apdex is now much better, and more consistent. It saves somewhere around 100ms of database time *and* around 100ms of python time.

The daily SLA report is also very nice:

https://rpm.newrelic.com/accounts/263620/applications/4786646/optimize/sla_report?interval=days
You need to log in before you can comment on or make changes to this bug.