Bug 656471 - JON241: Perf: Add a timestamp index on the 1d table
Summary: JON241: Perf: Add a timestamp index on the 1d table
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: RHQ Project
Classification: Other
Component: Performance
Version: 3.0.0
Hardware: Unspecified
OS: Unspecified
low
medium vote
Target Milestone: ---
: ---
Assignee: Lukas Krejci
QA Contact: Corey Welton
URL:
Whiteboard:
Depends On: 656469
Blocks: jon241-bugs
TreeView+ depends on / blocked
 
Reported: 2010-11-23 19:58 UTC by Heiko W. Rupp
Modified: 2011-05-24 01:15 UTC (History)
1 user (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of: 656469
Environment:
Last Closed:


Attachments (Terms of Use)

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@redhat.com>
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@redhat.com>
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.


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