LoadStorePerfTest


1   /*
2    Copyright (C) 2002-2004 MySQL AB
3   
4    This program is free software; you can redistribute it and/or modify
5    it under the terms of version 2 of the GNU General Public License as 
6    published by the Free Software Foundation.
7   
8    There are special exceptions to the terms and conditions of the GPL 
9    as it is applied to this software. View the full text of the 
10   exception in file EXCEPTIONS-CONNECTOR-J in the directory of this 
11   software distribution.
12  
13   This program is distributed in the hope that it will be useful,
14   but WITHOUT ANY WARRANTY; without even the implied warranty of
15   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16   GNU General Public License for more details.
17  
18   You should have received a copy of the GNU General Public License
19   along with this program; if not, write to the Free Software
20   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
21  
22  
23  
24   */
25  package testsuite.perf;
26  
27  import java.sql.PreparedStatement  ;
28  import java.sql.SQLException  ;
29  
30  import java.text.NumberFormat  ;
31  
32  import testsuite.BaseTestCase;
33  
34  /**
35   * Simple performance testing unit test.
36   * 
37   * @author Mark Matthews
38   */
39  public class LoadStorePerfTest extends BasePerfTest {
40      /** The table type to use (only for MySQL), 'HEAP' by default */
41      private String   tableType = "HEAP";
42  
43      private boolean takeMeasurements = false;
44  
45      private boolean useColumnNames = false;
46  
47      private boolean largeResults = false;
48  
49      /**
50       * Constructor for LoadStorePerfTest.
51       * 
52       * @param name
53       *            the name of the test to run
54       */
55      public LoadStorePerfTest(String   name) {
56          super(name);
57  
58          String   newTableType = System
59                  .getProperty("com.mysql.jdbc.test.tabletype");
60  
61          this.largeResults = "TRUE"
62                  .equalsIgnoreCase(System
63                          .getProperty("com.mysql.jdbc.testsuite.loadstoreperf.useBigResults"));
64  
65          if ((newTableType != null) && (newTableType.length() > 0)) {
66              this.tableType = newTableType;
67  
68              System.out.println("Using specified table type of '"
69                      + this.tableType + "'");
70          }
71      }
72  
73      /**
74       * Runs all tests in this test case
75       * 
76       * @param args
77       *            ignored
78       * 
79       * @throws Exception
80       *             if an error occurs
81       */
82      public static void main(String  [] args) throws Exception   {
83          new LoadStorePerfTest("test1000Transactions").run();
84      }
85  
86      /**
87       * @see junit.framework.TestCase#setUp()
88       */
89      public void setUp() throws Exception   {
90          super.setUp();
91  
92          try {
93              this.stmt.executeUpdate("DROP TABLE perfLoadStore");
94          } catch (SQLException   sqlEx) {
95              // ignore
96          }
97  
98          String   dateTimeType = "DATETIME";
99  
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, " + "fk2 INT NOT NULL, " + "dtField "
109                 + dateTimeType + ", " + "charField1 CHAR(32), "
110                 + "charField2 CHAR(32), " + "charField3 CHAR(32), "
111                 + "charField4 CHAR(32), " + "intField1 INT, "
112                 + "intField2 INT, " + "intField3 INT, " + "intField4 INT, "
113                 + "doubleField1 DECIMAL," + "doubleField2 DOUBLE,"
114                 + "doubleField3 DOUBLE," + "doubleField4 DOUBLE,"
115                 + "PRIMARY KEY (priKey))";
116 
117         if (BaseTestCase.dbUrl.indexOf("mysql") != -1) {
118             query += (" TYPE=" + this.tableType);
119         }
120 
121         this.stmt.executeUpdate(query);
122 
123         String   currentDateValue = "NOW()";
124 
125         if (BaseTestCase.dbUrl.indexOf("sqlserver") != -1) {
126             currentDateValue = "GETDATE()";
127         }
128 
129         if (BaseTestCase.dbUrl.indexOf("oracle") != -1) {
130             currentDateValue = "CURRENT_TIMESTAMP";
131         }
132 
133         int numLoops = 1;
134 
135         if (this.largeResults) {
136             numLoops = 32;
137         }
138 
139         System.out.println("Inserting " + numLoops + " rows to retrieve...");
140 
141         for (int i = 0; i < numLoops; i++) {
142             this.stmt.executeUpdate("INSERT INTO perfLoadStore (" + "priKey, "
143                     + "fk1, " + "fk2, " + "dtField, " + "charField1, "
144                     + "charField2, " + "charField3, " + "charField4, "
145                     + "intField1, " + "intField2, " + "intField3, "
146                     + "intField4, " + "doubleField1," + "doubleField2,"
147                     + "doubleField3," + "doubleField4" + ") VALUES (" + i + "," // priKey
148                     + "2," // fk1
149                     + "3," // fk2
150                     + currentDateValue + "," // dtField
151                     + "'0123456789ABCDEF0123456789ABCDEF'," // charField1
152                     + "'0123456789ABCDEF0123456789ABCDEF'," // charField2
153                     + "'0123456789ABCDEF0123456789ABCDEF'," // charField3
154                     + "'0123456789ABCDEF0123456789ABCDEF'," // charField4
155                     + "7," // intField1
156                     + "8," // intField2
157                     + "9," // intField3
158                     + "10," // intField4
159                     + "1.20," // doubleField1
160                     + "2.30," // doubleField2
161                     + "3.40," // doubleField3
162                     + "4.50" // doubleField4
163                     + ")");
164         }
165     }
166 
167     /**
168      * @see junit.framework.TestCase#tearDown()
169      */
170     public void tearDown() throws Exception   {
171         try {
172             this.stmt.executeUpdate("DROP TABLE perfLoadStore");
173         } catch (SQLException   sqlEx) {
174             // ignore
175         }
176 
177         super.tearDown();
178     }
179 
180     /**
181      * Tests and times 1000 load/store type transactions
182      * 
183      * @throws Exception
184      *             if an error occurs
185      */
186     public void test1000Transactions() throws Exception   {
187         this.takeMeasurements = false;
188         warmUp();
189         this.takeMeasurements = true;
190         doIterations(29);
191 
192         reportResults("\n\nResults for instance # 1: ");
193     }
194 
195     /**
196      * Runs one iteration of the test.
197      * 
198      * @see testsuite.perf.BasePerfTest#doOneIteration()
199      */
200     protected void doOneIteration() throws Exception   {
201         PreparedStatement   pStmtStore = this.conn
202                 .prepareStatement("UPDATE perfLoadStore SET " + "priKey = ?, "
203                         + "fk1 = ?, " + "fk2 = ?, " + "dtField = ?, "
204                         + "charField1 = ?, " + "charField2 = ?, "
205                         + "charField3 = ?, " + "charField4 = ?, "
206                         + "intField1 = ?, " + "intField2 = ?, "
207                         + "intField3 = ?, " + "intField4 = ?, "
208                         + "doubleField1 = ?," + "doubleField2 = ?,"
209                         + "doubleField3 = ?," + "doubleField4 = ?"
210                         + " WHERE priKey=?");
211         PreparedStatement   pStmtCheck = this.conn
212                 .prepareStatement("SELECT COUNT(*) FROM perfLoadStore WHERE priKey=?");
213         PreparedStatement   pStmtLoad = null;
214 
215         if (this.largeResults) {
216             pStmtLoad = this.conn.prepareStatement("SELECT " + "priKey, "
217                     + "fk1, " + "fk2, " + "dtField, " + "charField1, "
218                     + "charField2, " + "charField3, " + "charField4, "
219                     + "intField1, " + "intField2, " + "intField3, "
220                     + "intField4, " + "doubleField1," + "doubleField2, "
221                     + "doubleField3," + "doubleField4" + " FROM perfLoadStore");
222         } else {
223             pStmtLoad = this.conn.prepareStatement("SELECT " + "priKey, "
224                     + "fk1, " + "fk2, " + "dtField, " + "charField1, "
225                     + "charField2, " + "charField3, " + "charField4, "
226                     + "intField1, " + "intField2, " + "intField3, "
227                     + "intField4, " + "doubleField1," + "doubleField2, "
228                     + "doubleField3," + "doubleField4"
229                     + " FROM perfLoadStore WHERE priKey=?");
230         }
231 
232         NumberFormat   numFormatter = NumberFormat.getInstance();
233         numFormatter.setMaximumFractionDigits(4);
234         numFormatter.setMinimumFractionDigits(4);
235 
236         int transactionCount = 5000;
237 
238         if (this.largeResults) {
239             transactionCount = 50;
240         }
241 
242         long begin = System.currentTimeMillis();
243 
244         for (int i = 0; i < transactionCount; i++) {
245             this.conn.setAutoCommit(false);
246             pStmtCheck.setInt(1, 1);
247             this.rs = pStmtCheck.executeQuery();
248 
249             while (this.rs.next()) {
250                 this.rs.getInt(1);
251             }
252 
253             this.rs.close();
254 
255             if (!this.largeResults) {
256                 pStmtLoad.setInt(1, 1);
257             }
258 
259             this.rs = pStmtLoad.executeQuery();
260 
261             if (this.rs.next()) {
262                 int key = this.rs.getInt(1);
263 
264                 if (!this.useColumnNames) {
265                     pStmtStore.setInt(1, key); // priKey
266                     pStmtStore.setInt(2, this.rs.getInt(2)); // fk1
267                     pStmtStore.setInt(3, this.rs.getInt(3)); // fk2
268                     pStmtStore.setTimestamp(4, this.rs.getTimestamp(4)); // dtField
269                     pStmtStore.setString(5, this.rs.getString(5)); // charField1
270                     pStmtStore.setString(6, this.rs.getString(7)); // charField2
271                     pStmtStore.setString(7, this.rs.getString(7)); // charField3
272                     pStmtStore.setString(8, this.rs.getString(8)); // charField4
273                     pStmtStore.setInt(9, this.rs.getInt(9)); // intField1
274                     pStmtStore.setInt(10, this.rs.getInt(10)); // intField2
275                     pStmtStore.setInt(11, this.rs.getInt(11)); // intField3
276                     pStmtStore.setInt(12, this.rs.getInt(12)); // intField4
277                     pStmtStore.setDouble(13, this.rs.getDouble(13)); // doubleField1
278                     pStmtStore.setDouble(14, this.rs.getDouble(14)); // doubleField2
279                     pStmtStore.setDouble(15, this.rs.getDouble(15)); // doubleField3
280                     pStmtStore.setDouble(16, this.rs.getDouble(16)); // doubleField4
281 
282                     pStmtStore.setInt(17, key);
283                 } else {
284                     /*
285                      * "UPDATE perfLoadStore SET " + "priKey = ?, " + "fk1 = ?, " +
286                      * "fk2 = ?, " + "dtField = ?, " + "charField1 = ?, " +
287                      * "charField2 = ?, " + "charField3 = ?, " + "charField4 = ?, " +
288                      * "intField1 = ?, " + "intField2 = ?, " + "intField3 = ?, " +
289                      * "intField4 = ?, " + "doubleField1 = ?," + "doubleField2 =
290                      * ?," + "doubleField3 = ?," + "doubleField4 = ?" + " WHERE
291                      * priKey=?");
292                      */
293                     pStmtStore.setInt(1, key); // priKey
294                     pStmtStore.setInt(2, this.rs.getInt("fk1")); // fk1
295                     pStmtStore.setInt(3, this.rs.getInt("fk2")); // fk2
296                     pStmtStore.setTimestamp(4, this.rs.getTimestamp("dtField")); // dtField
297                     pStmtStore.setString(5, this.rs.getString("charField1")); // charField1
298                     pStmtStore.setString(6, this.rs.getString("charField2")); // charField2
299                     pStmtStore.setString(7, this.rs.getString("charField3")); // charField3
300                     pStmtStore.setString(8, this.rs.getString("charField4")); // charField4
301                     pStmtStore.setInt(9, this.rs.getInt("intField1")); // intField1
302                     pStmtStore.setInt(10, this.rs.getInt("intField2")); // intField2
303                     pStmtStore.setInt(11, this.rs.getInt("intField3")); // intField3
304                     pStmtStore.setInt(12, this.rs.getInt("intField4")); // intField4
305                     pStmtStore.setDouble(13, this.rs.getDouble("doubleField1")); // doubleField1
306                     pStmtStore.setDouble(14, this.rs.getDouble("doubleField2")); // doubleField2
307                     pStmtStore.setDouble(15, this.rs.getDouble("doubleField3")); // doubleField3
308                     pStmtStore.setDouble(16, this.rs.getDouble("doubleField4")); // doubleField4
309 
310                     pStmtStore.setInt(17, key);
311                 }
312 
313                 pStmtStore.executeUpdate();
314             }
315 
316             this.rs.close();
317 
318             this.conn.commit();
319             this.conn.setAutoCommit(true);
320         }
321 
322         pStmtStore.close();
323         pStmtCheck.close();
324         pStmtLoad.close();
325 
326         long end = System.currentTimeMillis();
327 
328         long timeElapsed = (end - begin);
329 
330         double timeElapsedSeconds = (double) timeElapsed / 1000;
331         double tps = transactionCount / timeElapsedSeconds;
332 
333         if (this.takeMeasurements) {
334             addResult(tps);
335             System.out.print("1 [ " + numFormatter.format(getMeanValue())
336                     + " ] ");
337         } else {
338             System.out.println("Warm-up: " + tps + " trans/sec");
339         }
340     }
341 
342     /**
343      * Runs the test 10 times to get JIT going, and GC going
344      * 
345      * @throws Exception
346      *             if an error occurs.
347      */
348     protected void warmUp() throws Exception   {
349         try {
350             System.out.print("Warm-up period (10 iterations)");
351 
352             for (int i = 0; i < 10; i++) {
353                 doOneIteration();
354                 System.out.print(".");
355             }
356 
357             System.out.println();
358             System.out.println("Warm-up period ends");
359             System.out.println("\nUnits for this test are transactions/sec.");
360         } catch (Exception   ex) {
361             ex.printStackTrace();
362 
363             throw ex;
364         }
365     }
366 }
367
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags