Missing index on allocated

Bug #1412348 reported by Attila Fazekas
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
neutron
Fix Released
Medium
Elena Ezhova

Bug Description

ml2_vxlan_allocations, ml2_gre_allocations, ml2_vlan_allocations tables has field named 'allocated'.

These tables frequently used by similar queries:
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;

This does select without an index, which causes very poor performance.
If Transaction which involved in allocation took long time, in parallel can lead to an allocation failure and retry.

These tables needs to have index on the 'allocated' field.

In the ml2_vlan_allocations table case consider creating an index on (physical_network, allocation) together.

Tags: db
description: updated
Elena Ezhova (eezhova)
Changed in neutron:
assignee: nobody → Elena Ezhova (eezhova)
tags: added: db
Changed in neutron:
importance: Undecided → Medium
status: New → Confirmed
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/148617

Changed in neutron:
status: Confirmed → In Progress
Revision history for this message
Elena Ezhova (eezhova) wrote :

Creating index on "allocated" field indeed improves the performance quite significantly, especially for MySQL:
https://docs.google.com/a/mirantis.com/spreadsheets/d/1VP3bzhIPjTUkT_0Mpm_IaK88Z7S0rPUXJZKLgM2O17k/edit#gid=0

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

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

commit f72b07e5d184946271b090905c2f2ea67fc1479a
Author: Elena Ezhova <email address hidden>
Date: Tue Jan 20 19:19:43 2015 +0300

    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

Changed in neutron:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
Changed in neutron:
milestone: none → kilo-2
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in neutron:
milestone: kilo-2 → 2015.1.0
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.