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