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

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
: 656471 (view as bug list)
Environment:
Last Closed: 2011-05-24 01:09:45 UTC


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Bugzilla 621597 None None None Never

Description Heiko W. Rupp 2010-11-23 19:56:57 UTC
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 Heiko W. Rupp 2010-11-24 08:24:30 UTC
Note, this can only be a shot term solution.

For a real fix, we need to "precompute" the data and store them in some separate table row.
See https://bugzilla.redhat.com/show_bug.cgi?id=621597

Comment 2 Lukas Krejci 2010-12-16 11:24:53 UTC
commit 2ba9e1c4cfcb7046bfce34996d4955b0091c08a6
Author: Lukas Krejci <lkrejci@redhat.com>
Date:   Thu Dec 16 12:23:48 2010 +0100

    BZ 656469 - adding a temporary performance measure on the RHQ_MEASUREMENT_DATA_NUM_1D. Having an index on TIME_STAMP column helps the performance of the hourly jobs but a more involv
    
    This fix is part of RHQ 3.0.1 as well and therefore was added as schema spec 2.92.4 even though master is currently on schema spec 2.101.
    This is to support seamless upgrade of the releases, but of course intermittently this is schema spec is not going to get applied until a full dbsetup is executed.

Comment 3 Lukas Krejci 2010-12-20 10:07:21 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 Lukas Krejci 2010-12-20 10:14:24 UTC
The previous commit hash is incorrect.

The correct one is:
6d4c02a946fe0117ba033b385a747b0a9d1e133b

Comment 5 Corey Welton 2011-01-04 03:34:14 UTC
QA closing - dev task, not much to readily QE here.

Comment 6 Corey Welton 2011-05-24 01:09:45 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.