[SRU] InstanceList.get_by_security_group_id can run very slow

[Paul Griffin]

[Impact]

 Backporting to Liberty Ubuntu Cloud Archive from Mitaka. The backport is
 fairly simple and clean with the exception of extra two unit tests that
 had to be ammended in order to work. The Liberty codebase still has the
 ec2 api code that is deprecated in Kilo and subsequently removed in Mitaka
 and there is a unit test for that api that was failing.

[Test Case]

 * Deploy Openstack Liberty with this patch

 * Populate some security groups and create/delete some instances, checking
   that the security groups are functioning properly.

 * Run full Tempest test suite (rev 13.0.0) against deployed cloud.

[Regression Potential]

 This patch has not received any testing with the ec2 api in future releases
 due the fact that that api is removed in M. Tempest did not find any errors
 when testing against L though so I not envisaging any regressions.

----------------------------------------------------------------------------

The nova.objects.instance.InstanceList class's get_by_security_group_id function calls the db.security_group_get function, which uses the _security_group_get_query() function to generate a query object. That query, by default, joins with the secgroup-rules table, and currently the db.security_group_get function offers no option to avoid joining with the rules. As a result:

If a group-source secgroup-rule exists on a security group with a large number of instances and a large number of rules, the db query result will be very large and take multiple seconds to complete, tying up conductor and making the system unresponsive.

Since the InstanceList.get_by_security_group_id call only aims to build a list of instances, there is no need in this case to join with the rules, and so the db.security_group_get call should optionally avoid joining with the rules table.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

commit e70468e87537965b5db61f32e72ececde84531f2
Author: Paul Griffin <email address hidden>
Date: Fri Mar 4 15:56:48 2016 +0000

    List instances for secgroup without joining on rules

    Make db.security_group_get only join rules if specified in
    the columns_to_join. This works around a performance issue
    with lots of instances and security groups.

    Co-Authored-By: Dan Smith <email address hidden>
    Change-Id: Ie3daed133419c41ed22646f9a790570ff47f0eec
    Closes-Bug: #1552971

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/355210
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=5d7a23e7be1e41fdd1b13f2f05528faed41e1b98
Submitter: Jenkins
Branch: stable/mitaka

commit 5d7a23e7be1e41fdd1b13f2f05528faed41e1b98
Author: Paul Griffin <email address hidden>
Date: Fri Mar 4 15:56:48 2016 +0000

    List instances for secgroup without joining on rules

    Make db.security_group_get only join rules if specified in
    the columns_to_join. This works around a performance issue
    with lots of instances and security groups.

    NOTE(mriedem): A legacy_v2 API test had to be updated which
    didn't exist in the original fix in Newton.

    Co-Authored-By: Dan Smith <email address hidden>
    Change-Id: Ie3daed133419c41ed22646f9a790570ff47f0eec
    Closes-Bug: #1552971
    (cherry picked from commit e70468e87537965b5db61f32e72ececde84531f2)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 14.0.0.0b3 development milestone.

[Andrew Bogott (andrewbogott)]

Any chance this could be backported to Liberty? It's still causing me trouble.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/nova 13.1.2 release.

[James Page (james-page)]

Hello Paul, or anyone else affected,

Accepted nova into liberty-proposed. The package will build now and be available in the Ubuntu Cloud Archive in a few hours, and then in the -proposed repository.

Please help us by testing this new package. To enable the -proposed repository:

  sudo add-apt-repository cloud-archive:liberty-proposed
  sudo apt-get update

Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-liberty-needed to verification-liberty-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-liberty-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

[Edward Hope-Morley (hopem)]

Deployed and tested liberty-proposed and lgtm.

[James Page (james-page)]

The verification of the Stable Release Update for nova has completed successfully and the package has now been released to -updates. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.

[James Page (james-page)]

This bug was fixed in the package nova - 2:12.0.6-0ubuntu1~cloud1
---------------

 nova (2:12.0.6-0ubuntu1~cloud1) trusty-liberty; urgency=medium
 .
   * Backport fix for 'InstanceList.get_by_security_group_id can run
     very slow' (LP: #1552971):
     - d/p/list-instances-for-secgroup-without-joining-on-rules.patch