security groups db queries load excessive data

Bug #1373851 reported by Kevin Benton
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
neutron
Fix Released
Medium
Kevin Benton
Icehouse
Fix Released
Undecided
Unassigned
Juno
Fix Released
Undecided
Unassigned

Bug Description

The security groups db queries are loading extra data from the ports table that is unnecessarily hindering performance.

Changed in neutron:
assignee: nobody → Kevin Benton (kevinbenton)
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (master)

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

Changed in neutron:
status: New → In Progress
tags: added: icehouse-backport-potential
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Related fix proposed to neutron (master)

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (master)

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (master)

Reviewed: https://review.openstack.org/129268
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=60dd689183469d2958d6dcb60d93a8d94ef694d1
Submitter: Jenkins
Branch: master

commit 60dd689183469d2958d6dcb60d93a8d94ef694d1
Author: Kevin Benton <email address hidden>
Date: Thu Oct 16 21:24:07 2014 -0700

    DB: Only ask for MAC instead of entire port

    Optimize a query in _get_lla_gateway_ip_for_subnet
    to only grab the column used instead of every column
    in the port table.

    Partial-Bug: #1373851
    Change-Id: I5257e1e22645f3df9a77c0967b09a0ad0cf8b251

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Reviewed: https://review.openstack.org/129264
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=6acadab5eb8b7b627e097a638d8486bef59a7f30
Submitter: Jenkins
Branch: master

commit 6acadab5eb8b7b627e097a638d8486bef59a7f30
Author: Kevin Benton <email address hidden>
Date: Thu Oct 16 21:21:15 2014 -0700

    Only fetch port_id from SG binding table

    Change a query to only retrieve the port_id instead of
    every column from the row of security group binding info.

    Partial-Bug: #1373851
    Change-Id: I0fba9c9623898ee52590207ebbb728503bb59a5b

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Reviewed: https://review.openstack.org/129270
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=8d430a7f2e903dda06d8d75d6abcd63423c4c0a1
Submitter: Jenkins
Branch: master

commit 8d430a7f2e903dda06d8d75d6abcd63423c4c0a1
Author: Kevin Benton <email address hidden>
Date: Thu Oct 16 21:27:47 2014 -0700

    Optimize query in _select_dhcp_ips_for_network_ids

    Only query the DB for relevant columns instead of
    all of the port columns.

    Partial-Bug: #1373851
    Change-Id: I32cd4a0bc6799ce77cea13188676308e3e641d19

Edgar Magana (emagana)
Changed in neutron:
importance: Undecided → Medium
Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Reviewed: https://review.openstack.org/123997
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=04df85b6e5a098f8f55bb82f04d9769763beb487
Submitter: Jenkins
Branch: master

commit 04df85b6e5a098f8f55bb82f04d9769763beb487
Author: Kevin Benton <email address hidden>
Date: Wed Sep 24 05:23:32 2014 -0700

    Improve performance of security group DB query

    The _select_ips_for_remote_group method was joining the
    IP allocation, port, allowed address pair, and security group tables
    together in a single query. Additionally, it was loading all of
    the port columns and using none of them. This resulted in a
    very expensive query with no benefit.

    This patch eliminates the unnecessary use of the port table by joining
    the IP allocation table directly to the security groups and allowed
    address pairs tables. In local testing of the method, this sped it up
    by an order of magnitude.

    Closes-Bug: #1373851
    Change-Id: I12899413004838d2d22b691f1e2f3b18f7ec2c27

Changed in neutron:
status: In Progress → Fix Committed
Yaguang Tang (heut2008)
tags: added: juno-backport-potential
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (stable/juno)

Fix proposed to branch: stable/juno
Review: https://review.openstack.org/130097

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Fix proposed to branch: stable/juno
Review: https://review.openstack.org/130098

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Fix proposed to branch: stable/juno
Review: https://review.openstack.org/130100

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Fix proposed to branch: stable/juno
Review: https://review.openstack.org/130101

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (feature/lbaasv2)

Fix proposed to branch: feature/lbaasv2
Review: https://review.openstack.org/130864

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (feature/lbaasv2)
Download full text (72.6 KiB)

