1165411 - Optimize repeated auth_user MySQL queries

Status: RESOLVED FIXED

(Mozilla QA Graveyard :: MozTrap, defect)

Description

[Peter Bengtsson [:peterbe]]

Attachment: 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

Comment 1

[Peter Bengtsson [:peterbe]]

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.

Comment 2

[Peter Bengtsson [:peterbe]]

Attachment: Link to Github pull-request: https://github.com/mozilla/moztrap/pull/96

Comment 3

[Peter Bengtsson [:peterbe]]

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.

Comment 4

[Sheeri Cabral [:sheeri]]

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).

Comment 5

[Peter Bengtsson [:peterbe]]

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.

Comment 6

[Stephen Donner [:stephend] Not actively reading bugmail]

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?

Comment 7

[Peter Bengtsson [:peterbe]]

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

For the record: https://github.com/mozilla/moztrap/commit/0469661d3f482585c10a33071fa878626ef4bb6d

Comment 8

[Sheeri Cabral [:sheeri]]

(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

[Jake Maul [:jakem]]

Attachment: NR graphs after change

Awesome.

Comment 10

[Jake Maul [:jakem]]

Attachment: 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