Bug 1512412 - missing indexes on engine db
Summary: missing indexes on engine db
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: ovirt-engine
Classification: oVirt
Component: Database.Core
Version: 4.2.0
Hardware: Unspecified
OS: Unspecified
medium
medium
Target Milestone: ovirt-4.2.3
: ---
Assignee: Eli Mesika
QA Contact: Lucie Leistnerova
URL:
Whiteboard:
Depends On:
Blocks: 1535899
TreeView+ depends on / blocked
 
Reported: 2017-11-13 07:26 UTC by Roy Golan
Modified: 2018-05-10 06:30 UTC (History)
5 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
: 1535899 (view as bug list)
Environment:
Last Closed: 2018-05-10 06:30:02 UTC
oVirt Team: Infra
Embargoed:
rule-engine: ovirt-4.2+
lsvaty: testing_ack+


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
oVirt gerrit 83976 0 master MERGED core: Add missing indexes on engine DB 2017-11-21 11:02:01 UTC
oVirt gerrit 89963 0 master POST db: Add missing indexes 2018-04-08 14:24:42 UTC
oVirt gerrit 90178 0 ovirt-engine-4.2 MERGED db: Add missing indexes 2018-04-15 12:29:42 UTC

Description Roy Golan 2017-11-13 07:26:11 UTC
Description of problem:

An analysis using pgcluu[1] resulted a handful of missing indexes on 28 of our foreign keys.

Since our schema is read and update mostly, and tables are small the expected cost of adding the indexes is negligible.
 

Steps to Reproduce:
1. run this query [2] or run the CI OST performance suite [2] and check the pgcluu report [3] or just run pgcluu against the engine db.

Actual results:
the output contains 28 missing indexes

Expected results:
0 missing indexes. 

Additional info:
May improve performance of some queries on views involving those foreign keys.

An integration test will be added to check that the report contains zero results.

Comment 2 Roy Golan 2017-11-13 07:46:51 UTC
severity and priority set to medium. If it will demonstrate a real impact I'll raise it. Anyhow, it's an easy fix

Comment 3 RHV bug bot 2017-12-06 15:58:01 UTC
Why is the bug on MODIFIED if one patch is still on POST?

Comment 4 RHV bug bot 2017-12-06 16:18:07 UTC
INFO: Bug status wasn't changed from MODIFIED to ON_QA due to the following reason:

[Open patch attached]

For more info please contact: infra

Comment 5 Roy Golan 2017-12-28 17:20:32 UTC
The extra patch [1] is a nice to have but not cardinal to bug. I removed it from the bug and I'm moving this back to ON_QA

[1] https://gerrit.ovirt.org/c/84012/3

Comment 6 Lucie Leistnerova 2018-01-18 08:26:17 UTC
pgcluu report shows other missing indexes:
Table 	Missing index
network	CREATE INDEX CONCURRENTLY idx_network_provider_physical_network_id ON network (provider_physical_network_id)
group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_group_id ON aaa_jdbc.group_groups (group_id)
group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_in_group_id ON aaa_jdbc.group_groups (in_group_id)
user_password_history	CREATE INDEX CONCURRENTLY idx_user_password_history_user_id ON aaa_jdbc.user_password_history (user_id)
user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_user_id ON aaa_jdbc.user_groups (user_id)
user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_in_group_id ON aaa_jdbc.user_groups (in_group_id)
group_attributes	CREATE INDEX CONCURRENTLY idx_group_attributes_group_id ON aaa_jdbc.group_attributes (group_id)
failed_logins	CREATE INDEX CONCURRENTLY idx_failed_logins_user_id ON aaa_jdbc.failed_logins (user_id)
user_attributes	CREATE INDEX CONCURRENTLY idx_user_attributes_user_id ON aaa_jdbc.user_attributes (user_id) 

Tested in ovirt-engine-4.2.1.1-0.1.el7.noarch

Should this indexes be also part of this BZ or we create new one? Except of one are the indexes in aaa_jdbc schema.

Comment 7 Martin Perina 2018-01-18 08:32:42 UTC
(In reply to Lucie Leistnerova from comment #6)
> group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_group_id ON
> aaa_jdbc.group_groups (group_id)
> group_groups	CREATE INDEX CONCURRENTLY idx_group_groups_in_group_id ON
> aaa_jdbc.group_groups (in_group_id)
> user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_user_id ON
> aaa_jdbc.user_groups (user_id)
> user_groups	CREATE INDEX CONCURRENTLY idx_user_groups_in_group_id ON
> aaa_jdbc.user_groups (in_group_id)
> group_attributes	CREATE INDEX CONCURRENTLY idx_group_attributes_group_id ON
> aaa_jdbc.group_attributes (group_id)
> failed_logins	CREATE INDEX CONCURRENTLY idx_failed_logins_user_id ON
> aaa_jdbc.failed_logins (user_id)
> user_attributes	CREATE INDEX CONCURRENTLY idx_user_attributes_user_id ON
> aaa_jdbc.user_attributes (user_id) 

All of above indexes are part of merged https://gerrit.ovirt.org/85100 but there was no new release of ovirt-engine-extension-aaa-jdbc, so that's why those indexes are still missing.

Comment 8 Lucie Leistnerova 2018-01-18 08:53:27 UTC
According to Comment 7, I created clone for aaa_jdbc Bug 1535899. But index on network table should be fixed within this BZ.

Comment 9 Red Hat Bugzilla Rules Engine 2018-01-18 08:53:33 UTC
Target release should be placed once a package build is known to fix a issue. Since this bug is not modified, the target version has been reset. Please use target milestone to plan a fix for a oVirt release.

Comment 10 Lucie Leistnerova 2018-04-23 14:15:08 UTC
No missing indexes in engine database.

verified in ovirt-engine-4.2.3.2-0.1.el7.noarch

Comment 11 Sandro Bonazzola 2018-05-10 06:30:02 UTC
This bugzilla is included in oVirt 4.2.3 release, published on May 4th 2018.

Since the problem described in this bug report should be
resolved in oVirt 4.2.3 release, it has been closed with a resolution of CURRENT RELEASE.

If the solution does not work for you, please open a new bug report.


Note You need to log in before you can comment on or make changes to this bug.