OmniSciDB  1dac507f6e
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
BenchmarkCloud.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 package com.mapd.bench;
17 
18 // STEP 1. Import required packages
20 
21 import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics;
22 import org.slf4j.Logger;
23 import org.slf4j.LoggerFactory;
24 
25 import java.io.BufferedReader;
26 import java.io.FileNotFoundException;
27 import java.io.FileReader;
28 import java.io.IOException;
29 import java.sql.*;
30 import java.text.SimpleDateFormat;
31 import java.util.ArrayList;
32 import java.util.LinkedHashMap;
33 import java.util.List;
34 import java.util.Map;
35 import java.util.UUID;
36 
37 public class BenchmarkCloud {
38  final static Logger logger = LoggerFactory.getLogger(BenchmarkCloud.class);
39 
40  static final String QUERY_RESULT_MACHINE = "bencher";
41 
42  // JDBC driver name and database URL
43  static final String DB_URL = "jdbc:omnisci:localhost:6274:mapd";
44  static final String JDBC_DRIVER = "com.omnisci.jdbc.OmniSciDriver";
45 
46  // Database credentials
47  static final String USER = "admin";
48  static final String PASS = "";
49 
50  // Database credentials
51  static final String RESULTS_USER = "admin";
52  static final String RESULTS_PASS = "";
53 
54  private String driver;
55  private String url;
56  private String iUser;
58  private String iPasswd;
61  private String rid;
62  private String rTimestamp;
63  private String tableName;
64  private String label;
65  private String gpuCount;
67  Connection bencherCon;
68  private List<String> LResult = new ArrayList<String>();
69 
71  "%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";
72  private String header2 = String.format(headDescriptor,
73  "QRY",
74  "T-Avg",
75  "T-Min",
76  "T-Max",
77  "T-85%",
78  "E-Avg",
79  "E-Min",
80  "E-Max",
81  "E-85%",
82  "E-25%",
83  "E-StdD",
84  "J-Avg",
85  "J-Min",
86  "J-Max",
87  "J-85%",
88  "I-Avg",
89  "I-Min",
90  "I-Max",
91  "I-85%",
92  "F-Exec",
93  "F-jdbc",
94  "F-iter",
95  "ITER",
96  "Total",
97  "Account");
99  "%3s, %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";
101  "('%s','%s','%s','%s','%s',%s,'%s','%s',%d,'%s', %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, '%s')";
102 
103  public static void main(String[] args) {
104  BenchmarkCloud bm = new BenchmarkCloud();
105  bm.doWork(args, 1);
106  }
107 
108  void doWork(String[] args, int query) {
109  // Grab parameters from args
110  // parm0 number of iterations per query
111  // parm1 file containing sql queries {contains quoted query, expected result
112  // count]
113  // parm2 table name
114  // parm3 run label
115  // parm4 gpu count
116  // parm5 optional query and result machine
117  // parm6 optional DB URL
118  // parm7 optional JDBC Driver class name
119  // parm8 optional user
120  // parm9 optional passwd
121  // parm10 optional query results db user
122  // parm11 optional query results db passwd
123  int iterations = Integer.valueOf(args[0]);
124  logger.debug("Iterations per query is " + iterations);
125 
126  String queryFile = args[1];
127  tableName = args[2];
128  label = args[3];
129  gpuCount = args[4];
130 
131  // int expectedResults = Integer.valueOf(args[2]);
132  queryResultMachine = (args.length > 5) ? args[5] : QUERY_RESULT_MACHINE;
133  url = (args.length > 6) ? args[6] : DB_URL;
134  driver = (args.length > 7) ? args[7] : JDBC_DRIVER;
135 
136  iUser = (args.length > 8) ? args[8] : USER;
137  iPasswd = (args.length > 9) ? args[9] : PASS;
138 
139  iResultsUser = (args.length > 10) ? args[10] : RESULTS_USER;
140  iResultsPasswd = (args.length > 11) ? args[11] : RESULTS_PASS;
141 
142  // register the driver
143  try {
144  // Register JDBC driver
145  Class.forName(driver);
146  } catch (ClassNotFoundException ex) {
147  logger.error("Could not load class " + driver + " " + ex.getMessage());
148  System.exit(1);
149  }
150 
151  UUID uuid = UUID.randomUUID();
152  rid = uuid.toString();
153  java.util.Date date = new java.util.Date();
154  Timestamp t = new Timestamp(date.getTime());
155  rTimestamp = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(t);
156 
157  System.out.println("run id is " + rid + " date is " + rTimestamp);
158 
159  // read from query file and execute queries
160  String sCurrentLine;
161  List<String> resultArray = new ArrayList();
162  Map<String, String> queryIDMap = new LinkedHashMap();
163  BufferedReader br;
164  try {
165  br = new BufferedReader(new FileReader(queryFile));
166 
167  while ((sCurrentLine = br.readLine()) != null) {
168  queryIDMap.put(sCurrentLine, null);
169  }
170  br.close();
171 
172  } catch (FileNotFoundException ex) {
173  logger.error("Could not find file " + queryFile + " " + ex.getMessage());
174  System.exit(2);
175  } catch (IOException ex) {
176  logger.error("IO Exeception " + ex.getMessage());
177  System.exit(3);
178  }
179 
180  bencherCon = getConnection("jdbc:omnisci:" + queryResultMachine + ":6274:mapd",
181  iResultsUser,
183 
184  getQueries(queryIDMap, bencherCon, tableName);
185 
186  runQueries(resultArray, queryIDMap, iterations);
187 
188  // if all completed ok store the results
189  storeResults();
190 
191  // All done dump out results
192  System.out.println(header2);
193  for (String s : resultArray) {
194  System.out.println(s);
195  }
196  }
197 
199  // Open a connection
200  logger.debug("Connecting to database url :" + url);
201  try {
202  Connection conn = DriverManager.getConnection(url, iUser, iPasswd);
203 
204  targetDBVersion = conn.getMetaData().getDatabaseProductVersion();
205  logger.debug("Target DB version is " + targetDBVersion);
206 
207  return conn;
208  } catch (SQLException ex) {
209  logger.error(
210  "Exception making connection to " + url + " text is " + ex.getMessage());
211  System.exit(2);
212  }
213  return null;
214  }
215 
216  String executeQuery(Connection conn1, String qid, String sql, int iterations) {
217  Statement stmt = null;
218  Connection conn = getConnection(url, iUser, iPasswd);
219 
220  Long firstExecute = 0l;
221  Long firstJdbc = 0l;
222  Long firstIterate = 0l;
223 
224  DescriptiveStatistics statsExecute = new DescriptiveStatistics();
225  DescriptiveStatistics statsJdbc = new DescriptiveStatistics();
226  DescriptiveStatistics statsIterate = new DescriptiveStatistics();
227  DescriptiveStatistics statsTotal = new DescriptiveStatistics();
228 
229  long totalTime = 0;
230  int resultCount = 0;
231  try {
232  long startTime = System.currentTimeMillis();
233  for (int loop = 0; loop < iterations; loop++) {
234  // Execute a query
235  stmt = conn.createStatement();
236 
237  long timer = System.currentTimeMillis();
238  if (loop == 0) {
239  System.out.println(String.format("Query Id is %s : query is '%s'", qid, sql));
240  }
241  ResultSet rs = stmt.executeQuery(sql);
242 
243  long executeTime = 0;
244  long jdbcTime = 0;
245 
246  // gather internal execute time for OmniSci as we are interested in that
247  if (driver.equals(JDBC_DRIVER)) {
248  executeTime = ((OmniSciStatement) stmt).getQueryInternalExecuteTime();
249  jdbcTime = (System.currentTimeMillis() - timer) - executeTime;
250  } else {
251  jdbcTime = (System.currentTimeMillis() - timer);
252  executeTime = 0;
253  }
254  // this is fake to get our intenal execute time.
255  logger.debug("Query Timeout/AKA internal Execution Time was "
256  + stmt.getQueryTimeout() + " ms Elapsed time in JVM space was "
257  + (System.currentTimeMillis() - timer) + "ms");
258 
259  timer = System.currentTimeMillis();
260  // Extract data from result set
261  resultCount = 0;
262  while (rs.next()) {
263  Object obj = rs.getObject(1);
264  if (obj != null && obj.equals(statsExecute)) {
265  logger.info("Impossible");
266  }
267  resultCount++;
268  }
269  long iterateTime = (System.currentTimeMillis() - timer);
270 
271  // if (resultCount != expected) {
272  // logger.error("Expect " + expected + " actual " + resultCount + " for
273  // query " + sql);
274  // // don't run anymore
275  // break;
276  // }
277  if (loop == 0) {
278  firstJdbc = jdbcTime;
279  firstExecute = executeTime;
280  firstIterate = iterateTime;
281 
282  } else {
283  statsJdbc.addValue(jdbcTime);
284  statsExecute.addValue(executeTime);
285  statsIterate.addValue(iterateTime);
286  statsTotal.addValue(jdbcTime + executeTime + iterateTime);
287  }
288 
289  // Clean-up environment
290  rs.close();
291  stmt.close();
292  }
293  totalTime = System.currentTimeMillis() - startTime;
294  conn.close();
295  } catch (SQLException se) {
296  // Handle errors for JDBC
297  se.printStackTrace();
298  System.exit(4);
299  } catch (Exception e) {
300  // Handle errors for Class.forName
301  e.printStackTrace();
302  System.exit(3);
303  } finally {
304  // finally block used to close resources
305  try {
306  if (stmt != null) {
307  stmt.close();
308  }
309  } catch (SQLException se2) {
310  } // nothing we can do
311  try {
312  if (conn != null) {
313  conn.close();
314  }
315  } catch (SQLException se) {
316  se.printStackTrace();
317  System.exit(6);
318  } // end finally try
319  } // end try
320 
321  // write it to the db here as well
322  String insertPart = String.format(insertDescriptor,
323  this.rid,
324  this.rTimestamp,
325  url,
326  this.driver,
327  label,
328  gpuCount,
329  this.tableName,
330  qid,
331  resultCount,
332  "",
333  statsTotal.getMean(),
334  statsTotal.getMin(),
335  statsTotal.getMax(),
336  statsTotal.getPercentile(85),
337  statsExecute.getMean(),
338  statsExecute.getMin(),
339  statsExecute.getMax(),
340  statsExecute.getPercentile(85),
341  statsExecute.getPercentile(25),
342  statsExecute.getStandardDeviation(),
343  statsJdbc.getMean(),
344  statsJdbc.getMin(),
345  statsJdbc.getMax(),
346  statsJdbc.getPercentile(85),
347  statsIterate.getMean(),
348  statsIterate.getMin(),
349  statsIterate.getMax(),
350  statsIterate.getPercentile(85),
351  firstExecute,
352  firstJdbc,
353  firstIterate,
354  iterations,
355  totalTime,
356  (long) statsTotal.getSum() + firstExecute + firstJdbc + firstIterate,
358 
359  LResult.add("Insert into results values " + insertPart);
360 
361  return String.format(lineDescriptor,
362  qid,
363  statsTotal.getMean(),
364  statsTotal.getMin(),
365  statsTotal.getMax(),
366  statsTotal.getPercentile(85),
367  statsExecute.getMean(),
368  statsExecute.getMin(),
369  statsExecute.getMax(),
370  statsExecute.getPercentile(85),
371  statsExecute.getPercentile(25),
372  statsExecute.getStandardDeviation(),
373  statsJdbc.getMean(),
374  statsJdbc.getMin(),
375  statsJdbc.getMax(),
376  statsJdbc.getPercentile(85),
377  statsIterate.getMean(),
378  statsIterate.getMin(),
379  statsIterate.getMax(),
380  statsIterate.getPercentile(85),
381  firstExecute,
382  firstJdbc,
383  firstIterate,
384  iterations,
385  totalTime,
386  (long) statsTotal.getSum() + firstExecute + firstJdbc + firstIterate);
387  }
388 
389  private void getQueries(
390  Map<String, String> queryIDMap, Connection benderCon, String tableName) {
391  for (Map.Entry<String, String> entry : queryIDMap.entrySet()) {
392  String key = entry.getKey();
393  String value = entry.getValue();
394 
395  Statement stmt = null;
396  try {
397  stmt = benderCon.createStatement();
398  } catch (SQLException ex) {
399  logger.error("Exception creating statement text is " + ex.getMessage());
400  System.exit(2);
401  }
402  String sql =
403  String.format("Select query_text from queries where query_id = '%s'", key);
404  ResultSet rs = null;
405  try {
406  rs = stmt.executeQuery(sql);
407  } catch (SQLException ex) {
408  logger.error("Exception running query " + sql + " text is " + ex.getMessage());
409  System.exit(2);
410  }
411  int resultCount = 0;
412  try {
413  while (rs.next()) {
414  String qString = rs.getString(1);
415  qString = qString.replaceAll("##TAB##", tableName);
416  // System.out.println(String.format("Query Id is %s : query is '%s'", key,
417  // qString));
418  queryIDMap.put(key, qString);
419  resultCount++;
420  }
421  } catch (SQLException ex) {
422  logger.error("Exception making next call text is " + ex.getMessage());
423  System.exit(2);
424  }
425  if (resultCount > 1) {
426  System.out.println("multiple values for queryId " + key);
427  }
428  }
429  }
430 
431  private void runQueries(
432  List<String> resultArray, Map<String, String> queryIDMap, int iterations) {
433  Connection conn = getConnection(url, iUser, iPasswd);
434  for (Map.Entry<String, String> entry : queryIDMap.entrySet()) {
435  String id = entry.getKey();
436  String query = entry.getValue();
437 
438  resultArray.add(executeQuery(conn, id, query, iterations));
439  }
440  }
441 
442  private void storeResults() {
443  for (String insertPart : LResult) {
444  Statement sin;
445  try {
446  sin = bencherCon.createStatement();
447  sin.execute(insertPart);
448  } catch (SQLException ex) {
449  logger.error("Exception performing insert '" + insertPart + "' text is "
450  + ex.getMessage());
451  System.exit(2);
452  }
453  }
454  }
455 }
static final String RESULTS_PASS
String executeQuery(Connection conn1, String qid, String sql, int iterations)
static final String JDBC_DRIVER
void doWork(String[] args, int query)
Connection getConnection(String url, String iUser, String iPasswd)
static final String RESULTS_USER
void getQueries(Map< String, String > queryIDMap, Connection benderCon, String tableName)
static final String QUERY_RESULT_MACHINE
void runQueries(List< String > resultArray, Map< String, String > queryIDMap, int iterations)
static void main(String[] args)