Tuning Hbase for optimized performance ( Part 5 ) - Phoenix

[#1]

In this article series, part 1 , part 2 , part 3, part 4 covered various Hbase tuning parameters, scenarios, system side of things etc, in last and part 5 of this series, I will discuss little bit about Phoenix performance parameters and general tips for tuning.

I am taking an example of a query which was performing very slow and how we investigated this situation. We will start by reading explain plan of this query. I cannot quote exact query (customer's data) here but it was a select query with some where clause and finally order by conditions. The explain plan of the query is as follows:

+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| CLIENT 5853-CHUNK 234762256 ROWS 1671023360974 BYTES PARALLEL 5853-WAY FULL SCAN OVER MESSAGE_LOGS.TEST_MESSAGE | | SERVER FILTER BY ((ROUTING IS NULL OR ROUTING = ‘TEST') AND TESTNAME = 'createTest' AND TESTID = ’TEST’ AND ERRORCODE | | SERVER TOP 20 ROWS SORTED BY [CREATEDTIMESTAMP DESC, TESTID] | | CLIENT MERGE SORT | 
+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 
4 rows selected (0.958 seconds) 

Firstly, lets learn to dissect an explain plan of a Phoenix query, following are my observations looking at this plan:

First statement

• ”CLIENT” , means this statement shall be executed at client side.
• “5853-CHUNK”, this means query plan has logically divided the data in about 5853 chunks . And each chunk would utilize one single thread. So for future reference, lets keep it in mind, one chunk == one thread of client thread-pool.
• “234762256 ROWS”, means these many rows will be processed by this query. Self explanatory.
• “1671023360974 BYTES”, means about 1.6 TB of data will be processed.
• “PARALLEL”, so the query processing on these 5853 chunks (5853-WAY) would be done in parallel.
• “FULL SCAN OVER MESSAGE_LOGS.TEST_MESSAGE“ , this means it will scan entire table , most inefficient way and anti-pattern for Hbase / Phoenix use cases. This table requires secondary index to convert this full scan into a range scan.

Second Statement

• “SERVER” , processing would happen in region servers
• “FILTER BY” , returns only results that match the expression

Third Statement

• “SERVER” , processing happening on server side, specifically “SORTED BY”

Fourth Statement

• “CLIENT MERGE SORT”, meaning all the SORTED ROWS at server side would be brought back to client node and be merge sorted again.

What tuning was done to make query run faster ?

• 5853 chunks appeared too much for the query specially with a thread-pool having at default value of 128, this was making whole query get slower as only 128 threads would work at a time and rest all tasks would wait in queue. (phoenix.query.queueSize)
• We decided to bump up thread-pool (phoenix.query.threadPoolSize) from default 128 to about 1000 , but customer did not have enough CPU cores on client side and he feared CPU contention there if we go beyond this number, so we decided to go for another tuning.
• We increased guidepost width (phoenix.stats.guidepost.width) which are markers to logically distribute data in chunks. (from its default 100 MB to 500 MB ). This effectively reduced the number of chunks and hence the threads.
• Read more about all tuning parameters including above ones here.
• For making this query more effective, recommended customer to create secondary index on top of this data table and include most frequently used columns in it. Read more about secondary index here.
• Thus after all the changes in place, the query which was earlier taking about 5 minutes was now taking about 15 - 20 seconds.

Tuning recommendations in general :

• For improving read performance, create global secondary index, it will have some write penalty as data of chosen columns for index would be duplicated to another table.
• For improving write performance, pre-split the table if you know the key ranges , also consider going for Local index which is written in same table and being added as another column. Local indexes will be more stable with HDP 3.0 with lot of bug fixes.
• Choose most frequently used columns for primary key. Since all these columns are concatenated to form Hbase’s “row key” ,their order of appearance in row-key as well as its length matters. Order matters because if most frequently used column comes first in row key the range scan becomes more efficient. Length matters because this row key will be part of each cell and hence would occupy some memory and some disk.
• Use Salt Buckets if you have a monotonically increasing row-key . Read more about it here
• Please note Salting would incur read penalties as scans would be repeated for each bucket.
• Don’t create too many salt buckets , thumb rule is to be equal to number of region servers in your cluster.

Reference:

http://phoenix.apache.org/explainplan.html

https://phoenix.apache.org/tuning.html

https://phoenix.apache.org/tuning_guide.html

Also see : PART 1 , PART 2 , PART 3, PART 4