Phoenix Tuning Guide

Phoenix Tuning Guide

The most important factor in performance is the design of your schema, especially as it affects the underlying HBase row keys

Primary Keys

The Phoenix primary keys are concatenated to create the underlying row key in Apache HBase

choose the most frequently queried columns as primary keys

If your primary keys are monotonically increasing, use salting to help distribute writes across the cluster and improve parallelization

the number of salt buckets should approximately equal the number of region servers. Do not salt automatically

General Tips

Random-Access

SSDs can improve performance because of their faster random seek time.

read-heavy

 Create global indexes This will affect write speed depending on the number of columns included in an index because each index writes to its own separate table

  Use multiple indexes to provide fast access to common queries

 When specifying machines for HBase, do not skimp on cores; HBase needs them

write-heavy

 Pre-split the table

  Create local indexes

Which columns will be accessed often

Create additional indexes to support common query patterns, including heavily accessed fields that are not in the primary key

Can the data be append-only (immutable)?

Set the UPDATE_CACHE_FREQUENCY option to 15 minutes or so if your metadata doesn’t change very often. This property determines how often an RPC is done to ensure you’re seeing the latest schema

If the data is not sparse (over 50% of the cells have values), use the SINGLE_CELL_ARRAY_WITH_OFFSETS data encoding scheme introduced in Phoenix 4.10, which obtains faster performance by reducing the size of the data

Is the table very large

Use the ASYNC keyword with your CREATE INDEX call to create the index asynchronously via MapReduce job

Is transactionality required

Block Encoding

Using compression or encoding is a must. Both SNAPPY and FAST_DIFF are good all around options

Set encoding at table creation time. Example: CREATE TABLE … ( … ) DATA_BLOCK_ENCODING=‘FAST_DIFF’

Schema Design

Because the schema affects the way the data is written to the underlying HBase layer, Phoenix performance relies on the design of your tables, indexes, and primary keys

Phoenix and the HBase data model

Phoenix creates a relational data model on top of HBase, enforcing a PRIMARY KEY constraint whose columns are concatenated to form the row key for the underlying HBase table. it’s important to be cognizant of the size and number of the columns you include in the PK constraint, because a copy of the row key is included with every cell in the underlying HBase table

Column Families

create multiple column families to separate the frequently-accessed columns from rarely-accessed columns

Columns

Keep VARCHAR columns under 1MB

For structured objects, don’t use JSON, which is not very compact. Use a format such as protobuf, Avro, msgpack, or BSON.

Use the column mapping feature

Indexes

A Phoenix index is a physical table that stores a pivoted copy of some or all of the data in the main table

Secondary indexes

Depending on your needs, consider creating covered indexes or functional indexes

create index if not exists event_object_id_idx_b on trans.event (object_id) ASYNC UPDATE_CACHE_FREQUENCY=60000

• Create local indexes for write-heavy use cases.
• Create global indexes for read-heavy use cases. To save read-time overhead, consider creating covered indexes.
• If the primary key is monotonically increasing, create salt buckets. The salt buckets can’t be changed later, so design them to handle future growth. Salt buckets help avoid write hotspots, but can decrease overall throughput due to the additional scans needed on read.
• Set up a cron job to build indexes. Use ASYNC with CREATE INDEX to avoid blocking.
• Only create the indexes you need.
• Limit the number of indexes on frequently updated tables.
• Use covered indexes to convert table scans into efficient point lookups or range queries over the index table instead of the primary table: CREATE INDEX index ON table( … )INCLUDE( … )

Queries

It’s important to know which queries are executed on the server side versus the client side, because this can impact performace

Reading

Avoid joins unless one side is small, especially on frequent queries. For larger joins, see “Hints,” below

• Avoid joins unless one side is small, especially on frequent queries. For larger joins, see “Hints,” below.
• In the WHERE clause, filter leading columns in the primary key constraint.
• Filtering the first leading column with IN or OR in the WHERE clause enables skip scan optimizations.
• Equality or comparisions (< or >) in the WHERE clause enables range scan optimizations.
• Let Phoenix optimize query parallelism using statistics. This provides an automatic benefit if using Phoenix 4.2 or greater in production.

