Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 61906 invoked from network); 6 Mar 2006 20:51:23 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 6 Mar 2006 20:51:23 -0000 Received: (qmail 61504 invoked by uid 500); 6 Mar 2006 20:51:22 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 61433 invoked by uid 500); 6 Mar 2006 20:51:21 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 61422 invoked by uid 99); 6 Mar 2006 20:51:21 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 06 Mar 2006 12:51:21 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 06 Mar 2006 12:51:20 -0800 Received: (qmail 61807 invoked by uid 65534); 6 Mar 2006 20:50:59 -0000 Message-ID: <20060306205059.61641.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: mikem@apache.org X-Mailer: svnmailer-1.0.7 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N 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. *

@@ -548,10 +544,6 @@ int getNextSlotNumber(RecordHandle handle) throws StandardException; - /** - Insert a record at the specified slot. -

- */ /** * Insert a record at the specified slot. *

@@ -618,10 +610,6 @@ int overflowThreshold) throws StandardException; - /** - Values for insertFlag: - - */ /** * Values for insertFlag. *

@@ -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. + *

+ * 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. + *

+ * 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. + *

+ * 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. + *

+ * 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. + *

+ * 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. + *

+ * 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. + *

+ * Takes care of dropping the table if it exists already. + *

+ * + * @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. + *

+ * 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]; + *

+ * + * @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. - *

- * 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]; - *

- * - * @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. *

* 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. + *

+ * 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. + *

+ * 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); + } + } +}