I am using jdbc connection to connect to phoenix and running select queries. These queries are taking too much time. Also, time is increasing as the data size is increasing. Also, latency is increasing by 2-3 times when I hit queries concurrently. My guess is I am missing some configuration tuning. These are my parameters:
I have two tables over hbase. apl_accrual_service.accruals (~20 million rows) and apl_accrual_service.accrual_items(~30 million rows).
These tables are salted tables with 20 salt_buckets each. I have 3 region servers with 14 cores each. These are the queries I am making:
1) explain select linked_accrual_item_id from apl_accrual_service.accrual_items where deleted_at is null and accrual_id='fkmpcai201603180103661306bf64ed0';
CLIENT 20-CHUNK PARALLEL 20-WAY RANGE SCAN OVER APL_ACCRUAL_SERVICE.ACCRUAL_ID_INDEX [0,'fkmpcai201603180103661306bf64ed0'] |
| SERVER FILTER BY "DELETED_AT" IS NULL
2) explain select party_id_from from apl_accrual_service.accruals where client_ref_id='076d7898-e65e-483c-8696-f312044f15bd' and type='RevenueAccrual';
CLIENT 20-CHUNK PARALLEL 20-WAY RANGE SCAN OVER APL_ACCRUAL_SERVICE.COVER_CLIENT_REF_ID_IDX [0,'076d7898-e65e-483c-8696-f312044f15bd'] |
| SERVER FILTER BY "TYPE" = 'RevenueAccrual' |
Both of these indexes are global indexes, including the columns which I am selecting.
Please note, the data size can increase to upto 100 million rows per table. What can be the best configuration to get least latency for these queries.
This is the configuration I am using,