You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2006/04/08 00:30:57 UTC
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/
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)