Missing indexes on records table hurting performance
Bug #1445115 reported by
Kiall Mac Innes
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`
KEY `id_index2` (`status`
Changed in designate: | |
assignee: | nobody → Tim Simmons (tim-simmons-t) |
Changed in designate: | |
milestone: | none → liberty-1 |
tags: | removed: kilo-rc-potential |
Changed in designate: | |
status: | Fix Committed → Fix Released |
Changed in designate: | |
milestone: | liberty-1 → 1.0.0 |
To post a comment you must log in.
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 1c8a74563f4d41b 144'
FROM records
WHERE records.status IN ('PENDING', 'ERROR')
AND records.serial <= '1429737400'
AND records.domain_id = 'f8c64099333047
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] ,
6000 records, 1000 pending, no index central. service [req-2aff4d8b- 0cc6-41af- 9092-f5aa78f81e 6b - - - - -] update_status function took 6613.709 ms
2015-04-22 20:08:01.138 INFO designate.
7000 records 1000 pending, update_ status_ index_1 central. service [req-0121d6ed- e529-4f2a- 82e7-ae79571a6f 46 - - - - -] update_status function took 6365.184 ms
2015-04-22 20:17:00.926 INFO designate.
8000 records 1000 pending, update_ status_ index_2 central. service [req-b64bfe85- 668d-4aac- a266-d07aaa80d2 7a - - - - -] update_status function took 6621.885 ms
2015-04-22 20:37:29.608 INFO designate.
9000 records, 1000 pending, update_ status_ index_3 central. service [req-73d52b4d- c0a9-4337- 9182-57d5836a23 82 noauth-user noauth-project - - -] update_status function took 6908.304 ms
2015-04-22 21:02:51.579 INFO designate.
Doesn't seem to have worked, is there something I'm missing?