a high-level goal across multiple teams this year is to improve bugzilla.mozilla.org’s performance, specifically focusing on the time it takes to load a bug (show_bug.cgi).
towards this end, in q1 2014 i focused primarily on two things: implementing a framework for bugzilla to use memcached, and deep instrumentation of bmo in our production environment to help identify areas which require optimisation and could leverage memcached.
i’ll talk more about memcached in a later blog post. today i’ll talk about a single little query.
the data gathered quickly identified a single query used to determine a user’s group membership was by far the slowest query, averaging more than 200 ms to complete, and was executed on every page:
SELECT DISTINCT groups.id FROM groups, user_group_map, group_group_map WHERE user_group_map.user_id = 3881 AND ( (user_group_map.isbless = 1 AND groups.id=user_group_map.group_id) OR (groups.id = group_group_map.grantor_id AND group_group_map.grant_type = 1 AND group_group_map.member_id IN (20,19,10,9,94,23,49,2,119,..)) )
in bug 993894 i rewrote this query to:
SELECT DISTINCT group_id FROM user_group_map WHERE user_id = 3881 AND isbless = 1 UNION SELECT DISTINCT grantor_id FROM group_group_map WHERE grant_type = 1 AND member_id IN (20,19,10,9,94,23,49,2,119,..)
which executes almost instantly.
the yellow bar on the following graph shows when this fix was deployed to bugzilla.mozilla.org: