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.
fyi: I reverted that one change made before: https://bugzilla.redhat.com/show_bug.cgi?id=797325#c8
(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
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. See: http://www.databasejournal.com/features/oracle/article.php/3631361/Managing-Tables-Logging-versus-Nologging.htm http://jakub.wartak.pl/blog/?page_id=107 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869 Even when used it is primarily temporary, and therefore not recommended to create the table and leave it that way.
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 table. Mazz notes that we explicitly set logging=false on the following tables: (4:39:31 PM) mazz: RHQ_MEASUREMENT_DATA_NUM_[1H, 6H, 1D] (4:40:00 PM) mazz: RHQ_ALERT, RHQ_ALERT_NOTIF_LOG, RHQ_ALERT_CONDITION_LOG 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.
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)
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.
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*/
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.
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.
git commit to master: eca0a39
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: * RHQ_CALLTIME_DATA_VALUE * the 15 raw metric tables named RHQ_MEAS_DATA_NUM_R00 through _R14
*** Bug 797325 has been marked as a duplicate of this bug. ***
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.