OmniSciDB  c07336695a
Benchmark.java
Go to the documentation of this file.
1 /*
2  * Copyright 2017 MapD Technologies, Inc.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 
17 package com.mapd.bench;
18 
19 // STEP 1. Import required packages
21 
22 import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics;
23 import org.slf4j.Logger;
24 import org.slf4j.LoggerFactory;
25 
26 import java.io.BufferedReader;
27 import java.io.FileNotFoundException;
28 import java.io.FileReader;
29 import java.io.IOException;
30 import java.sql.*;
31 import java.util.ArrayList;
32 import java.util.List;
33 
34 public class Benchmark {
35  final static Logger logger = LoggerFactory.getLogger(Benchmark.class);
36 
37  // JDBC driver name and database URL
38  static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";
39  static final String DB_URL = "jdbc:omnisci:localhost:6274:mapd";
40 
41  // Database credentials
42  static final String USER = "admin";
43  static final String PASS = "HyperInteractive";
44 
45  private String driver;
46  private String url;
47  private String iUser;
48  private String iPasswd;
49 
51  "%3s, %8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s,%8s";
52  private String header2 = String.format(headDescriptor,
53  "QRY",
54  "T-Avg",
55  "T-Min",
56  "T-Max",
57  "T-85%",
58  "E-Avg",
59  "E-Min",
60  "E-Max",
61  "E-85%",
62  "E-25%",
63  "E-StdD",
64  "J-Avg",
65  "J-Min",
66  "J-Max",
67  "J-85%",
68  "I-Avg",
69  "I-Min",
70  "I-Max",
71  "I-85%",
72  "F-Exec",
73  "F-jdbc",
74  "F-iter",
75  "ITER",
76  "Total",
77  "Account");
79  "Q%02d, %8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8.1f,%8d,%8d,%8d,%8d,%8d,%8d";
80 
81  public static void main(String[] args) {
82  Benchmark bm = new Benchmark();
83  bm.doWork(args, 1);
84  }
85 
86  void doWork(String[] args, int query) {
87  // Grab parameters from args
88  // parm0 number of iterations per query
89  // parm1 file containing sql queries {contains quoted query, expected result
90  // count]
91  // parm2 optional JDBC Driver class name
92  // parm3 optional DB URL
93  // parm4 optionsl user
94  // parm5 optional passwd
95  int iterations = Integer.valueOf(args[0]);
96  logger.debug("Iterations per query is " + iterations);
97 
98  String queryFile = args[1];
99 
100  // int expectedResults = Integer.valueOf(args[2]);
101  driver = (args.length > 2) ? args[2] : JDBC_DRIVER;
102  url = (args.length > 3) ? args[3] : DB_URL;
103  iUser = (args.length > 4) ? args[4] : USER;
104  iPasswd = (args.length > 5) ? args[5] : PASS;
105 
106  // register the driver
107  try {
108  // Register JDBC driver
109  Class.forName(driver);
110  } catch (ClassNotFoundException ex) {
111  logger.error("Could not load class " + driver + " " + ex.getMessage());
112  System.exit(1);
113  }
114 
115  // read from query file and execute queries
116  String sCurrentLine;
117  List<String> resultArray = new ArrayList();
118  BufferedReader br;
119  try {
120  br = new BufferedReader(new FileReader(queryFile));
121  int qCount = 1;
122 
123  while ((sCurrentLine = br.readLine()) != null) {
124  int expected = 0;
125  String sqlQuery = null;
126  // find the last comma and then grab the rest as sql
127  for (int i = sCurrentLine.length(); i > 0; i--) {
128  if (sCurrentLine.charAt(i - 1) == ',') {
129  // found the comma
130  expected = Integer.valueOf(sCurrentLine.substring(i).trim());
131  sqlQuery = sCurrentLine.substring(0, i - 1).trim().substring(1);
132  break;
133  }
134  }
135  // remove final "
136  sqlQuery = sqlQuery.substring(0, sqlQuery.length() - 1);
137 
138  System.out.println(String.format("Q%02d %s", qCount, sqlQuery));
139 
140  resultArray.add(executeQuery(sqlQuery, expected, iterations, qCount));
141 
142  qCount++;
143  }
144  } catch (FileNotFoundException ex) {
145  logger.error("Could not find file " + queryFile + " " + ex.getMessage());
146  System.exit(2);
147  } catch (IOException ex) {
148  logger.error("IO Exeception " + ex.getMessage());
149  System.exit(3);
150  }
151 
152  // All done dump out results
153  System.out.println(header2);
154  for (String s : resultArray) {
155  System.out.println(s);
156  }
157  }
158 
159  String executeQuery(String sql, int expected, int iterations, int queryNum) {
160  Connection conn = null;
161  Statement stmt = null;
162 
163  Long firstExecute = 0l;
164  Long firstJdbc = 0l;
165  Long firstIterate = 0l;
166 
167  DescriptiveStatistics statsExecute = new DescriptiveStatistics();
168  DescriptiveStatistics statsJdbc = new DescriptiveStatistics();
169  DescriptiveStatistics statsIterate = new DescriptiveStatistics();
170  DescriptiveStatistics statsTotal = new DescriptiveStatistics();
171 
172  long totalTime = 0;
173 
174  try {
175  // Open a connection
176  logger.debug("Connecting to database url :" + url);
177  conn = DriverManager.getConnection(url, iUser, iPasswd);
178 
179  long startTime = System.currentTimeMillis();
180  for (int loop = 0; loop < iterations; loop++) {
181  // Execute a query
182  stmt = conn.createStatement();
183 
184  long timer = System.currentTimeMillis();
185  ResultSet rs = stmt.executeQuery(sql);
186 
187  long executeTime = 0;
188  long jdbcTime = 0;
189 
190  // gather internal execute time for OmniSci as we are interested in that
191  if (driver.equals(JDBC_DRIVER)) {
192  executeTime = ((OmniSciStatement) stmt).getQueryInternalExecuteTime();
193  jdbcTime = (System.currentTimeMillis() - timer) - executeTime;
194  } else {
195  jdbcTime = (System.currentTimeMillis() - timer);
196  executeTime = 0;
197  }
198  // this is fake to get our intenal execute time.
199  logger.debug("Query Timeout/AKA internal Execution Time was "
200  + stmt.getQueryTimeout() + " ms Elapsed time in JVM space was "
201  + (System.currentTimeMillis() - timer) + "ms");
202 
203  timer = System.currentTimeMillis();
204  // Extract data from result set
205  int resultCount = 0;
206  while (rs.next()) {
207  Object obj = rs.getObject(1);
208  if (obj != null && obj.equals(statsExecute)) {
209  logger.info("Impossible");
210  }
211  resultCount++;
212  }
213  long iterateTime = (System.currentTimeMillis() - timer);
214 
215  if (resultCount != expected) {
216  logger.error(
217  "Expect " + expected + " actual " + resultCount + " for query " + sql);
218  // don't run anymore
219  break;
220  }
221 
222  if (loop == 0) {
223  firstJdbc = jdbcTime;
224  firstExecute = executeTime;
225  firstIterate = iterateTime;
226 
227  } else {
228  statsJdbc.addValue(jdbcTime);
229  statsExecute.addValue(executeTime);
230  statsIterate.addValue(iterateTime);
231  statsTotal.addValue(jdbcTime + executeTime + iterateTime);
232  }
233 
234  // Clean-up environment
235  rs.close();
236  stmt.close();
237  }
238  totalTime = System.currentTimeMillis() - startTime;
239  conn.close();
240  } catch (SQLException se) {
241  // Handle errors for JDBC
242  se.printStackTrace();
243  } catch (Exception e) {
244  // Handle errors for Class.forName
245  e.printStackTrace();
246  } finally {
247  // finally block used to close resources
248  try {
249  if (stmt != null) {
250  stmt.close();
251  }
252  } catch (SQLException se2) {
253  } // nothing we can do
254  try {
255  if (conn != null) {
256  conn.close();
257  }
258  } catch (SQLException se) {
259  se.printStackTrace();
260  } // end finally try
261  } // end try
262 
263  return String.format(lineDescriptor,
264  queryNum,
265  statsTotal.getMean(),
266  statsTotal.getMin(),
267  statsTotal.getMax(),
268  statsTotal.getPercentile(85),
269  statsExecute.getMean(),
270  statsExecute.getMin(),
271  statsExecute.getMax(),
272  statsExecute.getPercentile(85),
273  statsExecute.getPercentile(25),
274  statsExecute.getStandardDeviation(),
275  statsJdbc.getMean(),
276  statsJdbc.getMin(),
277  statsJdbc.getMax(),
278  statsJdbc.getPercentile(85),
279  statsIterate.getMean(),
280  statsIterate.getMin(),
281  statsIterate.getMax(),
282  statsIterate.getPercentile(85),
283  firstExecute,
284  firstJdbc,
285  firstIterate,
286  iterations,
287  totalTime,
288  (long) statsTotal.getSum() + firstExecute + firstJdbc + firstIterate);
289  }
290 }
static void main(String[] args)
Definition: Benchmark.java:81
auto sql(const std::string &sql_stmts)
void doWork(String[] args, int query)
Definition: Benchmark.java:86
static final String PASS
Definition: Benchmark.java:43
static final String USER
Definition: Benchmark.java:42
static final String DB_URL
Definition: Benchmark.java:39
static final String JDBC_DRIVER
Definition: Benchmark.java:38
String executeQuery(String sql, int expected, int iterations, int queryNum)
Definition: Benchmark.java:159