db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r383663 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/store/raw/ engine/org/apache/derby/impl/store/access/conglomerate/ engine/org/apache/derby/impl/store/raw/data/ testing/org/apache/derbyTesting/functionTests/master/ testi...
Date Mon, 06 Mar 2006 20:50:47 GMT
Author: mikem
Date: Mon Mar  6 12:50:46 2006
New Revision: 383663

URL: http://svn.apache.org/viewcvs?rev=383663&view=rev
Log:
DERBY-670 - improve automatic space reclamation of long rows and long columns.

This patch provides a non-upgrade solution to the problem.  It uses existing
row and column format information to determine at delete time if the row or
column is "long".  If so it schedules a post commit reclamation immediately
rather than waiting for all rows on a page to be deleted.



Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/st_reclaim_longcol.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/storetests/st_reclaim_longcol.java
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/store/raw/Page.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/conglomerate/GenericConglomerateController.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/BasePage.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/StoredRecordHeader.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storetests.runall
    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/store/raw/Page.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/store/raw/Page.java?rev=383663&r1=383662&r2=383663&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/store/raw/Page.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/store/raw/Page.java Mon Mar  6 12:50:46
2006
@@ -317,10 +317,6 @@
     int                 overflowThreshold)
 		throws StandardException;
 
-	/**
-		Update the complete record identified by the record handle.
-
-	*/
     /**
      * Update the record identified by the record handle.
      * <p>
@@ -548,10 +544,6 @@
 	int getNextSlotNumber(RecordHandle handle) 
         throws StandardException;
 
-	/**
-		Insert a record at the specified slot. 
-		<P>
-	 */
     /**
      * Insert a record at the specified slot. 
      * <p>
@@ -618,10 +610,6 @@
     int                     overflowThreshold)
 		throws StandardException;
 
-	/**
-		Values for insertFlag:
-		
-	*/
     /**
      * Values for insertFlag.
      * <p>
@@ -661,14 +649,14 @@
      * 4. If INSERT_DEFAULT, INSERT_OVERFLOW both are not set, then, default 
      *    insert action will be taken, i.e. no overflow will be allowed.
      **/
-	static final byte INSERT_INITIAL =		   (byte) 0x00;	// init the flag
-	static final byte INSERT_DEFAULT =		   (byte) 0x01;	// default flag
+	static final byte INSERT_INITIAL         = (byte) 0x00;	// init the flag
+	static final byte INSERT_DEFAULT         = (byte) 0x01;	// default flag
 	static final byte INSERT_UNDO_WITH_PURGE = (byte) 0x02;	// purge row on undo
-	static final byte INSERT_CONDITIONAL =     (byte) 0x04;	// conditional 
+	static final byte INSERT_CONDITIONAL     = (byte) 0x04;	// conditional 
                                                             // insert
-	static final byte INSERT_OVERFLOW =		   (byte) 0x08;	// insert with 
+	static final byte INSERT_OVERFLOW        = (byte) 0x08;	// insert with 
                                                             // possible overflow
-	static final byte INSERT_FOR_SPLIT =	   (byte) 0x10;	// rawstore only
+	static final byte INSERT_FOR_SPLIT       = (byte) 0x10;	// rawstore only
 
 
     /**
@@ -1135,6 +1123,40 @@
 	*/
 
 	public int nonDeletedRecordCount() throws StandardException;
