001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.bench;
007:
008: import java.sql.Connection;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.util.Random;
013:
014: /**
015: * This test is similar to the TPC-B test of the Transaction Processing Council
016: * (TPC). Multiple threads are used (one thread per connection). Referential
017: * integrity is not implemented.
018: * <p>
019: * See also http://www.tpc.org/tpcb
020: */
021: public class BenchB implements Bench, Runnable {
022:
023: // master data
024: private Database db;
025: private int scale = 1;
026: private int branches = 1;
027: private int tellers = 10;
028: private int accounts = 100000;
029: private int clients = 10;
030: private int transactionPerClient;
031:
032: // client data
033: private BenchB master;
034: private Connection conn;
035: private PreparedStatement updateAccount;
036: private PreparedStatement selectAccount;
037: private PreparedStatement updateTeller;
038: private PreparedStatement updateBranch;
039: private PreparedStatement insertHistory;
040: private Random random;
041:
042: public BenchB() {
043: }
044:
045: public void init(Database db, int size) throws Exception {
046: this .db = db;
047: this .transactionPerClient = size;
048:
049: db.start(this , "Init");
050: db.openConnection();
051: db.dropTable("BRANCHES");
052: db.dropTable("TELLERS");
053: db.dropTable("ACCOUNTS");
054: db.dropTable("HISTORY");
055: String[] create = {
056: "CREATE TABLE BRANCHES(BID INT NOT NULL PRIMARY KEY, BBALANCE INT, FILLER VARCHAR(88))",
057: "CREATE TABLE TELLERS(TID INT NOT NULL PRIMARY KEY, BID INT, TBALANCE INT, FILLER VARCHAR(84))",
058: "CREATE TABLE ACCOUNTS(AID INT NOT NULL PRIMARY KEY, BID INT, ABALANCE INT, FILLER VARCHAR(84))",
059: "CREATE TABLE HISTORY(TID INT, BID INT, AID INT, DELTA INT, TIME DATETIME, FILLER VARCHAR(22))" };
060: for (int i = 0; i < create.length; i++) {
061: db.update(create[i]);
062: }
063: PreparedStatement prep;
064: db.setAutoCommit(false);
065: int commitEvery = 1000;
066: prep = db
067: .prepare("INSERT INTO BRANCHES(BID, BBALANCE) VALUES(?, 0)");
068: for (int i = 0; i < branches * scale; i++) {
069: prep.setInt(1, i);
070: db.update(prep, "insertBranches");
071: if (i % commitEvery == 0) {
072: db.commit();
073: }
074: }
075: db.commit();
076: prep = db
077: .prepare("INSERT INTO TELLERS(TID, BID, TBALANCE) VALUES(?, ?, 0)");
078: for (int i = 0; i < tellers * scale; i++) {
079: prep.setInt(1, i);
080: prep.setInt(2, i / tellers);
081: db.update(prep, "insertTellers");
082: if (i % commitEvery == 0) {
083: db.commit();
084: }
085: }
086: db.commit();
087: int len = accounts * scale;
088: prep = db
089: .prepare("INSERT INTO ACCOUNTS(AID, BID, ABALANCE) VALUES(?, ?, 0)");
090: for (int i = 0; i < len; i++) {
091: prep.setInt(1, i);
092: prep.setInt(2, i / accounts);
093: db.update(prep, "insertAccounts");
094: if (i % commitEvery == 0) {
095: db.commit();
096: }
097: }
098: db.commit();
099: db.closeConnection();
100: db.end();
101: // db.start(this, "Open/Close");
102: // db.openConnection();
103: // db.closeConnection();
104: // db.end();
105: }
106:
107: private BenchB(BenchB master, int seed) throws Exception {
108: this .master = master;
109: random = new Random(seed);
110: conn = master.db.getConnection();
111: conn.setAutoCommit(false);
112: updateAccount = conn
113: .prepareStatement("UPDATE ACCOUNTS SET ABALANCE=ABALANCE+? WHERE AID=?");
114: selectAccount = conn
115: .prepareStatement("SELECT ABALANCE FROM ACCOUNTS WHERE AID=?");
116: updateTeller = conn
117: .prepareStatement("UPDATE TELLERS SET TBALANCE=TBALANCE+? WHERE TID=?");
118: updateBranch = conn
119: .prepareStatement("UPDATE BRANCHES SET BBALANCE=BBALANCE+? WHERE BID=?");
120: insertHistory = conn
121: .prepareStatement("INSERT INTO HISTORY(TID, BID, AID, DELTA) VALUES(?, ?, ?, ?)");
122: }
123:
124: public void run() {
125: int accountsPerBranch = accounts / branches;
126: for (int i = 0; i < master.transactionPerClient; i++) {
127: int branch = random.nextInt(master.branches);
128: int teller = random.nextInt(master.tellers);
129: int account;
130: if (random.nextInt(100) < 85) {
131: account = random.nextInt(accountsPerBranch) + branch
132: * accountsPerBranch;
133: } else {
134: account = random.nextInt(accounts);
135: }
136: int delta = random.nextInt(1000);
137: doOne(branch, teller, account, delta);
138: }
139: try {
140: conn.close();
141: } catch (SQLException e) {
142: // ignore
143: }
144: }
145:
146: private void doOne(int branch, int teller, int account, int delta) {
147: try {
148: // UPDATE ACCOUNTS SET ABALANCE=ABALANCE+? WHERE AID=?
149: updateAccount.setInt(1, delta);
150: updateAccount.setInt(2, account);
151: updateAccount.executeUpdate();
152:
153: // SELECT ABALANCE FROM ACCOUNTS WHERE AID=?
154: selectAccount.setInt(1, account);
155: ResultSet rs = selectAccount.executeQuery();
156: while (rs.next()) {
157: rs.getInt(1);
158: }
159:
160: // UPDATE TELLERS SET TBALANCE=TABLANCE+? WHERE TID=?
161: updateTeller.setInt(1, delta);
162: updateTeller.setInt(2, teller);
163: updateTeller.executeUpdate();
164:
165: // UPDATE BRANCHES SET BBALANCE=BBALANCE+? WHERE BID=?
166: updateBranch.setInt(1, delta);
167: updateBranch.setInt(2, branch);
168: updateBranch.executeUpdate();
169:
170: // INSERT INTO HISTORY(TID, BID, AID, DELTA) VALUES(?, ?, ?, ?)
171: insertHistory.setInt(1, teller);
172: insertHistory.setInt(2, branch);
173: insertHistory.setInt(3, account);
174: insertHistory.setInt(4, delta);
175: insertHistory.executeUpdate();
176: conn.commit();
177: } catch (SQLException e) {
178: e.printStackTrace();
179: }
180: }
181:
182: public void runTest() throws Exception {
183: db.start(this , "Transactions");
184: db.openConnection();
185: processTransactions();
186: db.closeConnection();
187: db.end();
188: db.openConnection();
189: processTransactions();
190: db.logMemory(this , "Memory Usage");
191: db.closeConnection();
192: }
193:
194: private void processTransactions() throws Exception {
195: Thread[] threads = new Thread[clients];
196: for (int i = 0; i < clients; i++) {
197: threads[i] = new Thread(new BenchB(this , i));
198: }
199: for (int i = 0; i < clients; i++) {
200: threads[i].start();
201: }
202: for (int i = 0; i < clients; i++) {
203: threads[i].join();
204: }
205: }
206:
207: public String getName() {
208: return "BenchB";
209: }
210: }
|