Missing index for expire_reservations

[Vish Ishaya]

While investigating some database performance problems, we discovered that there is no index on deleted for the reservations table. When this table gets large, the expire_reservations code will do a full table scan and take multiple seconds to complete. Because the expire runs on a periodic, it can slow down the master database significantly and cause nova or cinder to become extremely slow.

> EXPLAIN UPDATE reservations SET updated_at=updated_at, deleted_at='2014-07-24 22:26:17', deleted=id WHERE reservations.deleted = 0 AND reservations.expire < '2014-07-24 22:26:11';
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+
| 1 | SIMPLE | reservations | index | NULL | PRIMARY | 4 | NULL | 950366 | Using where; Using temporary |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+------------------------------+

An index on (deleted, expire) would be the most efficient.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/109660

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/109662

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/109662
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=4d56b91628518629b47997b93acd9cb1e48f87f5
Submitter: Jenkins
Branch: master

commit 4d56b91628518629b47997b93acd9cb1e48f87f5
Author: Vishvananda Ishaya <email address hidden>
Date: Fri Jul 25 10:15:27 2014 -0700

    Add index for reservations on (deleted, expire)

    the query for expire_reservations currently does a full table scan.
    This adds an index so frequent invocations of expire does not bog
    down the database.

    Change-Id: Ic6f6e4262746753d869ad37b8aaaa5ffc1c4efef
    Resolves-bug: 1348720

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/109711

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/109711
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=08b90190085a11bc99112472944c193672061e1f
Submitter: Jenkins
Branch: master

commit 08b90190085a11bc99112472944c193672061e1f
Author: Vishvananda Ishaya <email address hidden>
Date: Fri Jul 25 13:30:21 2014 -0700

    Modify the index migration slightly for backport

    If we choose to backport this fix, the migration should be catching
    an operational error. Also add a log statement as requested by the
    nova review.

    Change-Id: Ib33b8711900db9dc2826a099ff3cc6adffc8babb
    Partial-bug: 1348720

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/110074

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/109660
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=b67eedb8e255776e853536f7a86217845dc57ecf
Submitter: Jenkins
Branch: master

commit b67eedb8e255776e853536f7a86217845dc57ecf
Author: Vishvananda Ishaya <email address hidden>
Date: Fri Jul 25 10:03:26 2014 -0700

    Add index for reservations on (deleted, expire)

    the query for expire_reservations currently does a full table scan.
    This adds an index so frequent invocations of expire does not bog
    down the database.

    Change-Id: I82c6064159d7bfa7f4dc7fca5a7c9bc52b0f07fb
    Resolves-bug: 1348720

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/110074
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=479d6050f3f07e12488e6bb9a40b0aec3452a19c
Submitter: Jenkins
Branch: master

commit 479d6050f3f07e12488e6bb9a40b0aec3452a19c
Author: Vishvananda Ishaya <email address hidden>
Date: Mon Jul 28 10:39:29 2014 -0700

    Further cleanup of reservations index

    It was discovered that the duplicate index checking does not work
    on db2, so this fixes it.

    Change-Id: I1a80b9c1fb6cfe916a2110de3151a09fa3618e75
    Partial-bug: 1348720

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.openstack.org/112107

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/112108

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/112107
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=f4454f4c6962dd2c57c08dc7fecfcdebe7924e3b
Submitter: Jenkins
Branch: master

commit f4454f4c6962dd2c57c08dc7fecfcdebe7924e3b
Author: Vishvananda Ishaya <email address hidden>
Date: Tue Aug 5 11:34:09 2014 -0700

    Add expire reservations in backport position.

    Change-Id: If0e58da50ebe9b50b414737a9bd81d93752506e2
    Related-bug: #1348720

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/112417

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/112108
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=b53adea2d02458e20bb165c18c856b6816ab1983
Submitter: Jenkins
Branch: stable/icehouse

commit b53adea2d02458e20bb165c18c856b6816ab1983
Author: Vishvananda Ishaya <email address hidden>
Date: Tue Aug 5 11:34:09 2014 -0700

    Add expire reservations in backport position.

    Change-Id: If0e58da50ebe9b50b414737a9bd81d93752506e2
    Related-bug: #1348720
    (cherry picked from commit f4454f4c6962dd2c57c08dc7fecfcdebe7924e3b)

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/112417
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=3b4d5b01abaea01ac1b02e6d68c137b7f792d344
Submitter: Jenkins
Branch: stable/icehouse

commit 3b4d5b01abaea01ac1b02e6d68c137b7f792d344
Author: Vishvananda Ishaya <email address hidden>
Date: Wed Aug 6 02:58:12 2014 -0700

    Add fix for reservation index to icehouse

    Cinder doesn't have backportable migrations, so ensure the index
    is added every time the migration runs.

    DB migration in master that does the same thing was made in the
    following commits:
      4d56b91628518629b47997b93acd9cb1e48f87f5
      08b90190085a11bc99112472944c193672061e1f
      479d6050f3f07e12488e6bb9a40b0aec3452a19c

    Change-Id: I8bdab1a70f0076312aba4d0b6c4417b335947680
    Resolves-bug: #1348720