]> review.fuel-infra Code Review - openstack-build/neutron-build.git/commit
Add index on db "allocated" columns
authorElena Ezhova <eezhova@mirantis.com>
Tue, 20 Jan 2015 16:19:43 +0000 (19:19 +0300)
committerElena Ezhova <eezhova@mirantis.com>
Fri, 23 Jan 2015 16:18:52 +0000 (19:18 +0300)
commitf72b07e5d184946271b090905c2f2ea67fc1479a
tree08bbd0e358c52382214eec7aadb4bd2cb0d83f93
parent08438a6d3660f263fdf65b45c8c177a54f088ae7
Add index on db "allocated" columns

ml2_vxlan_allocations, ml2_gre_allocations, ml2_vlan_allocations tables
have the 'allocated' field.

There are a lot of similar queries to these tables which look
like the following:

SELECT ml2_vxlan_allocations.vxlan_vni
AS ml2_vxlan_allocations_vxlan_vni,
ml2_vxlan_allocations.allocated
AS ml2_vxlan_allocations_allocated
FROM ml2_vxlan_allocations
WHERE ml2_vxlan_allocations.allocated = 0 LIMIT 1;

Performing such selects can take quite a lot of time and if a transaction
which performs allocation is executed in parallel, it can lead to
allocation failure and retry.

Adding an index on "allocated" column significantly improves
the performance. For ml2_vlan_allocations table created
an index on (physical_network, allocation) together.

Example for MySQL for execution of query
select * from ml2_vxlan_allocations where allocated = 0;
when on the table with ~3 mln entries, ~500K of which
have allocated = 0:
+-----------------------+---------------------+
|No index on "allocated"| Index on "allocated"|
+---------------------------------------------+
|      2.02 sec         |       0.43 sec      |
+-----------------------+---------------------+

Closes-Bug: #1412348
Change-Id: Ie90ba611dcae6bd0cb7686a0c7b29b9484eae693
neutron/db/migration/alembic_migrations/versions/26b54cf9024d_add_index_on_allocated.py [new file with mode: 0644]
neutron/db/migration/alembic_migrations/versions/HEAD
neutron/plugins/ml2/drivers/type_gre.py
neutron/plugins/ml2/drivers/type_vlan.py
neutron/plugins/ml2/drivers/type_vxlan.py