1217249 - snippet.locale_set.all is slow/un-optimized - needs a prefetch

Status: VERIFIED FIXED

(Snippets :: Service, defect)

Description

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

Attachment: Screen Shot 2015-10-21 at 5.01.19 PM.png

STR:

Load https://snippets.allizom.org

Actual:

It takes nearly 10 seconds, right now, to load and render the page/app; see http://www.webpagetest.org/result/151022_MT_DZ/

Notes from discussion with jakem and osmose, today:

1) IndexView is very expensive, and we're spending 44% of it rendering base/index.html
2) ...25% of whose time is spent in MySQL, grabbing the base_snippetlocale table
3) Because staging currently has 43 test snippets, and due to our temporary PHX1 <-> SCL3 network traversal (which adds ~25ms for each call back and forth)
4) We do this 43 times for each request/GET of snippets.allizom.org
5) According to osmose, snippet.locale_set_all needs a prefetch (or something)

I'm sure I've missed a nuance, Giorgos, but hope this is a good starting place

New Relic: https://rpm.newrelic.com/accounts/263620/applications/4108343/transactions?type=app#id=5b225765625472616e73616374696f6e2f46756e6374696f6e2f736e6970706574732e626173652e76696577733a536e6970706574496e64657856696577222c22225d

Comment 1

[Giorgos Logiotatidis [:giorgos]]

Thanks for reporting Stephen and for the prefetch hint.

I deployed an optimized version on stage and re-run the tests 

https://github.com/mozilla/snippets-service/pull/126/
http://www.webpagetest.org/result/151023_VB_GRA/

Numbers decreased by about 50% and I don't get timeouts anymore. Is this OK?

Comment 2

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

(In reply to Giorgos Logiotatidis [:giorgos] from comment #1)
> Thanks for reporting Stephen and for the prefetch hint.
> 
> I deployed an optimized version on stage and re-run the tests 
> 
> https://github.com/mozilla/snippets-service/pull/126/
> http://www.webpagetest.org/result/151023_VB_GRA/
> 
> Numbers decreased by about 50% and I don't get timeouts anymore. Is this OK?

Looks a TON better; thanks!

http://www.webpagetest.org/result/151023_58_10Y4/

I'll get a separate bug filed about that broken/404'ing favicon.ico GET :-)

Comment 3

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

(In reply to Stephen Donner [:stephend] - please :need-info? me! from comment #2)

> http://www.webpagetest.org/result/151023_58_10Y4/
> 
> I'll get a separate bug filed about that broken/404'ing favicon.ico GET :-)

Sorry for the delay - filed bug 1218634

Comment 4

[[github robot]]

Commits pushed to master at https://github.com/mozilla/snippets-service

https://github.com/mozilla/snippets-service/commit/e1627bf68560080aa368fac22b0c44cb29f1cba7
[bug 1217249] Prefetch snippet relations.

https://github.com/mozilla/snippets-service/commit/30b8ad8cab277e5ae28cf7fb02e96700981cd209
Merge pull request #126 from glogiotatidis/prefetch

 [bug 1217249] Prefetch snippet relations.

Comment 5

[Giorgos Logiotatidis [:giorgos]]

This is now pushed all the way to production. Thanks for reporting Stephen

Comment 6

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

(In reply to Giorgos Logiotatidis [:giorgos] from comment #5)
> This is now pushed all the way to production. Thanks for reporting Stephen

NP, and thanks for fixing it!  Verified FIXED in prod (via New Relic).