With Phoenix 4.10, we are rolling out a new feature that introduces a layer of column mapping between Phoenix column names and HBase column qualifiers. We have also added the capability of packing all column values for a column family into a single HBase cell. These improvements have helped improve performance across the board for the majority of use cases. In this blog, I will be going providing a bit more detailed info on these performance improvements.
The motivation behind column mapping came from PHOENIX-1598. The key idea being that we should be using number based hbase column qualifiers for non-pk Phoenix columns instead of directly using column names. This helps Phoenix replace the need of having to do a binary search when looking for a cell in the sorted list of cells returned by HBase. This helps improve performance of certain queries (like ORDER BY or GROUP BY on non-pk axis) as the number of non-pk columns go up.
The indirection also enables us to write fast DDL operations like column rename (PHOENIX-2341) and metadata level column drops (PHOENIX-3680). Further, because these number based qualifiers are generally smaller (1 to 4 bytes) than column names, the disk size of tables is smaller which improves performance across the board.
To compare performance and disk space usage, we loaded 600 million rows of TPC-H data for LINEITEM table (downloaded from here) on to our test cluster using 1-byte qualifiers. HDFS disk size with column mapping was 40% smaller (100 GB) than with non-column mapped tables (160GB). As a consequence, the queries in the TPC-H benchmark against LINEITEM table (obtained from here) were also found to be 30-40% faster.
Column mapping also enables us to write custom projection and comparison filters that improve query performance as the number of columns being projected or filtered on go up (PHOENIX-3667). We did a test run where we compared query performance against non-column mapped and column mapped tables as the number of columns go up. As the graph below shows, as the number of columns projected increased, the performance gain by using the new filter also went up.
Using column mapping is generally recommended unless you expect number of columns in your table and views on it to exceed 2147483647 (which is a lot!). Keep in mind though that for mutable tables this limit applies across all column families. For immutable tables, when using SINGLE_CELL_ARRAY_WITH_OFFSETS encoding scheme, this limit applies to per column family. In general, we expect that using a 2-byte column mapping scheme, which gives you 65535 columns, is good enough. One can override these defaults by using various table properties and configs. For more details on how to use column mapping and immutable data encoding, go here.
Immutable Data Encoding
The immutable storage scheme called SINGLE_CELL_ARRAY_WITH_OFFSETS packs columns belonging to a column family in a single cell. This drastically reduces the size of immutable data resulting in impressive size reduction and faster performance across the board.
To compare performance of queries for immutable encoded and non-encoded tables, we created a table with 25 VARCHAR non-pk columns, with each column name being 10 characters long having 15 character wide values. The table was dense i.e. more than 50% of the columns had values. HBase FAST_DIFF encoding was enabled which is the default with phoenix tables. All the queries were run with NO_CACHE hint to negate the effect of query performance because of block cache. We also made sure to take into consideration the effect of data being present in the OS page cache by ignoring query results for the first few runs.
As the graphs below show, using SINGLE_CELL_ARRAY_WITH_OFFSETS encoding drastically improves the performance for most kinds of queries. Data load time for 1M records using UPSERT with a batch size of 1000 was 3x faster. So were aggregate queries and queries that filtered on key value column. There was no significant impact on point queries though, which is expected.
It is important to note that this encoding could only be used when one of the numbered column mapping schemes is used. This is because internally the encoding relies on these number based column qualifiers to look up values of columns.
Using the SINGLE_CELL_ARRAY_WITH_OFFSETS encoding scheme is recommended when the data is not sparse. Our general recommendation is to use this encoding when data is sufficiently dense (around 50% of columns have values). With growing sparseness the overhead of encoding starts negatively affecting performance (PHOENIX-3559). Also, we have seen that with the default HBase block size of 64K, performance starts to degrade once the size of the packed cell starts exceeding 50 KB. By default, for immutable multi-tenant tables, we use the ONE_CELL_PER_COLUMN encoding. Because of the way we assign column qualifiers for columns in views, it tends to make the data sparse especially when columns are added to views (PHOENIX-3575). There is also work that needs to be done for cleaning up data when a column is dropped from an immutable table with SINGLE_CELL_ARRAY_WITH_OFFSETS encoding (PHOENIX-3605).