Use salted tables in Phoenix
Overview
HBase distributes its data among regions hosted by region servers. Regions are determined by row key ranges. In some cases, regions that contain data belonging to a small range of row key space may experience extra high load (write and/or read), while other regions may remain relatively idle. This usually happens when row keys represent a monotonous sequence of some kind (integer IDs, timestamps) and is called hotspotting. It leads to lower performance and unnecessary region splits.
To avoid such situations, salting is used in Apache Phoenix. Salting means adding a prefix to row keys which is called salt — a single byte in case of Apache Phoenix. It is done so that adjacent keys would have much higher chance of going to different regions. This allows for more even data distribution and more parallel writes. Read performance can also improve, but not always (see Considerations).
Usage
To create a salted table, you need to specify a number of row key prefixes (salt buckets) to use throughout the table, for example:
CREATE TABLE people (
name VARCHAR(50) NOT NULL PRIMARY KEY,
age SMALLINT,
town VARCHAR(50),
state VARCHAR(50),
country VARCHAR(50))
SALT_BUCKETS = 10;This command creates an empty table with 10 salt buckets. Now you can load the data into the table. To be able to follow examples given in this article, you need to import the data from the people.csv sample file as described in the JOIN clause in Phoenix article. Use the table created in the example above.
To see how salting affects the row keys, execute the following query:
SELECT * FROM people
WHERE people.name > 'X';This query should return all people whose names begin with Y or Z.
Result:
+-----------------+-----+-------------+-----------------+---------+ | NAME | AGE | TOWN | STATE | COUNTRY | +-----------------+-----+-------------+-----------------+---------+ | Young Della | 21 | Chetumal | Quintana Roo | MEX | | Yates Bill | 48 | Juarez | Chihuahua | MEX | | Yates Francis | 28 | Juarez | Chihuahua | MEX | | Yates Justin | 47 | New Orleans | LA | USA | | Yates Danny | 47 | Merida | Yucatan | MEX | | Yates Douglas | 35 | Phoenix | AZ | USA | | Zimmerman Madge | 46 | Nogales | AZ | USA | | Young Mattie | 39 | San Jose | CA | USA | | Yates George | 39 | Chihuahua | Chihuahua | MEX | | Yates Lucile | 25 | Chihuahua | Chihuahua | MEX | | Zimmerman Gene | 35 | Tijuana | California Baja | MEX | | Yates Johnny | 20 | Phoenix | AZ | USA | | Young Catherine | 60 | Juarez | Chihuahua | MEX | | Young Josephine | 29 | Redding | OR | USA | +-----------------+-----+-------------+-----------------+---------+ 14 rows selected (0.019 seconds)
As you can see, the order of names is not exactly alphabetical, although HBase is supposed to sort the row keys. This happened because the names have prefixes which are not displayed, and the sorting is done according to those prefixes. You can still get the alphabetic order for names by explicitly sorting the column values. Example:
SELECT * FROM people
WHERE people.name > 'X'
ORDER BY people.name;Result:
+-----------------+-----+-------------+-----------------+---------+ | NAME | AGE | TOWN | STATE | COUNTRY | +-----------------+-----+-------------+-----------------+---------+ | Yates Bill | 48 | Juarez | Chihuahua | MEX | | Yates Danny | 47 | Merida | Yucatan | MEX | | Yates Douglas | 35 | Phoenix | AZ | USA | | Yates Francis | 28 | Juarez | Chihuahua | MEX | | Yates George | 39 | Chihuahua | Chihuahua | MEX | | Yates Johnny | 20 | Phoenix | AZ | USA | | Yates Justin | 47 | New Orleans | LA | USA | | Yates Lucile | 25 | Chihuahua | Chihuahua | MEX | | Young Catherine | 60 | Juarez | Chihuahua | MEX | | Young Della | 21 | Chetumal | Quintana Roo | MEX | | Young Josephine | 29 | Redding | OR | USA | | Young Mattie | 39 | San Jose | CA | USA | | Zimmerman Gene | 35 | Tijuana | California Baja | MEX | | Zimmerman Madge | 46 | Nogales | AZ | USA | +-----------------+-----+-------------+-----------------+---------+ 14 rows selected (0.024 seconds)
In this case, when the ORDER BY clause is used for the row key column, the salt is ignored.
To display the list of salted tables, execute the following query:
SELECT table_name, salt_buckets
FROM SYSTEM.CATALOG
WHERE salt_buckets IS NOT NULL;Result example:
+---------------+--------------+ | TABLE_NAME | SALT_BUCKETS | +---------------+--------------+ | PEOPLE | 10 | | PEOPLE_SALTED | 10 | | LOG | 32 | +---------------+--------------+
Considerations
-
The number of buckets (the range of possible salt values) should be chosen based on the size of your cluster and the expected write volume. A good starting point is to have at least one bucket per region server. Experimentation is key to finding the optimal value.
-
While salting generally improves write performance, it can potentially decrease read performance if your read queries rely on the original row key order. Phoenix will automatically scan across all salt buckets, which can be less efficient than a targeted region scan.
-
Salting introduces complexity. You need to understand your data distribution and access patterns to determine if the benefits outweigh the costs.
-
You might need to adjust your queries to account for the salted row keys. For example, you will probably have to use the
LIKEoperator along with wildcards on the salt portion of the key. -
Salting can break data locality. If you frequently need to access related data based on the original row key, salting can force you to scan multiple regions.