Re: [HACKERS] Open 6.5 items - Mailing list pgsql-hackers

[forwarded to hackers list]

>> Bingo! Your fix seems to solve the problem! Now 64 concurrent
>> transactions ran 100 transactions each without any problem. Thanks.  ~~~~~~~~~~~~users
>> 
>> BTW, the script I'm using for the heavy load testing is written in
>> Java(not written by me). Do you want to try it?
>
>I am doing some benchmarks and would really appreciate if you could
>let me have your Java routines.

It's "JDBCBench" available from:

http://www.worldserver.com/mm.mysql/performance/

Seems it is originally made for MySQL, can be used with PostgreSQL and
other commercial dbms including Oracle, however.

I like it since it:

o automatically creates test data
o simulates heavy loads with the specified number of users and transactions per user from the command line

I noticed minor bugs with JDBCBench 1.0. Also I added begin/end so
that the set of operations are performed in a transaction. Here are
diffs: (please make sure that the file is in Unix format. seems the
original file is in DOS format.)

*** JDBCBench.java.orig    Tue Jun  1 17:31:11 1999
--- JDBCBench.java    Tue Jun  1 17:32:04 1999
***************
*** 18,24 ****     public final static int TELLER  = 0;     public final static int BRANCH  = 1;     public final
staticint ACCOUNT = 2;
 
!              private Connection Conn = null;
--- 18,24 ----     public final static int TELLER  = 0;     public final static int BRANCH  = 1;     public final
staticint ACCOUNT = 2;
 
!   static String DBUrl = "";             private Connection Conn = null;
***************
*** 40,46 ****     public static void main(String[] Args)     {       String DriverName = "";
!       String DBUrl = "";       boolean initialize_dataset = false;              for (int i = 0; i < Args.length; i++)
{
--- 40,46 ----     public static void main(String[] Args)     {       String DriverName = "";
!        boolean initialize_dataset = false;              for (int i = 0; i < Args.length; i++) {
***************
*** 286,291 ****
--- 286,299 ----                  public void run()         {
+       Connection myC = null;
+       try {
+         myC = DriverManager.getConnection(DBUrl);
+       }
+       catch (Exception E) {
+         System.out.println(E.getMessage());
+         E.printStackTrace();
+       }             while (ntrans-- > 0) {                                  int account =
JDBCBench.getRandomID(ACCOUNT);
***************
*** 293,299 ****                 int teller  = JDBCBench.getRandomID(TELLER);                 int delta   =
JDBCBench.getRandomInt(0,1000);                
 
!                 doOne(account, branch, teller, delta);                 incrementTransactionCount();             }
       reportDone();
 
--- 301,307 ----                 int teller  = JDBCBench.getRandomID(TELLER);                 int delta   =
JDBCBench.getRandomInt(0,1000);                
 
!                 doOne(myC, account, branch, teller, delta);                 incrementTransactionCount();
}            reportDone();
 
***************
*** 303,320 ****          *  doOne() - Executes a single TPC BM B transaction.          */     
!         int doOne(int bid, int tid, int aid, int delta)         {             try {
!                 Statement Stmt = Conn.createStatement();                 
!                 String Query = "UPDATE accounts ";                        Query+= "SET     Abalance = Abalance + " +
delta+ " ";                        Query+= "WHERE   Aid = " + aid;
Stmt.executeUpdate(Query);                Stmt.clearWarnings();
 
!                                         Query = "SELECT Abalance ";                 Query+= "FROM   accounts ";
         Query+= "WHERE  Aid = " + aid;
 
--- 311,334 ----          *  doOne() - Executes a single TPC BM B transaction.          */     
!         int doOne(Connection myC, int aid, int bid, int tid, int delta)         {
+       int aBalance = 0;             try {
!           String Query;
!                 Statement Stmt = myC.createStatement();                 
!                 Stmt.executeUpdate("begin");
!                 Stmt.clearWarnings();
! 
!                 Query = "UPDATE accounts ";                        Query+= "SET     Abalance = Abalance + " + delta +
"";                        Query+= "WHERE   Aid = " + aid;
Stmt.executeUpdate(Query);                Stmt.clearWarnings();
 
! 
!                  Query = "SELECT Abalance ";                 Query+= "FROM   accounts ";                 Query+=
"WHERE Aid = " + aid;
 
***************
*** 322,333 ****                 ResultSet RS = Stmt.executeQuery(Query);                 Stmt.clearWarnings();
       
 
!                 int aBalance = 0;                                  while (RS.next()) {                     aBalance =
RS.getInt(1);                }
 
!                          Query = "UPDATE tellers ";                 Query+= "SET    Tbalance = Tbalance + " + delta +
"";                 Query+= "WHERE  Tid = " + tid;
 
--- 336,348 ----                 ResultSet RS = Stmt.executeQuery(Query);                 Stmt.clearWarnings();
       
 
!                 aBalance = 0;                 
+                  while (RS.next()) {                     aBalance = RS.getInt(1);                 }
!                  Query = "UPDATE tellers ";                 Query+= "SET    Tbalance = Tbalance + " + delta + " ";
            Query+= "WHERE  Tid = " + tid;
 
***************
*** 350,355 ****
--- 365,373 ----                 Query+= delta + ")";                                  Stmt.executeUpdate(Query);
+                 Stmt.clearWarnings();
+ 
+                 Stmt.executeUpdate("end");                 Stmt.clearWarnings();                          return
aBalance;