List ports operation reloads security group associations for each port

[Salvatore Orlando]

All the plugins implementing the security groups mixin invoke the extend_port_dict_security_group method for each port in get_ports: https://github.com/openstack/quantum/blob/master/quantum/plugins/openvswitch/ovs_quantum_plugin.py#L598

The implementation of the above method in the mixin executes another query in the database: https://github.com/openstack/quantum/blob/master/quantum/db/securitygroups_db.py#L382

As a result, get_ports results in at least(*) N+1 queries. This is not great news for working at scale, since this could be easily improved.

These are some results from the current source code (comple API request/response on local host, against in memory-db)

#### PORTS:100
#### ELAPSED TIME:0.355845928192
#### PORTS:200
#### ELAPSED TIME:0.682465076447
#### PORTS:300
#### ELAPSED TIME:1.22520112991
#### PORTS:400
#### ELAPSED TIME:1.57488203049
#### PORTS:500
#### ELAPSED TIME:1.78997802734
#### PORTS:600
#### ELAPSED TIME:2.14011096954
#### PORTS:700
#### ELAPSED TIME:2.43111586571
#### PORTS:800
#### ELAPSED TIME:3.13826489449
#### PORTS:900
#### ELAPSED TIME:3.53332614899
#### PORTS:1000
#### ELAPSED TIME:3.43961691856

Where as the following results are obtained with a joined load:

#### PORTS:100
#### ELAPSED TIME:0.26810002327
#### PORTS:200
#### ELAPSED TIME:0.464941978455
#### PORTS:300
#### ELAPSED TIME:0.683457136154
#### PORTS:400
#### ELAPSED TIME:1.03992795944
#### PORTS:500
#### ELAPSED TIME:1.34910178185
#### PORTS:600
#### ELAPSED TIME:1.503729105
#### PORTS:700
#### ELAPSED TIME:1.80103898048
#### PORTS:800
#### ELAPSED TIME:1.95511293411
#### PORTS:900
#### ELAPSED TIME:2.32195305824
#### PORTS:1000
#### ELAPSED TIME:2.76952195168

These numbers are probably not very well representative - and more measurements should be provided.
However, they show that there's undoubtedly a gain in performance - and considering a considerably smaller number of queries performed, it will improve also scalability.

(*) at least because if the plugin supports multiple extension which add attributes to the port resource the number of queries might actually increase.

[Salvatore Orlando (salvatore-orlando)]

I am setting importance for this bug report as 'low', meaning that the server is not broken at all because it, but that's something which can make the server work better at scale.

Also, the same kind of bug can be reported pretty much for each 'attribute' extension currently used in quantum.
This work can fall in the framework of db 'improvements' agreed at the summit.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/27660
Committed: http://github.com/openstack/quantum/commit/6f01194e9c30afc2656cef9b15fea5ae688e93d7
Submitter: Jenkins
Branch: master

commit 6f01194e9c30afc2656cef9b15fea5ae688e93d7
Author: Salvatore Orlando <email address hidden>
Date: Mon Apr 29 01:40:47 2013 +0200

    Perform a joined query for ports and security group associations

    bug 1174111

    Instead of loading security group associations with a separate query
    each time a port is loaded from the database, perform the load operation
    with a join using a joined sqlalchemy relationship.
    Also, this patch removes the need for invoking the mixin method
    'extend_port_dict_security_group' from the plugin code.

    Change-Id: I40b22281d45ff4f4bf8149211883799a9051c1a0

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/28479
Committed: http://github.com/openstack/quantum/commit/cfaec705c866cf99397612d417e70a8d0412bc0d
Submitter: Jenkins
Branch: master

commit cfaec705c866cf99397612d417e70a8d0412bc0d
Author: Salvatore Orlando <email address hidden>
Date: Wed May 8 00:07:17 2013 +0200

    Jointly load ExternalNetwork with Network

    Bug 1177572

    This patch leverages the same approach as bug 1174111 for avoiding
    loading ExternalNetwork element from db for each network element.
    make_network_dict, _extend_network_dict_l3 are refactored accordingly.

    Also, this patch sligthly alters the logic of process_l3_network_create
    and process_l3_network_update as these methods assumed
    _extend_network_dict_l3 was always executed before retuerning.

    Finally, calls to get_network and get_networks in plugins which only
    extended the response with l3 attributes have been completely removed.

    Change-Id: I3b4c28ec0c78f731a534c2b66150d529fd797e72