Attached is a slightly modified version of the test client found in
DERBY-1961. The main change I have done is to add a secondary index to
it that is used for the queries. Running this with two clients on a dual
CPU machine running Linux and using IBM JVM 1.5 I see a drop in
throughput of about 6 percent (down from 16656 tps to 15706 tps -
average of five runs).
To run this client do:
1. Create and initialize database:
java TestClient2 -a initdb -u "jdbc:derby:/tmp/tulldb;create=true"
2. Run test (which does SELECT * FROM ... WHERE SEC_ID= X) with two
client threads:
java TestClient2 -a select -r 60 -c 2 -u "jdbc:derby:/tmp/tulldb"
Olav
Mike Matrigali wrote:
Are these tests checked in so that I can run them in my environment?
Olav Sandstaa wrote:
The two last days I have seen a performance regression in some of the
performance tests I run. For some of the tests the reduction in
throughput is about 15 percent. It seems like the regression is
introduced by the latest check-in on DERBY-2537, svn 531971.
I have attached a graph showing the throughput I get when running
single-record select operation on a table. The queries use a
secondary index for finding the record to select. The test has been
run with 1 to 20 concurrent client against embedded Derby. I have run
the test with and without SVN 531971. As the graph shows, the
performance reduction is between 12 and 15 percent for all runs.
The schema looks like this:
CREATE TABLE t1 id INTEGER sec_id INTEGER data CHARACTER(100)
PRIMARY KEY(id))
CREATE INDEX nonprimary_index ON t1 (sec_id)
Each query do the select on the secondary index and retrieves the
data field (a CHARACTER(100) field).
I would expect some of the changes done in the patch to have some
influence on the performance, but not in the order of 10-15 percent.
The tests are run on a 2 CPU Opteron server running Solaris 10 and
JDK 6.
Olav
------------------------------------------------------------------------
import java.sql.*;
import java.util.Random;
/*
Test client which generates different types of load (single-record select
operations, single-record update operations). Could be
used for performance testing or as a load generator.
Usage: java TestClient2 options
Options:
-a action (initdb, select, update)
-d driver (default: org.apache.derby.jdbc.EmbeddedDriver)
-u url
-w time (warmup time in seconds, default 30)
-r time (run time in seconds, default 300)
-c clients (number of clients, default 1)
-h (print this screen)
Action and URL must always be specified.
When running with "-a initdb", derbyTesting.jar is needed in CLASSPATH.
*/
public class TestClient2 extends Thread {
private static String action;
private static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
private static String url;
private static int secondsWarmup = 30;
private static int secondsRuntime = 300;
private static int numberOfClients = 1;
private static int operation;
private static volatile boolean stop;
private static volatile boolean collect;
private final static int SELECT = 0;
private final static int UPDATE = 1;
private final static int JOIN = 2;
private static final String[] STRINGS = new String[16];
static {
String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
for (int i = 0; i < STRINGS.length; i++) {
StringBuffer buf = new StringBuffer(100);
for (int j = i; j < i + 100; j++) {
buf.append(chars.charAt(j % chars.length()));
}
STRINGS[i] = buf.toString();
}
}
public static void main(String[] args) throws Exception {
parseArgs(args);
Class.forName(driver).newInstance();
if (action.equals("initdb")) {
initDb();
} else {
if (action.equals("select")) {
operation = SELECT;
} else if (action.equals("update")) {
operation = UPDATE;
} else {
System.err.println("Unknown action: " + action);
System.exit(1);
}
runTest();
}
}
private static void parseArgs(String[] args) {
for (int i = 0; i < args.length; i++) {
String arg = args[i];
if (arg.equals("-a")) {
action = args[++i];
} else if (arg.equals("-d")) {
driver = args[++i];
} else if (arg.equals("-u")) {
url = args[++i];
} else if (arg.equals("-w")) {
secondsWarmup = Integer.parseInt(args[++i]);
} else if (arg.equals("-r")) {
secondsRuntime = Integer.parseInt(args[++i]);
} else if (arg.equals("-c")) {
numberOfClients = Integer.parseInt(args[++i]);
} else if (arg.equals("-h")) {
printUsage();
System.exit(0);
} else {
System.err.println("Invalid option: " + args[i]);
System.exit(1);
}
}
if (action == null) {
System.err.println("You must specify action.");
System.exit(1);
}
if (url == null) {
System.err.println("You must specify URL.");
System.exit(1);
}
}
private static void printUsage() {
System.out.println("Usage: java TestClient options");
System.out.println();
System.out.println("Options:");
System.out.println(" -a action (initdb, select, update)");
System.out.println(
" -d driver (default: org.apache.derby.jdbc.EmbeddedDriver)");
System.out.println(" -u url");
System.out.println(" -w time (warmup time in seconds, default 30)");
System.out.println(" -r time (run time in seconds, default 300)");
System.out.println(" -c clients (number of clients, default 1)");
System.out.println(" -h (print this screen)");
System.out.println();
System.out.println("Action and URL must always be specified.");
System.out.println("When running with \"-a initdb\", " +
"derbyTesting.jar is needed in CLASSPATH.");
}
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url);
}
private static void initDb() throws SQLException {
System.out.println("Initializing database...");
Connection c = getConnection();
c.setAutoCommit(false);
Statement s = c.createStatement();
try {
s.executeUpdate("DROP TABLE HUNDREDKTUP");
} catch (SQLException e) {}
s.executeUpdate("CREATE TABLE HUNDREDKTUP(ID INT PRIMARY KEY, SEC_ID INT, " +
"TEXT VARCHAR(100))");
s.executeUpdate("CREATE INDEX nonprimary_index ON HUNDREDKTUP (SEC_ID)");
PreparedStatement ps = c.prepareStatement(
"INSERT INTO HUNDREDKTUP VALUES (?, ?, ?)");
for (int i = 0; i < 100000; i++) {
ps.setInt(1, i);
ps.setInt(2, i);
ps.setString(3, STRINGS[i % STRINGS.length]);
ps.executeUpdate();
if ((i % 1000) == 999) {
c.commit();
}
}
ps.close();
s.close();
c.commit();
c.close();
System.out.println("Done.");
}
private static void runTest() throws InterruptedException {
System.out.println("Starting " + action + " test with " +
numberOfClients + " clients...");
TestClient2[] clients = new TestClient2[numberOfClients];
for (int i = 0; i < clients.length; i++) {
clients[i] = new TestClient2();
clients[i].start();
}
System.out.println("Starting warmup...");
Thread.sleep((long) secondsWarmup * 1000);
System.out.println("Warmup finished, collecting data...");
collect = true;
Thread.sleep((long) secondsRuntime * 1000);
stop = true;
collect = false;
System.out.println("Stopping threads...");
long total = 0;
for (int i = 0; i < clients.length; i++) {
clients[i].join();
total += clients[i].count;
}
System.out.println("\nTotal number of transactions: " + total);
System.out.println("Average throughput: " +
((double) total / secondsRuntime) + " TPS");
}
private long count;
public void run() {
try {
run_();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void run_() throws SQLException {
Connection c = getConnection();
c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
c.setAutoCommit(false);
Random r = new Random();
String sql = null;
switch (operation) {
case SELECT:
sql = "select * from hundredktup where sec_id = ?";
break;
case UPDATE:
sql = "update hundredktup set text = ? where id = ?";
break;
}
PreparedStatement ps = c.prepareStatement(sql);
while (!stop) {
ResultSet rs;
switch (operation) {
case SELECT:
ps.setInt(1, r.nextInt(100000));
rs = ps.executeQuery();
rs.next();
rs.getInt(1);
rs.getString(2);
rs.close();
break;
case UPDATE:
ps.setString(1, STRINGS[r.nextInt(STRINGS.length)]);
ps.setInt(2, r.nextInt(100000));
ps.executeUpdate();
break;
case JOIN:
rs = ps.executeQuery();
while (rs.next()) {
rs.getInt(1);
}
rs.close();
break;
}
c.commit();
if (collect) {
count++;
}
}
ps.close();
c.close();
}
}
