Re: odd jdbc driver synchronization issue

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(at)fastcrypt(dot)com]
Sent: Wednesday, December 31, 2003 2:28 PM
To: George Lessmann
Cc: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org so that
your
> message can get through to the mailing list cleanly
>
--
Dave Cramer
519 939 0336
ICQ # 1467551