Request for /on/github/github takes over 8s

[zbynekwinkler]

$ time curl "https://www.gittip.com/on/github/github/" -s > /dev/null

real    0m8.164s
user    0m0.012s
sys     0m0.008s

[mvdkleijn]

@zwn side question from me: what's the ’on’ url? doesn't seem to be a team or community?

[zbynekwinkler]

@mvdkleijn I figured that it represents the github organization on github. In general, the 'on' url helps identify gittip users by their associated accounts. For me the url https://www.gittip.com/on/github/zwn/ redirects to my gittip account because I have signed up already. The github org is has not signed up yet apparently.

[chadwhitacre]

/on/ is our URL-space for "accounts elsewhere".

• /on/bitbucket/
• /on/bountysource/
• /on/github/
• /on/twitter/

We want an unlimited number of these, eventually (#1369).

[zbynekwinkler]

On my localhost this page load takes 6.5s.

• It takes 2s to get info about github organization from github because we do not store that (there is not much we can do here)
• It takes almost no time to run db query to find out who is on gittip already
• It takes 4.5s to loop through the users and load each an every one

Having this break up I tried running EXPLAIN ANALYZE on the query for all gittip users that are part of the github organization on my almost empty local db with the following result:

"Nested Loop  (cost=0.00..25.27 rows=1 width=1025) (actual time=0.156..0.156 rows=0 loops=1)"
"  ->  Seq Scan on participants p  (cost=0.00..8.16 rows=2 width=12) (actual time=0.018..0.077 rows=2 loops=1)"
"        Filter: (claimed_time IS NOT NULL)"
"  ->  Index Scan using elsewhere_participant on elsewhere  (cost=0.00..8.54 rows=1 width=1025) (actual time=0.034..0.034 rows=0 loops=2)"
"        Index Cond: (participant = p.username)"
"        Filter: ((platform = 'github'::text) AND ((user_info -> 'login'::text) = ANY ('{string with usernames}'::text[])))"
"Total runtime: 0.242 ms"

I do not yet fully grok the output, but the thing to note is that it starts with sequential scan of the participants table. I guess there is no need to explain what that means 😄

Loading the participants one by one is also taking some time even with almost empty db. I am getting the rate of 60 per second. Looking at the code in AccountElsewhere:upsert, to get to a single user we do INSERT, UPDATE and SELECT with JOIN.

[chadwhitacre]

@zwn POI: http://explain.depesz.com/. (cf. #659)

[zbynekwinkler]

It is not getting better 😢

$ time curl "https://www.gittip.com/on/github/github/" -s > /dev/null

real        0m22.758s
user        0m0.024s
sys     0m0.000s

[chadwhitacre]

Dropping from Infrastructure, per #1417 (comment).

[Changaco]

Current response time is under 4 seconds for me, probably as a result of the elsewhere refactor (#1369).