Re: [HACKERS] Open 6.5 items

[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 static int ACCOUNT = 2;
!


private Connection Conn = null;
--- 18,24 ----
public final static int TELLER = 0;
public final static int BRANCH = 1;
public final static int 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;