964822 - Buildbot improvement indices

Status: RESOLVED FIXED

(Release Engineering :: General, defect)

Description

[Brandon Johnson [:cyborgshadow]]

Today I was asked by dustin about some builbdot slowness.

Upon investigating, I found a bunch of slow queries that could be drastically improved through a few indices. Both new indices have 100% cardinality and I added both to be certain that other queries selecting from this table might be positively affected, not just the one I was troubleshooting.

Table:
buildbot.changes

Query:
SELECT changes.id AS changes_id, changes.number AS changes_number, changes.branch AS changes_branch, changes.revision AS changes_revision, changes.who AS changes_who, changes.comments AS changes_comments, changes.`when` AS changes_when 
FROM changes 
WHERE changes.who = 'sendchange-unittest' AND changes.`when` = '2014-01-28 14:20:56' AND changes.number = 3364149 AND changes.comments = 'Bug 959958 - Implement ImageData constructor. r=bz' AND changes.branch = 'mozilla-inbound-win32-debug-unittest' AND changes.revision = '4a801fc05819';


New Indices: 
ALTER TABLE changes ADD KEY `when` (`when`);
ALTER TABLE changes ADD KEY `number` (`number`);



After these changes:
https://rpm.newrelic.com/accounts/263620/dashboard/3101981/page/2?tw[end]=1390925460&tw[start]=1390903860

- The slow query log went from having a new entry every 1-2 seconds to having only 2 in the last 40 minutes. (Slow queries dropped off the planet) 
- Resource utilization on the box dropped to an all time low, while network traffic was doing its daily ramp up due to time of day (this indicates positive success in the change and that it had a meaningful impact). 

All in all, positive and proactive win!

Comment 1

[Brandon Johnson [:cyborgshadow]]

At the request of Callek also added these to the seamonkey_status.changes table on generic in scl3.