Reviewed: https://review.openstack.org/130864
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=c089154a94e5872efc95eab33d3d0c9de8619fe4
Submitter: Jenkins
Branch: feature/lbaasv2

commit 62588957fbeccfb4f80eaa72bef2b86b6f08dcf8
Author: Kevin Benton <email address hidden>
Date: Wed Oct 22 13:04:03 2014 -0700

    Big Switch: Switch to TLSv1 in server manager

    Switch to TLSv1 for the connections to the backend
    controllers. The default SSLv3 is no longer considered
    secure.

    TLSv1 was chosen over .1 or .2 because the .1 and .2 weren't
    added until python 2.7.9 so TLSv1 is the only compatible option
    for py26.

    Closes-Bug: #1384487
    Change-Id: I68bd72fc4d90a102003d9ce48c47a4a6a3dd6e03

commit 17204e8f02fdad046dabdb8b31397289d72c877b
Author: OpenStack Proposal Bot <email address hidden>
Date: Wed Oct 22 06:20:15 2014 +0000

    Imported Translations from Transifex

    For more information about this automatic import see:
    https://wiki.openstack.org/wiki/Translations/Infrastructure

    Change-Id: I58db0476c810aa901463b07c42182eef0adb5114

commit d712663b99520e6d26269b0ca193527603178742
Author: Carl Baldwin <email address hidden>
Date: Mon Oct 20 21:48:42 2014 +0000

    Move disabling of metadata and ipv6_ra to _destroy_router_namespace

    I noticed that disable_ipv6_ra is called from the wrong place and that
    in some cases it was called with a bogus router_id because the code
    made an incorrect assumption about the context. In other case, it was
    never called because _destroy_router_namespace was being called
    directly. This patch moves the disabling of metadata and ipv6_ra in
    to _destroy_router_namespace to ensure they get called correctly and
    avoid duplication.

    Change-Id: Ia76a5ff4200df072b60481f2ee49286b78ece6c4
    Closes-Bug: #1383495

commit f82a5117f6f484a649eadff4b0e6be9a5a4d18bb
Author: OpenStack Proposal Bot <email address hidden>
Date: Tue Oct 21 12:11:19 2014 +0000

    Updated from global requirements

    Change-Id: Idcbd730f5c781d21ea75e7bfb15959c8f517980f

commit be6bd82d43fbcb8d1512d8eb5b7a106332364c31
Author: Angus Lees <email address hidden>
Date: Mon Aug 25 12:14:29 2014 +1000

    Remove duplicate import of constants module

    .. and enable corresponding pylint check now the only offending instance
    is fixed.

    Change-Id: I35a12ace46c872446b8c87d0aacce45e94d71bae

commit 9902400039018d77aa3034147cfb24ca4b2353f6
Author: rajeev <email address hidden>
Date: Mon Oct 13 16:25:36 2014 -0400

    Fix race condition on processing DVR floating IPs

    Fip namespace and agent gateway port can be shared by multiple dvr routers.
    This change uses a set as the control variable for these shared resources
    and ensures that Test and Set operation on the control variable are
    performed atomically so that race conditions do not occur among
    multiple threads processing floating IPs.
    Limitation: The scope of this change is limited to addressing the race
    condition described in the bug report. It may not address other issues
    such as pre-existing issue wit...

Revision history for this message
Wei Wang (damon-devops) wrote :

nice work

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Change abandoned on neutron (stable/juno)

Change abandoned by Yaguang Tang (<email address hidden>) on branch: stable/juno
Review: https://review.openstack.org/130097

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (stable/juno)

Reviewed: https://review.openstack.org/130101
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=c14b58b1f8ba2f87c717441cdfca1c4c1be2f050
Submitter: Jenkins
Branch: stable/juno

