Bug 814839

Summary: discuss the need for LOGGING on Oracle tables
Product: [Other] RHQ Project Reporter: John Mazzitelli <mazz>
Component: DatabaseAssignee: John Mazzitelli <mazz>
Status: CLOSED CURRENTRELEASE QA Contact: Mike Foley <mfoley>
Severity: unspecified Docs Contact:
Priority: urgent    
Version: 4.4CC: hrupp, jshaughn, mshirley
Target Milestone: ---   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2013-09-01 15:21:11 EDT Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---
Bug Depends On:    
Bug Blocks: 782579, 797325    
Description Flags
patch to enable logging except for places where the code wants it off
sql script to turn on oracle logging on relevant tables none

Description John Mazzitelli 2012-04-20 16:00:41 EDT
Bug #797325 asked that we turn on LOGGING on an alert table. It appears - looking at our schema.xml files - that it had logging=false (one of the few tables that had that).

Looking at the rest of the schema.xml files, most tables don't specify logging attribute, and those few that do all turn it to false (logging="false"). This implies we assumed logging=true is the default and thus all oracle tables will have LOGGING by default. Those with logging=false will have NOLOGGING set.

However, look at the field org.rhq.core.db.setup.Table.m_logging - its default is false. Also, note that if m_logging is true, we do NOT specify "LOGGING" in the CREATE TABLE command. We only use m_logging if it is false, and if it is, we specify NOLOGGING.

Thus, no where to do explicitly say LOGGING in the Java code, and by default (since m_logging=false) we will explicitly say NOLOGGING. The only time we would NOT specify NOLOGGING would be if our schema.xml specifies logging=true, but no where do we do that.

In short, what does this mean? It means no where do we ever create Oracle tables with LOGGING explicitly, and since we never specify logging="true" in our schema.xml files, EVERYWHERE we specify NOLOGGING explicitly when creating Oracle tables.

The end result? All of our tables have NOLOGGING specified.

The fact that no one ever complained that they couldn't recover their Oracle data due to logging no enabled tells me this is not a feature we really need to use.

