Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Date: Mon, 30 Apr 2007 13:58:01 +0200
From: Olav Sandstaa <Olav.Sandstaa@Sun.COM>
Subject: Re: Performance regression after check-in on DERBY 2537 (SVN 531971)
In-reply-to: <46310289.6070102@sbcglobal.net>
Sender: Olav.Sandstaa@Sun.COM
To: derby-dev@db.apache.org
Message-id: <4635D9C9.8010104@sun.com>
Organization: Sun Microsystems - Trondheim, Norway
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_4X7hme1lX/n0ibwEBVtqdA)"
References: <4630A25C.8020003@sun.com> <46310289.6070102@sbcglobal.net>
User-Agent: Thunderbird 2.0.0.0 (X11/20070419)

This is a multi-part message in MIME format.

--Boundary_(ID_4X7hme1lX/n0ibwEBVtqdA)
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT

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
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>


--Boundary_(ID_4X7hme1lX/n0ibwEBVtqdA)
Content-type: text/x-java; name=TestClient2.java
Content-transfer-encoding: 7BIT
Content-disposition: inline; filename=TestClient2.java

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();
    }
}

--Boundary_(ID_4X7hme1lX/n0ibwEBVtqdA)--