001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.perf;
026:
027: import java.sql.PreparedStatement;
028: import java.sql.SQLException;
029:
030: import java.text.NumberFormat;
031:
032: import testsuite.BaseTestCase;
033:
034: /**
035: * Simple performance testing unit test.
036: *
037: * @author Mark Matthews
038: */
039: public class LoadStorePerfTest extends BasePerfTest {
040: /** The table type to use (only for MySQL), 'HEAP' by default */
041: private String tableType = "HEAP";
042:
043: private boolean takeMeasurements = false;
044:
045: private boolean useColumnNames = false;
046:
047: private boolean largeResults = false;
048:
049: /**
050: * Constructor for LoadStorePerfTest.
051: *
052: * @param name
053: * the name of the test to run
054: */
055: public LoadStorePerfTest(String name) {
056: super (name);
057:
058: String newTableType = System
059: .getProperty("com.mysql.jdbc.test.tabletype");
060:
061: this .largeResults = "TRUE"
062: .equalsIgnoreCase(System
063: .getProperty("com.mysql.jdbc.testsuite.loadstoreperf.useBigResults"));
064:
065: if ((newTableType != null) && (newTableType.length() > 0)) {
066: this .tableType = newTableType;
067:
068: System.out.println("Using specified table type of '"
069: + this .tableType + "'");
070: }
071: }
072:
073: /**
074: * Runs all tests in this test case
075: *
076: * @param args
077: * ignored
078: *
079: * @throws Exception
080: * if an error occurs
081: */
082: public static void main(String[] args) throws Exception {
083: new LoadStorePerfTest("test1000Transactions").run();
084: }
085:
086: /**
087: * @see junit.framework.TestCase#setUp()
088: */
089: public void setUp() throws Exception {
090: super .setUp();
091:
092: try {
093: this .stmt.executeUpdate("DROP TABLE perfLoadStore");
094: } catch (SQLException sqlEx) {
095: // ignore
096: }
097:
098: String dateTimeType = "DATETIME";
099:
100: if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
101: dateTimeType = "TIMESTAMP";
102: }
103:
104: //
105: // Approximate a run-of-the-mill entity in a business application
106: //
107: String query = "CREATE TABLE perfLoadStore (priKey INT NOT NULL, "
108: + "fk1 INT NOT NULL, "
109: + "fk2 INT NOT NULL, "
110: + "dtField "
111: + dateTimeType
112: + ", "
113: + "charField1 CHAR(32), "
114: + "charField2 CHAR(32), "
115: + "charField3 CHAR(32), "
116: + "charField4 CHAR(32), "
117: + "intField1 INT, "
118: + "intField2 INT, "
119: + "intField3 INT, "
120: + "intField4 INT, "
121: + "doubleField1 DECIMAL,"
122: + "doubleField2 DOUBLE,"
123: + "doubleField3 DOUBLE,"
124: + "doubleField4 DOUBLE,"
125: + "PRIMARY KEY (priKey))";
126:
127: if (BaseTestCase.dbUrl.indexOf("mysql") != -1) {
128: query += (" TYPE=" + this .tableType);
129: }
130:
131: this .stmt.executeUpdate(query);
132:
133: String currentDateValue = "NOW()";
134:
135: if (BaseTestCase.dbUrl.indexOf("sqlserver") != -1) {
136: currentDateValue = "GETDATE()";
137: }
138:
139: if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
140: currentDateValue = "CURRENT_TIMESTAMP";
141: }
142:
143: int numLoops = 1;
144:
145: if (this .largeResults) {
146: numLoops = 32;
147: }
148:
149: System.out.println("Inserting " + numLoops
150: + " rows to retrieve...");
151:
152: for (int i = 0; i < numLoops; i++) {
153: this .stmt.executeUpdate("INSERT INTO perfLoadStore ("
154: + "priKey, " + "fk1, " + "fk2, " + "dtField, "
155: + "charField1, " + "charField2, " + "charField3, "
156: + "charField4, " + "intField1, " + "intField2, "
157: + "intField3, " + "intField4, " + "doubleField1,"
158: + "doubleField2," + "doubleField3,"
159: + "doubleField4" + ") VALUES (" + i + "," // priKey
160: + "2," // fk1
161: + "3," // fk2
162: + currentDateValue + "," // dtField
163: + "'0123456789ABCDEF0123456789ABCDEF'," // charField1
164: + "'0123456789ABCDEF0123456789ABCDEF'," // charField2
165: + "'0123456789ABCDEF0123456789ABCDEF'," // charField3
166: + "'0123456789ABCDEF0123456789ABCDEF'," // charField4
167: + "7," // intField1
168: + "8," // intField2
169: + "9," // intField3
170: + "10," // intField4
171: + "1.20," // doubleField1
172: + "2.30," // doubleField2
173: + "3.40," // doubleField3
174: + "4.50" // doubleField4
175: + ")");
176: }
177: }
178:
179: /**
180: * @see junit.framework.TestCase#tearDown()
181: */
182: public void tearDown() throws Exception {
183: try {
184: this .stmt.executeUpdate("DROP TABLE perfLoadStore");
185: } catch (SQLException sqlEx) {
186: // ignore
187: }
188:
189: super .tearDown();
190: }
191:
192: /**
193: * Tests and times 1000 load/store type transactions
194: *
195: * @throws Exception
196: * if an error occurs
197: */
198: public void test1000Transactions() throws Exception {
199: this .takeMeasurements = false;
200: warmUp();
201: this .takeMeasurements = true;
202: doIterations(29);
203:
204: reportResults("\n\nResults for instance # 1: ");
205: }
206:
207: /**
208: * Runs one iteration of the test.
209: *
210: * @see testsuite.perf.BasePerfTest#doOneIteration()
211: */
212: protected void doOneIteration() throws Exception {
213: PreparedStatement pStmtStore = this .conn
214: .prepareStatement("UPDATE perfLoadStore SET "
215: + "priKey = ?, " + "fk1 = ?, " + "fk2 = ?, "
216: + "dtField = ?, " + "charField1 = ?, "
217: + "charField2 = ?, " + "charField3 = ?, "
218: + "charField4 = ?, " + "intField1 = ?, "
219: + "intField2 = ?, " + "intField3 = ?, "
220: + "intField4 = ?, " + "doubleField1 = ?,"
221: + "doubleField2 = ?," + "doubleField3 = ?,"
222: + "doubleField4 = ?" + " WHERE priKey=?");
223: PreparedStatement pStmtCheck = this .conn
224: .prepareStatement("SELECT COUNT(*) FROM perfLoadStore WHERE priKey=?");
225: PreparedStatement pStmtLoad = null;
226:
227: if (this .largeResults) {
228: pStmtLoad = this .conn.prepareStatement("SELECT "
229: + "priKey, " + "fk1, " + "fk2, " + "dtField, "
230: + "charField1, " + "charField2, " + "charField3, "
231: + "charField4, " + "intField1, " + "intField2, "
232: + "intField3, " + "intField4, " + "doubleField1,"
233: + "doubleField2, " + "doubleField3,"
234: + "doubleField4" + " FROM perfLoadStore");
235: } else {
236: pStmtLoad = this .conn.prepareStatement("SELECT "
237: + "priKey, " + "fk1, " + "fk2, " + "dtField, "
238: + "charField1, " + "charField2, " + "charField3, "
239: + "charField4, " + "intField1, " + "intField2, "
240: + "intField3, " + "intField4, " + "doubleField1,"
241: + "doubleField2, " + "doubleField3,"
242: + "doubleField4"
243: + " FROM perfLoadStore WHERE priKey=?");
244: }
245:
246: NumberFormat numFormatter = NumberFormat.getInstance();
247: numFormatter.setMaximumFractionDigits(4);
248: numFormatter.setMinimumFractionDigits(4);
249:
250: int transactionCount = 5000;
251:
252: if (this .largeResults) {
253: transactionCount = 50;
254: }
255:
256: long begin = System.currentTimeMillis();
257:
258: for (int i = 0; i < transactionCount; i++) {
259: this .conn.setAutoCommit(false);
260: pStmtCheck.setInt(1, 1);
261: this .rs = pStmtCheck.executeQuery();
262:
263: while (this .rs.next()) {
264: this .rs.getInt(1);
265: }
266:
267: this .rs.close();
268:
269: if (!this .largeResults) {
270: pStmtLoad.setInt(1, 1);
271: }
272:
273: this .rs = pStmtLoad.executeQuery();
274:
275: if (this .rs.next()) {
276: int key = this .rs.getInt(1);
277:
278: if (!this .useColumnNames) {
279: pStmtStore.setInt(1, key); // priKey
280: pStmtStore.setInt(2, this .rs.getInt(2)); // fk1
281: pStmtStore.setInt(3, this .rs.getInt(3)); // fk2
282: pStmtStore.setTimestamp(4, this .rs.getTimestamp(4)); // dtField
283: pStmtStore.setString(5, this .rs.getString(5)); // charField1
284: pStmtStore.setString(6, this .rs.getString(7)); // charField2
285: pStmtStore.setString(7, this .rs.getString(7)); // charField3
286: pStmtStore.setString(8, this .rs.getString(8)); // charField4
287: pStmtStore.setInt(9, this .rs.getInt(9)); // intField1
288: pStmtStore.setInt(10, this .rs.getInt(10)); // intField2
289: pStmtStore.setInt(11, this .rs.getInt(11)); // intField3
290: pStmtStore.setInt(12, this .rs.getInt(12)); // intField4
291: pStmtStore.setDouble(13, this .rs.getDouble(13)); // doubleField1
292: pStmtStore.setDouble(14, this .rs.getDouble(14)); // doubleField2
293: pStmtStore.setDouble(15, this .rs.getDouble(15)); // doubleField3
294: pStmtStore.setDouble(16, this .rs.getDouble(16)); // doubleField4
295:
296: pStmtStore.setInt(17, key);
297: } else {
298: /*
299: * "UPDATE perfLoadStore SET " + "priKey = ?, " + "fk1 = ?, " +
300: * "fk2 = ?, " + "dtField = ?, " + "charField1 = ?, " +
301: * "charField2 = ?, " + "charField3 = ?, " + "charField4 = ?, " +
302: * "intField1 = ?, " + "intField2 = ?, " + "intField3 = ?, " +
303: * "intField4 = ?, " + "doubleField1 = ?," + "doubleField2 =
304: * ?," + "doubleField3 = ?," + "doubleField4 = ?" + " WHERE
305: * priKey=?");
306: */
307: pStmtStore.setInt(1, key); // priKey
308: pStmtStore.setInt(2, this .rs.getInt("fk1")); // fk1
309: pStmtStore.setInt(3, this .rs.getInt("fk2")); // fk2
310: pStmtStore.setTimestamp(4, this .rs
311: .getTimestamp("dtField")); // dtField
312: pStmtStore.setString(5, this .rs
313: .getString("charField1")); // charField1
314: pStmtStore.setString(6, this .rs
315: .getString("charField2")); // charField2
316: pStmtStore.setString(7, this .rs
317: .getString("charField3")); // charField3
318: pStmtStore.setString(8, this .rs
319: .getString("charField4")); // charField4
320: pStmtStore.setInt(9, this .rs.getInt("intField1")); // intField1
321: pStmtStore.setInt(10, this .rs.getInt("intField2")); // intField2
322: pStmtStore.setInt(11, this .rs.getInt("intField3")); // intField3
323: pStmtStore.setInt(12, this .rs.getInt("intField4")); // intField4
324: pStmtStore.setDouble(13, this .rs
325: .getDouble("doubleField1")); // doubleField1
326: pStmtStore.setDouble(14, this .rs
327: .getDouble("doubleField2")); // doubleField2
328: pStmtStore.setDouble(15, this .rs
329: .getDouble("doubleField3")); // doubleField3
330: pStmtStore.setDouble(16, this .rs
331: .getDouble("doubleField4")); // doubleField4
332:
333: pStmtStore.setInt(17, key);
334: }
335:
336: pStmtStore.executeUpdate();
337: }
338:
339: this .rs.close();
340:
341: this .conn.commit();
342: this .conn.setAutoCommit(true);
343: }
344:
345: pStmtStore.close();
346: pStmtCheck.close();
347: pStmtLoad.close();
348:
349: long end = System.currentTimeMillis();
350:
351: long timeElapsed = (end - begin);
352:
353: double timeElapsedSeconds = (double) timeElapsed / 1000;
354: double tps = transactionCount / timeElapsedSeconds;
355:
356: if (this .takeMeasurements) {
357: addResult(tps);
358: System.out.print("1 [ "
359: + numFormatter.format(getMeanValue()) + " ] ");
360: } else {
361: System.out.println("Warm-up: " + tps + " trans/sec");
362: }
363: }
364:
365: /**
366: * Runs the test 10 times to get JIT going, and GC going
367: *
368: * @throws Exception
369: * if an error occurs.
370: */
371: protected void warmUp() throws Exception {
372: try {
373: System.out.print("Warm-up period (10 iterations)");
374:
375: for (int i = 0; i < 10; i++) {
376: doOneIteration();
377: System.out.print(".");
378: }
379:
380: System.out.println();
381: System.out.println("Warm-up period ends");
382: System.out
383: .println("\nUnits for this test are transactions/sec.");
384: } catch (Exception ex) {
385: ex.printStackTrace();
386:
387: throw ex;
388: }
389: }
390: }
|