+
+    /**
+     * Is this page/deleted row a candidate for immediate reclaim space.
+     * <p>
+     * Used by access methods after executing a delete on "slot_just_deleted"
+     * to ask whether a post commit should be queued to try to reclaim space
+     * after the delete commits.  
+     * <p>
+     * Will return true if the number of non-deleted rows on the page is
+     * <= "num_non_deleted_rows".  For instance 0 means schedule reclaim
+     * only if all rows are deleted, 1 if all rows but one are deleted.  
+     * <p>
+     * Will return true if the row just deleted is either a long row or long
+     * column.  In this case doing a reclaim space on the single row may
+     * reclaim multiple pages of free space, so better to do it now rather
+     * than wait for all rows on page to be deleted.  This case is to address
+     * the worst case scenario of all rows with long columns, but very short
+     * rows otherwise.  In this case there could be 1000's of rows on the 
+     * main page with many gigabytes of data on overflow pages in deleted space
+     * that would not be reclaimed until all rows on the page were deleted.
+     *
+	 * @return true if a reclaim space should be scheduled post commit on this
+     *         page, false otherwise.
+     *
+     * @param num_non_deleted_rows threshold number of non-deleted rows to
+     *                             schedule reclaim space.
+     * @param slot_just_deleted    row on page to check for long row/long column
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    public boolean shouldReclaimSpace(
+    int     num_non_deleted_rows,
+    int     slot_just_deleted)
+        throws StandardException;
 
 	/**
 	  Set the aux object for this page.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/conglomerate/GenericConglomerateController.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/conglomerate/GenericConglomerateController.java?rev=383663&r1=383662&r2=383663&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/conglomerate/GenericConglomerateController.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/conglomerate/GenericConglomerateController.java
Mon Mar  6 12:50:46 2006
@@ -204,11 +204,11 @@
 
             // try to reclaim rows when the page is only full of deleted rows,
             // or in the special case of the first page when all rows except the
-            // "control row" are deleted.
-            if ((pos.current_page.nonDeletedRecordCount() == 0) ||
-                ((pos.current_page.nonDeletedRecordCount() == 1) && 
-                 (pos.current_page.getPageNumber() == 
-                  ContainerHandle.FIRST_PAGE_NUMBER)))
+            // "control row" are deleted.  Or if the row we just deleted is
+            // a long row or has a long column.
+            if (pos.current_page.shouldReclaimSpace(
+                pos.current_page.getPageNumber() == 1 ? 1 : 0,
+                pos.current_slot))
             {
                 queueDeletePostCommitWork(pos);
             }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/BasePage.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/BasePage.java?rev=383663&r1=383662&r2=383663&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/BasePage.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/BasePage.java Mon
Mar  6 12:50:46 2006
@@ -1496,6 +1496,62 @@
 
 	}
 
+    /**
+     * Is this page/deleted row a candidate for immediate reclaim space.
+     * <p>
+     * Used by access methods after executing a delete on "slot_just_deleted"
+     * to ask whether a post commit should be queued to try to reclaim space
+     * after the delete commits.  
+     * <p>
+     * Will return true if the number of non-deleted rows on the page is
+     * <= "num_non_deleted_rows".  For instance 0 means schedule reclaim
+     * only if all rows are deleted, 1 if all rows but one are deleted.  
+     * <p>
+     * Will return true if the row just deleted is either a long row or long
+     * column.  In this case doing a reclaim space on the single row may
+     * reclaim multiple pages of free space, so better to do it now rather
+     * than wait for all rows on page to be deleted.  This case is to address
+     * the worst case scenario of all rows with long columns, but very short
+     * rows otherwise.  In this case there could be 1000's of rows on the 
+     * main page with many gigabytes of data on overflow pages in deleted space
+     * that would not be reclaimed until all rows on the page were deleted.
+     *
+	 * @return true if a reclaim space should be scheduled post commit on this
+     *         page, false otherwise.
+     *
+     * @param num_non_deleted_rows threshold number of non-deleted rows to
+     *                             schedule reclaim space.
+     * @param slot_just_deleted    row on page to check for long row/long column
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    public boolean shouldReclaimSpace(
+    int     num_non_deleted_rows,
+    int     slot_just_deleted)
+        throws StandardException
+    {
+        if (SanityManager.DEBUG)
+        {
+            SanityManager.ASSERT(isLatched());
+        }
+
+        boolean ret_val = false;
+
+        if (internalNonDeletedRecordCount() <= num_non_deleted_rows)
+        {
+            ret_val = true;
+        }
+        else 
+        {
+            if (!entireRecordOnPage(slot_just_deleted))
+            {
+                ret_val = true;
+            }
+        }
+
+        return(ret_val);
+    }
+
 	// no need to check for slot on page, call already checked
 	protected final boolean isDeletedOnPage(int slot)
 	{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/StoredRecordHeader.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/StoredRecordHeader.java?rev=383663&r1=383662&r2=383663&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/StoredRecordHeader.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/StoredRecordHeader.java
Mon Mar  6 12:50:46 2006
@@ -70,8 +70,8 @@
      * RECORD_OVERFLOW			- used to indicate the record has been 
      *                            overflowed, it will point to the overflow 
      *                            page and ID
-     * RECORD_HAS_FIRST_FIELD	- used to indicate that firstField is stored 
-     *                            will be stored.  When RECORD_OVERFLOW and 
+     * RECORD_HAS_FIRST_FIELD	- used to indicate that firstField is stored. 
+     *                            When RECORD_OVERFLOW and 
      *                            RECORD_HAS_FIRST_FIELD both are set, part of 
      *                            record is on the page, the record header 
      *                            also stores the overflow point to the next 

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/st_reclaim_longcol.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/st_reclaim_longcol.out?rev=383663&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/st_reclaim_longcol.out
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/st_reclaim_longcol.out
Mon Mar  6 12:50:46 2006
@@ -0,0 +1,4 @@
+Beginning test: test1:insert/delete of 20 rows with blob(250000)
+Ending test: test1:insert/delete of 20 rows with blob(250000)
+Beginning test: test2:queue of 5 rows with blob(250000), total_work = 500
+Ending test: test2:queue of 5 rows with blob(250000), total_work = 500

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storetests.runall
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storetests.runall?rev=383663&r1=383662&r2=383663&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storetests.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storetests.runall
Mon Mar  6 12:50:46 2006
@@ -1,4 +1,5 @@
 storetests/st_schema.sql
+storetests/st_reclaim_longcol.java
 storetests/st_derby715.java
 storetests/st_1.sql
 storetests/st_b5772.sql

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=383663&r1=383662&r2=383663&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
Mon Mar  6 12:50:46 2006
@@ -43,7 +43,8 @@
 **/
 public abstract class BaseTest
 {
-    private static boolean debug_system_procedures_created = false;
+    private   static boolean debug_system_procedures_created = false;
+    protected static boolean verbose = false;
 
     abstract public void testList(Connection conn) throws SQLException;
 
@@ -357,4 +358,117 @@
 
         return(lock_output);
     }
