Bug 656471 - JON241: Perf: Add a timestamp index on the 1d table
JON241: Perf: Add a timestamp index on the 1d table
Status: CLOSED CURRENTRELEASE
Product: RHQ Project
Classification: Other
Component: Performance (Show other bugs)
3.0.0
Unspecified Unspecified
low Severity medium (vote)
: ---
: ---
Assigned To: Lukas Krejci
Corey Welton
:
Depends On: 656469
Blocks: jon241-bugs
  Show dependency treegraph
 
Reported: 2010-11-23 14:58 EST by Heiko W. Rupp
Modified: 2011-05-23 21:15 EDT (History)
1 user (show)

See Also:
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:


Attachments (Terms of Use)

  None (edit)
Description Heiko W. Rupp 2010-11-23 14:58:31 EST
+++ 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 06:25:24 EST
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 05:06:46 EST
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 05:07:49 EST
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 08:18:53 EST
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-23 21:14:43 EDT
Bookkeeping - closing bug - fixed in recent release.
Comment 6 Corey Welton 2011-05-23 21:14:44 EDT
Bookkeeping - closing bug - fixed in recent release.
Comment 7 Corey Welton 2011-05-23 21:15:13 EDT
Bookkeeping - closing bug - fixed in recent release.

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