missing index on reservations.uuid

[Andrew Laski]

In a large deployment we're getting entries in our slow query log for querying the reservations table on the uuid column. An example is

# Time: 130722 19:01:43
# User@Host: nova_20130124[nova_20130124] @ [10.23.244.148]
# Query_time: 5.253471 Lock_time: 0.000084 Rows_sent: 3 Rows_examined: 1110157
SET timestamp=1374519703;
SELECT reservations.created_at AS reservations_created_at, reservations.updated_at AS reservations_updated_at, reservations.deleted_at AS reservations_deleted_at, reservations.deleted AS reservations_deleted, reservations.id AS reservations_id, reservations.uuid AS reservations_uuid, reservations.usage_id AS reservations_usage_id, reservations.project_id AS reservations_project_id, reservations.resource AS reservations_resource, reservations.delta AS reservations_delta, reservations.expire AS reservations_expire
FROM reservations
WHERE reservations.deleted = 0 AND reservations.uuid IN ('33c6c791-72f8-48f2-b0ee-47c6084d2956', 'ad642dc9-1faf-4906-859c-b9563491fdd2', '29f8f769-a6bc-4a23-8bdb-29900f872d3a') FOR UPDATE;

This comes from the _quota_reservations_query method used by reservation_commit and reservation_rollback.

There should be an index on reservations.uuid.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/38200
Committed: http://github.com/openstack/nova/commit/577d2ae23461d12dcb6c43456cd346a57c548ac3
Submitter: Jenkins
Branch: master

commit 577d2ae23461d12dcb6c43456cd346a57c548ac3
Author: Andrew Laski <email address hidden>
Date: Mon Jul 22 16:58:20 2013 -0400

    Add index to reservations.uuid column

    Committing or rolling back reservations gets slow once a large number of
    reservations have built up in the db. Since they're being queried by
    uuid this adds an index to speed up that operation.

    Note that this can be slow with a large number of rows.

    bug 1203872

    Change-Id: Ida503206194a567a5c6811a304154d067c5faa83