+
+    /**
+     * create given table on the input connection.
+     * <p>
+     * Takes care of dropping the table if it exists already.
+     * <p>
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    public void createTable(
+    Connection  conn,
+    String      tbl_name,
+    String      create_str)
+		throws SQLException
+    {
+        Statement  stmt = conn.createStatement();
+
+        // drop table, ignore table does not exist error.
+
+        try
+        {
+            stmt.executeUpdate("drop table " + tbl_name);
+        }
+        catch (Exception e)
+        {
+            // ignore drop table errors.
+        }
+
+        stmt.executeUpdate(create_str);
+    }
+
+    /**
+     * call the space table vti.
+     * <p>
+     * Utility test function to call the space table vti to get information
+     * about allocated and free pages.  Information is passed back in an
+     * int array as follows:
+     *   is_index                 = ret_info[0];
+     *   num_alloc                = ret_info[1];
+     *   num_free                 = ret_info[2];
+     *   page_size                = ret_info[3];
+     *   estimate_space_savings   = ret_info[4];
+     * <p>
+     *
+	 * @return the space information about the table.
+     *
+	 * @exception  StandardException  Standard exception policy.
+     **/
+    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 int[] getSpaceInfo(
+    Connection  conn,
+    String      schemaName,
+    String      tableName,
+    boolean     commit_xact)
+		throws SQLException
+    {
+        String stmt_str = 
+            "select conglomeratename, isindex, numallocatedpages, numfreepages, 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();
+
+        if (!rs.next())
+        {
+            if (SanityManager.DEBUG)
+            {
+                SanityManager.THROWASSERT(
+                    "No rows returned from space table query on table: " +
+                    schemaName + "." + tableName);
+            }
+        }
+
+        int[] ret_info = new int[5];
+        String conglomerate_name        = rs.getString(1);
+        for (int i = 0; i < 5; i++)
+        {
+            ret_info[i] = rs.getInt(i + 2);
+        }
+
+        if (rs.next())
+        {
+            if (SanityManager.DEBUG)
+            {
+                SanityManager.THROWASSERT(
+                    "More than one row returned from space query on table: " +
+                    schemaName + "." + tableName);
+            }
+        }
+
+        if (verbose)
+        {
+            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(
+                "estimspacesaving = " + ret_info[SPACE_INFO_ESTIMSPACESAVING]);
+        }
+
+        rs.close();
+
+        if (commit_xact)
+            conn.commit();
+
+        return(ret_info);
+    }
+
 }

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=383663&r1=383662&r2=383663&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
Mon Mar  6 12:50:46 2006
@@ -74,89 +74,6 @@
     }
 
     /**
-     * call the space table vti.
-     * <p>
-     * Utility test function to call the space table vti to get information
-     * about allocated and free pages.  Information is passed back in an
-     * int array as follows:
-     *   is_index                 = ret_info[0];
-     *   num_alloc                = ret_info[1];
-     *   num_free                 = ret_info[2];
-     *   page_size                = ret_info[3];
-     *   estimate_space_savings   = ret_info[4];
-     * <p>
-     *
-	 * @return the space information about the table.
-     *
-	 * @exception  StandardException  Standard exception policy.
-     **/
-    private static final int SPACE_INFO_IS_INDEX        = 0;
-    private static final int SPACE_INFO_NUM_ALLOC       = 1;
-    private static final int SPACE_INFO_NUM_FREE        = 2;
-    private static final int SPACE_INFO_PAGE_SIZE       = 3;
-    private static final int SPACE_INFO_ESTIMSPACESAVING = 4;
-    private int[] getSpaceInfo(
-    Connection  conn,
-    String      schemaName,
-    String      tableName,
-    boolean     commit_xact)
-		throws SQLException
-    {
-        String stmt_str = 
-            "select conglomeratename, isindex, numallocatedpages, numfreepages, 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();
-
-        if (!rs.next())
-        {
-            if (SanityManager.DEBUG)
-            {
-                SanityManager.THROWASSERT(
-                    "No rows returned from space table query on table: " +
-                    schemaName + "." + tableName);
-            }
-        }
-
-        int[] ret_info = new int[5];
-        String conglomerate_name        = rs.getString(1);
-        for (int i = 0; i < 5; i++)
-        {
-            ret_info[i] = rs.getInt(i + 2);
-        }
-
-        if (rs.next())
-        {
-            if (SanityManager.DEBUG)
-            {
-                SanityManager.THROWASSERT(
-                    "More than one row returned from space query on table: " +
-                    schemaName + "." + tableName);
-            }
-        }
-
-        if (verbose)
-        {
-            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(
-                "estimspacesaving = " + ret_info[SPACE_INFO_ESTIMSPACESAVING]);
-        }
-
-        rs.close();
-
-        if (commit_xact)
-            conn.commit();
-
-        return(ret_info);
-    }
-
-
-    /**
      * Create and load a table.
      * <p>
      * If create_table is set creates a test data table with indexes.

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/storetests/st_reclaim_longcol.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/storetests/st_reclaim_longcol.java?rev=383663&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/storetests/st_reclaim_longcol.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/storetests/st_reclaim_longcol.java
Mon Mar  6 12:50:46 2006
@@ -0,0 +1,419 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.harness.procedure
+
+   Copyright 2005 The Apache Software Foundation or its licensors, as applicable.
+
+   Licensed under the Apache License, Version 2.0 (the "License");
+   you may not use this file except in compliance with the License.
+   You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.storetests;
+
+
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import org.apache.derbyTesting.functionTests.tests.store.BaseTest;
+
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import java.util.Arrays;
+
+import org.apache.derby.tools.ij;
+
+
+/**
+
+The purpose of this test space reclamation of long rows and long columns.
+This addresses DERBY-670.
+
+The main issue is that previous to fixes for DERBY-670, space reclamation
+was only automatically queued when the last row on a page was deleted.  In
+the case of long columns, the actual row on the main page can be quite small
+as the long data is streamed onto other pages.  So the table can grow 
+unexpectedly quite large before the default space reclamation kicks in.  The
+change queues space reclamation in the case of long columns (blob/clob),
+imediately post commit of the single delete.
+
+The testing strategy is to loop doing insert, delete, commit of a blob for
+a number of iterations and check that the actual size of the table is 
+reasonable.  A sleep will be added to allow time for post commit to catch up
+as the test may be run in a number of environments with varying performance
+of background activities.
+
+**/
+
+public class st_reclaim_longcol extends BaseTest
+{
+    static boolean verbose = false;
+
+    public st_reclaim_longcol()
+    {
+    }
+
+
+    /**
+     * Create the base table.
+     **/
+    private static void setup()
+        throws Exception
+    {
+    }
+
+    /**
+     * Test reclaim of a single deleted blob on a page with non-deleted rows.
+     * <p>
+     * loops through inserting alternating long and short column rows resulting
+     * in pages with 1 short and one long.  Deletes the long column row and
+     * tests that space from the long column row is reclaimed even though
+     * there are non-deleted rows on the page.
+     **/
+    private static final int SHORT_BLOB_SIZE = 10;
+    public void test1(Connection conn, int blob_size, int num_rows)
+        throws SQLException
+    {
+        byte[]  long_byteVal    = new byte[blob_size];
+        byte[]  short_byteVal   = new byte[10];
+
+        beginTest(
+            conn, 
+            "test1:insert/delete of " + num_rows + 
+                " rows with blob(" + blob_size + ")"); 
+
+        Arrays.fill(long_byteVal,  (byte)'L');
+        Arrays.fill(short_byteVal, (byte)'S');
+
+        createTable(
+            conn, 
+            "longcol", 
+            "create table longcol (id int primary key not null, val blob(" + 
+            blob_size + "))");
+
+        conn.commit();
+
+        PreparedStatement ins_stmt = 
+            conn.prepareStatement("insert into longcol values (?, ?)");
+        PreparedStatement del_stmt = 
+            conn.prepareStatement("delete from longcol where id = ?");
+
+        // worst case is a mixture of rows with long columns and those without.
+        // Insert of row with a long column always first goes onto a new 
+        // page by itself, but subsequent non-long column rows can be inserted
+        // on that page.  Then when the long column row is deleted - before the
+        // change - it and all it's chain won't get reclaimed until all rows
+        // on the page get deleted.
+
+        // now do insert/delete/commit for subsequent rows.  Before fix the
+        // space used in the table will grow until the deleted rows do not
+        // fit on the first page.  And even then before the fix the rows
+        // on the first page are never reclaimed as the 1st one is never
+        // deleted.
+        for (int iter = 1; iter < num_rows; iter++)
+        {
+            // insert the long blob
+            ins_stmt.setInt(  1, iter);
+            ins_stmt.setBytes(2, long_byteVal);
+            ins_stmt.executeUpdate();
+
+            // insert the short blob
+            ins_stmt.setInt(  1, -(iter));
+            ins_stmt.setBytes(2, short_byteVal);
+            ins_stmt.executeUpdate();
+
+            // delete the long blob
+            del_stmt.setInt(1, iter);
+            del_stmt.executeUpdate();
+
+            // commit the xact, post commit should kick in to reclaim the
+            // blob space sometime after the commit.
+            conn.commit();
+
+            // sleep, just in case on this machine background
+            // post commit is slow.
+            try
+            {
+                Thread.sleep(20);
+            }
+            catch (Exception ex)
+            {
+                // just ignore interupts of sleep.
+            }
+        }
+
+        int[] sp_info = getSpaceInfo(conn, "APP", "LONGCOL", true);
+
+        int total_pages = 
+            sp_info[SPACE_INFO_NUM_ALLOC] + sp_info[SPACE_INFO_NUM_FREE];
+
+        int total_expected_page_max = 12 + num_rows;
+
+        if (total_pages > total_expected_page_max)
+        {
+            // for the above test case we expect the following space:
+            //     page 0
+            //     page 1 
+            //     free space from 1 blob - 9 pages per blob
+            //     allocated page per long/short blob insert.  Each long
+            //         inserts onto a new page to try and fit it entirely
+            //         on a page.  Then the short blob goes to last inserted
+            //         page.  This process repeats.  The previous pages are
+            //         marked "half-filled" and can be used in future for
+            //         short rows that don't fit on the last page inserted.
+            
+
+
+            System.out.println(
+                "Test failed, expected less than " + 
+                total_expected_page_max + " pages - count is:\n" +
+                "free pages     : "   + sp_info[SPACE_INFO_NUM_FREE] +
+                "\nallocated pages: " + sp_info[SPACE_INFO_NUM_ALLOC]);
+        }
+
+        if (verbose)
+        {
+            System.out.println(
+                "Space information after " + num_rows + 
+                "insert/delete pairs of rows in longcol table containing " + 
+                blob_size + "blobs:");
+
+            System.out.println("isindex = "   + sp_info[SPACE_INFO_IS_INDEX]);
+            System.out.println("num_alloc = " + sp_info[SPACE_INFO_NUM_ALLOC]);
+            System.out.println("num_free = "  + sp_info[SPACE_INFO_NUM_FREE]);
+            System.out.println("page_size = " + sp_info[SPACE_INFO_PAGE_SIZE]);
+            System.out.println(
+                "estimspacesaving = " + sp_info[SPACE_INFO_ESTIMSPACESAVING]);
+        }
+
+        endTest(
+            conn, 
+            "test1:insert/delete of " + num_rows + 
+                " rows with blob(" + blob_size + ")"); 
+    }
+
+    /**
+     * Test reclaim of sequence of deleted blobs.
+     * <p>
+     * Simulates a "queue" of work of input "work_size".  Inserts "work_size"
+     * elements, and then subsequently in each transaction inserts a new 
+     * work item and deletes the oldest work item.  Checks that the used
+     * space reaches a steady state, rather than constantly growing.
+     *
+     **/
+    public void test2(
+    Connection  conn, 
+    int         blob_size, 
+    int         work_size, 
+    int         total_work)
+        throws SQLException
+    {
+        byte[]  long_byteVal    = new byte[blob_size];
+        byte[]  short_byteVal   = new byte[10];
+
+        beginTest(
+            conn, 
+            "test2:queue of " + work_size + 
+                " rows with blob(" + blob_size + "), total_work = " + 
+                total_work); 
+
+        Arrays.fill(long_byteVal,  (byte)'L');
+        Arrays.fill(short_byteVal, (byte)'S');
+
+        createTable(
+            conn, 
+            "longcol", 
+            "create table longcol (id int primary key not null, val blob(" + 
+            blob_size + "))");
+
+        conn.commit();
+
+        PreparedStatement ins_stmt = 
+            conn.prepareStatement("insert into longcol values (?, ?)");
+        PreparedStatement del_stmt = 
+            conn.prepareStatement("delete from longcol where id = ?");
+
+        // insert the "work_size" number of elements into the table
+        for (int iter = 0; iter < work_size; iter++)
+        {
+            // insert the long blob
+            ins_stmt.setInt(  1, iter);
+            ins_stmt.setBytes(2, long_byteVal);
+            ins_stmt.executeUpdate();
+
+            // commit the xact, post commit should kick in to reclaim the
+            // blob space sometime after the commit.
+        }
+        conn.commit();
+
+
+        // for each subsequent work item, queue it to the end and delete
+        // the oldes existing work item.
+        for (int iter = work_size; iter < total_work; iter++)
+        {
+            // insert the long blob
+            ins_stmt.setInt(  1, iter);
+            ins_stmt.setBytes(2, long_byteVal);
+            ins_stmt.executeUpdate();
+
+
+            // delete the long blob
+            del_stmt.setInt(1, iter - work_size - 1);
+            del_stmt.executeUpdate();
+
+            // commit the xact, post commit should kick in to reclaim the
+            // blob space sometime after the commit.
+            conn.commit();
+
+            try
+            {
+                Thread.sleep(20);
+            }
+            catch (Exception ex)
+            {
+                // just ignore interupts of sleep.
+            }
+        }
+
+
+        int[] sp_info = getSpaceInfo(conn, "APP", "LONGCOL", true);
+
+        int total_pages = 
+            sp_info[SPACE_INFO_NUM_ALLOC] + sp_info[SPACE_INFO_NUM_FREE];
+
+        // Expect at least allocated pages * 10 for each item in work_size, 
+        // plus some overhead for 1st page and such.
+        // Free page count depends on how quick post commit can free before
+        // subsequent insert, and very likely is machine/jvm/os dependent. In
+        // my testing adding a sleep of 100 ms. to the above insert/delete
+        // loop changed free from 60 to 30.  Minimum is 10 for the one row 
+        // that is deleted in the same xact as the first inserted row in the 
+        // insert/delete loop.  The 30 below is expected allocate of 10
+        // per work size, and then a guess at how fast post commit can keep
+        // up with free pages.  Run the test with total_work reasonably 
+        // bigger than worksize, something like work_size=5 and total_work >100
+        int total_expected_page_max = 30 * work_size; 
+
+        if (total_pages > total_expected_page_max)
+        {
+            System.out.println(
+                "Test failed, expected less than " + 
+                total_expected_page_max + " pages - count is:\n" +
+                "free pages     : "   + sp_info[SPACE_INFO_NUM_FREE] +
+                "\nallocated pages: " + sp_info[SPACE_INFO_NUM_ALLOC]);
+        }
+
+        if (verbose)
+        {
+            System.out.println("Space information:");
+
+            System.out.println("isindex = "   + sp_info[SPACE_INFO_IS_INDEX]);
+            System.out.println("num_alloc = " + sp_info[SPACE_INFO_NUM_ALLOC]);
+            System.out.println("num_free = "  + sp_info[SPACE_INFO_NUM_FREE]);
+            System.out.println("page_size = " + sp_info[SPACE_INFO_PAGE_SIZE]);
+            System.out.println(
+                "estimspacesaving = " + sp_info[SPACE_INFO_ESTIMSPACESAVING]);
+        }
+
+        // Run another iteration of the work loop, by now memory should 
+        // have gotten to constant.
+        for (int iter = work_size + total_work; iter < (total_work * 2); iter++)
+        {
+            // insert the long blob
+            ins_stmt.setInt(  1, iter);
+            ins_stmt.setBytes(2, long_byteVal);
+            ins_stmt.executeUpdate();
+
+
+            // delete the long blob
+            del_stmt.setInt(1, iter - work_size - 1);
+            del_stmt.executeUpdate();
+
+            // commit the xact, post commit should kick in to reclaim the
+            // blob space sometime after the commit.
+            conn.commit();
+
+            try
+            {
+                Thread.sleep(100);
+            }
+            catch (Exception ex)
+            {
+                // just ignore interupts of sleep.
+            }
+        }
+
+
+        int[] second_sp_info = getSpaceInfo(conn, "APP", "LONGCOL", true);
+
+        int second_total_pages = 
+            sp_info[SPACE_INFO_NUM_ALLOC] + sp_info[SPACE_INFO_NUM_FREE];
+
+        if (total_pages != second_total_pages)
+        {
+            System.out.println(
+                "Test failed, expected constant memory after second run." +
+                "initial total = " + total_pages +
+                "second total = " + second_total_pages);
+        }
+
+        if (verbose)
+        {
+            System.out.println("Space information:");
+
+            System.out.println("isindex = "   + sp_info[SPACE_INFO_IS_INDEX]);
+            System.out.println("num_alloc = " + sp_info[SPACE_INFO_NUM_ALLOC]);
+            System.out.println("num_free = "  + sp_info[SPACE_INFO_NUM_FREE]);
+            System.out.println("page_size = " + sp_info[SPACE_INFO_PAGE_SIZE]);
+            System.out.println(
+                "estimspacesaving = " + sp_info[SPACE_INFO_ESTIMSPACESAVING]);
+        }
+
+        endTest(
+            conn, 
+            "test2:queue of " + work_size + 
+                " rows with blob(" + blob_size + "), total_work = " + 
+                total_work); 
+    }
+
+
+    public void testList(Connection conn)
+        throws SQLException
+    {
+        test1(conn, 250000, 20);
+        test2(conn, 250000, 5, 500);
+    }
+
+    public static void main(String[] argv) 
+        throws Throwable
+    {
+        st_reclaim_longcol test = new st_reclaim_longcol();
+
+        ij.getPropertyArg(argv); 
+        Connection conn = ij.startJBMS();
+
+        try
+        {
+            test.testList(conn);
+        }
+        catch (SQLException sqle)
+        {
+			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
+                System.out, sqle);
+			sqle.printStackTrace(System.out);
+		}
+    }
+}



Mime
View raw message