If we agree that the way we create tables today is OK, we should remove all the "logging="false"" attributes (since that's the default anyway, and having just those implies logging=true is the default, which it is not). We need to also revert bug #797325 because it is explicitly turning on LOGGING for the RHQ_ALERT_NOTIFICATION table and if we agree we don't need this, none of our tables should set that option.
Comment 1 John Mazzitelli 2012-04-20 16:17:05 EDT
fyi: I reverted that one change made before: https://bugzilla.redhat.com/show_bug.cgi?id=797325#c8
Comment 2 John Mazzitelli 2012-04-20 16:26:26 EDT
(04:22:06 PM) mazz: because logging=false is our default, on EVERY table we create, we do so explicitly with NOLOGGING
(04:22:24 PM) mazz: CREATE TABLE foo NOLOGGING
(04:22:33 PM) mazz: that's what our create table SQL will look like
(04:22:38 PM) mazz: for EVERY table
(04:24:00 PM) mazz:     private boolean m_logging = false;
(04:24:00 PM) mazz: ...
(04:24:00 PM) mazz:         if (!this.m_logging) // Oracle does NOLOGGING   {
(04:24:00 PM) mazz:             strCmd += this.getLoggingSyntax();
(04:24:00 PM) mazz:         }
(04:24:00 PM) mazz: ...
(04:24:00 PM) mazz:     protected String getLoggingSyntax() {
(04:24:00 PM) mazz:         return " NOLOGGING";
(04:24:00 PM) mazz:     }
(04:24:07 PM) mazz: that's the revelent code
Comment 3 Jay Shaughnessy 2012-04-20 16:26:41 EDT
I think we should change our code such that Table.m_logging is true by
default, and therefore tables should be Logging = true (NOLOGGING should
not be set on all of our tables.  It means that we can always recover data
and customers won't worry that we have it turned off. From what I can see
it should make no difference to us because to the best of my knowledge
we don't perform table creates that load data in the create.  And we don't
subsequently load data with direct inserts (Append hints). Although we
should validate that for the metric tables, and maybe event tables.





Even when used it is primarily temporary, and therefore not recommended
to create the table and leave it that way.
Comment 4 Jay Shaughnessy 2012-04-20 16:54:10 EDT
It seems we do use INSERT /*+ APPEND */  in a few places.  This is a
direct insert and therefore may have a performance benefit from NOLOGGING,
at the expense of not being able to recover data without a backup.

I see it used for the raw metric tables and also the calltime data value

Mazz notes that we explicitly set logging=false on the following tables:

(4:39:31 PM) mazz: RHQ_MEASUREMENT_DATA_NUM_[1H, 6H, 1D]

Which is not quite right from what I can see, because those are not the
tables with the INSERT /*+ APPEND */.  Perhaps they need a closer look at
how they are loaded.

So, if we do change the logging default to true then we should also
ensure that logging is false on the tables that make sense.  Today it
doesn't matter because logging is off on all tables.
Comment 5 John Mazzitelli 2012-04-23 11:49:47 EDT
these are the two places where I see /*+ APPEND */ being used:

1. ...measurement.CallTimeDataManagerBean.CALLTIME_VALUE_INSERT_STATEMENT

(that inserts into the table RHQ_CALLTIME_DATA_VALUE)

2. ...measurement.MeasurementDataManagerBean.addNumericData(Set<MeasurementDataNumeric>)

(that inserts into one of the 14 raw measurement tables)
Comment 6 John Mazzitelli 2012-04-23 12:04:04 EDT
from one of those links that jay posted, which is a quote from oracle docs:

"The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo."

We do not use direct load INSERTs (which use the +APPEND hint in INSERT INTO...SELECT statements - for example: INSERT /*+ APPEND*/ INTO my_table SELECT * FROM my_other_table) except for that CALLTIME_VALUE_INSERT_STATEMENT insert (which I believe means we are OK with losing that data to gain the benefit of faster inserts).

I'm reading, however, that in production systems, you should not leave NOLOGGING on unless its what you really want.
Comment 7 John Mazzitelli 2012-04-23 13:37:34 EDT
i must really not understand what the person was trying to do when setting up logging="false" in our schema.xml files.

I just realized that only our aggregate meas data tables have logging="false" (the 1H, 6H and 1D tables). All of the 14 raw meas tables do NOT have this - but its those tables which we INSERT /*+APPEND*/

So, I must really not understand what NOLOGGING is trying to be used for - since the tables we use /*+APPEND*/ on are NOT set with NOLOGGING, and those that are set explicitly with logging="false" are never inserted with /*+APPEND*/
Comment 8 John Mazzitelli 2012-04-23 14:52:47 EDT
Created attachment 579660 [details]
patch to enable logging except for places where the code wants it off

see the attached patch for the proposed changes to turn on LOGGING on all tables except for those that we explicitly use +APPEND hint on. Those will have logging="false". The logging default is true in this patch.
Comment 9 John Mazzitelli 2012-04-24 15:56:37 EDT
Created attachment 579981 [details]
sql script to turn on oracle logging on relevant tables

attachment is a sql script that lets you turn on LOGGING or NOLOGGING on all tables appropriately. Use this if you already have RHQ installed and you want the logging option set on the relevant tables.
Comment 10 John Mazzitelli 2012-04-24 17:36:23 EDT
git commit to master: eca0a39
Comment 11 John Mazzitelli 2012-04-25 08:48:55 EDT
the dbsetup scripts have been adjusted. to test:

1) start with a clean Oracle DB (or when you install, tell the installer to not reuse any existing database tables - have it start clean).

2) After the installer finishes, go to a SQL tool (SQL*Plus or whatever), log in as the RHQ database user, and do a "select table_name, logging from user_tables" and verify that all tables have logging=YES except for the following tables, whose logging should be NO:

   * the 15 raw metric tables named RHQ_MEAS_DATA_NUM_R00 through _R14
Comment 12 John Mazzitelli 2012-04-25 08:50:28 EDT
*** Bug 797325 has been marked as a duplicate of this bug. ***
Comment 13 Heiko W. Rupp 2013-09-01 15:21:11 EDT
Bulk closing of BZs that have no target version set, but which are ON_QA for more than a year and thus are in production for a long time.