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

Oliver, all,

The original design of my test application allocated one Connection and
two CallableStatements per client (one for the parent insert and one for
the child insert). As I reported earlier, each client would then be
serialized, even though they each existed in their own
thread/connection/callablestatement.

I then refactored the application to allocate a connection per client,
and prepareCall() before every execute(). This change resulted in each
client being able to concurrently access the database, even though, in
my opinion, it violates the whole reason behind a prepareCall() which is
to reuse it often without recompilation.

So, now I have a test application which I can run against my test
Postgres server and my dev MSSQL Server box. With 5 client connections,
each inserting 1100 rows from 1100 function calls, the Postgres/Java
test is generating around 42 executions/second. MSSQL Server/Java is
generating around 368 executions/second. I then created a sql file with
the 1100 function calls, and it ran at about 580 executions/second in a
pgAdmin III query window. That's where I would like the Postgres/JDBC
test to be at, but obviously something is amiss with the Postgres JDBC
driver. I will work on getting this test application shareable, but does
anyone else have a cross-db loadtest app using JDBC?

Any help would be appreciated; I'm off to look at the ODBC driver...

George

ps--

The Postgres test DB server is a 2x2.4GHz Xeon, 1 GB ram, 10k SCSI
mirror, debian 2.6.0-test11
The MSSQL DB server is a 1x2.8Ghz Celeron, 1 GB ram, 7200RPM IDE mirror,
ms2k adv srvr

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Oliver Jowett
Sent: Wednesday, December 31, 2003 6:58 PM
To: George Lessmann
Cc: pg@fastcrypt.com; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] odd jdbc driver synchronization issue

George Lessmann wrote:

> 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?).

I have multiple connections from a single JVM accessing the same DB
concurrently with no problems; it's likely to be a quirk of the exact
query you run or how you're using the JDBC API.

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

Ideally we need a selfcontained, compilable, testcase that demonstrates
the problem. Otherwise it's just guesswork.

Some things you could try to narrow down the problem:

  - Run a couple of instances of your test in separate JVMs, see if you
get any concurrency between them.

  - Get thread dumps from the JVM while your test is running (under
solaris & linux sending SIGQUIT to the JVM does this) and see where your

client threads are blocked.

  - Turn on statement logging (in postgresql.conf) and see exactly when
the queries are being submitted.

  - Run the same queries by hand via psql and see if you get the same
behaviour.

-O


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org