0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.harness.procedure
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.store;
0023:
0024: import org.apache.derby.iapi.db.OnlineCompress;
0025:
0026: import org.apache.derby.iapi.services.sanity.SanityManager;
0027:
0028: import java.sql.CallableStatement;
0029: import java.sql.Connection;
0030: import java.sql.PreparedStatement;
0031: import java.sql.ResultSet;
0032: import java.sql.SQLException;
0033: import java.sql.Statement;
0034:
0035: import org.apache.derby.tools.ij;
0036:
0037: public class OnlineCompressTest extends BaseTest {
0038: boolean verbose = false;
0039:
0040: public OnlineCompressTest() {
0041: }
0042:
0043: /**
0044: * call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE() system procedure.
0045: * <p>
0046: * Utility test function to call the system procedure.
0047: *
0048: **/
0049: protected void callCompress(Connection conn, String schemaName,
0050: String tableName, boolean purgeRows,
0051: boolean defragmentRows, boolean truncateEnd,
0052: boolean commit_operation) throws SQLException {
0053: CallableStatement cstmt = conn
0054: .prepareCall("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, ?, ?, ?)");
0055: cstmt.setString(1, schemaName);
0056: cstmt.setString(2, tableName);
0057: cstmt.setInt(3, purgeRows ? 1 : 0);
0058: cstmt.setInt(4, defragmentRows ? 1 : 0);
0059: cstmt.setInt(5, truncateEnd ? 1 : 0);
0060:
0061: cstmt.execute();
0062:
0063: if (commit_operation)
0064: conn.commit();
0065: }
0066:
0067: /**
0068: * Create and load a table.
0069: * <p>
0070: * If create_table is set creates a test data table with indexes.
0071: * Loads num_rows into the table. This table defaults to 32k page size.
0072: * This schema fits 25 rows per page
0073: * <p>
0074: *
0075: *
0076: * @param conn Connection to use for sql execution.
0077: * @param create_table If true, create new table - otherwise load into
0078: * existing table.
0079: * @param tblname table to use.
0080: * @param num_rows number of rows to add to the table.
0081: *
0082: * @exception StandardException Standard exception policy.
0083: **/
0084: protected void createAndLoadTable(Connection conn,
0085: boolean create_table, String tblname, int num_rows,
0086: int start_value) throws SQLException {
0087: if (create_table) {
0088: Statement s = conn.createStatement();
0089:
0090: s
0091: .execute("create table "
0092: + tblname
0093: + "(keycol int, indcol1 int, indcol2 int, indcol3 int, data1 varchar(2000), data2 varchar(2000))");
0094: s.close();
0095: }
0096:
0097: PreparedStatement insert_stmt = conn
0098: .prepareStatement("insert into " + tblname
0099: + " values(?, ?, ?, ?, ?, ?)");
0100:
0101: char[] data1_data = new char[500];
0102: char[] data2_data = new char[500];
0103:
0104: for (int i = 0; i < data1_data.length; i++) {
0105: data1_data[i] = 'a';
0106: data2_data[i] = 'b';
0107: }
0108:
0109: String data1_str = new String(data1_data);
0110: String data2_str = new String(data2_data);
0111:
0112: int row_count = 0;
0113: try {
0114: for (int i = start_value; row_count < num_rows; row_count++, i++) {
0115: insert_stmt.setInt(1, i); // keycol
0116: insert_stmt.setInt(2, i * 10); // indcol1
0117: insert_stmt.setInt(3, i * 100); // indcol2
0118: insert_stmt.setInt(4, -i); // indcol3
0119: insert_stmt.setString(5, data1_str); // data1_data
0120: insert_stmt.setString(6, data2_str); // data2_data
0121:
0122: insert_stmt.execute();
0123: }
0124: } catch (SQLException sqle) {
0125: System.out
0126: .println("Exception while trying to insert row number: "
0127: + row_count);
0128: throw sqle;
0129: }
0130:
0131: if (create_table) {
0132: Statement s = conn.createStatement();
0133:
0134: s.execute("create index " + tblname + "_idx_keycol on "
0135: + tblname + "(keycol)");
0136: s.execute("create index " + tblname + "_idx_indcol1 on "
0137: + tblname + "(indcol1)");
0138: s.execute("create index " + tblname + "_idx_indcol2 on "
0139: + tblname + "(indcol2)");
0140: s.execute("create unique index " + tblname
0141: + "_idx_indcol3 on " + tblname + "(indcol3)");
0142: s.close();
0143: }
0144:
0145: conn.commit();
0146: }
0147:
0148: /**
0149: * Create and load a table with long columns and long rows.
0150: * <p>
0151: * If create_table is set creates a test data table with indexes.
0152: * Loads num_rows into the table. This table defaults to 32k page size.
0153: * <p>
0154: * schema of table:
0155: * keycol int,
0156: * longcol1 clob(200k),
0157: * longrow1 varchar(10000),
0158: * longrow2 varchar(10000),
0159: * longrow3 varchar(10000),
0160: * longrow4 varchar(10000),
0161: * indcol1 int,
0162: * indcol2 int,
0163: * indcol3 int,
0164: * data1 varchar(2000),
0165: * data2 varchar(2000)
0166: * longrow5 varchar(10000),
0167: * longrow6 varchar(10000),
0168: * longrow7 varchar(10000),
0169: * longrow8 varchar(10000),
0170: * longcol2 clob(200k),
0171: *
0172: *
0173: * @param conn Connection to use for sql execution.
0174: * @param create_table If true, create new table - otherwise load into
0175: * existing table.
0176: * @param tblname table to use.
0177: * @param num_rows number of rows to add to the table.
0178: *
0179: * @exception StandardException Standard exception policy.
0180: **/
0181: private void createAndLoadLongTable(Connection conn,
0182: boolean create_table, String tblname, int num_rows)
0183: throws SQLException {
0184: if (create_table) {
0185: Statement s = conn.createStatement();
0186:
0187: s
0188: .execute("create table "
0189: + tblname
0190: + " (keycol int, longcol1 clob(200k), longrow1 varchar(10000), longrow2 varchar(10000), longrow3 varchar(10000), longrow4 varchar(10000), indcol1 int, indcol2 int, indcol3 int, data1 varchar(2000), data2 varchar(2000), longrow5 varchar(10000), longrow6 varchar(10000), longrow7 varchar(10000), longrow8 varchar(10000), longcol2 clob(200k))");
0191: s.close();
0192: }
0193:
0194: PreparedStatement insert_stmt = conn
0195: .prepareStatement("insert into "
0196: + tblname
0197: + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
0198:
0199: char[] data1_data = new char[500];
0200: char[] data2_data = new char[500];
0201:
0202: for (int i = 0; i < data1_data.length; i++) {
0203: data1_data[i] = 'a';
0204: data2_data[i] = 'b';
0205: }
0206: String data1_str = new String(data1_data);
0207: String data2_str = new String(data2_data);
0208:
0209: // some data to force row to be bigger than a page, ie. long row
0210: char[] data3_data = new char[10000];
0211: char[] data4_data = new char[10000];
0212:
0213: for (int i = 0; i < data3_data.length; i++) {
0214: data3_data[i] = 'c';
0215: data4_data[i] = 'd';
0216: }
0217: String data3_str = new String(data3_data);
0218: String data4_str = new String(data4_data);
0219:
0220: // some data for the long columns
0221: char[] data5_data = new char[200000];
0222: char[] data6_data = new char[200000];
0223:
0224: for (int i = 0; i < data5_data.length; i++) {
0225: data5_data[i] = 'e';
0226: data6_data[i] = 'f';
0227: }
0228:
0229: String data5_str = new String(data5_data);
0230: String data6_str = new String(data6_data);
0231:
0232: for (int i = 0; i < num_rows; i++) {
0233: insert_stmt.setInt(1, i); // keycol
0234: insert_stmt.setString(2, data5_str); // longcol1
0235: insert_stmt.setString(3, data3_str); // longrow1
0236: insert_stmt.setString(4, data3_str); // longrow2
0237: insert_stmt.setString(5, data3_str); // longrow3
0238: insert_stmt.setString(6, data3_str); // longrow4
0239: insert_stmt.setInt(7, i * 10); // indcol1
0240: insert_stmt.setInt(8, i * 100); // indcol2
0241: insert_stmt.setInt(9, -i); // indcol3
0242: insert_stmt.setString(10, data1_str); // data1_data
0243: insert_stmt.setString(11, data2_str); // data2_data
0244: insert_stmt.setString(12, data4_str); // longrow5
0245: insert_stmt.setString(13, data4_str); // longrow6
0246: insert_stmt.setString(14, data4_str); // longrow7
0247: insert_stmt.setString(15, data4_str); // longrow8
0248: insert_stmt.setString(16, data5_str); // longcol2
0249:
0250: insert_stmt.execute();
0251: }
0252:
0253: if (create_table) {
0254: Statement s = conn.createStatement();
0255:
0256: s.execute("create index " + tblname + "_idx_keycol on "
0257: + tblname + "(keycol)");
0258: s.execute("create index " + tblname + "_idx_indcol1 on "
0259: + tblname + "(indcol1)");
0260: s.execute("create index " + tblname + "_idx_indcol2 on "
0261: + tblname + "(indcol2)");
0262: s.execute("create unique index " + tblname
0263: + "_idx_indcol3 on " + tblname + "(indcol3)");
0264: s.close();
0265: }
0266:
0267: conn.commit();
0268: }
0269:
0270: private void log_wrong_count(String error_msg, String table_name,
0271: int num_rows, int expected_val, int actual_val,
0272: int[] before_info, int[] after_info) {
0273: System.out.println(error_msg);
0274: System.out.println("ERROR: for " + num_rows
0275: + " row test. Expected " + expected_val + ", but got "
0276: + actual_val);
0277: System.out.println("before_info:");
0278: System.out.println(" IS_INDEX ="
0279: + before_info[SPACE_INFO_IS_INDEX]
0280: + "\n NUM_ALLOC ="
0281: + before_info[SPACE_INFO_NUM_ALLOC]
0282: + "\n NUM_FREE ="
0283: + before_info[SPACE_INFO_NUM_FREE]
0284: + "\n NUM_UNFILLED ="
0285: + before_info[SPACE_INFO_NUM_UNFILLED]
0286: + "\n PAGE_SIZE ="
0287: + before_info[SPACE_INFO_PAGE_SIZE]
0288: + "\n ESTIMSPACESAVING ="
0289: + before_info[SPACE_INFO_ESTIMSPACESAVING]);
0290: System.out.println("after_info:");
0291: System.out.println(" IS_INDEX ="
0292: + after_info[SPACE_INFO_IS_INDEX]
0293: + "\n NUM_ALLOC ="
0294: + after_info[SPACE_INFO_NUM_ALLOC]
0295: + "\n NUM_FREE ="
0296: + after_info[SPACE_INFO_NUM_FREE]
0297: + "\n NUM_UNFILLED ="
0298: + after_info[SPACE_INFO_NUM_UNFILLED]
0299: + "\n PAGE_SIZE ="
0300: + after_info[SPACE_INFO_PAGE_SIZE]
0301: + "\n ESTIMSPACESAVING ="
0302: + after_info[SPACE_INFO_ESTIMSPACESAVING]);
0303: }
0304:
0305: private void deleteAllRows(Connection conn, boolean create_table,
0306: boolean long_table, String schemaName, String table_name,
0307: int num_rows) throws SQLException {
0308: testProgress("begin deleteAllRows," + num_rows
0309: + " row test, create = " + create_table + ".");
0310:
0311: if (long_table)
0312: createAndLoadLongTable(conn, create_table, table_name,
0313: num_rows);
0314: else
0315: createAndLoadTable(conn, create_table, table_name,
0316: num_rows, 0);
0317:
0318: if (verbose)
0319: testProgress("Calling compress.");
0320:
0321: // compress with no deletes should not affect size
0322: int[] ret_before = getSpaceInfo(conn, "APP", table_name, true);
0323: callCompress(conn, "APP", table_name, true, true, true, true);
0324: int[] ret_after = getSpaceInfo(conn, "APP", table_name, true);
0325:
0326: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0327: log_wrong_count("Expected no alloc page change.",
0328: table_name, num_rows,
0329: ret_before[SPACE_INFO_NUM_ALLOC],
0330: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0331: ret_after);
0332: }
0333:
0334: if (verbose)
0335: testProgress("calling consistency checker.");
0336:
0337: if (!checkConsistency(conn, schemaName, table_name)) {
0338: logError("conistency check failed.");
0339: }
0340:
0341: testProgress("no delete case complete.");
0342:
0343: // delete all the rows.
0344: ret_before = getSpaceInfo(conn, "APP", table_name, true);
0345: executeQuery(conn, "delete from " + table_name, true);
0346:
0347: if (verbose)
0348: testProgress("deleted all rows, now calling compress.");
0349:
0350: callCompress(conn, "APP", table_name, true, true, true, true);
0351: ret_after = getSpaceInfo(conn, "APP", table_name, true);
0352:
0353: // An empty table has 2 pages, one allocation page and the 1st page
0354: // which will have a system row in it. The space vti only reports
0355: // a count of the user pages so the count is 1.
0356: if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) {
0357: log_wrong_count("Expected all pages to be truncated.",
0358: table_name, num_rows, 1,
0359: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0360: ret_after);
0361: }
0362:
0363: if (verbose)
0364: testProgress("calling consistency checker.");
0365:
0366: if (!checkConsistency(conn, schemaName, table_name)) {
0367: logError("conistency check failed.");
0368: }
0369:
0370: testProgress("delete all rows case succeeded.");
0371:
0372: conn.commit();
0373:
0374: testProgress("end deleteAllRows," + num_rows + " row test.");
0375: }
0376:
0377: private void simpleDeleteAllRows(Connection conn,
0378: boolean create_table, boolean long_table,
0379: String schemaName, String table_name, int num_rows)
0380: throws SQLException {
0381: testProgress("begin simpleDeleteAllRows," + num_rows
0382: + " row test, create = " + create_table + ".");
0383:
0384: if (long_table)
0385: createAndLoadLongTable(conn, create_table, table_name,
0386: num_rows);
0387: else
0388: createAndLoadTable(conn, create_table, table_name,
0389: num_rows, 0);
0390:
0391: if (verbose)
0392: testProgress("Calling compress.");
0393:
0394: // compress with no deletes should not affect size
0395: int[] ret_before = getSpaceInfo(conn, "APP", table_name, true);
0396: callCompress(conn, "APP", table_name, true, true, true, true);
0397: int[] ret_after = getSpaceInfo(conn, "APP", table_name, true);
0398:
0399: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0400: log_wrong_count("Expected no alloc page change.",
0401: table_name, num_rows,
0402: ret_before[SPACE_INFO_NUM_ALLOC],
0403: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0404: ret_after);
0405: }
0406:
0407: testProgress("no delete case complete.");
0408:
0409: // delete all the rows.
0410: ret_before = getSpaceInfo(conn, "APP", table_name, true);
0411: executeQuery(conn, "delete from " + table_name, true);
0412:
0413: if (verbose)
0414: testProgress("deleted all rows, now calling compress.");
0415:
0416: callCompress(conn, "APP", table_name, true, true, true, true);
0417: ret_after = getSpaceInfo(conn, "APP", table_name, true);
0418:
0419: // An empty table has 2 pages, one allocation page and the 1st page
0420: // which will have a system row in it. The space vti only reports
0421: // a count of the user pages so the count is 1.
0422: if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) {
0423: log_wrong_count("Expected all pages to be truncated.",
0424: table_name, num_rows, 1,
0425: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0426: ret_after);
0427: }
0428:
0429: testProgress("delete all rows case succeeded.");
0430:
0431: conn.commit();
0432:
0433: testProgress("end simple deleteAllRows," + num_rows
0434: + " row test.");
0435: }
0436:
0437: /**
0438: * Check/exercise purge pass phase.
0439: * <p>
0440: * Assumes that either test creates the table, or called on an empty
0441: * table with no committed deleted rows or free pages in the middle of
0442: * the table in it.
0443: * <p>
0444: *
0445: * @exception StandardException Standard exception policy.
0446: **/
0447: private void checkPurgePhase(Connection conn, boolean create_table,
0448: boolean long_table, String schemaName, String table_name,
0449: int num_rows) throws SQLException {
0450: testProgress("begin checkPurgePhase" + num_rows
0451: + " row test, create = " + create_table + ".");
0452:
0453: if (long_table)
0454: createAndLoadLongTable(conn, create_table, table_name,
0455: num_rows);
0456: else
0457: createAndLoadTable(conn, create_table, table_name,
0458: num_rows, 0);
0459:
0460: // dump_table(conn, schemaName, table_name, false);
0461:
0462: // delete all the rows, but don't commit the delete
0463: int[] ret_before = getSpaceInfo(conn, "APP", table_name, false);
0464: executeQuery(conn, "delete from " + table_name, false);
0465:
0466: // dump_table(conn, schemaName, table_name, false);
0467:
0468: // Purge pass on non-committed deleted rows should do nothing.
0469:
0470: // System.out.println("lock info before compress call:\n " + get_lock_info(conn, true));
0471:
0472: // Calling compress with just the "purge" pass option, no commit called.
0473: callCompress(conn, "APP", table_name, true, false, false, false);
0474:
0475: int[] ret_after = getSpaceInfo(conn, "APP", table_name, false);
0476:
0477: // expect no change in the number of allocated pages!
0478: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0479: log_wrong_count("Expected no alloc page change(1).",
0480: table_name, num_rows,
0481: ret_before[SPACE_INFO_NUM_ALLOC],
0482: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0483: ret_after);
0484: }
0485:
0486: // expect no change in the number of free pages, if there are there
0487: // is a problem with purge locking recognizing committed deleted rows.
0488: if (ret_after[SPACE_INFO_NUM_FREE] != ret_before[SPACE_INFO_NUM_FREE]) {
0489: log_wrong_count("Expected no free page change(1).",
0490: table_name, num_rows,
0491: ret_before[SPACE_INFO_NUM_FREE],
0492: ret_after[SPACE_INFO_NUM_FREE], ret_before,
0493: ret_after);
0494: }
0495:
0496: // Test that it is ok to call multiple purge passes in single xact.
0497:
0498: // Calling compress with just the "purge" pass option, no commit called.
0499: callCompress(conn, "APP", table_name, true, false, false, false);
0500: ret_after = getSpaceInfo(conn, "APP", table_name, false);
0501:
0502: // expect no change in the number of allocated pages!
0503: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0504: log_wrong_count("Expected no alloc page change(2).",
0505: table_name, num_rows,
0506: ret_before[SPACE_INFO_NUM_ALLOC],
0507: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0508: ret_after);
0509: }
0510:
0511: // expect no change in the number of free pages, if there are there
0512: // is a problem with purge locking recognizing committed deleted rows.
0513: if (ret_after[SPACE_INFO_NUM_FREE] != ret_before[SPACE_INFO_NUM_FREE]) {
0514: log_wrong_count("Expected no free page change(2).",
0515: table_name, num_rows,
0516: ret_before[SPACE_INFO_NUM_FREE],
0517: ret_after[SPACE_INFO_NUM_FREE], ret_before,
0518: ret_after);
0519: }
0520:
0521: // since table was just loaded a defragment pass also should
0522: // not find anything to do.
0523:
0524: // Calling compress with just the "defragment" option, no commit called.
0525:
0526: // currently the defragment option requires a table level lock in
0527: // the nested user transaction, which will conflict and cause a
0528: // lock timeout.
0529:
0530: try {
0531: callCompress(conn, "APP", table_name, false, true, false,
0532: false);
0533:
0534: logError("Defragment pass did not get a lock timeout.");
0535: } catch (SQLException sqle) {
0536: // ignore exception.
0537: }
0538:
0539: ret_after = getSpaceInfo(conn, "APP", table_name, false);
0540:
0541: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0542: log_wrong_count("Expected no alloc page change(3).",
0543: table_name, num_rows,
0544: ret_before[SPACE_INFO_NUM_ALLOC],
0545: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0546: ret_after);
0547: }
0548: if (ret_after[SPACE_INFO_NUM_FREE] != ret_before[SPACE_INFO_NUM_FREE]) {
0549: log_wrong_count("Expected no free page change(3).",
0550: table_name, num_rows,
0551: ret_before[SPACE_INFO_NUM_FREE],
0552: ret_after[SPACE_INFO_NUM_FREE], ret_before,
0553: ret_after);
0554: }
0555:
0556: // make sure table is back to all deleted row state. lock timeout
0557: // will abort transaction.
0558:
0559: // delete all rows and commit.
0560: executeQuery(conn, "delete from " + table_name, true);
0561:
0562: // compress all space and commit.
0563: callCompress(conn, "APP", table_name, true, true, true, true);
0564:
0565: // add back all rows and commit.
0566: if (long_table)
0567: createAndLoadLongTable(conn, create_table, table_name,
0568: num_rows);
0569: else
0570: createAndLoadTable(conn, create_table, table_name,
0571: num_rows, 0);
0572: conn.commit();
0573:
0574: // delete all rows, and NO commit.
0575: executeQuery(conn, "delete from " + table_name, false);
0576:
0577: // Calling compress with just the truncate option, may change allocated
0578: // and free page count as they system may have preallocated pages to
0579: // the end of the file as part of the load. The file can't shrink
0580: // any more than the free page count before the compress.
0581:
0582: // running the truncate pass only. If it compresses anything it is
0583: // just the preallocated pages at end of the file.
0584:
0585: // currently the defragment option requires a table level lock in
0586: // the nested user transaction, which will conflict and cause a
0587: // lock timeout.
0588:
0589: ret_before = getSpaceInfo(conn, "APP", table_name, false);
0590: callCompress(conn, "APP", table_name, false, false, true, false);
0591: ret_after = getSpaceInfo(conn, "APP", table_name, false);
0592:
0593: // expect no change in the number of allocated pages!
0594: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0595: log_wrong_count("Expected no alloc page change(4).",
0596: table_name, num_rows,
0597: ret_before[SPACE_INFO_NUM_ALLOC],
0598: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0599: ret_after);
0600: }
0601:
0602: // The only space that truncate only pass can free are free pages
0603: // located at end of file, so after free space can be anywhere from
0604: // what it was before to 0 pages.
0605: if (ret_after[SPACE_INFO_NUM_FREE] > ret_before[SPACE_INFO_NUM_FREE]) {
0606: log_wrong_count("Expected no increase in free pages(4).",
0607: table_name, num_rows,
0608: ret_before[SPACE_INFO_NUM_FREE],
0609: ret_after[SPACE_INFO_NUM_FREE], ret_before,
0610: ret_after);
0611: }
0612:
0613: // now commit the deletes, run all phases and make sure empty table
0614: // results.
0615: conn.commit();
0616:
0617: // check the table. Note that this will accumulate locks and
0618: // will commit the transaction.
0619: if (!checkConsistency(conn, schemaName, table_name)) {
0620: logError("conistency check failed.");
0621: }
0622:
0623: // test running each phase in order.
0624: callCompress(conn, "APP", table_name, true, false, false, false);
0625: callCompress(conn, "APP", table_name, false, true, false, false);
0626: callCompress(conn, "APP", table_name, false, false, true, false);
0627: ret_after = getSpaceInfo(conn, "APP", table_name, false);
0628:
0629: // An empty table has 2 pages, one allocation page and the 1st page
0630: // which will have a system row in it. The space vti only reports
0631: // a count of the user pages so the count is 1.
0632: if (ret_after[SPACE_INFO_NUM_ALLOC] != 1) {
0633: log_wrong_count("Expected all pages to be truncated.",
0634: table_name, num_rows, 1,
0635: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0636: ret_after);
0637: }
0638: if (ret_after[SPACE_INFO_NUM_FREE] != 0) {
0639: log_wrong_count(
0640: "Expected no free page after all pages truncated.",
0641: table_name, num_rows, 1,
0642: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0643: ret_after);
0644: }
0645:
0646: if (verbose)
0647: testProgress("calling consistency checker.");
0648:
0649: if (!checkConsistency(conn, schemaName, table_name)) {
0650: logError("conistency check failed.");
0651: }
0652:
0653: testProgress("end checkPurgePhase" + num_rows + " row test.");
0654: }
0655:
0656: /**
0657: * Test 1 - various # page tests, regular row/columns
0658: * <p>
0659: * perform a number of insert/delete/compress operations on a variety
0660: * of sized tables, use space allocation information to verify that
0661: * compression is happening and use consistency checker to verify that
0662: * tables and indexes are all valid following the operations.
0663: * <p>
0664: * loop through testing interesting row count cases. The cases are
0665: * 0 rows - basic edge case, 2 page table: 1 alloc, 1 user page
0666: * 1 row - another edge case, 2 page table: 1 alloc, 1 user page
0667: * 50 rows - 3 page table case: 1 alloc, 1 user page, 1 user page freed
0668: * 4000 rows - reasonable number of pages to test out, still 1 alloc page
0669: *
0670: * note that row numbers greater than 4000 may lead to lock escalation
0671: * issues, if queries like "delete from x" are used to delete all the
0672: * rows.
0673: *
0674: * <p>
0675: *
0676: **/
0677: private void test1(Connection conn, String test_name,
0678: String table_name) throws SQLException {
0679: beginTest(conn, test_name);
0680:
0681: int[] test_cases = { 0, 1, 50, 4000 };
0682:
0683: for (int i = 0; i < test_cases.length; i++) {
0684: // first create new table and run the tests.
0685: deleteAllRows(conn, true, false, "APP", table_name,
0686: test_cases[i]);
0687:
0688: // now rerun tests on existing table, which had all rows deleted
0689: // and truncated.
0690: deleteAllRows(conn, false, false, "APP", table_name,
0691: test_cases[i]);
0692:
0693: checkPurgePhase(conn, false, false, "APP", table_name,
0694: test_cases[i]);
0695:
0696: executeQuery(conn, "drop table " + table_name, true);
0697: }
0698:
0699: endTest(conn, test_name);
0700: }
0701:
0702: /**
0703: * Test 2 - check repeated delete tests.
0704: * <p>
0705: * There was a timing error where test1 would usually pass, but
0706: * repeated execution of this test found a timing problem with
0707: * allocation using an "unallocated" page and getting an I/O error.
0708: *
0709: **/
0710: private void test2(Connection conn, String test_name,
0711: String table_name) throws SQLException {
0712: beginTest(conn, test_name);
0713:
0714: int[] test_cases = { 4000 };
0715:
0716: for (int i = 0; i < test_cases.length; i++) {
0717: // first create new table and run the tests.
0718: simpleDeleteAllRows(conn, true, false, "APP", table_name,
0719: test_cases[i]);
0720:
0721: for (int j = 0; j < 100; j++) {
0722:
0723: // now rerun tests on existing table, which had all rows deleted
0724: // and truncated.
0725: deleteAllRows(conn, false, false, "APP", table_name,
0726: test_cases[i]);
0727: }
0728:
0729: executeQuery(conn, "drop table " + table_name, true);
0730: }
0731:
0732: endTest(conn, test_name);
0733: }
0734:
0735: /**
0736: * Test 3 - various # page tests, long row and long columns
0737: * <p>
0738: * perform a number of insert/delete/compress operations on a variety
0739: * of sized tables, use space allocation information to verify that
0740: * compression is happening and use consistency checker to verify that
0741: * tables and indexes are all valid following the operations.
0742: * <p>
0743: * loop through testing interesting row count cases. The cases are
0744: * 0 rows - basic edge case
0745: * 1 row - another edge case
0746: * 100 rows - ~50 meg table
0747: * 4000 rows - ~2 gig table
0748: *
0749: * note that row numbers greater than 4000 may lead to lock escalation
0750: * issues, if queries like "delete from x" are used to delete all the
0751: * rows.
0752: *
0753: * <p>
0754: *
0755: **/
0756: private void test3(Connection conn, String test_name,
0757: String table_name) throws SQLException {
0758: beginTest(conn, test_name);
0759:
0760: // note that 500 rows took 30 minutes on a ~1.5 ghz laptop
0761: int[] test_cases = { 1, 2, 50 };
0762:
0763: for (int i = 0; i < test_cases.length; i++) {
0764: // first create new table and run the tests.
0765: deleteAllRows(conn, true, true, "APP", table_name,
0766: test_cases[i]);
0767:
0768: // now rerun tests on existing table, which had all rows deleted
0769: // and truncated.
0770: deleteAllRows(conn, false, true, "APP", table_name,
0771: test_cases[i]);
0772:
0773: checkPurgePhase(conn, false, true, "APP", table_name,
0774: test_cases[i]);
0775:
0776: executeQuery(conn, "drop table " + table_name, true);
0777: }
0778:
0779: endTest(conn, test_name);
0780: }
0781:
0782: /**
0783: * Test 4 - check repeated delete tests.
0784: * <p>
0785: * There was a timing error where test1 would usually pass, but
0786: * repeated execution of this test found a timing problem with
0787: * allocation using an "unallocated" page and getting an I/O error.
0788: *
0789: **/
0790: private void test4(Connection conn, String test_name,
0791: String table_name) throws SQLException {
0792: beginTest(conn, test_name);
0793:
0794: int[] test_cases = { 4000 };
0795:
0796: for (int i = 0; i < test_cases.length; i++) {
0797:
0798: for (int j = 0; j < 100; j++) {
0799: // first create new table and run the tests.
0800: simpleDeleteAllRows(conn, true, false, "APP",
0801: table_name, test_cases[i]);
0802:
0803: // now rerun tests on existing table, which had all rows deleted
0804: // and truncated.
0805: deleteAllRows(conn, false, false, "APP", table_name,
0806: test_cases[i]);
0807:
0808: executeQuery(conn, "drop table " + table_name, true);
0809: }
0810:
0811: }
0812:
0813: endTest(conn, test_name);
0814: }
0815:
0816: /**
0817: * Create and load table for test5.
0818: * <p>
0819: * schema of table:
0820: * keycol int,
0821: * onehalf int,
0822: * onethird int,
0823: * c varchar(300)
0824: *
0825: * @param conn Connection to use for sql execution.
0826: * @param create_table If true, create new table - otherwise load into
0827: * existing table.
0828: * @param tblname table to use.
0829: * @param num_rows number of rows to add to the table.
0830: *
0831: * @exception StandardException Standard exception policy.
0832: **/
0833: private void test5_load(Connection conn, String schemaName,
0834: String table_name, int num_rows) throws SQLException {
0835: Statement s = conn.createStatement();
0836:
0837: s
0838: .execute("create table "
0839: + table_name
0840: + " (keycol integer primary key, onehalf integer, onethird integer, c varchar(300))");
0841: s.close();
0842:
0843: PreparedStatement insert_stmt = conn
0844: .prepareStatement("insert into " + table_name
0845: + " values(?, ?, ?, ?)");
0846:
0847: char[] data1_data = new char[200];
0848:
0849: for (int i = 0; i < data1_data.length; i++) {
0850: data1_data[i] = 'b';
0851: }
0852: String data1_str = new String(data1_data);
0853:
0854: for (int i = 0; i < num_rows; i++) {
0855: insert_stmt.setInt(1, i); // keycol
0856: insert_stmt.setInt(2, i % 2); // onehalf: 0 or 1
0857: insert_stmt.setInt(3, i % 3); // onethird: 0, 1, or 3
0858: insert_stmt.setString(4, data1_str); // c
0859: insert_stmt.execute();
0860: }
0861:
0862: conn.commit();
0863: }
0864:
0865: /**
0866: * Execute test5, simple defragement test.
0867: * <p>
0868: * o delete every other row, defragment
0869: * o delete every third row, defragment
0870: * o delete last 1000 rows, defragment
0871: * o delete first 512 rows, defragment.
0872: * <p>
0873: * run test with at least 2000 rows.
0874: **/
0875: private void test5_run(Connection conn, String schemaName,
0876: String table_name, int num_rows) throws SQLException {
0877: testProgress("begin test5: " + num_rows + " row test.");
0878:
0879: if (verbose)
0880: testProgress("Calling compress.");
0881:
0882: // compress with no deletes should not affect size
0883: int[] ret_before = getSpaceInfo(conn, "APP", table_name, true);
0884: callCompress(conn, "APP", table_name, true, true, true, true);
0885: int[] ret_after = getSpaceInfo(conn, "APP", table_name, true);
0886:
0887: if (ret_after[SPACE_INFO_NUM_ALLOC] != ret_before[SPACE_INFO_NUM_ALLOC]) {
0888: log_wrong_count("Expected no alloc page change.",
0889: table_name, num_rows,
0890: ret_before[SPACE_INFO_NUM_ALLOC],
0891: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0892: ret_after);
0893: }
0894:
0895: if (verbose)
0896: testProgress("calling consistency checker.");
0897:
0898: if (!checkConsistency(conn, schemaName, table_name)) {
0899: logError("conistency check failed.");
0900: }
0901:
0902: // DELETE EVERY OTHER ROW, COMPRESS, CHECK
0903: //
0904: //
0905:
0906: // delete all the rows every other row.
0907: ret_before = getSpaceInfo(conn, "APP", table_name, true);
0908: executeQuery(conn, "delete from " + table_name
0909: + " where onehalf = 0", true);
0910:
0911: if (verbose)
0912: testProgress("deleted every other row, now calling compress.");
0913:
0914: callCompress(conn, "APP", table_name, true, true, true, true);
0915: ret_after = getSpaceInfo(conn, "APP", table_name, true);
0916:
0917: if (total_pages(ret_after) != total_pages(ret_before)) {
0918: // currently deleting every other row does not add free or unfilled
0919: // pages to the container so defragment has nowhere to put the rows.
0920:
0921: log_wrong_count("Expected no truncation.", table_name,
0922: num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
0923: ret_before, ret_after);
0924: }
0925:
0926: if (verbose)
0927: testProgress("calling consistency checker.");
0928:
0929: if (!checkConsistency(conn, schemaName, table_name)) {
0930: logError("conistency check failed.");
0931: }
0932:
0933: // DELETE EVERY THIRD ROW in original dataset, COMPRESS, CHECK
0934: //
0935: //
0936:
0937: // delete every third row
0938: ret_before = getSpaceInfo(conn, "APP", table_name, true);
0939: executeQuery(conn, "delete from " + table_name
0940: + " where onethird = 0", true);
0941:
0942: if (verbose)
0943: testProgress("deleted every third row, now calling compress.");
0944:
0945: callCompress(conn, "APP", table_name, true, true, true, true);
0946: ret_after = getSpaceInfo(conn, "APP", table_name, true);
0947:
0948: if (total_pages(ret_after) != total_pages(ret_before)) {
0949: // currently deleting every third row does not create any free
0950: // or unfilled pages so defragment has no place to move rows.
0951: log_wrong_count("Expected no truncation.", table_name,
0952: num_rows, 1, ret_after[SPACE_INFO_NUM_ALLOC],
0953: ret_before, ret_after);
0954: }
0955:
0956: if (verbose)
0957: testProgress("calling consistency checker.");
0958:
0959: if (!checkConsistency(conn, schemaName, table_name)) {
0960: logError("conistency check failed.");
0961: }
0962:
0963: // DELETE top "half" of rows in original dataset, COMPRESS, CHECK
0964: //
0965: //
0966:
0967: // delete top "half" of the rows in the original dataset.
0968: ret_before = getSpaceInfo(conn, "APP", table_name, true);
0969: executeQuery(conn, "delete from " + table_name
0970: + " where keycol > " + (num_rows / 2), true);
0971:
0972: if (verbose)
0973: testProgress("deleted top half of the rows, now calling compress.");
0974:
0975: callCompress(conn, "APP", table_name, true, true, true, true);
0976: ret_after = getSpaceInfo(conn, "APP", table_name, true);
0977:
0978: // compress should be able to clean up about 1/2 of the pages.
0979: if (verbose) {
0980: log_wrong_count("deleted top half keys, spaceinfo:",
0981: table_name, num_rows,
0982: ((total_pages(ret_before) / 2) + 2),
0983: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0984: ret_after);
0985: }
0986:
0987: if (total_pages(ret_after) > ((total_pages(ret_before) / 2) + 2)) {
0988: log_wrong_count("Expected at least "
0989: + (ret_before[SPACE_INFO_NUM_ALLOC] / 2 + 2)
0990: + " pages to be truncated.", table_name, num_rows,
0991: 1, ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
0992: ret_after);
0993: }
0994:
0995: if (verbose)
0996: testProgress("calling consistency checker.");
0997:
0998: if (!checkConsistency(conn, schemaName, table_name)) {
0999: logError("conistency check failed.");
1000: }
1001:
1002: // DELETE 1st 500 rows in original dataset, COMPRESS, CHECK
1003: //
1004: //
1005:
1006: // delete keys less than 500
1007: ret_before = getSpaceInfo(conn, "APP", table_name, true);
1008: executeQuery(conn, "delete from " + table_name
1009: + " where keycol < 500 ", true);
1010:
1011: if (verbose)
1012: testProgress("deleted keys < 500, now calling compress.");
1013:
1014: callCompress(conn, "APP", table_name, true, true, true, true);
1015: ret_after = getSpaceInfo(conn, "APP", table_name, true);
1016:
1017: if (verbose) {
1018: log_wrong_count("deleted bottom 500 keys, spaceinfo:",
1019: table_name, num_rows,
1020: (total_pages(ret_before) - 33),
1021: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
1022: ret_after);
1023: }
1024:
1025: // The bottom 500 keys, assuming 4k pages, takes about 33 pages
1026: if (total_pages(ret_after) > (total_pages(ret_before) - 33)) {
1027: log_wrong_count("Expected at least 33 pages reclaimed.",
1028: table_name, num_rows, 1,
1029: ret_after[SPACE_INFO_NUM_ALLOC], ret_before,
1030: ret_after);
1031: }
1032:
1033: if (verbose)
1034: testProgress("calling consistency checker.");
1035:
1036: if (!checkConsistency(conn, schemaName, table_name)) {
1037: logError("conistency check failed.");
1038: }
1039:
1040: conn.commit();
1041:
1042: testProgress("end test5: " + num_rows + " row test.");
1043: }
1044:
1045: /**
1046: * Cleanup after test5_run
1047: **/
1048: private void test5_cleanup(Connection conn, String schemaName,
1049: String table_name, int num_rows) throws SQLException {
1050: executeQuery(conn, "drop table " + table_name, true);
1051: }
1052:
1053: /**
1054: * Test 5 - simple defragment test.
1055: * <p>
1056: * Create dataset and then:
1057: * o delete every other row, defragment
1058: * o delete every third row, defragment
1059: * o delete last 1000 rows, defragment
1060: * o delete first 512 rows, defragment.
1061: * <p>
1062: * run test with at least 2000 rows.
1063: *
1064: **/
1065: private void test5(Connection conn, String test_name,
1066: String table_name) throws SQLException {
1067: beginTest(conn, test_name);
1068:
1069: int[] test_cases = { 2000, 10000 };
1070:
1071: for (int i = 0; i < test_cases.length; i++) {
1072: test5_load(conn, "APP", table_name, test_cases[i]);
1073: test5_run(conn, "APP", table_name, test_cases[i]);
1074: test5_cleanup(conn, "APP", table_name, test_cases[i]);
1075: }
1076:
1077: endTest(conn, test_name);
1078: }
1079:
1080: public void testList(Connection conn) throws SQLException {
1081: test1(conn, "test1", "TEST1");
1082: // test2(conn, "test2", "TEST2");
1083: test3(conn, "test3", "TEST3");
1084: // test4(conn, "test4", "TEST4");
1085: test5(conn, "test5", "TEST5");
1086: }
1087:
1088: public static void main(String[] argv) throws Throwable {
1089: OnlineCompressTest test = new OnlineCompressTest();
1090:
1091: ij.getPropertyArg(argv);
1092: Connection conn = ij.startJBMS();
1093: conn.setAutoCommit(false);
1094:
1095: try {
1096: test.testList(conn);
1097: } catch (SQLException sqle) {
1098: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
1099: System.out, sqle);
1100: sqle.printStackTrace(System.out);
1101: }
1102: }
1103: }
|