Usage notes
Phoenix is a powerful and flexible tool designed for online transactional processing (OLTP) and operational analytics. It is primarily used for online services where stability is the top priority. This topic provides recommendations based on extensive user experience to help you avoid common pitfalls.
User manuals
- Official Phoenix community documentation
- Alibaba Cloud HBase team's Yunqi community documents (in Chinese)
Recommendations
Secondary index guide
Secondary indexes are a critical feature in Phoenix. Before using them, you should gain a deep understanding of how they work. For more information, see the secondary index community documentation, secondary index Chinese documentation, and global index design best practices.
- Should you use covering indexes?
A covering index includes the fields returned by a query in the index table. When the index is hit, the system only needs to query the index table. For a non-covering index, the system must look up the primary table to retrieve the complete result. Covering indexes provide better query performance but use more storage space and negatively affect write performance. When you use a non-covering index, the execution plan may not hit the index by default. In this case, you can add an index hint.
- Should you use local or global indexes?In implementation, a global index table corresponds to an HBase table, whereas a local index adds a new column to the primary table to store index data. Global indexes are suitable for read-heavy workloads but can cause high network overhead during index synchronization. Local indexes synchronize data faster because the index data is stored in the same table as the original data. Although local indexes are useful in some scenarios, we recommend that you use global indexes for the following reasons:
- The implementation of local indexes in the current version of Phoenix is less mature than that of global indexes, which means it has more issues and poses certain risks.
- Because the local index implementation is not mature, major changes may cause compatibility issues, which complicates the upgrade process.
- For large data volumes, storing raw data and index data together increases region splits. After a split, the data locality of the index is lost.
For these reasons, the LOCAL INDEX feature is disabled in the ApsaraDB for HBase SQL service.
- What is the maximum number of index tables you can create?
Indexes are synchronized in real-time, which can cause write amplification. We recommend that you create no more than 10 indexes. If you require more, you can use the HBase full-text index feature.
- What should you note when you build an index?
When you use the CREATE INDEX statement, if you specify the `async` parameter, the index is built asynchronously. After the statement is executed, the metadata for the index table is created in the SYSTEM.CATALOG table and associated with the primary table. However, the index status is `building`, the index table contains no data, and the index cannot be queried. You must use the REBUILD statement later to build the index.
Notes on salting
Salting is typically used to handle scenarios that involve both data hot spots and range queries. For an explanation of the principles, see the Phoenix community documentation and the Chinese documentation.
- Write hot spots or unbalanced writes. For example, if you use a timestamp as the first part of the primary key, writes always occur at the beginning or end of the table.
- Range query requirements. To perform range queries on the first column of the primary key, you cannot use hashing to discretize the data.
Data must be discretized to prevent hotspots, but this makes range queries difficult. To address these conflicting requirements, a compromise is necessary. The solution is a technique called salting, or using salt buckets, which discretizes data to some degree while maintaining order. Data is ordered within a bucket, but the buckets themselves are random. During write operations, a modulo operation is performed based on the number of buckets to randomly place data into a bucket. This ensures that write requests are balanced across all buckets. During queries, all buckets must be read to ensure the result set is ordered and complete.
In general, business scenarios that strictly meet these conditions are uncommon. In most scenarios, you can use other business fields to help with hashing. Due to its serious side effects, we do not recommend using this feature.
- Write bottleneck: The entire table typically has only a few regions, which is equal to the number of buckets, to handle writes. As the business grows, you cannot adjust the number of buckets. This means you cannot add more regions to share the write load. This leads to a write bottleneck that prevents write throughput from scaling linearly with cluster scale-out and can limit business growth.
- Read Diffusion: A `select` statement is split and run concurrently based on the number of buckets. Each concurrent task occupies a thread during execution. Too many concurrent `select` statements can quickly exhaust the thread pool or cause the QueryServer to perform full garbage collection (FGC) due to high concurrency. A simple query that should be completed in one remote procedure call (RPC) is split into multiple RPCs, which greatly increases the query response time (RT).
These side effects can hinder business development, especially for large and fast-growing businesses. Because the number of buckets cannot be changed, the write bottleneck affects business expansion. The increased RT from read diffusion also greatly reduces resource efficiency.
- Pre-splitting: The most common misuse is using bucketing to pre-split a table at creation. This is because the `split on` pre-splitting syntax provided by Phoenix is difficult to use. You can use the HBase Shell to create a table, specify pre-splitting, and then map it as a Phoenix table. In massive data scenarios, proper pre-splitting is a challenge. We will discuss this in a future topic.
- False Hot Spots: Write hot spots or imbalances are often an illusion. In most cases, other fields can be used to discretize the data. For example, in a monitoring data scenario, using the hash value of the metric name as the first column of the primary key can effectively balance writes.
Do not use the salting feature solely for pre-splitting. Your table design should be based on the read and write patterns of your business. If you are unable to determine these patterns, contact ApsaraDB for HBase support on DingTalk for assistance.
- 8 GB memory per node: 2 × N
- 16 GB memory per node: 3 × N
- 32 GB memory per node: 4 × N
- 64 GB memory per node: 5 × N
- 128 GB memory per node: 6 × N
Use full table scans, OR, Join, and subqueries with caution
Although Phoenix supports various Join operations, it is primarily designed as an online database. Complex joins, such as subqueries that return a large amount of data or joins between two large tables, consume significant system resources during computation. This can severely affect online services and even cause out-of-memory (OOM) errors. For users who have high requirements for online stability and real-time performance, we recommend that you use only simple Phoenix queries that hit the primary key of a primary table or an index table. We also recommend that you run `explain` before you execute an SQL statement to confirm whether an index or primary key is hit. For more information, see the explain community documentation and the explain Chinese documentation.
Phoenix does not support complex queries
Phoenix secondary indexes are essentially based on prefix matching. You can create multiple secondary indexes to support more query patterns for your data. The consistency of secondary indexes is implemented through coprocessors. Index data is visible in real-time, but this visibility affects write performance, especially when multiple indexes are created. Phoenix does not support complex queries, such as combinations of `and` or `or` with arbitrary conditions, fuzzy searches, or token-based retrieval.
Phoenix does not support complex analysis
Phoenix is designed for operational analytics and is not suitable for complex analysis, such as the complex joins mentioned earlier. For such tasks, you should use a dedicated big data computing engine such as Spark. For more information, see X-Pack Spark analysis service and HBase SQL (Phoenix) vs. Spark.
Does Phoenix support mapping to existing HBase tables?
Yes, it does. For more information, see the related community documentation. You can create a view or table in Phoenix to map to an existing HBase table. If you use a table to map to an HBase table, executing the `DROP TABLE` statement in Phoenix also deletes the HBase table. In addition, column family and column names are case-sensitive, so they must match exactly for the mapping to succeed. The field codecs in Phoenix are mostly different from those of the Bytes utility class in HBase. We recommend that you map tables only if they contain `varchar` type fields. Do not use mapping for tables that contain other data types.
Problems and solutions
This section describes common problems you may encounter when using the SQL feature (Phoenix) for ApsaraDB for HBase and provides solutions.
- Metadata may be out of sync.
Problem description: In a scenario where multiple connections access Phoenix concurrently, if one connection performs a Data Definition Language (DDL) operation, such as creating a table, deleting a table, adding a column, adding an index, or deleting an index, other connections are not aware of the change. This can cause read and write failures, such as "table not found" or "index not found" errors.
Solution:- If you are using a Phoenix 4.x thick client, restart the client.
- If you use a Phoenix 5.x thin client, restart the queryserver.
- Query bug.
Problem description: In some cases, Phoenix has a bug in its lookup to the primary table for some indexes, which causes an exception when a query is executed.
Solution: You can avoid lookups to the primary table by making all columns redundant in the index.
- RegionServer out-of-memory (OOM) error.
Problem description: A LookupJoin initiates a Get request within a ScanRpc request. The scanner for this Get request is not properly closed.
Solution: You can disable LookupJoin.
- Server-side error: Unable to find cached index metadata.
Problem description: If the cache for a user's index is not accessed within a certain period, it is removed from the cache.
Solution: You can restart the QueryServer or the client, or reset the time-to-live (TTL) for the cache.
- For complex queries, such as JOIN, ORDER BY, or GROUP BY, on large data volumes, you must manage resources reasonably. Otherwise, cluster stability is affected.
Solution: To use SQL statements, you can learn about Lindorm SQL. If you have any questions, you can submit a ticket.