db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r392439 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/db/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/store/
Date Fri, 07 Apr 2006 22:30:57 GMT
Author: mikem
Date: Fri Apr  7 15:30:55 2006
New Revision: 392439

URL: http://svn.apache.org/viewcvs?rev=392439&view=rev
Log:
DERBY-1187

Defragment pass was not being committed properly, so when rows at
front of table were being deleted and freeing pages the defragment
pass did not move the rows to the free pages.  Longer term the 
defragment pass should commit more often, see DERBY-1188.
Added extra test to OnlineCompressTest.java for this case.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java?rev=392439&r1=392438&r2=392439&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/db/OnlineCompress.java Fri Apr 
7 15:30:55 2006
@@ -400,6 +400,15 @@
                     }
                 }
             }
+
+            // TODO - It would be better if commits happened more frequently
+            // in the nested transaction, but to do that there has to be more
+            // logic to catch a ddl that might jump in the middle of the 
+            // above loop and invalidate the various table control structures
+            // which are needed to properly update the indexes.  For example
+            // the above loop would corrupt an index added midway through
+            // the loop if not properly handled.  See DERBY-1188.  
+            nested_tc.commit();
 			
 		}
 		catch (StandardException se)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out?rev=392439&r1=392438&r2=392439&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/OnlineCompressTest.out
Fri Apr  7 15:30:55 2006
@@ -72,3 +72,9 @@
 Executing test: begin checkPurgePhase50 row test, create = false.
 Executing test: end checkPurgePhase50 row test.
 Ending test: test3
+Beginning test: test5
+Executing test: begin test5: 2000 row test.
+Executing test: end test5: 2000 row test.
+Executing test: begin test5: 10000 row test.
+Executing test: end test5: 10000 row test.
+Ending test: test5

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java?rev=392439&r1=392438&r2=392439&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/BaseTest.java
Fri Apr  7 15:30:55 2006
@@ -409,8 +409,12 @@
     protected static final int SPACE_INFO_IS_INDEX          = 0;
     protected static final int SPACE_INFO_NUM_ALLOC         = 1;
     protected static final int SPACE_INFO_NUM_FREE          = 2;
-    protected static final int SPACE_INFO_PAGE_SIZE         = 3;
-    protected static final int SPACE_INFO_ESTIMSPACESAVING  = 4;
+    protected static final int SPACE_INFO_NUM_UNFILLED      = 3;
+    protected static final int SPACE_INFO_PAGE_SIZE         = 4;
+    protected static final int SPACE_INFO_ESTIMSPACESAVING  = 5;
+
+    protected static final int SPACE_INFO_NUMCOLS           = 6;
+
     protected int[] getSpaceInfo(
     Connection  conn,
     String      schemaName,
@@ -419,7 +423,7 @@
 		throws SQLException
     {
         String stmt_str = 
-            "select conglomeratename, isindex, numallocatedpages, numfreepages, pagesize,
estimspacesaving from new org.apache.derby.diag.SpaceTable('" +
+            "select conglomeratename, isindex, numallocatedpages, numfreepages, numunfilledpages,
pagesize, estimspacesaving from new org.apache.derby.diag.SpaceTable('" +
             tableName + "') t where isindex = 0";
         PreparedStatement space_stmt = conn.prepareStatement(stmt_str);
         ResultSet rs = space_stmt.executeQuery();
@@ -434,9 +438,9 @@
             }
         }
 
-        int[] ret_info = new int[5];
+        int[] ret_info = new int[SPACE_INFO_NUMCOLS];
         String conglomerate_name        = rs.getString(1);
-        for (int i = 0; i < 5; i++)
+        for (int i = 0; i < SPACE_INFO_NUMCOLS; i++)
         {
             ret_info[i] = rs.getInt(i + 2);
         }
@@ -455,10 +459,16 @@
         {
             System.out.println(
                 "Space information for " + schemaName + "." + tableName + ":");
-            System.out.println("isindex = " + ret_info[SPACE_INFO_IS_INDEX]);
-            System.out.println("num_alloc = " + ret_info[SPACE_INFO_NUM_ALLOC]);
-            System.out.println("num_free = " + ret_info[SPACE_INFO_NUM_FREE]);
-            System.out.println("page_size = " + ret_info[SPACE_INFO_PAGE_SIZE]);
+            System.out.println(
+                "isindex = " + ret_info[SPACE_INFO_IS_INDEX]);
+            System.out.println(
+                "num_alloc = " + ret_info[SPACE_INFO_NUM_ALLOC]);
+            System.out.println(
+                "num_free = " + ret_info[SPACE_INFO_NUM_FREE]);
+            System.out.println(
+                "num_unfilled = " + ret_info[SPACE_INFO_NUM_UNFILLED]);
+            System.out.println(
+                "page_size = " + ret_info[SPACE_INFO_PAGE_SIZE]);
             System.out.println(
                 "estimspacesaving = " + ret_info[SPACE_INFO_ESTIMSPACESAVING]);
         }
@@ -471,4 +481,15 @@
         return(ret_info);
     }
 
+    /**
+     * Given output from getSpaceInfo(), return total pages in file.
+     * <p>
+     * simply the sum of allocated and free pages.
+     *
+     **/
+    protected int total_pages(int[] space_info)
+    {
+        return(space_info[SPACE_INFO_NUM_FREE] + 
+               space_info[SPACE_INFO_NUM_ALLOC]);
+    }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java?rev=392439&r1=392438&r2=392439&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/OnlineCompressTest.java
Fri Apr  7 15:30:55 2006
@@ -318,13 +318,15 @@
         "    IS_INDEX         =" + before_info[SPACE_INFO_IS_INDEX]     + 
         "\n    NUM_ALLOC        =" + before_info[SPACE_INFO_NUM_ALLOC]    +
         "\n    NUM_FREE         =" + before_info[SPACE_INFO_NUM_FREE]     +
+        "\n    NUM_UNFILLED     =" + before_info[SPACE_INFO_NUM_UNFILLED] +
         "\n    PAGE_SIZE        =" + before_info[SPACE_INFO_PAGE_SIZE]    +
         "\n    ESTIMSPACESAVING =" + before_info[SPACE_INFO_ESTIMSPACESAVING]);
         System.out.println("after_info:");
         System.out.println(
-        "    IS_INDEX         =" + after_info[SPACE_INFO_IS_INDEX]     + 
+        "    IS_INDEX         =" + after_info[SPACE_INFO_IS_INDEX]       + 
         "\n    NUM_ALLOC        =" + after_info[SPACE_INFO_NUM_ALLOC]    +
         "\n    NUM_FREE         =" + after_info[SPACE_INFO_NUM_FREE]     +
+        "\n    NUM_UNFILLED     =" + after_info[SPACE_INFO_NUM_UNFILLED] +
         "\n    PAGE_SIZE        =" + after_info[SPACE_INFO_PAGE_SIZE]    +
         "\n    ESTIMSPACESAVING =" + after_info[SPACE_INFO_ESTIMSPACESAVING]);
     }
@@ -911,6 +913,311 @@
         endTest(conn, test_name);
     }
 
+    /**
+     * Create and load table for test5.
+     * <p>
+     * schema of table:
+     *     keycol   int, 
+     *     onehalf  int, 
+     *     onethird int, 
+     *     c        varchar(300)
+     *
+     * @param conn          Connection to use for sql execution.
+     * @param create_table  If true, create new table - otherwise load into
+     *                      existing table.
+     * @param tblname       table to use.
+     * @param num_rows      number of rows to add to the table.
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    private void test5_load(
+    Connection  conn,
+    String      schemaName,
+    String      table_name,
+    int         num_rows)
+        throws SQLException
+    {
+        Statement s = conn.createStatement();
+
+        s.execute(
+            "create table " + table_name + 
+            " (keycol integer primary key, onehalf integer, onethird integer, c varchar(300))");
+        s.close();
+
+        PreparedStatement insert_stmt = 
+            conn.prepareStatement(
+                "insert into " + table_name + " values(?, ?, ?, ?)");
+
+        char[]  data1_data = new char[200];
+
+        for (int i = 0; i < data1_data.length; i++)
+        {
+            data1_data[i] = 'b';
+        }
+        String  data1_str = new String(data1_data);
+
+        for (int i = 0; i < num_rows; i++)
+        {
+            insert_stmt.setInt(1, i);               // keycol
+            insert_stmt.setInt(2, i % 2);           // onehalf:  0 or 1 
+            insert_stmt.setInt(3, i % 3);           // onethird: 0, 1, or 3
+            insert_stmt.setString(4, data1_str);    // c
+            insert_stmt.execute();
+        }
+
+        conn.commit();
+    }
+    
+    /**
+     * Execute test5, simple defragement test. 
+     * <p>
+     * o delete every other row, defragment
+     * o delete every third row, defragment
+     * o delete last 1000 rows, defragment
+     * o delete first 512 rows, defragment.
+     * <p>
+     * run test with at least 2000 rows.
+     **/
+    private void test5_run(
+    Connection  conn,
+    String      schemaName,
+    String      table_name,
+    int         num_rows)
+        throws SQLException
+    {
+        testProgress("begin test5: " + num_rows + " row test.");
+
+        if (verbose)
+            testProgress("Calling compress.");
+
+        // compress with no deletes should not affect size
+        int[] ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        int[] ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC])
+        {
+            log_wrong_count(
+                "Expected no alloc page change.", 
+                table_name, num_rows, 
+                ret_before[SPACE_INFO_NUM_ALLOC], 
+                ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE EVERY OTHER ROW, COMPRESS, CHECK
+        //
+        //
+
+        // delete all the rows every other row.
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where onehalf = 0", true);
+
+        if (verbose)
+            testProgress("deleted every other row, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (total_pages(ret_after) != total_pages(ret_before))
+        {
+            // currently deleting every other row does not add free or unfilled
+            // pages to the container so defragment has nowhere to put the rows.
+
+            log_wrong_count(
+                "Expected no truncation.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE EVERY THIRD ROW in original dataset, COMPRESS, CHECK
+        //
+        //
+
+        // delete every third row
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where onethird = 0", true);
+
+        if (verbose)
+            testProgress("deleted every third row, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (total_pages(ret_after) != total_pages(ret_before))
+        {
+            // currently deleting every third row does not create any free 
+            // or unfilled pages so defragment has no place to move rows.
+            log_wrong_count(
+                "Expected no truncation.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE top "half" of rows in original dataset, COMPRESS, CHECK
+        //
+        //
+
+        // delete top "half" of the rows in the original dataset.
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where keycol > " + 
+            (num_rows / 2), true);
+
+        if (verbose)
+            testProgress("deleted top half of the rows, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        // compress should be able to clean up about 1/2 of the pages.
+        if (verbose)
+        {
+            log_wrong_count(
+                "deleted top half keys, spaceinfo:",
+                table_name, num_rows, 
+                ((total_pages(ret_before) / 2) + 2),
+                ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (total_pages(ret_after) > ((total_pages(ret_before) / 2) + 2))
+        {
+            log_wrong_count(
+                "Expected at least " + 
+                (ret_before[SPACE_INFO_NUM_ALLOC] / 2 + 2) +
+                " pages to be truncated.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+        // DELETE 1st 500 rows in original dataset, COMPRESS, CHECK
+        //
+        //
+
+        // delete keys less than 500
+        ret_before = getSpaceInfo(conn, "APP", table_name, true);
+        executeQuery(
+            conn, "delete from " + table_name + " where keycol < 500 ", true);
+
+        if (verbose)
+            testProgress("deleted keys < 500, now calling compress.");
+
+        callCompress(conn, "APP", table_name, true, true, true, true);
+        ret_after  = getSpaceInfo(conn, "APP", table_name, true);
+
+        if (verbose)
+        {
+            log_wrong_count(
+                "deleted bottom 500 keys, spaceinfo:",
+                table_name, num_rows, 
+                (total_pages(ret_before) - 33),
+                ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        // The bottom 500 keys, assuming 4k pages, takes about 33 pages
+        if (total_pages(ret_after) > (total_pages(ret_before) - 33))
+        {
+            log_wrong_count(
+                "Expected at least 33 pages reclaimed.",
+                table_name, num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
+                ret_before, ret_after);
+        }
+
+        if (verbose)
+            testProgress("calling consistency checker.");
+
+        if (!checkConsistency(conn, schemaName, table_name))
+        {
+            logError("conistency check failed.");
+        }
+
+
+        conn.commit();
+
+        testProgress("end test5: " + num_rows + " row test.");
+    }
+
+    /**
+     * Cleanup after test5_run
+     **/
+    private void test5_cleanup(
+    Connection  conn,
+    String      schemaName,
+    String      table_name,
+    int         num_rows)
+        throws SQLException
+    {
+        executeQuery(conn, "drop table " + table_name, true);
+    }
+
+    /**
+     * Test 5 - simple defragment test.
+     * <p>
+     * Create dataset and then:
+     * o delete every other row, defragment
+     * o delete every third row, defragment
+     * o delete last 1000 rows, defragment
+     * o delete first 512 rows, defragment.
+     * <p>
+     * run test with at least 2000 rows.
+     *
+     **/
+    private void test5(
+    Connection  conn,
+    String      test_name,
+    String      table_name)
+        throws SQLException 
+    {
+        beginTest(conn, test_name);
+
+        int[] test_cases = {2000, 10000};
+
+        for (int i = 0; i < test_cases.length; i++)
+        {
+            test5_load(conn, "APP", table_name, test_cases[i]);
+            test5_run(conn, "APP", table_name, test_cases[i]);
+            test5_cleanup(conn, "APP", table_name, test_cases[i]);
+        }
+
+        endTest(conn, test_name);
+    }
+
 
 
     public void testList(Connection conn)
@@ -919,7 +1226,8 @@
         test1(conn, "test1", "TEST1");
         // test2(conn, "test2", "TEST2");
         test3(conn, "test3", "TEST3");
-        // test4(conn, "test2", "TEST2");
+        // test4(conn, "test4", "TEST4");
+        test5(conn, "test5", "TEST5");
     }
 
     public static void main(String[] argv) 



Mime
View raw message