Bug 748484

Summary: Make dbupgrade sequence id cache size configurable
Product: [Other] RHQ Project Reporter: Robert Buck <rbuck>
Component: DatabaseAssignee: Robert Buck <rbuck>
Status: CLOSED CURRENTRELEASE QA Contact: Mike Foley <mfoley>
Severity: low Docs Contact:
Priority: low    
Version: 4.0.1CC: ccrouch, hrupp, rbuck
Target Milestone: ---   
Target Release: RHQ 4.3.0   
Hardware: Unspecified   
OS: Unspecified   
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: 748483 Environment:
Last Closed: 2013-08-31 10:08:32 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Bug Depends On: 728547, 748483    
Bug Blocks: 620933    

Description Robert Buck 2011-10-24 15:19:04 UTC
+++ This bug was initially created as a clone of Bug #748483 +++

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

currently dbssetup and dbupgrade both hardcode the cache size to 10, e.g.:

  START WITH 10001
  CACHE 10

We should make this a configurable option and increase the value for most of our tables.

--- Additional comment from rbuck@redhat.com on 2011-10-20 08:42:02 EDT ---


For stuff that rarely changes (think e.g. resources / resource types  / ..) 10 is probably good so that we don't have huge holes in sequences when RHQ is restarted inbetween; or perhaps 20 .. 50 for other things (call time data or events ?) where larger values would sure be beneficial.

--- Additional comment from rbuck@redhat.com on 2011-10-24 10:34:41 EDT ---

When there is a mistake as in this:

<schemaSpec version="2.115">
  <schema-createSequence name="SOMESILLYSEQUENCE_SEQ"
    initial="10001" seqIdCacheSize="WOOF"/>

You will get this sort of error:

/home/rbuck/dev/sandboxes/rhq/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml:27: Failed to upgrade - error in spec version [2.115]. Cause: Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_CreateSequence] in schema spec version [2.115]. Cause: Database SEQID Cache Size is not an integer: WOOF

To specify NOCACHE:

<schema-createSequence name="SOMESILLYSEQUENCE_SEQ"
   initial="10001" seqIdCacheSize="0"/>

N.B. that for some open-source databases NOCACHE means "1", which effectively means they really don't support NOCACHE in the true sense of the word.

If the CACHE size specified is identical to the factory defaults, no CACHE terms will be applied to the generated SQL.

All negative values are mapped to the mode of using factory defaults, whatever they may be.

We need to have someone test MS Sql Server; here are the steps to test:

1. Change db-upgrade.xml:

<schemaSpec version="2.115">
  <schema-createSequence name="SOMESILLYSEQUENCE_SEQ"
    initial="10001" seqIdCacheSize="0"/>

2. Change POM.xml:

-        <db.schema.version>2.114</db.schema.version>
+        <db.schema.version>2.115</db.schema.version>

3. Run:

  mvn -Ddbsetup

--- Additional comment from rbuck@redhat.com on 2011-10-24 11:02:02 EDT ---

commit 9f79315397c9bd048998bb0f72a367385f9a05cf
Author: Robert Buck <rbuck@redhat.com>
Date:   2011-10-24 11:00:54 -0400

    [BZ 728547] Make SEQID cache sizes configurable; the new solution supports NOCACHE and CACHE semantics, it supports factory default sizes; for cases where factory default sizes are larger than the previous default value of 10, we opt for the factory default sizes.

Comment 1 Mike Foley 2011-11-09 12:55:24 UTC
being backed out of jon 3 branch.  see https://bugzilla.redhat.com/show_bug.cgi?id=752408

Comment 2 Mike Foley 2011-11-09 13:49:32 UTC
being backed out of jon 3 branch.  see https://bugzilla.redhat.com/show_bug.cgi?id=752408

Comment 3 Mike Foley 2011-11-21 19:47:44 UTC
ms sql server is out of scope.
upgrade testing on oracle and postgres completed

Comment 4 Heiko W. Rupp 2013-08-31 10:08:32 UTC
Bulk close of old bugs in VERIFIED state.