Re: odd jdbc driver synchronization issue - Mailing list pgsql-jdbc

Dave, all,

Sorry, I thought this was such an obvious thing to do; it would be some
setting or something I missed (does the jdbc driver have any debug
options?). Some of the code is proprietary. Hopefully, this will give
you an idea of what is happening. Basically, create a number of clients,
load them with parents, and then run() them. While running, a client
inserts one parent and a number of children.


package jdbhammer;
/**
 * @author George Lessmann
 * copyright 2003, George Lessmann
 * code may not be reused without permission
  */
import java.util.ArrayList;

import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;

public final class Main {
    private static String resultsLocation = null;

    private static  String username = "postgres";
    private static  String password = "";

    public static void main(String[] args) {

        if (args == null | "?".equals(args[0])) {
            System.out.println("Usage: java -jar
jDBHammer.jar [#clients] [#dbconnections] [#parents] [#children per
parent] [server] [username] [password]");
            return;
        }// if

        if (args.length >= 5) {
            server = args[4];
        }// if

        if (args.length >= 6) {
            username = args[5];
        }// if

        if (args.length >= 7) {
            password = args[6];
        }// if

        resultsLocation =
"c:\\jdbhammer"+"-"+args[0]+"-"+args[1]+"-"+args[2]+"-"+args[3]+".txt";

        System.out.println("Creating DB Pool.");


        try {
            Class.forName("org.postgresql.Driver");
        } catch (Exception e) {
            System.out.println("Error! Unable to load JDBC
Driver! Error was:" + e.getMessage());
            return;
        }// try

        // create the pool
        GenericObjectPool connectionPool = new
GenericObjectPool(null, Integer.parseInt(args[1]),
GenericObjectPool.WHEN_EXHAUSTED_GROW, 0);
        DriverManagerConnectionFactory connectionFactory = new
DriverManagerConnectionFactory("jdbc:postgresql://theserver/thedb",
username, password);

        try {
            PoolableConnectionFactory
poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory,connectionPool,null,null,fal
se,true);
        } catch (Exception e) {
            System.out.println("Error! Unable to create
PoolableConnectionFactory! Error was:" + e.getMessage());
            return;
        }// try

        PoolingDriver driver = new PoolingDriver();
        driver.registerPool("jdbhammer",connectionPool);

        ArrayList clients = new ArrayList();
        DbClient client = null;

        System.out.println("Creating Clients.");

        // load them
        for (int i=0, j=Integer.parseInt(args[0]); i < j; i++) {

            client = new DbClient("DbClient" +
Integer.toString(i), resultsLocation);
            client.loadParents(Integer.parseInt(args[2]),
Integer.parseInt(args[3]));
            System.out.println("Creating Clients: "
+"DbClient" + Integer.toString(i));
            clients.add(client);
        }// for

        System.out.println("Starting Threads.");
        Thread[] t = new Thread[clients.size()];

        for (int i=0, j=clients.size(); i < j; i++) {
            client = (DbClient) clients.get(i);
            t[i] = new Thread(client, client.getClientId());
            t[i].start();
        }// for

    }// main

}// Main

// DBClient
    private ArrayList workload = new ArrayList(10000);
    private String clientId = null;
    private long startTime = 0L;
    private long endTime = 0L;
    private boolean isComplete = Boolean.FALSE.booleanValue();

    private static final String procInsertParent = "{ call
proc_insert_parent( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
) }";
    private static final String procInsertChild = "{ call
proc_insert_child( ?,?,?,?,?,?,?,?,?,?,?,?,? ) }";
    private String resultsLocation = null;

    private Connection conn = null;
    private CallableStatement parentCall = null;
    private CallableStatement childCall = null;

    public DbClient(String clientId, String resultsLocation) {
        this.clientId = clientId;
        this.resultsLocation = resultsLocation;

        try {
            System.out.println("grabbing connection");
            conn =
DriverManager.getConnection("jdbc:apache:commons:dbcp:jdbhammer");
            conn.setAutoCommit(false);
            parentCall = conn.prepareCall(procInsertParent);
            childCall = conn.prepareCall(procInsertChild);
        } catch (SQLException sql) {
            System.out.println("Error! There was an error
creating the connection. Error was: " + sql.getMessage());
        } // try
    } // DbClient

    public final void run() {

        Parent p = null;
        Child c = null;
        ArrayList children = null;

        startTime = System.currentTimeMillis();
        try {
            for (int i = 0, j = workload.size(); i < j; i++)
{
                p = (Parent) workload.get(i);

                try {

p.setStartTime(System.currentTimeMillis());
                    p.insert(parentCall);

p.setEndTime(System.currentTimeMillis());
                } catch (SQLException sql) {
                    System.out.println("Error! " +
sql.getMessage());
                    continue; // skip the children
                } // try

                children = p.getChildren();

                for (int k = 0, m = children.size(); k <
m; k++) {
                    child = (Child) children.get(k);

                    try {

child.setStartTime(System.currentTimeMillis());
                        child.insert(childCall);

child.setEndTime(System.currentTimeMillis());
                    } catch (SQLException sql) {

System.out.println("Error! " + sql.getMessage());
                        // do nothing, keep on
running
                    } // try
                } // for

                if ((i + 1 == j)) {
                    conn.commit();
                } // if

            } // for
        } catch (SQLException sql) {
            System.out.println("Error! " +
sql.getMessage());
            try {
                conn.rollback();
            } catch (SQLException sqle) {
            }
        } finally {
            try {
                parentCall.close();
                childCall.close();
                conn.close();
            } catch (SQLException sql) {
            }
        }
        endTime = System.currentTimeMillis();
        isComplete = Boolean.TRUE.booleanValue();

        // done
        System.out.println(Thread.currentThread().getName() + "
finished!");

        writeOutput();
    } // run

The parent and child insert() method sets a number of parameters one
each callable statement, and then execute() a function on the PostgreSQL
server, resulting in a row being inserted into a table.

Let me know what other information I can provide,

George

-----Original Message-----
From: Dave Cramer [mailto:pg@fastcrypt.com]
Sent: Wednesday, December 31, 2003 2:28 PM
To: George Lessmann
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] odd jdbc driver synchronization issue

One thing that would help is the code?

Dave
On Wed, 2003-12-31 at 15:06, George Lessmann wrote:
> Hi,
>
> I'm trying to move my database platform from MSSQL Server to
PostgreSQL.
> I have a load simulation program I wrote which I'm running against a
> test PostgreSQL instance. The application creates a number of client
> threads, each with their own database connection, and turns them loose
> on a workload. Currently, it's lagging the SQLServer test. I'm still
> tuning the postgresql.conf file, but I also noticed something very odd
> wrt the postmaster process. I see something like the following:
>
> 3 threads started, 0 done,
>     pid 1        99%
>     pid 2        0%
>     pid 3        0%
>
> 2 threads started, 1 done,
>     pid 1        0%
>     pid 2        99%
>     pid 3        0%
>
> 1 threads started, 2 done,
>     pid 1        0%
>     pid 2        0%
>     pid 3        99%
>
> Even though all the treads are runnable, with their own connection,
and
> their own postmaster process on the DB, they are still serializing
their
> writes. If I look in the table where the data is being generated, I
see
> rows inserted in order similar to what top is showing (Client3's data,
> then Client6's data, then Client1's data, etc).
>
> As this application is written in java and using the PostgreSQL jdbc
> driver I'm starting here, if you feel I should post elsewhere, let me
> know.
>
> Thanks,
>
> George
>
> --Details--
>
> Client machine:
>  Windows XP, Java 1.4.2 (build 1.4.2-b28)
>  PostgreSQL JDBC driver 7.4, 7.4.1 (no difference)
>
> Database machine:
>  Debian Linux 2.6.0-test11 #4 SMP i686 GNU/Linux
>  2x 2.4GHz Xeon w/HT, 1GB RAM
>  PostgreSQL 7.4
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that
your
>       message can get through to the mailing list cleanly
>
--
Dave Cramer
519 939 0336
ICQ # 1467551