Support Questions
Find answers, ask questions, and share your expertise

Phoenix exec times are faster but terrible fetch times

Phoenix exec times are faster but terrible fetch times

New Contributor

I am dealing with Phoenix query performance. The nature of the problem is the execution times of the query is sub-sec but the fetch time of the result set is in the order of secs. Hence, the total execution time of the query is suffering.


These are all total 14 different queries that have been run with different work loads and here the results below:

Example resultset:
2018-02-20 16:19:37 ERROR PhoenixJDBCApp:76 - QUERY1 execution time: 1333 ms, fetch time: 3314 ms, total time:4647 ms, resultset size:1620
2018-02-20 16:19:42 ERROR PhoenixJDBCApp:76 - QUERY2 execution time: 108 ms, fetch time: 4524 ms, total time:4632 ms, resultset size:2196
2018-02-20 16:19:49 ERROR PhoenixJDBCApp:76 - QUERY3 execution time: 247 ms, fetch time: 7093 ms, total time:7340 ms, resultset size:1620
2018-02-20 16:20:23 ERROR PhoenixJDBCApp:76 - QUERY4 execution time: 222 ms, fetch time: 33678 ms, total time:33900 ms, resultset size:7230
2018-02-20 16:20:56 ERROR PhoenixJDBCApp:76 - QUERY5 execution time: 231 ms, fetch time: 32818 ms, total time:33049 ms, resultset size:3642
2018-02-20 16:21:11 ERROR PhoenixJDBCApp:80 - QUERY6 failed!
2018-02-20 16:21:22 ERROR PhoenixJDBCApp:76 - QUERY7 execution time: 201 ms, fetch time: 10140 ms, total time:10341 ms, resultset size:100000
2018-02-20 16:22:01 ERROR PhoenixJDBCApp:76 - QUERY8 execution time: 206 ms, fetch time: 38949 ms, total time:39155 ms, resultset size:100000
2018-02-20 16:22:09 ERROR PhoenixJDBCApp:76 - QUERY9 execution time: 131 ms, fetch time: 8593 ms, total time:8724 ms, resultset size:2196
2018-02-20 16:22:48 ERROR PhoenixJDBCApp:76 - QUERY10 execution time: 132 ms, fetch time: 38677 ms, total time:38809 ms, resultset size:6066
2018-02-20 16:23:37 ERROR PhoenixJDBCApp:76 - QUERY11 execution time: 132 ms, fetch time: 48742 ms, total time:48874 ms, resultset size:3642
2018-02-20 16:27:14 ERROR PhoenixJDBCApp:80 - QUERY12 failed!
2018-02-20 16:27:20 ERROR PhoenixJDBCApp:76 - QUERY13 execution time: 116 ms, fetch time: 5361 ms, total time:5477 ms, resultset size:6066
2018-02-20 16:28:13 ERROR PhoenixJDBCApp:76 - QUERY14 execution time: 133 ms, fetch time: 52899 ms, total time:53032 ms, resultset size:3642


Scala code sample:
val props = new Properties()
props.setProperty(QueryServices.COLLECT_REQUEST_LEVEL_METRICS, "true")
//phoenix.query.maxServerCacheBytes=2GB
//props.setProperty(QueryServices.MAX_SERVER_CACHE_SIZE_ATTRIB, "2147483648")
props.setProperty(QueryServices.QUERY_SERVER_SERIALIZATION_ATTRIB, "PROTOBUF")
// props.setProperty(QueryServices.QUERY_SERVER_SERIALIZATION_ATTRIB, "JSON")

// props.setProperty("phoenix.trace.frequency", "always")

connection = DriverManager.getConnection(jdbcURL, props)
statement = connection.createStatement()
statement.setFetchSize(10000)
statement.setFetchDirection(ResultSet.FETCH_FORWARD)
statement.setMaxRows(100000)

// for ((queryName, query) <- BenchmarkQueries.BENCHMARK_QUERIES.filter(p => p._1.equals("QUERY1"))) {
for ((queryName, query) <- BenchmarkQueries.BENCHMARK_QUERIES) {
try {
val startTime1 = System.currentTimeMillis
val rs = statement.executeQuery(query)
val endTime1 = System.currentTimeMillis

val startTime2 = System.currentTimeMillis
var count = 0
while (rs.next()) {
count = count + 1
}
val endTime2 = System.currentTimeMillis

val overAllQueryMetrics = PhoenixRuntime.getOverAllReadRequestMetrics(rs)
val requestReadMetrics = PhoenixRuntime.getRequestReadMetrics(rs)

LOG.info("requestReadMetrics are " + requestReadMetrics)
LOG.info("overAllQueryMetrics are " + overAllQueryMetrics)

// log or report metrics as needed
PhoenixRuntime.resetMetrics(rs)
rs.close()

val total = (endTime1 - startTime1) + (endTime2 - startTime2)
LOG.error(queryName + " execution time: " + (endTime1 - startTime1) + " ms, fetch time: " + (endTime2 - startTime2) + " ms, total time:" + total + " ms, resultset size:" + count)
} catch {
case t: Throwable =>
t.printStackTrace()
LOG.error(queryName + " failed!")
} finally {
}
}

1 REPLY 1

Re: Phoenix exec times are faster but terrible fetch times

New Contributor

btw, this is HDP 2.6.3, HBase 1.1.2 and Phoenix 4.7.0