Missing indexes on records table hurting performance

Bug #1445115 reported by Kiall Mac Innes
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Designate
Fix Released
High
Tim Simmons

Bug Description

Missing indexes on records table hurting performance

Adding these two indexes (with more descriptive names!) increases performance of central's update_status method dramatically.

  KEY `id_index` (`status`,`domain_id`,`tenant_id`,`created_at`,`serial`),
  KEY `id_index2` (`status`,`serial`,`domain_id`,`tenant_id`,`created_at`),

Tim Simmons (timsim)
Changed in designate:
assignee: nobody → Tim Simmons (tim-simmons-t)
Revision history for this message
Tim Simmons (timsim) wrote :

So it looks like this is the query we're trying to optmize:

SELECT records.id, records.version, records.created_at, records.updated_at, records.tenant_id, records.domain_id, records.recordset_id, records.data, records.description, records.hash, records.managed, records.managed_extra, records.managed_plugin_type, records.managed_plugin_name, records.managed_resource_type, records.managed_resource_region, records.managed_resource_id, records.managed_tenant_id, records.status, records.action, records.serial
FROM records
WHERE records.status IN ('PENDING', 'ERROR')
AND records.serial <= '1429737400'
AND records.domain_id = 'f8c640993330471c8a74563f4d41b144'
AND (records.tenant_id = 'noauth-project' OR records.tenant_id IS NULL)
ORDER BY records.created_at ASC, records.id ASC;

These are the three indices I tried.
        ['update_status_index_1', records_table.c.status,
         records_table.c.domain_id, records_table.c.tenant_id,
         records_table.c.created_at, records_table.c.serial],

        ['update_status_index_2', records_table.c.status,
         records_table.c.serial, records_table.c.domain_id,
         records_table.c.tenant_id, records_table.c.created_at]

        ['update_status_index_3', records_table.c.status,
         records_table.c.serial, records_table.c.domain_id,
         records_table.c.tenant_id, records_table.c.created_at,
         records_table.c.id]

6000 records, 1000 pending, no index
2015-04-22 20:08:01.138 INFO designate.central.service [req-2aff4d8b-0cc6-41af-9092-f5aa78f81e6b - - - - -] update_status function took 6613.709 ms

7000 records 1000 pending, update_status_index_1
2015-04-22 20:17:00.926 INFO designate.central.service [req-0121d6ed-e529-4f2a-82e7-ae79571a6f46 - - - - -] update_status function took 6365.184 ms

8000 records 1000 pending, update_status_index_2
2015-04-22 20:37:29.608 INFO designate.central.service [req-b64bfe85-668d-4aac-a266-d07aaa80d27a - - - - -] update_status function took 6621.885 ms

9000 records, 1000 pending, update_status_index_3
2015-04-22 21:02:51.579 INFO designate.central.service [req-73d52b4d-c0a9-4337-9182-57d5836a2382 noauth-user noauth-project - - -] update_status function took 6908.304 ms

Doesn't seem to have worked, is there something I'm missing?

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

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

Changed in designate:
status: Confirmed → In Progress
Revision history for this message
Tim Simmons (timsim) wrote :

So tried all of these indices, and the combinations.
       ['update_status_index_1', records_table.c.status,
         records_table.c.domain_id, records_table.c.tenant_id,
         records_table.c.created_at, records_table.c.serial],

        ['update_status_index_2', records_table.c.status,
         records_table.c.serial, records_table.c.domain_id,
         records_table.c.tenant_id, records_table.c.created_at]

        ['update_status_index_3', records_table.c.status,
         records_table.c.serial, records_table.c.domain_id,
         records_table.c.tenant_id, records_table.c.created_at,
         records_table.c.id]

They all helped a little bit individually, but none as much as number 1, and the last two in combination with 1 weren't any better than just 1. So, I just proposed the one.

Revision history for this message
Tim Simmons (timsim) wrote :

Combined with https://review.openstack.org/#/c/176340/ (update_status transactional) I saw a about a 5x improvement on average for calls to update_status. The index was responsible for about half of it.

Kiall Mac Innes (kiall)
Changed in designate:
milestone: none → liberty-1
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to designate (master)

Reviewed: https://review.openstack.org/176993
Committed: https://git.openstack.org/cgit/openstack/designate/commit/?id=eaa5bad75434ac050f438dff730936164106bfa9
Submitter: Jenkins
Branch: master

commit eaa5bad75434ac050f438dff730936164106bfa9
Author: Tim Simmons <email address hidden>
Date: Thu Apr 23 21:00:38 2015 +0000

    Add an index to speed up update_status

    This adds an index that speeds up the update_status method in
    designate-central

    Change-Id: I62600ebbf066dc746a696263f0e72ca373076353
    Closes-Bug: 1445115

Changed in designate:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
tags: removed: kilo-rc-potential
Thierry Carrez (ttx)
Changed in designate:
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in designate:
milestone: liberty-1 → 1.0.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.