Bug 656471

Summary:	JON241: Perf: Add a timestamp index on the 1d table
Product:	[Other] RHQ Project	Reporter:	Heiko W. Rupp <hrupp>
Component:	Performance	Assignee:	Lukas Krejci <lkrejci>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Corey Welton <cwelton>
Severity:	medium	Docs Contact:
Priority:	low
Version:	3.0.0	CC:	rtimaniy
Target Milestone:	---
Target Release:	---
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:	656469	Environment:
Last Closed:		Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:	656469
Bug Blocks:	616081

Description Heiko W. Rupp 2010-11-23 19:58:31 UTC

+++ This bug was initially created as a clone of Bug #656469 +++

SELECT MAX(time_stamp) FROM RHQ_MEASUREMENT_DATA_NUM_1D

is very expensive with a huge number of data -- basically Oracle does a full index scan as the
existing index is (schedule_id, time_stamp).

Putting an index on time_stamp only would remove the FIS burden and speed up this operation.
This also follows the similar index on the 1h and 6h tables.

Comment 1 Lukas Krejci 2010-12-16 11:25:24 UTC

commit 15b395b1294158dc65979d3b896e5eca6db421f1
Author: Lukas Krejci <lkrejci>
Date:   Thu Dec 16 12:11:25 2010 +0100

    BZ 656471 - add index on the timestamp of the RHQ_MEASUREMENT_DATA_NUM_1D table to speed up the data compression

Comment 2 Rajan Timaniya 2010-12-20 10:06:46 UTC

Tested on JON-2.4.1 build #47 (build number: 10963:e45ddc8)

Steps:
1) Installed JON-2.4.1 on new (don't use upgrade) database

2) Check table description
jon241=# \d "rhq_measurement_data_num_1d"
 Table "public.rhq_measurement_data_num_1d"
   Column    |       Type       | Modifiers 
-------------+------------------+-----------
 time_stamp  | bigint           | not null
 schedule_id | integer          | not null
 value       | double precision | 
 minvalue    | double precision | 
 maxvalue    | double precision | 
Indexes:
    "rhq_meas_data_1d_id_time_pk" PRIMARY KEY, btree (schedule_id, time_stamp)

3) Check database schema version
jon241=# select property_value from rhq_system_config where property_key = 'DB_SCHEMA_VERSION';
 property_value 
----------------
 2.92.4
(1 row)

Observation:
There isn't index on the timestamp of the RHQ_MEASUREMENT_DATA_NUM_1D table.

As discuss with Lukas, need to add the definition to dbsetup, currently it works with upgrade.

Comment 3 Lukas Krejci 2010-12-20 10:07:49 UTC

commit 4f29065c937833a03f96d5d09f2f36d9fd5781f9
Author: Lukas Krejci <lkrejci>
Date:   Mon Dec 20 10:58:40 2010 +0100

    BZ 656471 - adding missing index definition in db setup.

Comment 4 Rajan Timaniya 2010-12-20 13:18:53 UTC

Verified on JON-2.4.1 build #49 (build number: 10966:4f29065)

There is index on the timestamp of the RHQ_MEASUREMENT_DATA_NUM_1D table.

jon241=# \d "rhq_measurement_data_num_1d"
 Table "public.rhq_measurement_data_num_1d"
   Column    |       Type       | Modifiers 
-------------+------------------+-----------
 time_stamp  | bigint           | not null
 schedule_id | integer          | not null
 value       | double precision | 
 minvalue    | double precision | 
 maxvalue    | double precision | 
Indexes:
    "rhq_meas_data_1d_id_time_pk" PRIMARY KEY, btree (schedule_id, time_stamp)
    "rhq_meas_data_1d_time_idx" btree (time_stamp)

Comment 5 Corey Welton 2011-05-24 01:14:43 UTC

Bookkeeping - closing bug - fixed in recent release.

Comment 6 Corey Welton 2011-05-24 01:14:44 UTC

Bookkeeping - closing bug - fixed in recent release.

Comment 7 Corey Welton 2011-05-24 01:15:13 UTC

Bookkeeping - closing bug - fixed in recent release.