1030145 - assistance optimising a bugzilla query

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[:glob ✱]

hello DBAs :)

as part of the year-long goal of both the bugzilla and IT teams i've been analysing bugzilla queries which may benefit from optimisation.

the following query is the current slowest query executed when loading a bug, and i would appreciate your feedback with regards to optimisations if possible.

SELECT DISTINCT
    groups.id,
    name,
    description,
    CASE
        WHEN bug_group_map.group_id IS NOT NULL THEN 1
        ELSE 0
    END ison,
    CASE
        WHEN groups.id IN (
            13, 20, 19, 87, 30, 31, 10, 155, 3, 9, 18, 4, 7, 8, 1, 94, 93,
            132, 21, 57, 53, 48, 92, 149, 146, 23, 123, 151, 150, 2, 125, 142,
            119, 69, 136, 130, 84, 11, 35, 159, 126, 158, 111, 144, 88, 160,
            38, 14, 128, 90, 32, 156, 6, 42, 26, 120, 121, 122, 85, 86, 109,
            89, 140, 96, 106, 100, 101, 73, 152, 153, 113, 116, 58, 76, 17,
            65, 134, 91, 52, 12, 50, 103
        )
        THEN
            1
        ELSE
            0
    END ingroup,
    isactive,
    membercontrol,
    othercontrol
FROM
    groups
    LEFT JOIN bug_group_map
        ON bug_group_map.group_id = groups.id
        AND bug_id = 35
    LEFT JOIN group_control_map
        ON group_control_map.group_id = groups.id
        AND group_control_map.product_id = 7
WHERE
    isbuggroup = 1
ORDER BY
    description

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: groups
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 159
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bug_group_map
         type: eq_ref
possible_keys: bug_group_map_bug_id_idx,bug_group_map_group_id_idx
          key: bug_group_map_bug_id_idx
      key_len: 6
          ref: const,bugs_bmo_201404.groups.id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: group_control_map
         type: eq_ref
possible_keys: group_control_map_product_id_idx,group_control_map_group_id_idx
          key: group_control_map_product_id_idx
      key_len: 5
          ref: const,bugs_bmo_201404.groups.id
         rows: 1
     filtered: 100.00
        Extra: NULL


the long list of group_ids is generated from two queries as well some logic in perl so that can't be replaced.


thank you!

Comment 1

[Brandon Johnson [:cyborgshadow]]

This query returns in 0.00 to 0.01 seconds on all the bugzilla read slaves as well as the master. What kind of slowness were you seeing?

A hundredth of a second is pretty quick when given a case statement.

The secondary tables are completely optimal. The only optimization I could see here is an added index on the groups table to index the majority of the table that you're selecting with that query: (isactive, name, description(255), isbuggroup), but at only 165 records I don't think that'd speed this up much at all. This table lives in memory according to the InnoDB buffer pool.

Have we tried caching it? It's only 32KB.

Comment 2

[:glob ✱]

(In reply to Brandon Johnson [:cyborgshadow] from comment #1)
> Have we tried caching it? It's only 32KB.

with the query cache?  that's disabled (see bug 974338 comment 27).

> This query returns in 0.00 to 0.01 seconds on all the bugzilla read slaves
> as well as the master. What kind of slowness were you seeing?

it's our #1 query in terms of time taken when loading a bug (in ms):

 count     avg    min    max  line
  2949    11.3    6.6   69.5  Bugzilla/Bug.pm:3682 # groups

all other db queries executed during show_bug are quicker than 10ms, most are quicker than 4ms.

as such, i was hoping to optimise this query somehow.  if it's already as good as it can get from a db perspective, then i'm happy here and you can close this bug.

Comment 3

[Brandon Johnson [:cyborgshadow]]

No, not with query cache. (I'll never recommend that, but that's outside the scope of this bug).

I mean either caching within the webhead or via memcached, etc.

It's so small that caching it on the webhead would even be a very good idea (if you're not using memcached). Have the webhead run it once a minute or so and return a cached copy to the client 32K is really tiny and groups aren't modified enough that a 1-60 second old group would make a difference for anyone.

Comment 4

[:glob ✱]

thanks; caching this query with perl isn't viable for us right now for various reasons (mostly surrounding invalidation of bug-related data).

i'll resolve this bug as it looks like there aren't any obvious db-level magic that will help here.