Missing indexes on records table hurting performance

[Kiall Mac Innes]

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)]

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?

[OpenStack Infra (hudson-openstack)]

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

[Tim Simmons (timsim)]

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.

[Tim Simmons (timsim)]

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.

[OpenStack Infra (hudson-openstack)]

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