commit c14b58b1f8ba2f87c717441cdfca1c4c1be2f050
Author: Kevin Benton <email address hidden>
Date: Wed Sep 24 05:23:32 2014 -0700

    Improve performance of security group DB query

    The _select_ips_for_remote_group method was joining the
    IP allocation, port, allowed address pair, and security group tables
    together in a single query. Additionally, it was loading all of
    the port columns and using none of them. This resulted in a
    very expensive query with no benefit.

    This patch eliminates the unnecessary use of the port table by joining
    the IP allocation table directly to the security groups and allowed
    address pairs tables. In local testing of the method, this sped it up
    by an order of magnitude.

    Closes-Bug: #1373851
    Change-Id: I12899413004838d2d22b691f1e2f3b18f7ec2c27
    (cherry picked from commit 04df85b6e5a098f8f55bb82f04d9769763beb487)

tags: added: in-stable-juno
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (stable/icehouse)

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

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

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (stable/juno)

Reviewed: https://review.openstack.org/130100
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=25c828007d62c0114a0e3eb4c4c17b93cf7b7481
Submitter: Jenkins
Branch: stable/juno

commit 25c828007d62c0114a0e3eb4c4c17b93cf7b7481
Author: Kevin Benton <email address hidden>
Date: Thu Oct 16 21:27:47 2014 -0700

    Optimize query in _select_dhcp_ips_for_network_ids

    Only query the DB for relevant columns instead of
    all of the port columns.

    Partial-Bug: #1373851
    Change-Id: I32cd4a0bc6799ce77cea13188676308e3e641d19
    (cherry picked from commit 8d430a7f2e903dda06d8d75d6abcd63423c4c0a1)

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Reviewed: https://review.openstack.org/130098
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=c5ae9dd2789570ad0c885aa88ae08e0a24e41d52
Submitter: Jenkins
Branch: stable/juno

commit c5ae9dd2789570ad0c885aa88ae08e0a24e41d52
Author: Kevin Benton <email address hidden>
Date: Thu Oct 16 21:21:15 2014 -0700

    Only fetch port_id from SG binding table

    Change a query to only retrieve the port_id instead of
    every column from the row of security group binding info.

    Partial-Bug: #1373851
    Change-Id: I0fba9c9623898ee52590207ebbb728503bb59a5b
    (cherry picked from commit 6acadab5eb8b7b627e097a638d8486bef59a7f30)

Yaguang Tang (heut2008)
tags: removed: juno-backport-potential
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (stable/icehouse)

Reviewed: https://review.openstack.org/134446
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=90e841998bb1973f8bb1e173d6a47b1f173ccdf0
Submitter: Jenkins
Branch: stable/icehouse

commit 90e841998bb1973f8bb1e173d6a47b1f173ccdf0
Author: Yaguang Tang <email address hidden>
Date: Fri Nov 14 14:07:43 2014 +0800

    Optimize query in _select_dhcp_ips_for_network_ids

    Only query the DB for relevant columns instead of
    all of the port columns.

     Conflicts:
            neutron/db/securitygroups_rpc_base.py

    Partial-Bug: #1373851
    (cherry picked from commit 8d430a7f2e903dda06d8d75d6abcd63423c4c0a1)

    Change-Id: I32cd4a0bc6799ce77cea13188676308e3e641d19

tags: added: in-stable-icehouse
Thierry Carrez (ttx)
Changed in neutron:
milestone: none → kilo-1
status: Fix Committed → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Change abandoned on neutron (stable/icehouse)

Change abandoned by Ihar Hrachyshka (<email address hidden>) on branch: stable/icehouse
Review: https://review.openstack.org/134442
Reason: Not critical for Icehouse.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

Change abandoned by Ihar Hrachyshka (<email address hidden>) on branch: stable/icehouse
Review: https://review.openstack.org/134449
Reason: Not critical for Icehouse.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Change abandoned on neutron (master)

Change abandoned by Kyle Mestery (<email address hidden>) on branch: master
Review: https://review.openstack.org/129200
Reason: This review is > 4 weeks without comment, and failed Jenkins the last time it was checked. We are abandoning this for now. Feel free to reactivate the review by pressing the restore button and leaving a 'recheck' comment to get fresh test results.

Thierry Carrez (ttx)
Changed in neutron:
milestone: kilo-1 → 2015.1.0
Revision history for this message
norman shen (jshen28) wrote :

Hi,

  is there an estimation on how much performance improvement we will have? thank you.

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.