This article provides the fundamental techniques you should consider when optimizing the peformance of your Phoenix deployment on HDInsight.
One of the most important considerations when optimizing the performance of Phoenix really boils down to making sure HBase is well optimized. Recall that Phoenix creates a relational data model atop HBase that converts SQL queries into HBase operations like scans. This means that the design of your table schema, the selection and ordering of the fields in your primary key, and your use of indexes all affect how performant Phoenix can be in querying HBase.
When you create a table in Phoenix, that table is stored in an HBase table. The HBase table contains column families, which are groups of columns that are accessed together. A row in the Phoenix table is a row in the HBase table, where each row consists of versioned cells associated with one or more columns. A single HBase row is logically a collection of key-value pairs each having the same rowkey value. In other words where for each of the key-value pairs they each have a rowkey attribute, and the value of that rowkey attribute is the same when they are part of the same row.
When it comes to the schema design of your table in Phoenix it is important to consider the primary key design, column family design, column design and how the data is partitioned.
The primary key that you define on a table in Phoenix actually dictates the semantics for how data is stored within the rowkey of the underlying HBase table. In HBase, the only way to access a particular row is via the rowkey. In addition, data stored in an HBase table is sorted by the rowkey. The way Phoenix builds the rowkey value is by concatenating the values of each of the columns in the row, in the order they are defined in the primary key.
For example, say you had a table for contacts that had the first name, last name, phone number and address (all in the same column family). You could define a Primary Key based on an increasing sequence number, so your rowkeys would look like:
| rowkey | address | phone | firstName | lastName |
|---|---|---|---|---|
| 1000 | 1111 San Gabriel Dr. | 1-425-000-0002 | John | Dole |
| 8396 | 5415 San Gabriel Dr. | 1-230-555-0191 | Calvin | Raji |
However, if you frequently query by lastName this would not perform well because you would need to do a table scan and read the value of every lastname, so you might define a primary key on the lastName, firstName and a social security number (to disambiguate two residents at the same address with the same name, like a father and son).
| rowkey | address | phone | firstName | lastName | socialSecurityNum |
|---|---|---|---|---|---|
| 1000 | 1111 San Gabriel Dr. | 1-425-000-0002 | John | Dole | 111 |
| 8396 | 5415 San Gabriel Dr. | 1-230-555-0191 | Calvin | Raji | 222 |
If you defined a primary key on the table for on lastName, firstName and socialSecurityNum your row keys as generated by Phoenix might look as follows:
| rowkey | address | phone | firstName | lastName | socialSecurityNum |
|---|---|---|---|---|---|
| Dole-John-111 | 1111 San Gabriel Dr. | 1-425-000-0002 | John | Dole | 111 |
| Raji-Calvin-222 | 5415 San Gabriel Dr. | 1-230-555-0191 | Calvin | Raji | 222 |
By way of example, for the fist row in the example above the data stored actually looks like:
| rowkey | key | value |
|---|---|---|
| Dole-John-111 | address | 1111 San Gabriel Dr. |
| Dole-John-111 | phone | 1-425-000-0002 |
| Dole-John-111 | firstName | John |
| Dole-John-111 | lastName | Dole |
| Dole-John-111 | socialSecurityNum | 111 |
As you can see, the rowkey now stores a duplicate copy of the data. It is important to be aware of the size and number of columns you include in your Primary Key, because this value is included with every cell in the underlying HBase table.
Also, if the primary key you have selected has values that are monotonically increasing, you should create the table with salt buckets (see the Partition Data section below) to help avoid creating write hotspots.
Choosing which columns to group into the same column family is another important consideration. If some columns are accessed more frequently than others, you should create multiple column families to separate the frequently-accessed columns from rarely-accessed columns.
Another consideration, is to identify which columns tend to be accessed together and put those in the same column family.
There are a few considerations to what you store within a column as well:
- Keep VARCHAR columns under 1MB or so due to the I/O costs of large columns. When processing queries, HBase materializes cells in full before sending them over to the client, and the client receives them in full before handing them off to the application code.
- Store column values using a compact format such as protobuf, Avro, msgpack or BSON. Avoid JSON if you can as it is significantly less compact.
- Consider compressing data before storage to cut latency and I/O costs.
Phoenix enables you to control the number of regions on which your data is distributed, which can significantly increase read/write performance. You can accomplish this either by salting or pre-splitting your data. Both are tasks you perform when creating your Phoenix table.
To salt a table during creation, you specify the number of salt buckets, for example:
CREATE TABLE CONTACTS (...) SALT_BUCKETS = 16
Salting does the splitting of the table along the value of primary key lines, choosing the values automatically. If you want to control where the table splits occur, you can pre-split the table by providing the actual values that define the ranges alon which the splitting occurs. For example, the following creates a table that will be split along five regions:
CREATE TABLE CONTACTS (...) SPLIT ON ('CS','EU','NA')
A Phoenix index is an HBase table that stores a copy of some or all of the data from the table that it indexes. You apply an index to benefit specific kinds of queries.
When you have an index in place and you query a table, Phoenix selects the best index for the query automatically. The primary index is created automatically based on the primary keys you select. You can also create secondary indexes by specifying which columns are included based on the anticipated queries the index will support.
When designing your indexes, keep the following points in mind:
- Only create the indexes you need.
- Limit the number of indexes on frequently updated tables (since updates to a table translates into writes to both the main table and the tables containing the indexes).
Secondary indexes can improve read performance by turning what would normally be a full table scan into a point lookup (at the cost of storage space and write speed). Secondary indexes can be added or removed after table creation and don’t require changes to existing queries – queries simply run faster. Depending on your needs, consider creating covered indexes, functional indexes, or both.
Covered indexes are indexes that include additional data from the row in addition to the values that are indexed, eliminating the need to go back to the primary table once the desired index entry has been found.
For example, in our contact table we could create a secondary index on the socialSecurityNum column alone. This would speedup queries that filter by socialSecurityNum values, but to pull back other field values like firstName and lastName would require another read against the main table.
| rowkey | address | phone | firstName | lastName | socialSecurityNum |
|---|---|---|---|---|---|
| Dole-John-111 | 1111 San Gabriel Dr. | 1-425-000-0002 | John | Dole | 111 |
| Raji-Calvin-222 | 5415 San Gabriel Dr. | 1-230-555-0191 | Calvin | Raji | 222 |
However, if we frequently want to look up the firstName and lastName given the socialSecurityNum, we could create a covered index as follows that includes the firstName and lastName as actual data in the index table:
CREATE INDEX ssn_idx ON CONTACTS (socialSecurityNum) INCLUDE(firstName, lastName);
This would enable the following query to acquire all data just be reading from the table containing the secondary index:
SELECT socialSecurityNum, firstName, lastName FROM CONTACTS WHERE socialSecurityNum > 100;
Functional indexes allow you to create an index on an arbitrary expressions that you expect to be used in queries. Once you have a functional index in place and a query uses that expression, the index may be used to retrieve the results instead of the data table.
For example, you could create an index to allow you to do case insensitive searches on the combined first name and last name of a person:
CREATE INDEX FULLNAME_UPPER_IDX ON "Contacts" (UPPER("firstName"||' '||"lastName"));
Naturally, an important aspect of performant Phoenix queries is the actual design of your query.
The main considerations in query design are:
- Understand the query plan and make sure it is as expected
- Join efficiently
In SQLLine, use EXPLAIN followed by your SQL query to view the plan of operations that Phoenix will perform when executing the query.
The key things to look for are:
- Plan is using your primary key when appropriate.
- Plan uses the secondary indexes you intend, as opposed to the data table.
- Plan uses RANGE SCAN or SKIP SCAN whenever possible rather than TABLE SCAN.
Assume you have a table called FLIGHTS that stores flight delay information.
Say you wanted to select all the flights with the AIRLINEID of "19805" which is not a field that is in the primary key nor in any index:
select * from "FLIGHTS" where airlineid = '19805';
You would run the explain command as follows:
explain select * from "FLIGHTS" where airlineid = '19805';
A plan for this query would look like this:
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER FLIGHTS
SERVER FILTER BY AIRLINEID = '19805'
In the above plan, notice the phrase FULL SCAN OVER FLIGHTS. This indicates the execution will do a TABLE SCAN over all rows in the table, instead of using the more efficient RANGE SCAN or SKIP scan option.
Now, say you want to query for flights in January 1st, 2014 for the carrier "AA" where its flightnum was greater than 1. Let's assume that the columns year, month, dayofmonth, carrier and flightnum exist in our table, and are all part of the composite primary key. Our query would look as follows:
select * from "FLIGHTS" where year = 2014 and month = 1 and dayofmonth = 2 and carrier = 'AA' and flightnum > 1;
Let's examine the plan for this query with:
explain select * from "FLIGHTS" where year = 2014 and month = 1 and dayofmonth = 2 and carrier = 'AA' and flightnum = 1;
The resulting plan looks as follows:
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER FLIGHTS [2014,1,2,'AA',2] - [2014,1,2,'AA',*]
Notice in the above the values in square brackets after the table name show the range of values for the primary keys having values that are fixed with year of 2014, month of 1, dayofmonth of 2 but allow values for flightnum starting (and including) 2 and upwards. This query plan confirms our primary key is being used as we would expect.
Next, say we created an index on the flights table called carrier2_idx that is on the carrier field only, but includes flightdate, tailnum, origin and flightnum as covered columns whose data is also stored in the index.
CREATE INDEX carrier2_idx ON FLIGHTS (carrier) INCLUDE(FLIGHTDATE,TAILNUM,ORIGIN,FLIGHTNUM);
Say we want to get the carrier along with the flightdate and tailnum, as in the following query:
select carrier,flightdate,tailnum from "FLIGHTS" where carrier = 'AA';
We should see this index used, as the explain command would show:
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER CARRIER2_IDX ['AA']
In the above notice it used our CARRIER2_IDX with the key value of 'AA', as was expected.
For a complete listing of the items that can appear in explain plan results, see the Explain Plans section in the Apache Phoenix Tuning Guide
Generally, you will want to avoid joins unless one side is small, especially on frequent queries.
If necessary, you can do large joins with the /*+ USE_SORT_MERGE_JOIN / hint, but a big join will be an expensive operation over huge numbers of rows. If the overall size of all right-hand-side tables would exceed the available memory, use the /+ NO_STAR_JOIN */hint.
There are some common patterns we can use to apply the aforementioned guidance. The following provides guidance on each.
For read-heavy use cases, make sure you are using indexes. Additionally, to save read-time overhead, consider creating covered indexes.
For write-heavy workloads, if the primary key is monotonically increasing, create salt buckets to help avoid write hotspots at the expense of overall read throughput due to the additional scans needed. Also, when using UPSERT to write a large number of records, turn off autocommit and batch records (instead of writing them one by one).
When deleting a large data set, turn on autoCommit before issuing the DELETE query so that the client does not need to remember the row keys of all the keys as they are deleted. This prevents the client from buffering the rows affected by the DELETE so that Phoenix can delete them directly on the region servers without the expense of returning them to the client.
If your scenario favors write-speed over data integrity, you can consider disabling the write ahead log. This is an option specified when you are creating your table and takes effect when the DISABLE_WAL option is set to true. For example:
CREATE TABLE CONTACTS (...) DISABLE_WAL=true;
For details on this and other options, see Phoenix Grammer