Range Queries

Apache Phoenix makes it easy to utilize many cores to increase scan performance

For range queries, the HBase block cache does not provide much advantage

Large Range Queries

For large range queries, consider setting Scan.setCacheBlocks(false) even if the whole scan could fit into the block cache  基于操作系统缓存

Point Lookups

For point lookups it is quite important to have your data set cached, and you should use the HBase block cache

Hints

Hints let you override default query processing behavior and specify such factors as which index to use, what type of scan to perform, and what type of join to use

• During the query, Hint global index if you want to force it when query includes a column not in the index.
• If necessary, you can do bigger 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 memory size limit, use the /*+ NO_STAR_JOIN */hint.

Writing

Batching large numbers of records

When using UPSERT to write a large number of records, turn off autocommit and batch records. Note: Phoenix uses commit() instead of executeBatch() to control batch updates

try (Connection conn = DriverManager.getConnection(url)) {

  conn.setAutoCommit(false);

  int batchSize = 0;

  int commitSize = 1000; // number of rows you want to commit per batch.  

  try (Statement stmt = conn.prepareStatement(upsert)) {

    stmt.set ... while (there are records to upsert) {

      stmt.executeUpdate(); 

      batchSize++; 

      if (batchSize % commitSize == 0) { 

        conn.commit(); 

      } 

   } 

 conn.commit(); // commit the last batch of records 

 }

Note: Because the Phoenix client keeps uncommitted rows in memory, be careful not to set commitSize too high

Reducing RPC traffic

To reduce RPC traffic, set the UPDATE_CACHE_FREQUENCY (4.7 or above) on your table and indexes when you create them (or issue an ALTER TABLE/INDEX call. See https://phoenix.apache.org/#Altering.

Using local indexes

consider using local indexes to minimize the write time. the writes for the secondary index will be to the same region server as your base table. This approach does involve a performance hit on the read side, though, so make sure to quantify both write speed improvement and read speed reduction.

Deleting

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.

Explain Plans

An EXPLAIN plan tells you a lot about how a query will be run:

• All the HBase range queries that will be executed
• The number of bytes that will be scanned
• The number of rows that will be traversed
• Which HBase table will be used for each scan
• Which operations (sort, merge, scan, limit) are executed on the client versus the server

Use an EXPLAIN plan to check how a query will run, and consider rewriting queries to meet the following goals:

• Emphasize operations on the server rather than the client. Server operations are distributed across the cluster and operate in parallel, while client operations execute within the single client JDBC driver.
• Use RANGE SCAN or SKIP SCAN whenever possible rather than TABLE SCAN.
• Filter against leading columns in the primary key constraint. This assumes you have designed the primary key to lead with frequently-accessed or frequently-filtered columns as described in “Primary Keys,” above.
• If necessary, introduce a local index or a global index that covers your query.
• If you have an index that covers your query but the optimizer is not detecting it, try hinting the query: SELECT /*+ INDEX() */

Improving parallelization

You can improve parallelization with the UPDATE STATISTICS command

With Phoenix 4.9, the user can set guidepost width for each table

In Phoenix 4.12, we have added a new configuration phoenix.use.stats.parallelization that controls whether statistics should be used for driving parallelization

Further Tuning

For advice about tuning the underlying HBase and JVM layers, see Operational and Performance Configuration Options in the Apache HBase™ Reference Guide

Special Cases

The following sections provide Phoenix-specific additions to the tuning recommendations in the Apache HBase™ Reference Guide section referenced above.

For applications where failing quickly is better than waiting

In addition to the HBase tuning referenced above, set phoenix.query.timeoutMs in hbase-site.xml on the client side to the maximum tolerable wait time in milliseconds.

For applications that can tolerate slightly out of date information

In addition to the HBase tuning referenced above, set phoenix.connection.consistency = timeline in hbase-site.xml on the client side for all connections.