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 ka...@apache.org on 2008/05/21 09:15:18 UTC
svn commit: r658572 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting:
functionTests/util/ perf/clients/
Author: kahatlen
Date: Wed May 21 00:15:18 2008
New Revision: 658572
URL: http://svn.apache.org/viewvc?rev=658572&view=rev
Log:
DERBY-3619: Implement more load types for org.apache.derbyTesting.perf.clients.Runner
Added BLOB/CLOB load for the single-record update client.
Added possibility to access rows by a non-indexed column or a column
with a non-unique index instead of the primary key column (in the
single-record update client and the single-record select client).
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/UniqueRandomSequence.java (with props)
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/Runner.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordFiller.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordSelectClient.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordUpdateClient.java
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/UniqueRandomSequence.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/UniqueRandomSequence.java?rev=658572&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/UniqueRandomSequence.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/UniqueRandomSequence.java Wed May 21 00:15:18 2008
@@ -0,0 +1,95 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.util.UniqueRandomSequence
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You 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.util;
+
+import java.util.Random;
+
+/**
+ * Utility class that generates a sequence of unique numbers in random order.
+ * Example of how to use the generator to print all the numbers from 0 to 9
+ * in random order:
+ * <pre>
+ * UniqueRandomSequence sequence = new UniqueRandomSequence(10);
+ * while (sequence.hasMore()) {
+ * System.out.println(sequence.nextValue());
+ * }
+ * </pre>
+ */
+public class UniqueRandomSequence {
+
+ /** Array of the numbers to be used in the sequence. */
+ private final int[] numbers;
+
+ /** Random number generator. */
+ private final Random random = new Random();
+
+ /** How many numbers are left in the sequence. */
+ private int numbersLeft;
+
+ /**
+ * Generate a random sequence with all the numbers from 0 up to
+ * {@code length-1}.
+ * @param length the length of the sequence
+ */
+ public UniqueRandomSequence(int length) {
+ this(0, length, 1);
+ }
+
+ /**
+ * Generate a random sequence in the specified range.
+ * @param start the smallest number in the sequence
+ * @param length the size of the sequence
+ * @param step the difference between adjacent numbers if the sequence is
+ * sorted
+ */
+ public UniqueRandomSequence(int start, int length, int step) {
+ if (step <= 0) {
+ throw new IllegalArgumentException("step must be greater than 0");
+ }
+ numbers = new int[length];
+ for (int i = 0, val = start; i < length; i++, val += step) {
+ numbers[i] = val;
+ }
+ numbersLeft = length;
+ }
+
+ /**
+ * Check whether there are more numbers in the sequence.
+ * @return {@code true} if there are more numbers in the sequence,
+ * {@code false} otherwise
+ */
+ public boolean hasMore() {
+ return numbersLeft > 0;
+ }
+
+ /**
+ * Fetch the next number from the sequence.
+ * @return a unique value in this generator's range
+ */
+ public int nextValue() {
+ int pos = random.nextInt(numbersLeft);
+ int value = numbers[pos];
+ numbers[pos] = numbers[numbersLeft - 1];
+ numbersLeft--;
+ return value;
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/util/UniqueRandomSequence.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/Runner.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/Runner.java?rev=658572&r1=658571&r2=658572&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/Runner.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/Runner.java Wed May 21 00:15:18 2008
@@ -26,6 +26,8 @@
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
+import java.util.Arrays;
+import java.util.HashSet;
/**
* Class used for running a performance test from the command line. To learn
@@ -56,6 +58,8 @@
private static boolean init = false;
/** The name of the type of load to use in the test. */
private static String load; // required argument
+ /** Set of load-specific options. */
+ private final static HashSet loadOpts = new HashSet();
/** The name of the load generator to use in the test. */
private static String generator = "b2b";
/** The number of client threads to use in the test. */
@@ -137,6 +141,8 @@
init = true;
} else if (args[i].equals("-load")) {
load = args[++i];
+ } else if (args[i].equals("-load_opts")) {
+ loadOpts.addAll(Arrays.asList(args[++i].split(",")));
} else if (args[i].equals("-gen")) {
generator = args[++i];
} else if (args[i].equals("-threads")) {
@@ -167,10 +173,16 @@
" -init: initialize database (otherwise, reuse database)\n" +
" -load: type of load, required argument, valid types:\n" +
" * sr_select - single-record (primary key) select from table with\n" +
-" 100 000 rows. If _blob or _clob is appended to the\n" +
-" name, BLOB/CLOB is used for the data columns.\n" +
+" 100 000 rows. It accepts the following load-specific\n" +
+" options (see also -load_opts):\n" +
+" - blob or clob: use BLOB or CLOB data instead of VARCHAR\n" +
+" - secondary: select on a column with a secondary (non-unique)\n" +
+" index instead of the primary key\n" +
+" - nonIndexed: select on a non-indexed column instead of the\n" +
+" primary key\n" +
" * sr_update - single-record (primary key) update on table with\n" +
-" 100 000 rows\n" +
+" 100 000 rows. It accepts the same load-specific\n" +
+" options as sr_select.\n" +
" * sr_select_big - single-record (primary key) select from table with\n" +
" 100 000 000 rows\n" +
" * sr_update_big - single-record (primary key) update on table with\n" +
@@ -180,6 +192,7 @@
" * sr_update_multi - single-record update on a random table\n" +
" (32 tables with a single row each)\n" +
" * index_join - join of two tables (using indexed columns)\n" +
+" -load_opts: comma-separated list of load-specific options\n" +
" -gen: load generator, default: b2b, valid types:\n" +
" * b2b - clients perform operations back-to-back\n" +
" * poisson - load is Poisson distributed\n" +
@@ -199,6 +212,28 @@
}
/**
+ * Get the data type to be used for sr_select and sr_update types of load.
+ *
+ * @return one of the {@code java.sql.Types} data type constants
+ */
+ private static int getTextType() {
+ boolean blob = loadOpts.contains("blob");
+ boolean clob = loadOpts.contains("clob");
+ if (blob && clob) {
+ System.err.println("Cannot specify both 'blob' and 'clob'");
+ printUsage(System.err);
+ System.exit(1);
+ }
+ if (blob) {
+ return Types.BLOB;
+ }
+ if (clob) {
+ return Types.CLOB;
+ }
+ return Types.VARCHAR;
+ }
+
+ /**
* Find the {@code DBFiller} instance for the load specified on the
* command line.
*
@@ -206,17 +241,15 @@
*/
private static DBFiller getDBFiller() {
if (load.equals("sr_select") || load.equals("sr_update")) {
- return new SingleRecordFiller(100000, 1, Types.VARCHAR);
- } else if (load.equals("sr_select_blob")) {
- return new SingleRecordFiller(100000, 1, Types.BLOB);
- } else if (load.equals("sr_select_clob")) {
- return new SingleRecordFiller(100000, 1, Types.CLOB);
+ return new SingleRecordFiller(100000, 1, getTextType(),
+ loadOpts.contains("secondary"),
+ loadOpts.contains("nonIndexed"));
} else if (load.equals("sr_select_big") ||
load.equals("sr_update_big")) {
- return new SingleRecordFiller(100000000, 1, Types.VARCHAR);
+ return new SingleRecordFiller(100000000, 1);
} else if (load.equals("sr_select_multi") ||
load.equals("sr_update_multi")) {
- return new SingleRecordFiller(1, 32, Types.VARCHAR);
+ return new SingleRecordFiller(1, 32);
} else if (load.equals("index_join")) {
return new WisconsinFiller();
}
@@ -233,19 +266,19 @@
*/
private static Client newClient() {
if (load.equals("sr_select")) {
- return new SingleRecordSelectClient(100000, 1, Types.VARCHAR);
- } else if (load.equals("sr_select_blob")) {
- return new SingleRecordSelectClient(100000, 1, Types.BLOB);
- } else if (load.equals("sr_select_clob")) {
- return new SingleRecordSelectClient(100000, 1, Types.CLOB);
+ return new SingleRecordSelectClient(100000, 1, getTextType(),
+ loadOpts.contains("secondary"),
+ loadOpts.contains("nonIndexed"));
} else if (load.equals("sr_update")) {
- return new SingleRecordUpdateClient(100000, 1);
+ return new SingleRecordUpdateClient(100000, 1, getTextType(),
+ loadOpts.contains("secondary"),
+ loadOpts.contains("nonIndexed"));
} else if (load.equals("sr_select_big")) {
- return new SingleRecordSelectClient(100000000, 1, Types.VARCHAR);
+ return new SingleRecordSelectClient(100000000, 1);
} else if (load.equals("sr_update_big")) {
return new SingleRecordUpdateClient(100000000, 1);
} else if (load.equals("sr_select_multi")) {
- return new SingleRecordSelectClient(1, 32, Types.VARCHAR);
+ return new SingleRecordSelectClient(1, 32);
} else if (load.equals("sr_update_multi")) {
return new SingleRecordUpdateClient(1, 32);
} else if (load.equals("index_join")) {
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordFiller.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordFiller.java?rev=658572&r1=658571&r2=658572&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordFiller.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordFiller.java Wed May 21 00:15:18 2008
@@ -29,6 +29,7 @@
import java.sql.Statement;
import java.sql.Types;
import java.util.Random;
+import org.apache.derbyTesting.functionTests.util.UniqueRandomSequence;
/**
* Class which generates and populates tables that can be used by
@@ -38,15 +39,45 @@
*/
public class SingleRecordFiller implements DBFiller {
+ /** The number of tables to distribute the load over. */
private final int numberOfTables;
+ /** The number of rows in each table. */
private final int tableSize;
+ /**
+ * The data type of the text column (a constant from
+ * {@code java.sql.Types}).
+ */
private final int dataType;
+ /** SQL name of the data type specified by {@code dataType}. */
private final String dataTypeString;
+ /**
+ * Whether or not the table includes an integer column with unique values
+ * in random order. A UNIQUE index will be created for the column.
+ */
+ private final boolean withSecIndexColumn;
+ /**
+ * Whether or not the table includes an integer column with unique values
+ * in random order not backed by an index.
+ */
+ private final boolean withNonIndexedColumn;
static final int TEXT_SIZE = 100;
/**
* Generate a filler that creates the specified number of tables, each of
+ * which contains the specified number of records. When this constructor
+ * is used, the table only contains two columns: a primary key column (INT)
+ * and a text column (VARCHAR(100)).
+ *
+ * @param records the number of records in each table
+ * @param tables the number of tables to create
+ */
+ public SingleRecordFiller(int records, int tables) {
+ this(records, tables, Types.VARCHAR, false, false);
+ }
+
+ /**
+ * Generate a filler that creates the specified number of tables, each of
* which contains the specified number of records.
*
* @param records the number of records in each table
@@ -55,7 +86,8 @@
* {@code java.sql.Types.VARCHAR}, {@code java.sql.Types.BLOB} and
* {@code java.sql.Types.CLOB}.
*/
- public SingleRecordFiller(int records, int tables, int type) {
+ public SingleRecordFiller(int records, int tables, int type,
+ boolean withSecIndex, boolean withNonIndexed) {
tableSize = records;
numberOfTables = tables;
dataType = type;
@@ -72,33 +104,67 @@
default:
throw new IllegalArgumentException("type = " + type);
}
+ withSecIndexColumn = withSecIndex;
+ withNonIndexedColumn = withNonIndexed;
}
public void fill(Connection c) throws SQLException {
c.setAutoCommit(false);
Statement s = c.createStatement();
for (int table = 0; table < numberOfTables; table++) {
- String tableName = getTableName(tableSize, table, dataType);
+ String tableName = getTableName(tableSize, table, dataType,
+ withSecIndexColumn, withNonIndexedColumn);
WisconsinFiller.dropTable(c, tableName);
s.executeUpdate(
"CREATE TABLE " + tableName + "(ID INT PRIMARY KEY, " +
+ (withSecIndexColumn ? "SEC INT, " : "") +
+ (withNonIndexedColumn ? "NI INT, " : "") +
"TEXT " + dataTypeString + "(" + TEXT_SIZE + "))");
+ String extraCols = "";
+ String extraParams = "";
+ if (withSecIndexColumn) {
+ extraCols += ", SEC";
+ extraParams += ", ?";
+ }
+ if (withNonIndexedColumn) {
+ extraCols += ", NI";
+ extraParams += ", ?";
+ }
+
PreparedStatement ps =
c.prepareStatement("INSERT INTO " + tableName +
- "(ID, TEXT) VALUES (?, ?)");
+ "(ID, TEXT" + extraCols +
+ ") VALUES (?, ?" + extraParams + ")");
+
+ UniqueRandomSequence secIdSequence = null;
+ if (withSecIndexColumn) {
+ secIdSequence = new UniqueRandomSequence(tableSize);
+ }
+
+ UniqueRandomSequence nonIndexedSequence = null;
+ if (withNonIndexedColumn) {
+ nonIndexedSequence = new UniqueRandomSequence(tableSize);
+ }
for (int i = 0; i < tableSize; i++) {
- ps.setInt(1, i);
+ int col = 1;
+ ps.setInt(col++, i);
if (dataType == Types.VARCHAR) {
- ps.setString(2, randomString(i));
+ ps.setString(col++, randomString(i));
} else if (dataType == Types.CLOB) {
StringReader reader = new StringReader(randomString(i));
- ps.setCharacterStream(2, reader, TEXT_SIZE);
+ ps.setCharacterStream(col++, reader, TEXT_SIZE);
} else if (dataType == Types.BLOB) {
ByteArrayInputStream stream =
new ByteArrayInputStream(randomBytes(i));
- ps.setBinaryStream(2, stream, TEXT_SIZE);
+ ps.setBinaryStream(col++, stream, TEXT_SIZE);
+ }
+ if (withSecIndexColumn) {
+ ps.setInt(col++, secIdSequence.nextValue());
+ }
+ if (withNonIndexedColumn) {
+ ps.setInt(col++, nonIndexedSequence.nextValue());
}
ps.executeUpdate();
if ((i % 1000) == 0) {
@@ -106,6 +172,12 @@
}
}
+ if (withSecIndexColumn) {
+ s.executeUpdate(
+ "CREATE INDEX " + tableName + "_SECONDARY_INDEX ON " +
+ tableName + "(SEC)");
+ }
+
ps.close();
c.commit();
}
@@ -162,17 +234,24 @@
* data type of the text column
* @return the name of the table specified by the arguments
*/
- static String getTableName(int records, int table, int dataType) {
- String suffix;
+ static String getTableName(int records, int table, int dataType,
+ boolean withSecIndex, boolean withNonIndexed) {
+ String name = "SINGLE_RECORD_" + records + "_" + table;
+ if (withSecIndex) {
+ name += "_SECIDX";
+ }
+ if (withNonIndexed) {
+ name += "_NONIDX";
+ }
if (dataType == Types.VARCHAR) {
- suffix = "";
+ name += "_VARCHAR";
} else if (dataType == Types.BLOB) {
- suffix = "_BLOB";
+ name += "_BLOB";
} else if (dataType == Types.CLOB) {
- suffix = "_CLOB";
+ name += "_CLOB";
} else {
throw new IllegalArgumentException("dataType = " + dataType);
}
- return "SINGLE_RECORD_" + records + "_" + table + suffix;
+ return name;
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordSelectClient.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordSelectClient.java?rev=658572&r1=658571&r2=658572&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordSelectClient.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordSelectClient.java Wed May 21 00:15:18 2008
@@ -42,6 +42,19 @@
private final Random r;
private final int tableSize;
private final int dataType;
+ private final boolean secondaryIndex;
+ private final boolean noIndex;
+
+ /**
+ * Construct a new single-record select client which fetches VARCHAR data
+ * by primary key.
+ *
+ * @param records the number of records in each table in the test
+ * @param tables the number of tables in the test
+ */
+ public SingleRecordSelectClient(int records, int tables) {
+ this(records, tables, Types.VARCHAR, false, false);
+ }
/**
* Construct a new single-record select client.
@@ -51,19 +64,39 @@
* @param type the data type of the text column
* ({@code java.sql.Types.VARCHAR}, {@code java.sql.Types.BLOB} or
* {@code java.sql.Types.CLOB})
+ * @param secIndex if {@code true}, select by secondary index column
+ * instead of primary key column
+ * @param nonIndexed if {@code true}, select by non-indexed column
+ * instead of primary key column
*/
- public SingleRecordSelectClient(int records, int tables, int type) {
+ public SingleRecordSelectClient(int records, int tables, int type,
+ boolean secIndex, boolean nonIndexed) {
tableSize = records;
r = new Random();
pss = new PreparedStatement[tables];
dataType = type;
+ if (secIndex && nonIndexed) {
+ throw new IllegalArgumentException(
+ "Cannot select on both secondary index and non-index column");
+ }
+ secondaryIndex = secIndex;
+ noIndex = nonIndexed;
}
public void init(Connection c) throws SQLException {
for (int i = 0; i < pss.length; i++) {
String tableName =
- SingleRecordFiller.getTableName(tableSize, i, dataType);
- String sql = "SELECT * FROM " + tableName + " WHERE ID = ?";
+ SingleRecordFiller.getTableName(tableSize, i, dataType,
+ secondaryIndex, noIndex);
+ String column = "ID";
+ if (secondaryIndex) {
+ column = "SEC";
+ } else if (noIndex) {
+ column = "NI";
+ }
+ String sql =
+ "SELECT ID, TEXT FROM " + tableName +
+ " WHERE " + column + " = ?";
pss[i] = c.prepareStatement(sql);
}
c.setAutoCommit(false);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordUpdateClient.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordUpdateClient.java?rev=658572&r1=658571&r2=658572&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordUpdateClient.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/perf/clients/SingleRecordUpdateClient.java Wed May 21 00:15:18 2008
@@ -21,6 +21,8 @@
package org.apache.derbyTesting.perf.clients;
+import java.io.ByteArrayInputStream;
+import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
@@ -40,6 +42,9 @@
private final PreparedStatement[] pss;
private final Random r;
private final int tableSize;
+ private final int dataType;
+ private final boolean secondaryIndex;
+ private final boolean noIndex;
/**
* Construct a new single-record update client.
@@ -48,16 +53,42 @@
* @param tables the number of tables in the test
*/
public SingleRecordUpdateClient(int records, int tables) {
+ this(records, tables, Types.VARCHAR, false, false);
+ }
+
+ /**
+ * Construct a new single-record update client.
+ *
+ * @param records the number of records in each table in the test
+ * @param tables the number of tables in the test
+ */
+ public SingleRecordUpdateClient(int records, int tables, int type,
+ boolean secIndex, boolean nonIndexed) {
tableSize = records;
r = new Random();
pss = new PreparedStatement[tables];
+ dataType = type;
+ if (secIndex && nonIndexed) {
+ throw new IllegalArgumentException(
+ "Cannot select on both secondary index and non-index column");
+ }
+ secondaryIndex = secIndex;
+ noIndex = nonIndexed;
}
public void init(Connection c) throws SQLException {
for (int i = 0; i < pss.length; i++) {
String tableName =
- SingleRecordFiller.getTableName(tableSize, i, Types.VARCHAR);
- String sql = "UPDATE " + tableName + " SET TEXT = ? WHERE ID = ?";
+ SingleRecordFiller.getTableName(tableSize, i, dataType,
+ secondaryIndex, noIndex);
+ String column = "ID";
+ if (secondaryIndex) {
+ column = "SEC";
+ } else if (noIndex) {
+ column = "NI";
+ }
+ String sql = "UPDATE " + tableName + " SET TEXT = ? WHERE " +
+ column + " = ?";
pss[i] = c.prepareStatement(sql);
}
c.setAutoCommit(false);
@@ -66,7 +97,20 @@
public void doWork() throws SQLException {
PreparedStatement ps = pss[r.nextInt(pss.length)];
- ps.setString(1, SingleRecordFiller.randomString(r.nextInt()));
+ int seed = r.nextInt();
+ if (dataType == Types.VARCHAR) {
+ ps.setString(1, SingleRecordFiller.randomString(seed));
+ } else if (dataType == Types.BLOB) {
+ byte[] bytes = SingleRecordFiller.randomBytes(seed);
+ ps.setBinaryStream(1, new ByteArrayInputStream(bytes),
+ SingleRecordFiller.TEXT_SIZE);
+ } else if (dataType == Types.CLOB) {
+ String string = SingleRecordFiller.randomString(seed);
+ ps.setCharacterStream(1, new StringReader(string),
+ SingleRecordFiller.TEXT_SIZE);
+ } else {
+ throw new IllegalArgumentException();
+ }
ps.setInt(2, r.nextInt(tableSize));
ps.executeUpdate();
conn.commit();