Empty nova list causes expensive in predicate queries

[Andrew Laski]

Requesting a list of servers through the API runs some queries that become more expensive when there are no instances returned.

Example:

/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:1927: SAWarning: The IN-predicate on "instance_system_metadata.instance_u
uid" was invoked with an empty sequence. This results in a contradiction, which nonetheless can be expensive to evaluate. Consider alternati
ve strategies for improved performance.
  return self._in_impl(operators.in_op, operators.notin_op, other)
2013-07-24 20:42:46.126 DEBUG sqlalchemy.pool.QueuePool [-] Connection <_mysql.connection open to 'localhost' at 60bc290> checked out from po
ol from (pid=24610) __init__ /usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py:415
2013-07-24 20:42:46.127 INFO sqlalchemy.engine.base.Engine [-] SELECT instance_system_metadata.created_at AS instance_system_metadata_created_at, instance_system_metadata.updated_at AS instance_system_metadata_updated_at, instance_system_metadata.deleted_at AS instance_system_metadata_deleted_at, instance_system_metadata.deleted AS instance_system_metadata_deleted, instance_system_metadata.id AS instance_system_metadata_id, instance_system_metadata.`key` AS instance_system_metadata_key, instance_system_metadata.value AS instance_system_metadata_value, instance_system_metadata.instance_uuid AS instance_system_metadata_instance_uuid
FROM instance_system_metadata
WHERE instance_system_metadata.deleted = %s AND instance_system_metadata.instance_uuid != instance_system_metadata.instance_uuid

instance_system_metadata, instance_metadata, and instance_faults all do the same thing. You can see by the WHERE clause that this will not return any rows, but it still scans a large part of the tables.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/38809
Committed: http://github.com/openstack/nova/commit/7bbe9752bd0676947b43718a47583c0524e9a8af
Submitter: Jenkins
Branch: master

commit 7bbe9752bd0676947b43718a47583c0524e9a8af
Author: Andrew Laski <email address hidden>
Date: Fri Jul 26 09:53:31 2013 -0400

    Bypass queries which cause a contradiction

    This addresses the cause of the log lines like:

    SAWarning: The IN-predicate on "instance_system_metadata.instance_uuid"
    was invoked with an empty sequence. This results in a contradiction,
    which nonetheless can be expensive to evaluate. Consider alternative
    strategies for improved performance.

    for instance_metadata, instance_system_metadata, and instance_faults.

    It especially helps the case of a call to /servers/detail when there are
    no servers to list because each of those tables is scanned despite a
    guarantee that there will be now rows.

    Bug 1204916

    Change-Id: I62e65c4409d3e3ab8373eb66450414565c222547