Some DB queries are reported slow as no indices are used

Bug #1505664 reported by Moshe Elisha
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mistral
Fix Released
High
Winson Chan

Bug Description

When enabling slow SQL log in MariaDB with "log_queries_not_using_indexes = 1" we can find that some scheduled queries are executed without the use of indexes.

Attached is a log of such a setup that has an empty Mistral DB and no work is being done on Mistral.
Taking this log on a setup where Mistral is in active use - might result in more schema / queries that should be improved.

Indexes to add:

class Definition
* is_system

class ActionDefinition
* action_class

class Execution
* type
* project_id
* state
* updated_at

class Environment
* name

class DelayedCall
* execution_time
* processing

class CronTrigger
* workflow_name
* next_execution_time

Revision history for this message
Moshe Elisha (melisha) wrote :
Changed in mistral:
importance: Undecided → High
status: New → Confirmed
assignee: nobody → Renat Akhmerov (rakhmerov)
assignee: Renat Akhmerov (rakhmerov) → Winson Chan (winson-c-chan)
Revision history for this message
Renat Akhmerov (rakhmerov) wrote :

TODO(rakhmerov): Add all needed info about what indexes need to be created.

tags: added: liberty-backport-potential
Changed in mistral:
milestone: none → mitaka-1
Revision history for this message
Moshe Elisha (melisha) wrote :

Adding a log of MariaDB slow sql queries while performing basic operations against Mistral.

description: updated
description: updated
description: updated
Moshe Elisha (melisha)
description: updated
Revision history for this message
Winson Chan (winson-c-chan) wrote :

Renat, Are these composite index? Or are you expecting index for each DB field you identified?

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

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

Changed in mistral:
status: Confirmed → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to mistral (master)

Reviewed: https://review.openstack.org/249511
Committed: https://git.openstack.org/cgit/openstack/mistral/commit/?id=2fb4c290766cf4b693ee8f1272e191d6178441fe
Submitter: Jenkins
Branch: master

commit 2fb4c290766cf4b693ee8f1272e191d6178441fe
Author: Winson Chan <email address hidden>
Date: Wed Nov 25 19:38:37 2015 +0000

    Add database indices to improve query performance

    Database queries are slow as the number of records grew because indices are
    not created to optimize query filters.

    Change-Id: Ib31d47d3198e1ac1c02a448f28cd9ac5f3106064
    Closes-Bug: #1505664

Changed in mistral:
status: In Progress → Fix Committed
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to mistral (stable/liberty)

Fix proposed to branch: stable/liberty
Review: https://review.openstack.org/251822

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

Reviewed: https://review.openstack.org/251822
Committed: https://git.openstack.org/cgit/openstack/mistral/commit/?id=8c1b5efb14cc66a2bc041ef75b8788e6bc61bc7a
Submitter: Jenkins
Branch: stable/liberty

commit 8c1b5efb14cc66a2bc041ef75b8788e6bc61bc7a
Author: Winson Chan <email address hidden>
Date: Wed Nov 25 19:38:37 2015 +0000

    Add database indices to improve query performance

    Database queries are slow as the number of records grew because indices are
    not created to optimize query filters.

    Change-Id: Ib31d47d3198e1ac1c02a448f28cd9ac5f3106064
    Closes-Bug: #1505664
    (cherry picked from commit 2fb4c290766cf4b693ee8f1272e191d6178441fe)

tags: added: in-stable-liberty
Revision history for this message
Thierry Carrez (ttx) wrote : Fix included in openstack/mistral 2.0.0.0b1

This issue was fixed in the openstack/mistral 2.0.0.0b1 development milestone.

Changed in mistral:
status: Fix Committed → Fix Released
Revision history for this message
Doug Hellmann (doug-hellmann) wrote : Fix included in openstack/mistral 1.0.1

This issue was fixed in the openstack/mistral 1.0.1 release.

Changed in mistral:
milestone: mitaka-1 → 2.0.0
Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

This issue was fixed in the openstack/mistral 1.0.1 release.

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.