Very slow net-list command with --shared=false argument

[Piotr Misiak]

I have a very slow network list response time when i add --shared false parameter to neutron cli command. Look at this: http://paste.openstack.org/show/584409/
without --shared False argument I've got response in 2 seconds
with --shared False argument I've got response in 32 seconds

I debugged a little bit and I see that database returns over 182000 records which is 200MB of data but there are only 4000 unique records.
There are more or less 45 duplicates for every unique record and I have 45 records in neutron RBAC so I see a correlation here.

The issue is quite important because Horizon uses the request with shared=false to show up the "Launch Instance" form and it takes ages.

I have 127 neutron networks in my env.
I use Midonet plugin.

Here you have a SQL query which returns over 182000 records:
http://paste.openstack.org/show/584642/
SQL query result is filtered by neutron-server (100% CPU) an produces only a few networks in the response.

This is stable/mitaka version installed from source.

[OpenStack Infra (hudson-openstack)]

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

[Kevin Benton (kevinbenton)]

Can you cherry-pick the proposed patch and confirm that it fixes the issue for you? It should eliminate the exponential growth.

[Kevin Benton (kevinbenton)]

There will still be a record for each network's RBAC entry, but changing that will require a much more invasive fix.

[Piotr Misiak (piotr-misiak)]

Great job Kevin!

The patch fixes the issue. Now the request takes 2sec instead of 32sec.
I verified --shared False/True filtering and it works (I have got different results).
Please backport the patch to stable/mitaka branch.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/383542
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=145dbaab21c7b541294869eec547731a9694cbad
Submitter: Jenkins
Branch: master

commit 145dbaab21c7b541294869eec547731a9694cbad
Author: Kevin Benton <email address hidden>
Date: Thu Oct 6 21:25:58 2016 -0700

    Get rid of double-join to rbac_entries without filter

    apply_filters_to_query was performing an outerjoin to rbac_entries
    unconditionally when model_query could have already performed an
    outerjoin (if the request was from an unprivileged user) and/or when
    the join wasn't even necessary (the '?shared=False' query that uses
    a subquery and not a join). This resulted in terrible performance
    because of cartesian products of rbac entries with themselves.

    This fixes the issue by ensuring there is only an outerjoin to the
    rbac table if it's going to be used for a filter condition and it's
    not already joined because of a query scope imposed due to the user
    not being privileged.

    Unfortunately this doesn't include tests to prevent regressions because
    we don't have any methods for testing the performance of individual
    queries.

    Closes-Bug: #1630939
    Change-Id: I4364f4a97a29041e86b2fbd8aa895578153f4cf9

[Piotr Misiak (piotr-misiak)]

Will you backport the patch to the sable/mitaka branch?

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/mitaka
Review: https://review.openstack.org/384841

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/liberty
Review: https://review.openstack.org/384846

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/384841
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=369faa28731efd8f79820a0f8aa45dc6d614bec7
Submitter: Jenkins
Branch: stable/mitaka

commit 369faa28731efd8f79820a0f8aa45dc6d614bec7
Author: Kevin Benton <email address hidden>
Date: Thu Oct 6 21:25:58 2016 -0700

    Get rid of double-join to rbac_entries without filter

    apply_filters_to_query was performing an outerjoin to rbac_entries
    unconditionally when model_query could have already performed an
    outerjoin (if the request was from an unprivileged user) and/or when
    the join wasn't even necessary (the '?shared=False' query that uses
    a subquery and not a join). This resulted in terrible performance
    because of cartesian products of rbac entries with themselves.

    This fixes the issue by ensuring there is only an outerjoin to the
    rbac table if it's going to be used for a filter condition and it's
    not already joined because of a query scope imposed due to the user
    not being privileged.

    Unfortunately this doesn't include tests to prevent regressions because
    we don't have any methods for testing the performance of individual
    queries.

    Closes-Bug: #1630939
    Change-Id: I4364f4a97a29041e86b2fbd8aa895578153f4cf9
    (cherry picked from commit 145dbaab21c7b541294869eec547731a9694cbad)

[OpenStack Infra (hudson-openstack)]

Change abandoned by Ihar Hrachyshka (<email address hidden>) on branch: stable/liberty
Review: https://review.openstack.org/384846
Reason: Liberty is in CVE only mode.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 10.0.0.0b1 development milestone.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/newton
Review: https://review.openstack.org/421982

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/421982
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=4f985fa5e62038f698b7cdd3c432cc7a53d5a32f
Submitter: Jenkins
Branch: stable/newton

commit 4f985fa5e62038f698b7cdd3c432cc7a53d5a32f
Author: Kevin Benton <email address hidden>
Date: Thu Oct 6 21:25:58 2016 -0700

    Get rid of double-join to rbac_entries without filter

    apply_filters_to_query was performing an outerjoin to rbac_entries
    unconditionally when model_query could have already performed an
    outerjoin (if the request was from an unprivileged user) and/or when
    the join wasn't even necessary (the '?shared=False' query that uses
    a subquery and not a join). This resulted in terrible performance
    because of cartesian products of rbac entries with themselves.

    This fixes the issue by ensuring there is only an outerjoin to the
    rbac table if it's going to be used for a filter condition and it's
    not already joined because of a query scope imposed due to the user
    not being privileged.

    Unfortunately this doesn't include tests to prevent regressions because
    we don't have any methods for testing the performance of individual
    queries.

    Closes-Bug: #1630939
    Change-Id: I4364f4a97a29041e86b2fbd8aa895578153f4cf9
    (cherry picked from commit 145dbaab21c7b541294869eec547731a9694cbad)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 9.2.0 release.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 8.4.0 release.