001: /*
002: * Distributed as part of c3p0 v.0.9.1.2
003: *
004: * Copyright (C) 2005 Machinery For Change, Inc.
005: *
006: * Author: Steve Waldman <swaldman@mchange.com>
007: *
008: * This library is free software; you can redistribute it and/or modify
009: * it under the terms of the GNU Lesser General Public License version 2.1, as
010: * published by the Free Software Foundation.
011: *
012: * This software is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
015: * GNU Lesser General Public License for more details.
016: *
017: * You should have received a copy of the GNU Lesser General Public License
018: * along with this software; see the file LICENSE. If not, write to the
019: * Free Software Foundation, Inc., 59 Temple Place, Suite 330,
020: * Boston, MA 02111-1307, USA.
021: */
022:
023: package com.mchange.v2.c3p0.test;
024:
025: import java.util.*;
026: import java.sql.*;
027: import javax.sql.*;
028: import com.mchange.v2.c3p0.*;
029: import com.mchange.v1.db.sql.*;
030: import com.mchange.v2.c3p0.DriverManagerDataSource;
031:
032: public final class C3P0BenchmarkApp {
033: final static String EMPTY_TABLE_CREATE = "CREATE TABLE emptyyukyuk (a varchar(8), b varchar(8))";
034: final static String EMPTY_TABLE_SELECT = "SELECT * FROM emptyyukyuk";
035: final static String EMPTY_TABLE_DROP = "DROP TABLE emptyyukyuk";
036:
037: final static String EMPTY_TABLE_CONDITIONAL_SELECT = "SELECT * FROM emptyyukyuk where a = ?";
038:
039: final static String N_ENTRY_TABLE_CREATE = "CREATE TABLE n_entryyukyuk (a INTEGER)";
040: final static String N_ENTRY_TABLE_INSERT = "INSERT INTO n_entryyukyuk VALUES ( ? )";
041: final static String N_ENTRY_TABLE_SELECT = "SELECT * FROM n_entryyukyuk";
042: final static String N_ENTRY_TABLE_DROP = "DROP TABLE n_entryyukyuk";
043:
044: //final static int NUM_ITERATIONS = 20;
045: final static int NUM_ITERATIONS = 2000;
046:
047: //final static int NUM_ITERATIONS = 10000;
048: //final static int NUM_ITERATIONS = 20000;
049: //final static int NUM_ITERATIONS = 100000;
050:
051: public static void main(String[] argv) {
052: if (argv.length > 0) {
053: System.err
054: .println(C3P0BenchmarkApp.class.getName()
055: + " now requires no args. Please set everything in standard c3p0 config files.");
056: return;
057: }
058:
059: // com.mchange.v2.log.MLog.getLogger( C3P0BenchmarkApp.class ).info("this is some info.");
060: // com.mchange.v2.log.MLog.getLogger( C3P0BenchmarkApp.class ).log(com.mchange.v2.log.MLevel.WARNING, "this is a warning.", new Exception("test"));
061: // com.mchange.v2.log.MLog.getLogger( C3P0BenchmarkApp.class ).log(com.mchange.v2.log.MLevel.FINE, "this is fine.");
062:
063: // System.getProperties().put("sprong", java.awt.Color.blue);
064: // System.getProperties().put(java.awt.Color.blue, "sprong");
065:
066: DataSource ds_unpooled = null;
067: DataSource ds_pooled = null;
068: try {
069: /*
070: String jdbc_url = null;
071: String username = null;
072: String password = null;
073: if (argv.length == 3)
074: {
075: jdbc_url = argv[0];
076: username = argv[1];
077: password = argv[2];
078: }
079: else if (argv.length == 1)
080: {
081: jdbc_url = argv[0];
082: username = null;
083: password = null;
084: }
085: else
086: usage();
087:
088: if (! jdbc_url.startsWith("jdbc:") )
089: usage();
090: */
091:
092: // ds_unpooled = DriverManagerDataSourceFactory.create(jdbc_url, username, password);
093: // ds_pooled
094: // // = PoolBackedDataSourceFactory.create(jdbc_url, username, password);
095: // = PoolBackedDataSourceFactory.create(jdbc_url,
096: // username,
097: // password,
098: // 5,
099: // 20,
100: // 5,
101: // 0,
102: // 100 );
103: //ds_unpooled = DataSources.unpooledDataSource(jdbc_url, username, password);
104: //ds_pooled = DataSources.pooledDataSource( ds_unpooled );
105: ds_unpooled = new DriverManagerDataSource();
106:
107: //DataSource ds_unpooled_screwy = C3P0TestUtils.unreliableCommitDataSource( ds_unpooled );
108: //ds_pooled = DataSources.pooledDataSource( ds_unpooled_screwy );
109:
110: // PoolConfig pc = new PoolConfig();
111: // pc.setMaxStatements(200);
112: // pc.setCheckoutTimeout(500);
113: // ds_pooled = DataSources.pooledDataSource( ds_unpooled, pc );
114: // ds_pooled = DataSources.pooledDataSource( ds_unpooled, "foo", "goo" );
115:
116: //ds_pooled = DataSources.pooledDataSource(ds_unpooled);
117:
118: //ComboPooledDataSource cpds = new ComboPooledDataSource("dumbTestConfig");
119: ComboPooledDataSource cpds = new ComboPooledDataSource();
120: //cpds.setJdbcUrl( jdbc_url );
121: //cpds.setUser( username );
122: //cpds.setPassword( password );
123: ds_pooled = cpds;
124:
125: // ComboPooledDataSource cpds2 = new ComboPooledDataSource();
126: // System.err.println("Made second ComboPooledDataSource.");
127: // cpds.getNumIdleConnectionsDefaultUser();
128: // cpds2.getNumIdleConnectionsDefaultUser();
129:
130: // Properties badProps = new Properties();
131: // badProps.put("badprop", null);
132: // DataSource appendix = DataSources.pooledDataSource(ds_unpooled, badProps);
133:
134: create(ds_pooled);
135:
136: System.out.println("Please wait. Tests can be very slow.");
137: List l = new ArrayList();
138: l.add(new ConnectionAcquisitionTest());
139: l.add(new StatementCreateTest());
140: l.add(new StatementEmptyTableSelectTest());
141: //l.add( new DataBaseMetaDataListNonexistentTablesTest() );
142: l.add(new PreparedStatementEmptyTableSelectTest());
143: l.add(new PreparedStatementAcquireTest());
144: l.add(new ResultSetReadTest());
145: l.add(new FiveThreadPSQueryTestTest());
146: for (int i = 0, len = l.size(); i < len; ++i)
147: ((Test) l.get(i)).perform(ds_unpooled, ds_pooled,
148: NUM_ITERATIONS);
149: } catch (Throwable t) {
150: System.err.print("Aborting tests on Throwable -- ");
151: t.printStackTrace();
152: if (t instanceof Error)
153: throw (Error) t;
154: } finally {
155: //System.err.println( "pooled data sources: " + C3P0Registry.getPooledDataSources() );
156:
157: try {
158: drop(ds_pooled);
159: } catch (Exception e) {
160: e.printStackTrace();
161: }
162:
163: try {
164: DataSources.destroy(ds_pooled);
165: } catch (Exception e) {
166: e.printStackTrace();
167: }
168:
169: try {
170: DataSources.destroy(ds_unpooled);
171: } catch (Exception e) {
172: e.printStackTrace();
173: }
174: }
175: }
176:
177: /*
178: private static void usage()
179: {
180: System.err.println("java " +
181: "-Djdbc.drivers=<comma_sep_list_of_drivers> " +
182: C3P0BenchmarkApp.class.getName() +
183: " <jdbc_url> [<username> <password>]" );
184: System.exit(-1);
185: }
186: */
187: static void create(DataSource ds) throws SQLException {
188: System.err.println("Creating test schema.");
189: Connection con = null;
190: PreparedStatement ps1 = null;
191: PreparedStatement ps2 = null;
192: PreparedStatement ps3 = null;
193: try {
194: con = ds.getConnection();
195: ps1 = con.prepareStatement(EMPTY_TABLE_CREATE);
196: ps2 = con.prepareStatement(N_ENTRY_TABLE_CREATE);
197: ps3 = con.prepareStatement(N_ENTRY_TABLE_INSERT);
198:
199: ps1.executeUpdate();
200: ps2.executeUpdate();
201:
202: for (int i = 0; i < NUM_ITERATIONS; ++i) {
203: ps3.setInt(1, i);
204: ps3.executeUpdate();
205: System.err.print('.');
206: }
207: System.err.println();
208: System.err.println("Test schema created.");
209: } finally {
210: StatementUtils.attemptClose(ps1);
211: StatementUtils.attemptClose(ps2);
212: StatementUtils.attemptClose(ps3);
213: ConnectionUtils.attemptClose(con);
214: }
215: }
216:
217: static void drop(DataSource ds) throws SQLException {
218: Connection con = null;
219: PreparedStatement ps1 = null;
220: PreparedStatement ps2 = null;
221: try {
222: con = ds.getConnection();
223: ps1 = con.prepareStatement(EMPTY_TABLE_DROP);
224: ps2 = con.prepareStatement(N_ENTRY_TABLE_DROP);
225:
226: ps1.executeUpdate();
227: ps2.executeUpdate();
228:
229: // should be superfluous 'cuz should be autocommit
230: //con.commit();
231:
232: System.err.println("Test schema dropped.");
233: } finally {
234: StatementUtils.attemptClose(ps1);
235: StatementUtils.attemptClose(ps2);
236: ConnectionUtils.attemptClose(con);
237: }
238: }
239:
240: static abstract class Test {
241: String name;
242:
243: Test(String name) {
244: this .name = name;
245: }
246:
247: public void perform(DataSource unpooled, DataSource pooled,
248: int iterations) throws Exception {
249: double msecs_unpooled = test(unpooled, iterations)
250: / ((double) iterations);
251: double msecs_pooled = test(pooled, iterations)
252: / ((double) iterations);
253: System.out.println(name + " [ " + iterations
254: + " iterations ]:");
255: System.out.println('\t' + "unpooled: " + msecs_unpooled
256: + " msecs");
257: System.out.println('\t' + " pooled: " + msecs_pooled
258: + " msecs");
259: System.out.println('\t' + "speed-up factor: "
260: + msecs_unpooled / msecs_pooled + " times");
261: System.out.println('\t' + "speed-up absolute: "
262: + (msecs_unpooled - msecs_pooled) + " msecs");
263: System.out.println();
264:
265: // PooledDataSource pds = (PooledDataSource) pooled;
266: // System.out.println( pds.getNumConnections() );
267: // System.out.println( pds.getNumIdleConnections() );
268: // System.out.println( pds.getNumBusyConnections() );
269: // System.out.println( pds.getNumConnectionsAllAuths() );
270: }
271:
272: protected abstract long test(DataSource ds, int n)
273: throws Exception;
274: }
275:
276: static class ConnectionAcquisitionTest extends Test {
277: ConnectionAcquisitionTest() {
278: super ("Connection Acquisition and Cleanup");
279: }
280:
281: protected long test(DataSource ds, int n) throws Exception {
282: long start;
283: long end;
284:
285: start = System.currentTimeMillis();
286: for (int i = 0; i < n; ++i) {
287: Connection con = null;
288: try {
289: con = ds.getConnection();
290: } finally {
291: ConnectionUtils.attemptClose(con);
292: }
293: //System.err.print(i + "\t");
294: }
295: end = System.currentTimeMillis();
296: return end - start;
297: }
298: }
299:
300: static class StatementCreateTest extends Test {
301: StatementCreateTest() {
302: super ("Statement Creation and Cleanup");
303: }
304:
305: protected long test(DataSource ds, int n) throws SQLException {
306: Connection con = null;
307: try {
308: con = ds.getConnection();
309: return test(con, n);
310: } finally {
311: ConnectionUtils.attemptClose(con);
312: }
313: //{}
314: }
315:
316: long test(Connection con, int n) throws SQLException {
317: long start;
318: long end;
319:
320: Statement stmt = null;
321: start = System.currentTimeMillis();
322: for (int i = 0; i < n; ++i) {
323: try {
324: stmt = con.createStatement();
325: } finally {
326: StatementUtils.attemptClose(stmt);
327: }
328: }
329: end = System.currentTimeMillis();
330: return end - start;
331: }
332: }
333:
334: static class StatementEmptyTableSelectTest extends Test {
335: StatementEmptyTableSelectTest() {
336: super (
337: "Empty Table Statement Select (on a single Statement)");
338: }
339:
340: protected long test(DataSource ds, int n) throws SQLException {
341: Connection con = null;
342: Statement stmt = null;
343: try {
344: con = ds.getConnection();
345: stmt = con.createStatement();
346: //System.err.println( stmt.getClass().getName() );
347: return test(stmt, n);
348: } finally {
349: StatementUtils.attemptClose(stmt);
350: ConnectionUtils.attemptClose(con);
351: }
352: }
353:
354: long test(Statement stmt, int n) throws SQLException {
355: long start;
356: long end;
357:
358: start = System.currentTimeMillis();
359: for (int i = 0; i < n; ++i)
360: stmt.executeQuery(EMPTY_TABLE_SELECT).close();
361: end = System.currentTimeMillis();
362: return end - start;
363: }
364: }
365:
366: static class DataBaseMetaDataListNonexistentTablesTest extends Test {
367: DataBaseMetaDataListNonexistentTablesTest() {
368: super ("DataBaseMetaDataListNonexistentTablesTest");
369: }
370:
371: protected long test(DataSource ds, int n) throws SQLException {
372: Connection con = null;
373: Statement stmt = null;
374: try {
375: con = ds.getConnection();
376: return test(con, n);
377: } finally {
378: StatementUtils.attemptClose(stmt);
379: ConnectionUtils.attemptClose(con);
380: }
381: }
382:
383: long test(Connection con, int n) throws SQLException {
384: ResultSet rs = null;
385:
386: try {
387: long start;
388: long end;
389:
390: start = System.currentTimeMillis();
391: for (int i = 0; i < n; ++i)
392: rs = con.getMetaData().getTables(null, null,
393: "PROBABLYNOT", new String[] { "TABLE" });
394: end = System.currentTimeMillis();
395: return end - start;
396: } finally {
397: ResultSetUtils.attemptClose(rs);
398: }
399: }
400: }
401:
402: static class PreparedStatementAcquireTest extends Test {
403: PreparedStatementAcquireTest() {
404: super (
405: "Acquire and Cleanup a PreparedStatement (same statement, many times)");
406: }
407:
408: protected long test(DataSource ds, int n) throws SQLException {
409: long start;
410: long end;
411:
412: Connection con = null;
413: PreparedStatement pstmt = null;
414: try {
415: con = ds.getConnection();
416: start = System.currentTimeMillis();
417: for (int i = 0; i < n; ++i) {
418: try {
419: pstmt = con
420: .prepareStatement(EMPTY_TABLE_CONDITIONAL_SELECT);
421: }
422:
423: /*
424: Leftover random abuses from ad hoc testing...
425:
426: {
427: pstmt = con.prepareStatement(EMPTY_TABLE_CONDITIONAL_SELECT,
428: ResultSet.TYPE_SCROLL_SENSITIVE,
429: ResultSet.CONCUR_UPDATABLE,
430: ResultSet.HOLD_CURSORS_OVER_COMMIT);
431: }
432:
433:
434: { pstmt = con.prepareStatement(N_ENTRY_TABLE_INSERT); }
435: */
436: finally {
437: StatementUtils.attemptClose(pstmt);
438: }
439: }
440: end = System.currentTimeMillis();
441: return end - start;
442: } finally {
443: ConnectionUtils.attemptClose(con);
444: }
445: }
446: }
447:
448: static class PreparedStatementEmptyTableSelectTest extends Test {
449: PreparedStatementEmptyTableSelectTest() {
450: super (
451: "Empty Table PreparedStatement Select (on a single PreparedStatement)");
452: }
453:
454: protected long test(DataSource ds, int n) throws SQLException {
455: Connection con = null;
456: PreparedStatement pstmt = null;
457: try {
458: con = ds.getConnection();
459: pstmt = con.prepareStatement(EMPTY_TABLE_SELECT);
460:
461: // Leftover from ad-hoc testing...
462: //
463: // pstmt = con.prepareStatement(EMPTY_TABLE_SELECT,
464: // ResultSet.TYPE_SCROLL_SENSITIVE,
465: // ResultSet.CONCUR_UPDATABLE,
466: // ResultSet.HOLD_CURSORS_OVER_COMMIT);
467: return test(pstmt, n);
468: } finally {
469: StatementUtils.attemptClose(pstmt);
470: ConnectionUtils.attemptClose(con);
471: }
472: }
473:
474: long test(PreparedStatement pstmt, int n) throws SQLException {
475: long start;
476: long end;
477:
478: start = System.currentTimeMillis();
479: for (int i = 0; i < n; ++i)
480: pstmt.executeQuery().close();
481: end = System.currentTimeMillis();
482: return end - start;
483: }
484: }
485:
486: static class ResultSetReadTest extends Test {
487: ResultSetReadTest() {
488: super ("Reading one row / one entry from a result set");
489: }
490:
491: protected long test(DataSource ds, int n) throws SQLException {
492: if (n > 10000)
493: throw new IllegalArgumentException("10K max.");
494:
495: long start;
496: long end;
497:
498: Connection con = null;
499: PreparedStatement pstmt = null;
500: ResultSet rs = null;
501:
502: try {
503: con = ds.getConnection();
504: pstmt = con.prepareStatement(N_ENTRY_TABLE_SELECT);
505: rs = pstmt.executeQuery();
506:
507: start = System.currentTimeMillis();
508: for (int i = 0; i < n; ++i) {
509: if (!rs.next())
510: System.err.println("huh?");
511: rs.getInt(1);
512: }
513: end = System.currentTimeMillis();
514: return end - start;
515: } finally {
516: ResultSetUtils.attemptClose(rs);
517: StatementUtils.attemptClose(pstmt);
518: ConnectionUtils.attemptClose(con);
519: }
520: }
521: }
522:
523: static class FiveThreadPSQueryTestTest extends Test {
524: // only for stupid test to simulate (illegal) concurrent access to a Statement
525: // volatile Statement stmt;
526:
527: FiveThreadPSQueryTestTest() {
528: super (
529: "Five threads getting a connection, executing a query, "
530: + System.getProperty("line.separator")
531: + "and retrieving results concurrently via a prepared statement (in a transaction).");
532: }
533:
534: protected long test(final DataSource ds, final int n)
535: throws Exception {
536: class QueryThread extends Thread {
537: QueryThread(int num) {
538: super ("QueryThread-" + num);
539: }
540:
541: public void run() {
542: Connection con = null;
543: PreparedStatement pstmt = null;
544: ResultSet rs = null;
545:
546: for (int i = 0; i < (n / 5); ++i) {
547: try {
548: con = ds.getConnection();
549:
550: // System.err.println("before txn isolation set: " + con.getTransactionIsolation());
551: // con.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );
552: // //con.setTransactionIsolation( Connection.TRANSACTION_READ_UNCOMMITTED );
553: // System.err.println("after txn isolation set: " + con.getTransactionIsolation());
554:
555: con.setAutoCommit(false);
556:
557: pstmt = con
558: .prepareStatement(EMPTY_TABLE_CONDITIONAL_SELECT);
559:
560: // if (Math.random() < 0.5)
561: // stmt = pstmt;
562: // else if (stmt != null)
563: // stmt.getResultSet();
564:
565: // if (Math.random() < 0.1 && con instanceof C3P0ProxyConnection)
566: // con.close();
567:
568: pstmt.setString(1, "boo");
569: rs = pstmt.executeQuery();
570: while (rs.next())
571: System.err
572: .println("Huh?? Empty table has values?");
573: //System.out.println(this + " " + i);
574:
575: // if (ds instanceof PooledDataSource)
576: // {
577: // PooledDataSource pds = (PooledDataSource) ds;
578: // System.err.println("numConnections: " + pds.getNumConnections() );
579: // System.err.println("numIdleConnections: " + pds.getNumIdleConnections() );
580: // System.err.println("numBusyConnections: " + pds.getNumBusyConnections() );
581: // System.err.println();
582: // }
583:
584: con.commit();
585: } catch (Exception e) {
586: System.err
587: .print("FiveThreadPSQueryTestTest exception -- ");
588: e.printStackTrace();
589: try {
590: if (con != null)
591: con.rollback();
592: } catch (SQLException e2) {
593: System.err
594: .print("Rollback on exception failed! -- ");
595: e2.printStackTrace();
596: }
597: } finally {
598: ResultSetUtils.attemptClose(rs);
599: StatementUtils.attemptClose(pstmt);
600: ConnectionUtils.attemptClose(con);
601: con = null;
602:
603: // StatementUtils.attemptClose( pstmt ); //dup close
604: // ConnectionUtils.attemptClose( con ); //dup close
605: // try { System.err.println( pstmt.getConnection() ); } catch (Exception e) {e.printStackTrace();}
606: // ResultSetUtils.attemptClose( rs );
607: }
608: }
609: //System.out.println(this + " finished.");
610: }
611: }
612:
613: long start = System.currentTimeMillis();
614:
615: Thread[] ts = new Thread[5];
616: for (int i = 0; i < 5; ++i) {
617: ts[i] = new QueryThread(i);
618: ts[i].start();
619: }
620: for (int i = 0; i < 5; ++i)
621: ts[i].join();
622:
623: return System.currentTimeMillis() - start;
624: }
625:
626: }
627:
628: // static class TenByTwoResultSetReadTest extends Test
629: // {
630: // TenByTwoResultSetReadTest()
631: // { super("Reading all entryies from a 10 row 2 col result set"); }
632:
633: // protected long test(DataSource ds, int n) throws SQLException
634: // {
635: // long start;
636: // long end;
637:
638: // long start_ctrl;
639: // long end_ctrl;
640:
641: // Connection con = null;
642: // PreparedStatement pstmt = null;
643: // ResultSet rs = null;
644:
645: // start = System.currentTimeMillis();
646: // for (int i = 0; i < n; ++i)
647: // {
648: // try
649: // {
650: // con = ds.getConnection();
651: // pstmt = con.prepareStatement(N_ENTRY_TABLE_SELECT);
652: // rs = pstmt.executeQuery();
653: // while( rs.next() )
654: // {
655: // rs.getInt(1);
656: // rs.getInt(2);
657: // }
658: // }
659: // finally
660: // {
661: // ResultSetUtils.attemptClose( rs );
662: // StatementUtils.attemptClose( pstmt );
663: // ConnectionUtils.attemptClose( con );
664: // }
665: // }
666: // end = System.currentTimeMillis();
667:
668: // start_ctrl = System.currentTimeMillis();
669: // for (int i = 0; i < n; ++i)
670: // {
671: // try
672: // {
673: // con = ds.getConnection();
674: // pstmt = con.prepareStatement(N_ENTRY_TABLE_SELECT);
675: // rs = pstmt.executeQuery();
676: // }
677: // finally
678: // {
679: // ResultSetUtils.attemptClose( rs );
680: // StatementUtils.attemptClose( pstmt );
681: // ConnectionUtils.attemptClose( con );
682: // }
683: // }
684: // end_ctrl = System.currentTimeMillis();
685:
686: // return (end - start) - (end_ctrl - start_ctrl);
687: // }
688: // }
689: }
|