Re: odd jdbc driver synchronization issue - Mailing list pgsql-jdbc
From | George Lessmann |
---|---|
Subject | Re: odd jdbc driver synchronization issue |
Date | |
Msg-id | 009201c3d5f6$b1d60450$85342fa5@elvis Whole thread Raw |
In response to | odd jdbc driver synchronization issue ("George Lessmann" <georgel@speedtradinggroup.com>) |
List | pgsql-jdbc |
Kris, Thanks for the test program. It helped a lot, and I believe I've found my problem. The main issue seems to be the transaction isolation and locking behavior of PostgreSQL as compared to MSSQL Server, and the way I was writing my code (specifically, commits) between all my different tests. My workload uses two stored procedures; each one issues a few selects, possibly updates, and then an insert. The update branch is what caused the issues, as a table could be concurrently updated by any number of clients (basically, something like balance = balance + amount). Here's my work section, modeled after your example: public void execute(int num) throws SQLException { for (int i=0; i<num; i++) { parentCall.execute(); // #1 for (int j=0; j<9; j++) { childCall.execute(); // #1 } // #2 } // #3 conn.commit(); } #1 a conn.commit() placed here allowed multiple clients/postmasters to work. No postmaster process went above 8% cpu. #2 a conn.commit() placed here allowed multiple clients/postmasters to work. Faster than #1, naturally. No postmaster process went above 25% cpu. #3 a conn.commit() placed here will serialize all clients/postmasters (if you have updates in your functions). 1 postmaster process always at 99% cpu. Although it finished with the highest throughput, it's not the correct isolation level for the workload. Here's some comparison data for those who are interested, Postgres #1 39.38 tps Postgres #2 108.50 tps Postgres #3 563.76 tps (near pgAdmin levels?) MSSQL #1 389.81 tps MSSQL #2 459.18 tps MSSQL #3 767.23 tps Thanks to Dave, Oliver, and Kris for the help. George -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Kris Jurka Sent: Monday, January 05, 2004 4:42 PM To: George Lessmann Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] odd jdbc driver synchronization issue On Mon, 5 Jan 2004, George Lessmann wrote: > > 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. I've attached a program I wrote to try and verify your claims. I see no serialization despite a single prepareCall and many executes. This program runs with java CallTest <num clients> <num runs per client> each client does a loop over numRuns, calling a SELECT stored procedure and then inserts that value into a table via another stored procedure. Let me know if you see a problem with this test or how your application differs. Kris Jurka
pgsql-jdbc by date: