Bug 748483 - Make dbsetup sequence id cache size configurable
Summary: Make dbsetup sequence id cache size configurable
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: RHQ Project
Classification: Other
Component: Database
Version: 4.0.1
Hardware: Unspecified
OS: Unspecified
medium
medium
Target Milestone: ---
: RHQ 4.3.0
Assignee: Robert Buck
QA Contact: Mike Foley
URL:
Whiteboard:
Depends On: 728547
Blocks: rhq-perf 748484
TreeView+ depends on / blocked
 
Reported: 2011-10-24 15:18 UTC by Robert Buck
Modified: 2013-09-12 21:16 UTC (History)
4 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of: 728547
: 748484 (view as bug list)
Environment:
Last Closed: 2013-09-12 21:16:09 UTC
Embargoed:


Attachments (Terms of Use)
Adds ability to flexibly configure sequence id cache sizes. (8.19 KB, patch)
2011-10-25 13:47 UTC, Robert Buck
no flags Details | Diff

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

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

CREATE SEQUENCE SEQ_FOO
  START WITH 10001
  NOCYCLE
  CACHE 10
  NOORDER;

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

--- Additional comment from rbuck on 2011-10-20 08:42:02 EDT ---

Feedback:

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 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"/>
</schemaSpec>

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"/>
</schemaSpec>

2. Change POM.xml:

         <scm.module.path>modules/core/dbutils/</scm.module.path>
-        <db.schema.version>2.114</db.schema.version>
+        <db.schema.version>2.115</db.schema.version>
         <rhq.ds.type-mapping>${rhq.test.ds.type-mapping}</rhq.ds.type-mapping>

3. Run:

  mvn -Ddbsetup

--- Additional comment from rbuck on 2011-10-24 11:02:02 EDT ---

commit 9f79315397c9bd048998bb0f72a367385f9a05cf
Author: Robert Buck <rbuck>
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 Robert Buck 2011-10-25 13:27:18 UTC
Here is how to negative test this:

+++ b/modules/core/dbutils/src/main/scripts/dbsetup/inventory-schema.xml
@@ -36,7 +36,7 @@
     <!-- NEW RESOURCE SUBCATEGORIES -->
     <table name="RHQ_RESOURCE_SUBCAT">
         <column name="ID" default="sequence-only" initial="10001"
-                primarykey="true" required="true" type="INTEGER"/>
+                primarykey="true" required="true" type="INTEGER" sequencecachesize="WOOF"/>
         <column name="NAME" type="VARCHAR2" size="100"/>
         <column name="DISPLAY_NAME" type="VARCHAR2" size="100"/>

Here is a positive test:

+++ b/modules/core/dbutils/src/main/scripts/dbsetup/inventory-schema.xml
@@ -36,7 +36,7 @@
     <!-- NEW RESOURCE SUBCATEGORIES -->
     <table name="RHQ_RESOURCE_SUBCAT">
         <column name="ID" default="sequence-only" initial="10001"
-                primarykey="true" required="true" type="INTEGER"/>
+                primarykey="true" required="true" type="INTEGER" sequencecachesize="64"/>
         <column name="NAME" type="VARCHAR2" size="100"/>
         <column name="DISPLAY_NAME" type="VARCHAR2" size="100"/>

Comment 2 Robert Buck 2011-10-25 13:47:39 UTC
Created attachment 530087 [details]
Adds ability to flexibly configure sequence id cache sizes.

Comment 3 Robert Buck 2011-10-27 13:38:52 UTC
branch: bug/748483
commit 077a1b19bfdb4a4b814adf403488b2442d932e9a
Author: Robert Buck <rbuck>
Date:   2011-10-27 09:36:04 -0400
[BZ 748483] Make dbsetup sequence id cache size configurable.

Comment 4 Jay Shaughnessy 2013-09-12 21:16:09 UTC

This seems to be done, although I believe we use the default of 10 everywhere still.  Moreover, we use increments of 1 for all entities.  If we plan on making perf enhancements for sequence cache sizes it should likely also include changes to the sequence increments for the relevant entities, as that has even more effect on perf, I think.  See Constants.ALLOCATION_SIZE for more.

Closing as CURRENTRELEASE


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