db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From krist...@apache.org
Subject svn commit: r1340549 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/services/daemon/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTe...
Date Sat, 19 May 2012 19:31:18 GMT
Author: kristwaa
Date: Sat May 19 19:31:18 2012
New Revision: 1340549

URL: http://svn.apache.org/viewvc?rev=1340549&view=rev
Log:
DERBY-5680: indexStat daemon processing tables over an over even when there are no changes
in the tables 

Added functionality in the update statistics code to drop statistics considered
disposable; orphaned (i.e. the referenced index doesn't exist), or not
required (the optimizer doesn't need the statistics).
Disposable statistics are only dropped when the istat daemon kicks in, or
SYSCS_UPDATE_STATISTICS is run without specifying an index.
The functionality is not enabled for soft-upgraded databases.
Included a debug property to force the old behavior.


Patch file: derby-5680-1b-remove_disposable_stats.diff


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/Property.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/Property.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/Property.java?rev=1340549&r1=1340548&r2=1340549&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/Property.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/Property.java Sat May
19 19:31:18 2012
@@ -720,6 +720,12 @@ public interface Property { 
             "derby.storage.indexStats.debug.queueSize";
     int STORAGE_AUTO_INDEX_STATS_DEBUG_QUEUE_SIZE_DEFAULT = 20;
 
+    /**
+     * Specifies whether to force old behavior (pre 10.9) for statistics update.
+     */
+    String STORAGE_AUTO_INDEX_STATS_DEBUG_FORCE_OLD_BEHAVIOR =
+            "derby.storage.indexStats.debug.forceOldBehavior";
+
 	/*
 	** Transactions
 	*/

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java?rev=1340549&r1=1340548&r2=1340549&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
Sat May 19 19:31:18 2012
@@ -1280,7 +1280,7 @@ public class TableDescriptor extends Tup
 	
 	/** Returns a list of statistics for this table.
 	 */
-	private synchronized List getStatistics() throws StandardException
+	public synchronized List getStatistics() throws StandardException
 	{
 		// if table already has the statistics descriptors initialized
 		// no need to do anything

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java?rev=1340549&r1=1340548&r2=1340549&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
Sat May 19 19:31:18 2012
@@ -23,6 +23,7 @@ package org.apache.derby.impl.services.d
 import java.io.PrintWriter;
 import java.sql.Connection;
 import java.util.ArrayList;
+import java.util.List;
 
 import org.apache.derby.catalog.UUID;
 import org.apache.derby.catalog.types.StatisticsImpl;
@@ -118,6 +119,16 @@ public class IndexStatisticsDaemonImpl
                 Property.STORAGE_AUTO_INDEX_STATS_DEBUG_QUEUE_SIZE_DEFAULT);
     }
 
+    /**
+     * Tells if the user want us to fall back to pre 10.9 behavior.
+     * <p>
+     * This means do not drop any disposable statistics, and do not skip
+     * statistics for single-column primary key indexes.
+     */
+    private static final boolean FORCE_OLD_BEHAVIOR =
+            PropertyUtil.getSystemBoolean(
+              Property.STORAGE_AUTO_INDEX_STATS_DEBUG_FORCE_OLD_BEHAVIOR);
+
     private final HeaderPrintWriter logStream;
     /** Tells if logging is enabled. */
     private final boolean doLog;
@@ -133,6 +144,8 @@ public class IndexStatisticsDaemonImpl
     private boolean daemonDisabled;
     /** The context manager for the worker thread. */
     private final ContextManager ctxMgr;
+    /** Tells if the database is older than 10.9 (for soft upgrade). */
+    private final boolean dbIsPre10_9;
     /** The language connection context for the worker thread. */
     private LanguageConnectionContext daemonLCC;
     /**
@@ -206,6 +219,7 @@ public class IndexStatisticsDaemonImpl
         this.traceToStdOut = (traceLevel.equalsIgnoreCase("both") ||
                 traceLevel.equalsIgnoreCase("stdout"));
         this.doTrace = traceToDerbyLog || traceToStdOut;
+        this.dbIsPre10_9 = checkIfDbIsPre10_9(db);
 
         this.db = db;
         this.dbOwner = userName;
@@ -224,6 +238,20 @@ public class IndexStatisticsDaemonImpl
                 "}) -> " + databaseName);
     }
 
+    /** Tells if the database is older than 10.9. */
+    private boolean checkIfDbIsPre10_9(Database db) {
+        try {
+            // Note the negation.
+            return !db.getDataDictionary().checkVersion(
+                DataDictionary.DD_VERSION_DERBY_10_9, null);
+        } catch (StandardException se) {
+            if (SanityManager.DEBUG) {
+                SanityManager.THROWASSERT("dd version check failed", se);
+            }
+            return true;
+        }
+    }
+
     /**
      * Schedules an update of the index statistics for the specified table.
      * <p>
@@ -318,8 +346,7 @@ public class IndexStatisticsDaemonImpl
         boolean lockConflictSeen = false;
         while (true) {
             try {
-                ConglomerateDescriptor[] cds = td.getConglomerateDescriptors();
-                updateIndexStatsMinion(lcc, td, cds, AS_BACKGROUND_TASK);
+                updateIndexStatsMinion(lcc, td, null, AS_BACKGROUND_TASK);
                 break;
             } catch (StandardException se) {
 
@@ -364,11 +391,17 @@ public class IndexStatisticsDaemonImpl
     /**
      * Updates the index statistics for the given table and the specified
      * indexes.
+     * <p>
+     * <strong>API note</strong>: Using {@code null} to update the statistics
+     * for all conglomerates is preferred over explicitly passing an array with
+     * all the conglomerates for the table. Doing so allows for some
+     * optimizations, and will cause a disposable statistics check to be
+     * performed.
      *
      * @param lcc language connection context used to perform the work
      * @param td the table to update index stats for
      * @param cds the conglomerates to update statistics for (non-index
-     *      conglomerates will be ignored)
+     *      conglomerates will be ignored), {@code null} means all indexes
      * @param asBackgroundTask whether the updates are done automatically as
      *      part of a background task or if explicitly invoked by the user
      * @throws StandardException if something goes wrong
@@ -378,6 +411,12 @@ public class IndexStatisticsDaemonImpl
                                         ConglomerateDescriptor[] cds,
                                         boolean asBackgroundTask)
             throws StandardException {
+        final boolean identifyDisposableStats =
+                (cds == null && !FORCE_OLD_BEHAVIOR && !dbIsPre10_9);
+        // Fetch descriptors if we're updating statistics for all indexes.
+        if (cds == null) {
+            cds = td.getConglomerateDescriptors();
+        }
         // Extract/derive information from the table descriptor
         long[] conglomerateNumber = new long[cds.length];
         ExecIndexRow[] indexRow = new ExecIndexRow[cds.length];
@@ -417,6 +456,56 @@ public class IndexStatisticsDaemonImpl
             heapCC.close();
         }
 
+        // Check for disposable statistics if we have the required information.
+        // Note that the algorithm would drop valid statistics entries if
+        // working on a subset of the table conglomerates/indexes.
+        if (identifyDisposableStats) {
+            List existingStats = td.getStatistics();
+            StatisticsDescriptor[] stats = (StatisticsDescriptor[])
+                    existingStats.toArray(
+                        new StatisticsDescriptor[existingStats.size()]);
+            // For now we know that disposable stats only exist in two cases,
+            // and that we'll only get one match for both of them per table:
+            //  a) orphaned statistics entries (i.e. DERBY-5681)
+            //  b) single-column primary keys (TODO: after DERBY-3790 is done)
+            for (int si=0; si < stats.length; si++) {
+                UUID referencedIndex = stats[si].getReferenceID();
+                boolean isValid = false;
+                for (int ci=0; ci < conglomerateNumber.length; ci++) {
+                    if (conglomerateNumber[ci] == -1) {
+                        continue;
+                    }
+                    if (referencedIndex.equals(objectUUID[ci])) {
+                        isValid = true;
+                        break;
+                    }
+                }
+                // If the statistics entry is orphaned or not required, drop
+                // the statistics entries for this index. Those we really need
+                // will be rebuilt below. We expect this scenario to be rare,
+                // typically you would only see it on upgrades. On the other
+                // hand, this check is cheap enough such that it is feasible to
+                // do it as part of the stats update to get a "self healing"
+                // mechanism in case of another bug like DERBY-5681 in Derby.
+                if (!isValid) {
+                    String msg = "dropping disposable statistics entry " +
+                            stats[si].getUUID() + " for table " +
+                            stats[si].getTableUUID();
+                    logAlways(td, null, msg);
+                    trace(1, msg);
+                    DataDictionary dd = lcc.getDataDictionary();
+                    if (!lcc.dataDictionaryInWriteMode()) {
+                        dd.startWriting(lcc);
+                    }
+                    dd.dropStatisticsDescriptors(
+                            td.getUUID(), stats[si].getReferenceID(), tc); 
+                    if (asBackgroundTask) {
+                        lcc.internalCommit(true);
+                    }
+                }
+            }
+        }
+
         // [x][0] = conglomerate number, [x][1] = start time, [x][2] = stop time
         long[][] scanTimes = new long[conglomerateNumber.length][3];
         int sci = 0;
@@ -1087,18 +1176,29 @@ public class IndexStatisticsDaemonImpl
     private void log(boolean asBackgroundTask, TableDescriptor td, Throwable t,
             String msg) {
         if (asBackgroundTask && (doLog || t != null)) {
-            PrintWriter pw = null;
-            String hdrMsg = "{istat} " +
-                    (td == null ? "" : td.getQualifiedName() + ": ") + msg;
-            if (t != null) {
-                pw = new PrintWriter(logStream.getPrintWriter(), false);
-                pw.print(logStream.getHeader().getHeader());
-                pw.println(hdrMsg);
-                t.printStackTrace(pw);
-                pw.flush();
-            } else {
-                logStream.printlnWithHeader(hdrMsg);
-            }
+            logAlways(td, t, msg);
+        }
+    }
+
+    /**
+     * Logs the information given.
+     *
+     * @param td current table descriptor being worked on, may be {@code null}
+     * @param t raised error, may be {@code null}
+     * @param msg the message to log
+     */
+    private void logAlways(TableDescriptor td, Throwable t, String msg) {
+        PrintWriter pw;
+        String hdrMsg = "{istat} " +
+                (td == null ? "" : td.getQualifiedName() + ": ") + msg;
+        if (t != null) {
+            pw = new PrintWriter(logStream.getPrintWriter(), false);
+            pw.print(logStream.getHeader().getHeader());
+            pw.println(hdrMsg);
+            t.printStackTrace(pw);
+            pw.flush();
+        } else {
+            logStream.printlnWithHeader(hdrMsg);
         }
     }
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1340549&r1=1340548&r2=1340549&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Sat May 19 19:31:18 2012
@@ -711,7 +711,7 @@ class AlterTableConstantAction extends D
         td = dd.getTableDescriptor(tableId);
 
         if (updateStatisticsAll) {
-            cds = td.getConglomerateDescriptors();
+            cds = null;
         } else {
             cds = new ConglomerateDescriptor[1];
             cds[0] = dd.getConglomerateDescriptor(

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java?rev=1340549&r1=1340548&r2=1340549&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
Sat May 19 19:31:18 2012
@@ -27,6 +27,7 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.Types;
 import junit.framework.Test;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
@@ -58,7 +59,6 @@ public class UpdateStatisticsTest extend
         Test test = TestConfiguration.defaultSuite(UpdateStatisticsTest.class);
         Test statsDisabled = DatabasePropertyTestSetup.singleProperty
             ( test, "derby.storage.indexStats.auto", "false", true );
-
         return statsDisabled;
     }
 
@@ -488,6 +488,101 @@ public class UpdateStatisticsTest extend
     }
 
     /**
+     * Tests that the functionality that drops disposable statistics leaves
+     * useful statistics intact.
+     */
+    public void testDisposableStatsEagerness()
+            throws SQLException {
+        setAutoCommit(false);
+        String tbl = "DISPOSABLE_STATS_EAGERNESS";
+        String tbl_fk = tbl + "_FK";
+        String nuIdx = "NU_" + tbl;
+        Statement stmt = createStatement();
+
+        // Create and populate the foreign key table.
+        stmt.executeUpdate("create table " + tbl_fk + "(" +
+                "pk1 int generated always as identity)");
+        PreparedStatement ps = prepareStatement(
+                "insert into " + tbl_fk + " values (DEFAULT)");
+        for (int i=1; i <= 1000; i++) {
+            ps.executeUpdate();
+        }
+
+        // Create and populate the main table.
+        stmt.executeUpdate("create table " + tbl + "(" +
+                "pk1 int generated always as identity," +
+                "pk2 int not null," +
+                "mynonunique int, " +
+                "fk int not null)");
+        ps = prepareStatement("insert into " + tbl +
+                " values (DEFAULT, ?, ?, ?)");
+        for (int i=1; i <= 1000; i++) {
+            ps.setInt(1, i);
+            ps.setInt(2, i % 35);
+            ps.setInt(3, i);
+            ps.executeUpdate();
+        }
+        
+        // Create the various indexes.
+        stmt.executeUpdate("alter table " + tbl_fk + " add constraint PK_" +
+                tbl_fk + " primary key (pk1)");
+        
+        stmt.executeUpdate("alter table " + tbl + " add constraint PK_" + tbl +
+                " primary key (pk1, pk2)");
+        stmt.executeUpdate("alter table " + tbl + " add constraint FK_" + tbl +
+                " foreign key (fk) references " + tbl_fk + "(pk1)");
+        stmt.executeUpdate("create index " + nuIdx + " on " + tbl +
+                "(mynonunique)");
+        commit();
+        setAutoCommit(true);
+        IndexStatsUtil stats = new IndexStatsUtil(getConnection());
+        // Expected FK table: 1 (PK only)
+        // Expected main table: 2xPK, 1 non-unique, 1 FK = 4
+        stats.assertTableStats(tbl_fk, 1);
+        IndexStatsUtil.IdxStats tbl_fk_pk_0 = stats.getStatsTable(tbl_fk)[0];
+        stats.assertTableStats(tbl, 4);
+        IndexStatsUtil.IdxStats[] tbl_stats_0 = stats.getStatsTable(tbl);
+        // Avoid timestamp comparison problems on super-fast machines...
+        try {
+            Thread.sleep(10);
+        } catch (InterruptedException ie) {
+            Thread.currentThread().interrupt();
+        }
+
+        // Run the update statistics procedure.
+        ps = prepareStatement(
+                "call syscs_util.syscs_update_statistics('APP', ?, ?)");
+        ps.setNull(2, Types.VARCHAR);
+        ps.setString(1, tbl);
+        ps.execute();
+        ps.setString(1, tbl_fk);
+        ps.execute();
+
+        // Check the counts.
+        stats.assertTableStats(tbl_fk, 1);
+        stats.assertTableStats(tbl, 4);
+        // Check the timestamps (i.e. were they actually updated?).
+        IndexStatsUtil.IdxStats tbl_fk_pk_1 = stats.getStatsTable(tbl_fk)[0];
+        assertTrue(tbl_fk_pk_1.after(tbl_fk_pk_0));
+        IndexStatsUtil.IdxStats[] tbl_stats_1 = stats.getStatsTable(tbl);
+        assertEquals(tbl_stats_0.length, tbl_stats_1.length);
+        for (int i=0; i < tbl_stats_1.length; i++) {
+            assertTrue(tbl_stats_1[i].after(tbl_stats_0[i]));
+        }
+
+        // Now make sure updating one index doesn't modify the others' stats.
+        ps.setString(1, tbl);
+        ps.setString(2, nuIdx);
+        ps.execute();
+        // Just use any of the previous stats as a reference point.
+        IndexStatsUtil.IdxStats nonUniqueIdx = stats.getStatsIndex(nuIdx)[0];
+        assertTrue(nonUniqueIdx.after(tbl_stats_1[0]));
+        // Check the counts again.
+        stats.assertTableStats(tbl_fk, 1);
+        stats.assertTableStats(tbl, 4);
+    }
+
+    /**
      * A thread class that repeatedly calls SYSCS_UTIL.SYSCS_UPDATE_STATISTICS
      * until the flag {@code done} is set to true. Any exception thrown during
      * the lifetime of the thread can be found in the field {@code exception}.



Mime
View raw message