629684 - Add index on buildsets.sourcestampid in buildbot_schedulers

Status: RESOLVED FIXED

(mozilla.org Graveyard :: Server Operations, task)

Description

[Chris AtLee [:catlee]]

I need an index created on the buildsets.sourcestampid column in the buildbot_schedulers database.

If this will lock the db for more than a few seconds, please let me know so we can coordinate a time.

Also, if analyze_table is quick and/or doesn't lock the tables, can it be run against all the tables?  There are several tables where mysql thinks the index cardinality is null.

Comment 1

[Dave Miller [:justdave]]

mysql> alter table buildsets add key `buildsets_sourcestampid` (`sourcestampid`);
Query OK, 1252530 rows affected (15.80 sec)
Records: 1252530  Duplicates: 0  Warnings: 0

Comment 2

[Dave Miller [:justdave]]

>[root@tm-b01-master01 ~]# mysql buildbot_schedulers -e "analyze table `mysql buildbot_schedulers --skip-column-names -e 'show tables' | tr '\n' ',' | sed 's/,$//'`"
>+--------------------------------------------------+---------+----------+-----------------------------+
>| Table                                            | Op      | Msg_type | Msg_text                    |
>+--------------------------------------------------+---------+----------+-----------------------------+
>| buildbot_schedulers.buildrequests                | analyze | status   | OK                          | 
>| buildbot_schedulers.builds                       | analyze | status   | OK                          | 
>| buildbot_schedulers.buildset_properties          | analyze | status   | OK                          | 
>| buildbot_schedulers.buildsets                    | analyze | status   | OK                          | 
>| buildbot_schedulers.change_files                 | analyze | status   | OK                          | 
>| buildbot_schedulers.change_links                 | analyze | status   | Table is already up to date | 
>| buildbot_schedulers.change_properties            | analyze | status   | OK                          | 
>| buildbot_schedulers.changes                      | analyze | status   | OK                          | 
>| buildbot_schedulers.patches                      | analyze | status   | Table is already up to date | 
>| buildbot_schedulers.scheduler_changes            | analyze | status   | OK                          | 
>| buildbot_schedulers.scheduler_upstream_buildsets | analyze | status   | OK                          | 
>| buildbot_schedulers.schedulers                   | analyze | status   | OK                          | 
>| buildbot_schedulers.sourcestamp_changes          | analyze | status   | OK                          | 
>| buildbot_schedulers.sourcestamps                 | analyze | status   | OK                          | 
>| buildbot_schedulers.version                      | analyze | status   | OK                          | 
>+--------------------------------------------------+---------+----------+-----------------------------+