We are trying to improve the read performance of our HBase setup using the Apache Phoenix drivers against a data set of ~ 11.5 M records.
HBase 0.98
Apache Phoenix driver 4.3.1
Squirrel Client 3.2
The table comprises of 21 columns, below is the DDL statement:
create table *table_name* (PKEY BIGINT not null primary key,DATE_KEY BIGINT,TIMEOFDAY_KEY BIGINT,GMT_TZ_PKEY BIGINT,FACT_DATE TIMESTAMP,PAGE_KEY BIGINT,FFER_KEY BIGINT,OFFER_TYPE_KEY BIGINT,SESSION_KEY BIGINT,CUSTOMER_KEY BIGINT,VISITS_CNTR BIGINT,ELIGIBLE_CNTR smallint, PRESENTED_CNTR smallint,ACCEPTED smallint, ACCEPTED_CLICK smallint,FIRST_RESPONSE_CNTR smallint,REJECTED_CNTR smallint,IS_FIXED smallint, IGNORED_CNTR smallint,ENGAGED_CNTR smallint,CONVERTED_CNTR smallint)
We have performed salting on the table (salt_buckets = 3) and created a secondary index on all the columns (immutable indexing).
We are executing the following queries, with the corresponding times as mention in the Squirrel client:
Select count(*) from *table_name* :
Query Time (A) = 0.031 s
Transport time (B) = 2.631 s
Total Execution Time (A+B) = 2.661 s
Execution Plan:
PLAN
CLIENT 6-CHUNK PARALLEL 6-WAY
FULL SCAN OVER OFR_FCT_IDX_SALTED
SERVER FILTER BY FIRST KEY ONLY
SERVER AGGREGATE INTO SINGLE ROW
CLIENT 100 ROW LIMIT
select MAX(session_key) from *table_name* group by TIMEOFDAY_KEY having count(SESSION_KEY) > 100 order by TIMEOFDAY_KEY : Rows returned 431
Query Time (A) = 0.04 s
Transport time (B) = 11.894 s
Total Execution Time (A+B) = 11.934 s
Execution Plan:
PLAN
CLIENT 6-CHUNK PARALLEL 6-WAY FULL SCAN OVER OFR_FCT_IDX_SALTED
SERVER FILTER BY FIRST KEY ONLY SERVER AGGREGATE INTO DISTINCT ROWS BY ["TIMEOFDAY_KEY"]
CLIENT MERGE SORT
CLIENT FILTER BY COUNT(TO_BIGINT("SESSION_KEY")) > 100
CLIENT SORTED BY ["TIMEOFDAY_KEY"]
As you can see, the query times are great but the Transport time (reading/output time) appears to be pretty high.
My questions are following:
- Do these results appear in tune with what we should expect for the mentioned data set? Considering the latest performance test results: Latest Performance Test
- Can we somehow improve the performance of the transport time(reading time) further?
