Java > Open Source Codes > com > mchange > v2 > c3p0 > test > C3P0BenchmarkApp


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags