You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2017/03/25 01:44:50 UTC
svn commit: r1788628 - in /phoenix/site: publish/tuning_guide.html
source/src/site/markdown/tuning_guide.md
Author: jamestaylor
Date: Sat Mar 25 01:44:50 2017
New Revision: 1788628
URL: http://svn.apache.org/viewvc?rev=1788628&view=rev
Log:
Add Tuning Guide for Phoenix (Peter Conrad)
Added:
phoenix/site/publish/tuning_guide.html
phoenix/site/source/src/site/markdown/tuning_guide.md
Added: phoenix/site/publish/tuning_guide.html
URL: http://svn.apache.org/viewvc/phoenix/site/publish/tuning_guide.html?rev=1788628&view=auto
==============================================================================
--- phoenix/site/publish/tuning_guide.html (added)
+++ phoenix/site/publish/tuning_guide.html Sat Mar 25 01:44:50 2017
@@ -0,0 +1,678 @@
+
+<!DOCTYPE html>
+<!--
+ Generated by Apache Maven Doxia at 2017-03-24
+ Rendered using Reflow Maven Skin 1.1.0 (http://andriusvelykis.github.io/reflow-maven-skin)
+-->
+<html xml:lang="en" lang="en">
+
+ <head>
+ <meta charset="UTF-8" />
+ <title>Tuning Guide | Apache Phoenix</title>
+ <meta name="viewport" content="width=device-width, initial-scale=1.0" />
+ <meta name="description" content="" />
+ <meta http-equiv="content-language" content="en" />
+
+ <link href="//netdna.bootstrapcdn.com/bootswatch/2.3.2/flatly/bootstrap.min.css" rel="stylesheet" />
+ <link href="//netdna.bootstrapcdn.com/twitter-bootstrap/2.3.1/css/bootstrap-responsive.min.css" rel="stylesheet" />
+ <link href="./css/bootswatch.css" rel="stylesheet" />
+ <link href="./css/reflow-skin.css" rel="stylesheet" />
+
+ <link href="//yandex.st/highlightjs/7.5/styles/default.min.css" rel="stylesheet" />
+
+ <link href="./css/lightbox.css" rel="stylesheet" />
+
+ <link href="./css/site.css" rel="stylesheet" />
+ <link href="./css/print.css" rel="stylesheet" media="print" />
+
+ <!-- Le HTML5 shim, for IE6-8 support of HTML5 elements -->
+ <!--[if lt IE 9]>
+ <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
+ <![endif]-->
+
+
+
+ </head>
+
+ <body class="page-tuning_guide project-phoenix-site" data-spy="scroll" data-offset="60" data-target="#toc-scroll-target">
+
+ <div class="navbar navbar-fixed-top">
+ <div class="navbar-inner">
+ <div class="container">
+ <a class="btn btn-navbar" data-toggle="collapse" data-target="#top-nav-collapse">
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ </a>
+ <a class="brand" href="index.html"><div class="xtoplogo"></div></a>
+ <div class="nav-collapse collapse" id="top-nav-collapse">
+ <ul class="nav pull-right">
+ <li class="dropdown">
+ <a href="#" class="dropdown-toggle" data-toggle="dropdown">About <b class="caret"></b></a>
+ <ul class="dropdown-menu">
+ <li ><a href="index.html" title="Overview">Overview</a></li>
+ <li ><a href="who_is_using.html" title="Who is Using">Who is Using</a></li>
+ <li ><a href="recent.html" title="New Features">New Features</a></li>
+ <li ><a href="roadmap.html" title="Roadmap">Roadmap</a></li>
+ <li ><a href="performance.html" title="Performance">Performance</a></li>
+ <li ><a href="team.html" title="Team">Team</a></li>
+ <li ><a href="resources.html" title="Presentations">Presentations</a></li>
+ <li ><a href="mailing_list.html" title="Mailing Lists">Mailing Lists</a></li>
+ <li ><a href="source.html" title="Source Repository">Source Repository</a></li>
+ <li ><a href="issues.html" title="Issue Tracking">Issue Tracking</a></li>
+ <li ><a href="download.html" title="Download">Download</a></li>
+ <li ><a href="installation.html" title="Installation">Installation</a></li>
+ <li class="divider"/>
+ <li ><a href="contributing.html" title="How to Contribute">How to Contribute</a></li>
+ <li ><a href="develop.html" title="How to Develop">How to Develop</a></li>
+ <li ><a href="building_website.html" title="How to Update Website">How to Update Website</a></li>
+ <li ><a href="release.html" title="How to Release">How to Release</a></li>
+ <li class="divider"/>
+ <li ><a href="http://www.apache.org/licenses/" title="License" class="externalLink">License</a></li>
+ </ul>
+ </li>
+ <li class="dropdown">
+ <a href="#" class="dropdown-toggle" data-toggle="dropdown">Using <b class="caret"></b></a>
+ <ul class="dropdown-menu">
+ <li ><a href="faq.html" title="F.A.Q.">F.A.Q.</a></li>
+ <li ><a href="Phoenix-in-15-minutes-or-less.html" title="Quick Start">Quick Start</a></li>
+ <li ><a href="building.html" title="Building">Building</a></li>
+ <li class="active"><a href="" title="Tuning">Tuning</a></li>
+ <li ><a href="tuning.html" title="Configuration">Configuration</a></li>
+ <li ><a href="upgrading.html" title="Backward Compatibility">Backward Compatibility</a></li>
+ <li ><a href="release_notes.html" title="Release Notes">Release Notes</a></li>
+ <li ><a href="pherf.html" title="Performance Testing">Performance Testing</a></li>
+ <li class="divider"/>
+ <li ><a href="phoenix_spark.html" title="Apache Spark Integration">Apache Spark Integration</a></li>
+ <li ><a href="hive_storage_handler.html" title="Phoenix Storage Handler for Apache Hive">Phoenix Storage Handler for Apache Hive</a></li>
+ <li ><a href="pig_integration.html" title="Apache Pig Integration">Apache Pig Integration</a></li>
+ <li ><a href="phoenix_mr.html" title="Map Reduce Integration">Map Reduce Integration</a></li>
+ <li ><a href="flume.html" title="Apache Flume Plugin">Apache Flume Plugin</a></li>
+ <li ><a href="kafka.html" title="Apache Kafka Plugin">Apache Kafka Plugin</a></li>
+ </ul>
+ </li>
+ <li class="dropdown">
+ <a href="#" class="dropdown-toggle" data-toggle="dropdown">Addons <b class="caret"></b></a>
+ <ul class="dropdown-menu">
+ <li ><a href="http://docs.aws.amazon.com/ElasticMapReduce/latest/ReleaseGuide/emr-phoenix.html" title="Phoenix on Amazon EMR" class="externalLink">Phoenix on Amazon EMR</a></li>
+ <li ><a href="http://python-phoenixdb.readthedocs.io/en/latest" title="Phoenix Adapter for Python" class="externalLink">Phoenix Adapter for Python</a></li>
+ <li ><a href="phoenix_orm.html" title="Phoenix ORM Library">Phoenix ORM Library</a></li>
+ </ul>
+ </li>
+ <li class="dropdown">
+ <a href="#" class="dropdown-toggle" data-toggle="dropdown">Features <b class="caret"></b></a>
+ <ul class="dropdown-menu">
+ <li ><a href="transactions.html" title="Transactions">Transactions</a></li>
+ <li ><a href="udf.html" title="User-defined Functions">User-defined Functions</a></li>
+ <li class="divider"/>
+ <li ><a href="secondary_indexing.html" title="Secondary Indexes">Secondary Indexes</a></li>
+ <li ><a href="columnencoding.html" title="Storage Formats">Storage Formats</a></li>
+ <li ><a href="atomic_upsert.html" title="Atomic Upsert">Atomic Upsert</a></li>
+ <li ><a href="namspace_mapping.html" title="Namespace Mapping">Namespace Mapping</a></li>
+ <li ><a href="update_statistics.html" title="Statistics Collection">Statistics Collection</a></li>
+ <li ><a href="rowtimestamp.html" title="Row Timestamp Column">Row Timestamp Column</a></li>
+ <li ><a href="paged.html" title="Paged Queries">Paged Queries</a></li>
+ <li ><a href="salted.html" title="Salted Tables">Salted Tables</a></li>
+ <li ><a href="skip_scan.html" title="Skip Scan">Skip Scan</a></li>
+ <li class="divider"/>
+ <li ><a href="views.html" title="Views">Views</a></li>
+ <li ><a href="multi-tenancy.html" title="Multi tenancy">Multi tenancy</a></li>
+ <li ><a href="dynamic_columns.html" title="Dynamic Columns">Dynamic Columns</a></li>
+ <li class="divider"/>
+ <li ><a href="bulk_dataload.html" title="Bulk Loading">Bulk Loading</a></li>
+ <li ><a href="server.html" title="Query Server">Query Server</a></li>
+ <li ><a href="tracing.html" title="Tracing">Tracing</a></li>
+ <li ><a href="metrics.html" title="Metrics">Metrics</a></li>
+ </ul>
+ </li>
+ <li class="dropdown">
+ <a href="#" class="dropdown-toggle" data-toggle="dropdown">Reference <b class="caret"></b></a>
+ <ul class="dropdown-menu">
+ <li ><a href="language/index.html" title="Grammar">Grammar</a></li>
+ <li ><a href="language/functions.html" title="Functions">Functions</a></li>
+ <li ><a href="language/datatypes.html" title="Datatypes">Datatypes</a></li>
+ <li ><a href="array_type.html" title="ARRAY type">ARRAY type</a></li>
+ <li class="divider"/>
+ <li ><a href="sequences.html" title="Sequences">Sequences</a></li>
+ <li ><a href="joins.html" title="Joins">Joins</a></li>
+ <li ><a href="subqueries.html" title="Subqueries">Subqueries</a></li>
+ </ul>
+ </li>
+ </ul>
+ </div><!--/.nav-collapse -->
+ </div>
+ </div>
+ </div>
+
+ <div class="container">
+
+ <!-- Masthead
+ ================================================== -->
+
+ <header>
+ </header>
+
+ <div class="main-body">
+ <div class="row">
+ <div class="span12">
+ <div class="body-content">
+<div class="page-header">
+ <h1>Tuning Guide</h1>
+</div>
+<p>Tuning Phoenix can be complex, but with a little knowledge of how it works you can make significant changes to the performance of your reads and writes. The most important factor in performance is the design of your schema, especially as it affects the underlying HBase row keys. Look in “General Tips” below to find design advice for different anticipated data access patterns. Subsequent sections describe how to use secondary indexes, hints, and explain plans.</p>
+<h1>Primary Keys</h1>
+<p>The underlying row key design is the single most important factor in Phoenix performance, and it’s important to get it right at design time because you cannot change it later without re-writing the data and index tables.</p>
+<p>The Phoenix primary keys are concatenated to create the underlying row key in Apache HBase. The columns for the primary key constraint should be chosen and ordered in a way that aligns with the common query patterns—choose the most frequently queried columns as primary keys. The key that you place in the leading position is the most performant one. For example, if you lead off with a column containing org ID values, it is easy to select all rows pertaining to a specific org. You can add the HBase row timestamp to the primary key to improve scan efficiency by skipping rows outside the queried time range. </p>
+<p>Every primary key imposes a cost because the entire row key is appended to every piece of data in memory and on disk. The larger the row key, the greater the storage overhead. Find ways to store information compactly in columns you plan to use for primary keys—store deltas instead of complete time stamps, for example.</p>
+<p>To sum up, the best practice is to design primary keys to add up to a row key that lets you scan the smallest amount of data.</p>
+<p>*<i>Tip: </i>*When choosing primary keys, lead with the column you filter most frequently across the queries that are most important to optimize. If you will use <tt>ORDER BY</tt> in your query, make sure your PK columns match the expressions in your <tt>ORDER BY</tt> clause. </p>
+<div class="section">
+ <div class="section">
+ <h3 id="Monotonically_increasing_Primary_keys">Monotonically increasing Primary keys</h3>
+ <p>If your primary keys are monotonically increasing, use salting to help distribute writes across the cluster and improve parallelization. Example:</p>
+ <p><tt>CREATE TABLE … ( … ) SALT_BUCKETS = N</tt></p>
+ <p>For optimal performance the number of salt buckets should approximately equal the number of region servers. Do not salt automatically. Use salting only when experiencing hotspotting. The downside of salting is that it imposes a cost on read because when you want to query the data you have to run multiple queries to do a range scan.</p>
+ <h1>General Tips</h1>
+ <p>The following sections provide a few general tips for different access scenarios.</p>
+ </div>
+ <div class="section">
+ <h3 id="Is_the_Data_Random-Access">Is the Data Random-Access?</h3>
+ <ul>
+ <li>As with any random read workloads, SSDs can improve performance because of their faster random seek time.</li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Is_the_data_read-heavy_or_write-heavy">Is the data read-heavy or write-heavy?</h3>
+ <ul>
+ <li>For read-heavy data:
+ <ul>
+ <li>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.</li>
+ <li>Use multiple indexes to provide fast access to common queries.</li>
+ <li>When specifying machines for HBase, do not skimp on cores; HBase needs them.</li>
+ </ul></li>
+ <li>For write-heavy data:
+ <ul>
+ <li>Pre-split the table. It can be helpful to split the table into pre-defined regions, or if the keys are monotonically increasing use salting to to avoid creating write hotspots on a small number of nodes. Use real data types rather than raw byte data.</li>
+ <li>Create local indexes. Reads from local indexes have a performance penalty, so it’s important to do performance testing. See the <a class="externalLink" href="https://phoenix.apache.org/pherf.html">Pherf</a> tool.</li>
+ </ul></li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Which_columns_will_be_accessed_often">Which columns will be accessed often?</h3>
+ <ul>
+ <li>Choose commonly-queried columns as primary keys. For more information, see “Primary Keys” below.
+ <ul>
+ <li>Create additional indexes to support common query patterns, including heavily accessed fields that are not in the primary key.</li>
+ </ul></li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Can_the_data_be_append-only_immutable">Can the data be append-only (immutable)?</h3>
+ <ul>
+ <li>If the data is immutable or append-only, declare the table and its indexes as immutable using the <tt>IMMUTABLE_ROWS</tt> <a class="externalLink" href="http://phoenix.apache.org/language/index.html#options">option</a> at creation time to reduce the write-time cost. If you need to make an existing table immutable, you can do so with <tt>ALTER TABLE trans.event SET IMMUTABLE_ROWS=true</tt> after creation time.
+ <ul>
+ <li>If speed is more important than data integrity, you can use the <tt>DISABLE_WAL</tt> <a class="externalLink" href="http://phoenix.apache.org/language/index.html#options">option</a>. Note: it is possible to lose data with <tt>DISABLE_WAL</tt> if a region server fails.</li>
+ </ul></li>
+ <li>Set the <tt>UPDATE_CACHE_FREQUENCY</tt> <a class="externalLink" href="http://phoenix.apache.org/language/index.html#options">option</a> 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.</li>
+ <li>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. For more information, see “<a class="externalLink" href="https://blogs.apache.org/phoenix/entry/column-mapping-and-immutable-data">Column Mapping and Immutable Data Encoding</a>” on the Apache Phoenix blog.</li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Is_the_table_very_large">Is the table very large?</h3>
+ <ul>
+ <li>Use the <tt>ASYNC</tt> keyword with your <tt>CREATE INDEX</tt> call to create the index asynchronously via MapReduce job. You’ll need to manually start the job; see <a class="externalLink" href="https://phoenix.apache.org/secondary_indexing.html#Index_Population">https://phoenix.apache.org/secondary_indexing.html#Index_Population</a> for details.</li>
+ <li>If the data is too large to scan the table completely, use primary keys to create an underlying composite row key that makes it easy to return a subset of the data or facilitates <a class="externalLink" href="https://phoenix.apache.org/skip_scan.html">skip-scanning</a>—Phoenix can jump directly to matching keys when the query includes key sets in the predicate.</li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Is_transactionality_required">Is transactionality required?</h3>
+ <p>A transaction is a data operation that is atomic—that is, guaranteed to succeed completely or not at all. For example, if you need to make cross-row updates to a data table, then you should consider your data transactional.</p>
+ <ul>
+ <li>If you need transactionality, use the <tt>TRANSACTIONAL</tt> <a class="externalLink" href="http://phoenix.apache.org/language/index.html#options">option</a>. (See also <a class="externalLink" href="http://phoenix.apache.org/transactions.html.">http://phoenix.apache.org/transactions.html.</a>)</li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Block_Encoding">Block Encoding</h3>
+ <p>Using compression or encoding is a must. Both SNAPPY and FAST_DIFF are good all around options.</p>
+ <p><tt>FAST_DIFF</tt> encoding is automatically enabled on all Phoenix tables by default, and almost always improves overall read latencies and throughput by allowing more data to fit into blockcache. Note: <tt>FAST_DIFF</tt> encoding can increase garbage produced during request processing.</p>
+ <p>Set encoding at table creation time. Example: <tt>CREATE TABLE … ( … ) DATA_BLOCK_ENCODING=‘FAST_DIFF’</tt></p>
+ <h1>Schema Design</h1>
+ <p>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. </p>
+ </div>
+</div>
+<div class="section">
+ <h2 id="Phoenix_and_the_HBase_data_model">Phoenix and the HBase data model</h2>
+ <p>HBase stores data in tables, which in turn contain columns grouped in column families. A row in an HBase table consists of versioned cells associated with one or more columns. An HBase row is a collection of many key-value pairs in which the rowkey attribute of the keys are equal. Data in an HBase table is sorted by the rowkey, and all access is via the rowkey. 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. For this reason, 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.</p>
+</div>
+<div class="section">
+ <h2 id="Column_Families">Column Families</h2>
+ <p>If some columns are accessed more frequently than others, <a class="externalLink" href="https://phoenix.apache.org/faq.html#Are_there_any_tips_for_optimizing_Phoenix">create multiple column families</a> to separate the frequently-accessed columns from rarely-accessed columns. This improves performance because HBase reads only the column families specified in the query.</p>
+</div>
+<div class="section">
+ <h2 id="Columns">Columns</h2>
+ <p>Here are a few tips that apply to columns in general, whether they are indexed or not:</p>
+ <ul>
+ <li>Keep <tt>VARCHAR</tt> columns under 1MB or so due to I/O costs. 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.</li>
+ <li>For structured objects, don’t use JSON, which is not very compact. Use a format such as protobuf, Avro, msgpack, or BSON.</li>
+ <li>Consider compressing data before storage using a fast LZ variant to cut latency and I/O costs.</li>
+ <li>Use the column mapping feature (added in Phoenix 4.10), which uses numerical HBase column qualifiers for non-PK columns instead of directly using column names. This improves performance when looking for a cell in the sorted list of cells returned by HBase, adds further across-the-board performance by reducing the disk size used by tables, and speeds up DDL operations like column rename and metadata-level column drops. For more information, see “<a class="externalLink" href="https://blogs.apache.org/phoenix/entry/column-mapping-and-immutable-data">Column Mapping and Immutable Data Encoding</a>” on the Apache Phoenix blog.</li>
+ </ul>
+ <h1>Indexes</h1>
+ <p>A Phoenix index is a physical table that stores a pivoted copy of some or all of the data in the main table, to serve specific kinds of queries. When you issue a query, Phoenix selects the best index for the query automatically. The primary index is created automatically based on the primary keys you select. You can create secondary indexes, specifying which columns are included based on the anticipated queries the index will support.</p>
+ <p>See also: <a class="externalLink" href="https://phoenix.apache.org/secondary_indexing.html">Secondary Indexing</a></p>
+</div>
+<div class="section">
+ <h2 id="Secondary_indexes">Secondary indexes</h2>
+ <p>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. A small number of secondary indexes is often sufficient. Depending on your needs, consider creating <i><a class="externalLink" href="http://phoenix.apache.org/secondary_indexing.html#Covered_Indexes">covered</a></i> indexes or <i><a class="externalLink" href="http://phoenix.apache.org/secondary_indexing.html#Functional_Indexes">functional</a></i> indexes, or both.</p>
+ <p>If your table is large, use the <tt>ASYNC</tt> keyword with <tt>CREATE INDEX</tt> to create the index asynchronously. In this case, the index will be built through MapReduce, which means that the client going up or down won’t impact index creation and the job is retried automatically if necessary. You’ll need to manually start the job, which you can then monitor just as you would any other MapReduce job.</p>
+ <p>Example: <tt>create index if not exists event_object_id_idx_b on trans.event (object_id) ASYNC UPDATE_CACHE_FREQUENCY=60000;</tt></p>
+ <p>See <a class="externalLink" href="https://phoenix.apache.org/secondary_indexing.html#Index_Population">Index Population</a> for details.</p>
+ <p>If you can’t create the index asynchronously for some reason, then increase the query timeout (<tt>phoenix.query.timeoutMs</tt>) to be larger than the time it’ll take to build the index. If the <tt>CREATE INDEX</tt> call times out or the client goes down before it’s finished, then the index build will stop and must be run again. You can monitor the index table as it is created—you’ll see new regions created as splits occur. You can query the <tt>SYSTEM.STATS</tt> table, which gets populated as splits and compactions happen. You can also run a <tt>count(*)</tt> query directly against the index table, though that puts more load on your system because requires a full table scan.</p>
+ <p>Tips:</p>
+ <ul>
+ <li>Create <a class="externalLink" href="https://phoenix.apache.org/secondary_indexing.html#Local_Indexes">local</a> indexes for write-heavy use cases.</li>
+ <li>Create global indexes for read-heavy use cases. To save read-time overhead, consider creating <a class="externalLink" href="https://phoenix.apache.org/secondary_indexing.html#Covered_Indexes">covered</a> indexes.</li>
+ <li>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.</li>
+ <li>Set up a cron job to build indexes. Use <tt>ASYNC</tt> with <tt>CREATE INDEX</tt> to avoid blocking.</li>
+ <li>Only create the indexes you need.</li>
+ <li>Limit the number of indexes on frequently updated tables.</li>
+ <li>Use covered indexes to convert table scans into efficient point lookups or range queries over the index table instead of the primary table: <tt>CREATE INDEX index ON table</tt>( … )<tt>INCLUDE</tt>( … )</li>
+ </ul>
+ <h1>Queries</h1>
+ <p>It’s important to know which queries are executed on the server side versus the client side, because this can impact performace due to network I/O and other bottlenecks. If you’re querying a billion-row table, you want to do as much computation as possible on the server side rather than transmitting a billion rows to the client for processing. Some queries, on the other hand, must be executed on the client. Sorting data that lives on multiple region servers, for example, requires that you aggregate and re-sort on the client. </p>
+</div>
+<div class="section">
+ <h2 id="Reading">Reading</h2>
+ <ul>
+ <li>Avoid joins unless one side is small, especially on frequent queries. For larger joins, see “Hints,” below.</li>
+ <li>In the <tt>WHERE</tt> clause, filter leading columns in the primary key constraint.</li>
+ <li>Filtering the first leading column with <tt>IN</tt> or <tt>OR</tt> in the <tt>WHERE</tt> clause enables skip scan optimizations.</li>
+ <li>Equality or comparisions (<tt><</tt> or <tt>></tt>) in the <tt>WHERE</tt> clause enables range scan optimizations.</li>
+ <li>Let Phoenix optimize query parallelism using statistics. This provides an automatic benefit if using Phoenix 4.2 or greater in production.</li>
+ </ul>
+ <p>See also: <a class="externalLink" href="https://phoenix.apache.org/joins.html">https://phoenix.apache.org/joins.html</a></p>
+ <div class="section">
+ <h3 id="Range_Queries">Range Queries</h3>
+ <p>If you regularly scan large data sets from spinning disk, you’re best off with GZIP (but watch write speed). Use a lot of cores for a scan to utilize the available memory bandwidth. Apache Phoenix makes it easy to utilize many cores to increase scan performance.</p>
+ <p>For range queries, the HBase block cache does not provide much advantage.</p>
+ </div>
+ <div class="section">
+ <h3 id="Large_Range_Queries">Large Range Queries</h3>
+ <p>For large range queries, consider setting <tt>Scan.setCacheBlocks(false)</tt> even if the whole scan could fit into the block cache.</p>
+ <p>If you mostly perform large range queries you might even want to consider running HBase with a much smaller heap and size the block cache down, to only rely on the OS Cache. This will alleviate some garbage collection related issues.</p>
+ </div>
+ <div class="section">
+ <h3 id="Point_Lookups">Point Lookups</h3>
+ <p>For point lookups it is quite important to have your data set cached, and you should use the HBase block cache. </p>
+ </div>
+ <div class="section">
+ <h3 id="Hints">Hints</h3>
+ <p>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. </p>
+ <ul>
+ <li>During the query, Hint global index if you want to force it when query includes a column not in the index.</li>
+ <li>If necessary, you can do bigger joins with the <tt>/*+ USE_SORT_MERGE_JOIN */</tt> hint, but a big join will be an expensive operation over huge numbers of rows.</li>
+ <li>If the overall size of all right-hand-side tables would exceed the memory size limit, use the <tt>/*+ NO_STAR_JOIN */</tt>hint.</li>
+ </ul>
+ <p>See also: <a class="externalLink" href="https://phoenix.apache.org/language/#hint">Hint</a>.</p>
+ </div>
+</div>
+<div class="section">
+ <h2 id="Writing">Writing</h2>
+ <div class="section">
+ <h3 id="Batching_large_numbers_of_records">Batching large numbers of records</h3>
+ <p>When using <tt>UPSERT</tt> to write a large number of records, turn off autocommit and batch records. <b>Note:</b> Phoenix uses <tt>commit()</tt> instead of <tt>executeBatch()</tt> to control batch updates.</p>
+ <p>Start with a batch size of 1000 and adjust as needed. Here’s some pseudocode showing one way to commit records in batches:</p>
+ <div class="source">
+ <pre>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
+ }
+</pre>
+ </div>
+ <p><b>Note:</b> Because the Phoenix client keeps uncommitted rows in memory, be careful not to set <tt>commitSize</tt> too high.</p>
+ </div>
+ <div class="section">
+ <h3 id="Reducing_RPC_traffic">Reducing RPC traffic</h3>
+ <p>To reduce RPC traffic, set the <tt>UPDATE_CACHE_FREQUENCY</tt> (4.7 or above) on your table and indexes when you create them (or issue an <tt>ALTER TABLE</tt>/<tt>INDEX</tt> call. See <a class="externalLink" href="https://phoenix.apache.org/#Altering">https://phoenix.apache.org/#Altering</a>.</p>
+ </div>
+ <div class="section">
+ <h3 id="Using_local_indexes">Using local indexes</h3>
+ <p>If using 4.8, consider using local indexes to minimize the write time. In this case, 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.</p>
+ </div>
+</div>
+<div class="section">
+ <h2 id="Deleting">Deleting</h2>
+ <p>When deleting a large data set, turn on autoCommit before issuing the <tt>DELETE</tt> 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 <tt>DELETE</tt> so that Phoenix can delete them directly on the region servers without the expense of returning them to the client.</p>
+ <h1>Explain Plans</h1>
+ <p>An <tt>EXPLAIN</tt> plan tells you a lot about how a query will be run:</p>
+ <ul>
+ <li>All the HBase range queries that will be executed</li>
+ <li>The number of bytes that will be scanned</li>
+ <li>The number of rows that will be traversed</li>
+ <li>Which HBase table will be used for each scan</li>
+ <li>Which operations (sort, merge, scan, limit) are executed on the client versus the server</li>
+ </ul>
+ <p>Use an <tt>EXPLAIN</tt> plan to check how a query will run, and consider rewriting queries to meet the following goals:</p>
+ <ul>
+ <li>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.</li>
+ <li>Use <tt>RANGE SCAN</tt> or <tt>SKIP SCAN</tt> whenever possible rather than <tt>TABLE SCAN</tt>.</li>
+ <li>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.</li>
+ <li>If necessary, introduce a local index or a global index that covers your query.</li>
+ <li>If you have an index that covers your query but the optimizer is not detecting it, try hinting the query: <tt>SELECT /*+ INDEX() */ …</tt></li>
+ </ul>
+ <div class="section">
+ <h3 id="Anatomy_of_an_Explain_Plan">Anatomy of an Explain Plan</h3>
+ <p>An explain plan consists of lines of text that describe operations that Phoenix will perform during a query, using the following terms:</p>
+ <ul>
+ <li><tt>AGGREGATE INTO ORDERED DISTINCT ROWS</tt>—aggregates the returned rows using an operation such as addition. When <tt>ORDERED</tt> is used, the <tt>GROUP BY</tt> operation is applied to the leading part of the primary key constraint, which allows the aggregation to be done in place rather than keeping all distinct groups in memory on the server side.</li>
+ <li><tt>AGGREGATE INTO SINGLE ROW</tt>—aggregates the results into a single row using an aggregate function with no <tt>GROUP BY</tt> clause. For example, the <tt>count()</tt> statement returns one row with the total number of rows that match the query.</li>
+ <li><tt>CLIENT</tt>—the operation will be performed on the client side. It’s faster to perform most operations on the server side, so you should consider whether there’s a way to rewrite the query to give the server more of the work to do.</li>
+ <li><tt>FILTER BY</tt> expression—returns only results that match the expression.</li>
+ <li><tt>FULL SCAN OVER</tt> tableName—the operation will scan every row in the specified table.</li>
+ <li><tt>INNER-JOIN</tt>—the operation will join multiple tables on rows where the join condition is met.</li>
+ <li><tt>MERGE SORT</tt>—performs a merge sort on the results.</li>
+ <li><tt>RANGE SCAN OVER</tt> tableName <tt>[</tt> … <tt>]</tt>—The information in the square brackets indicates the start and stop for each primary key that’s used in the query.</li>
+ <li><tt>ROUND ROBIN</tt>—when the query doesn’t contain <tt>ORDER BY</tt> and therefore the rows can be returned in any order, <tt>ROUND ROBIN</tt> order maximizes parallelization on the client side.</li>
+ <li>x<tt>-CHUNK</tt>—describes how many threads will be used for the operation. The maximum parallelism is limited to the number of threads in thread pool. The minimum parallelization corresponds to the number of regions the table has between the start and stop rows of the scan. The number of chunks will increase with a lower guidepost width, as there is more than one chunk per region.</li>
+ <li><tt>PARALLEL</tt>x-<tt>WAY</tt>—describes how many parallel scans will be merge sorted during the operation.</li>
+ <li><tt>SERIAL</tt>—some queries run serially. For example, a single row lookup or a query that filters on the leading part of the primary key and limits the results below a configurable threshold.</li>
+ </ul>
+ </div>
+ <div class="section">
+ <h3 id="Example">Example</h3>
+ <div class="source">
+ <pre>+------------------------------------------+
+| PLAN |
++------------------------------------------+
+| CLIENT 36-CHUNK PARALLEL 36-WAY FULL SCAN OVER exDocStoreb |
+| PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE) |
+| CLIENT 36-CHUNK PARALLEL 36-WAY RANGE SCAN OVER indx_exdocb [0,' 42ecf4abd4bd7e7606025dc8eee3de 6a3cc04418cbc2619ddc01f54d88d7 c3bf'] - [0,' 42ecf4abd4bd7e7606025dc8eee3de 6a3cc04418cbc2619ddc01f54d88d7 c3bg' |
+| SERVER FILTER BY FIRST KEY ONLY |
+| SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY ["ID"] |
+| CLIENT MERGE SORT |
+| DYNAMIC SERVER FILTER BY (A.CURRENT_TIMESTAMP, [A.ID](http://a.id/)) IN ((TMP.MCT, TMP.TID)) |
++-------------------------------------------+
+</pre>
+ </div>
+ <p>See also: <a class="externalLink" href="http://phoenix.apache.org/language/index.html#explain">http://phoenix.apache.org/language/index.html#explain</a></p>
+ <h1>Improving parallelization</h1>
+ <p>You can improve parallelization with the <a class="externalLink" href="https://phoenix.apache.org/update_statistics.html">UPDATE STATISTICS</a> command. This command subdivides each region by determining keys called <i>guideposts</i> that are equidistant from each other, then uses these guideposts to break up queries into multiple parallel scans. Statistics are turned on by default. With Phoenix 4.9, the user can set guidepost width for each table. Optimal guidepost width depends on a number of factors such as cluster size, cluster usage, number of cores per node, table size, and disk I/O.</p>
+ <h1>Further Tuning</h1>
+ <p>For advice about tuning the underlying HBase and JVM layers, see <a class="externalLink" href="https://hbase.apache.org/book.html#schema.ops">Operational and Performance Configuration Options</a> in the Apache HBase™ Reference Guide.</p>
+ </div>
+</div>
+<div class="section">
+ <h2 id="Special_Cases">Special Cases</h2>
+ <p>The following sections provide Phoenix-specific additions to the tuning recommendations in the Apache HBase™ Reference Guide section referenced above. </p>
+ <div class="section">
+ <h3 id="For_applications_where_failing_quickly_is_better_than_waiting">For applications where failing quickly is better than waiting</h3>
+ <p>In addition to the HBase tuning referenced above, set <tt>phoenix.query.timeoutMs</tt> in <tt>hbase-site.xml</tt> on the client side to the maximum tolerable wait time in milliseconds.</p>
+ </div>
+ <div class="section">
+ <h3 id="For_applications_that_can_tolerate_slightly_out_of_date_information">For applications that can tolerate slightly out of date information</h3>
+ <p>In addition to the HBase tuning referenced above, set <tt>phoenix.connection.consistency = timeline</tt> in <tt>hbase-site.xml</tt> on the client side for all connections.</p>
+ </div>
+</div>
+ </div>
+ </div>
+ </div>
+ </div>
+
+ </div><!-- /container -->
+
+ <!-- Footer
+ ================================================== -->
+ <footer class="well">
+ <div class="container">
+ <div class="row">
+ <div class="span2 bottom-nav">
+ <ul class="nav nav-list">
+ <li class="nav-header">About</li>
+ <li >
+ <a href="index.html" title="Overview">Overview</a>
+ </li>
+ <li >
+ <a href="who_is_using.html" title="Who is Using">Who is Using</a>
+ </li>
+ <li >
+ <a href="recent.html" title="New Features">New Features</a>
+ </li>
+ <li >
+ <a href="roadmap.html" title="Roadmap">Roadmap</a>
+ </li>
+ <li >
+ <a href="performance.html" title="Performance">Performance</a>
+ </li>
+ <li >
+ <a href="team.html" title="Team">Team</a>
+ </li>
+ <li >
+ <a href="resources.html" title="Presentations">Presentations</a>
+ </li>
+ <li >
+ <a href="mailing_list.html" title="Mailing Lists">Mailing Lists</a>
+ </li>
+ <li >
+ <a href="source.html" title="Source Repository">Source Repository</a>
+ </li>
+ <li >
+ <a href="issues.html" title="Issue Tracking">Issue Tracking</a>
+ </li>
+ <li >
+ <a href="download.html" title="Download">Download</a>
+ </li>
+ <li >
+ <a href="installation.html" title="Installation">Installation</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="contributing.html" title="How to Contribute">How to Contribute</a>
+ </li>
+ <li >
+ <a href="develop.html" title="How to Develop">How to Develop</a>
+ </li>
+ <li >
+ <a href="building_website.html" title="How to Update Website">How to Update Website</a>
+ </li>
+ <li >
+ <a href="release.html" title="How to Release">How to Release</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="http://www.apache.org/licenses/" title="License" class="externalLink">License</a>
+ </li>
+ </ul>
+ </div>
+ <div class="span2 bottom-nav">
+ <ul class="nav nav-list">
+ <li class="nav-header">Using</li>
+ <li >
+ <a href="faq.html" title="F.A.Q.">F.A.Q.</a>
+ </li>
+ <li >
+ <a href="Phoenix-in-15-minutes-or-less.html" title="Quick Start">Quick Start</a>
+ </li>
+ <li >
+ <a href="building.html" title="Building">Building</a>
+ </li>
+ <li class="active">
+ <a href="#" title="Tuning">Tuning</a>
+ </li>
+ <li >
+ <a href="tuning.html" title="Configuration">Configuration</a>
+ </li>
+ <li >
+ <a href="upgrading.html" title="Backward Compatibility">Backward Compatibility</a>
+ </li>
+ <li >
+ <a href="release_notes.html" title="Release Notes">Release Notes</a>
+ </li>
+ <li >
+ <a href="pherf.html" title="Performance Testing">Performance Testing</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="phoenix_spark.html" title="Apache Spark Integration">Apache Spark Integration</a>
+ </li>
+ <li >
+ <a href="hive_storage_handler.html" title="Phoenix Storage Handler for Apache Hive">Phoenix Storage Handler for Apache Hive</a>
+ </li>
+ <li >
+ <a href="pig_integration.html" title="Apache Pig Integration">Apache Pig Integration</a>
+ </li>
+ <li >
+ <a href="phoenix_mr.html" title="Map Reduce Integration">Map Reduce Integration</a>
+ </li>
+ <li >
+ <a href="flume.html" title="Apache Flume Plugin">Apache Flume Plugin</a>
+ </li>
+ <li >
+ <a href="kafka.html" title="Apache Kafka Plugin">Apache Kafka Plugin</a>
+ </li>
+ </ul>
+ </div>
+ <div class="span2 bottom-nav">
+ <ul class="nav nav-list">
+ <li class="nav-header">Features</li>
+ <li >
+ <a href="transactions.html" title="Transactions">Transactions</a>
+ </li>
+ <li >
+ <a href="udf.html" title="User-defined Functions">User-defined Functions</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="secondary_indexing.html" title="Secondary Indexes">Secondary Indexes</a>
+ </li>
+ <li >
+ <a href="columnencoding.html" title="Storage Formats">Storage Formats</a>
+ </li>
+ <li >
+ <a href="atomic_upsert.html" title="Atomic Upsert">Atomic Upsert</a>
+ </li>
+ <li >
+ <a href="namspace_mapping.html" title="Namespace Mapping">Namespace Mapping</a>
+ </li>
+ <li >
+ <a href="update_statistics.html" title="Statistics Collection">Statistics Collection</a>
+ </li>
+ <li >
+ <a href="rowtimestamp.html" title="Row Timestamp Column">Row Timestamp Column</a>
+ </li>
+ <li >
+ <a href="paged.html" title="Paged Queries">Paged Queries</a>
+ </li>
+ <li >
+ <a href="salted.html" title="Salted Tables">Salted Tables</a>
+ </li>
+ <li >
+ <a href="skip_scan.html" title="Skip Scan">Skip Scan</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="views.html" title="Views">Views</a>
+ </li>
+ <li >
+ <a href="multi-tenancy.html" title="Multi tenancy">Multi tenancy</a>
+ </li>
+ <li >
+ <a href="dynamic_columns.html" title="Dynamic Columns">Dynamic Columns</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="bulk_dataload.html" title="Bulk Loading">Bulk Loading</a>
+ </li>
+ <li >
+ <a href="server.html" title="Query Server">Query Server</a>
+ </li>
+ <li >
+ <a href="tracing.html" title="Tracing">Tracing</a>
+ </li>
+ <li >
+ <a href="metrics.html" title="Metrics">Metrics</a>
+ </li>
+ </ul>
+ </div>
+ <div class="span3 bottom-nav">
+ <ul class="nav nav-list">
+ <li class="nav-header">Reference</li>
+ <li >
+ <a href="language/index.html" title="Grammar">Grammar</a>
+ </li>
+ <li >
+ <a href="language/functions.html" title="Functions">Functions</a>
+ </li>
+ <li >
+ <a href="language/datatypes.html" title="Datatypes">Datatypes</a>
+ </li>
+ <li >
+ <a href="array_type.html" title="ARRAY type">ARRAY type</a>
+ </li>
+ <li >
+ <a href="http:divider" title=""></a>
+ </li>
+ <li >
+ <a href="sequences.html" title="Sequences">Sequences</a>
+ </li>
+ <li >
+ <a href="joins.html" title="Joins">Joins</a>
+ </li>
+ <li >
+ <a href="subqueries.html" title="Subqueries">Subqueries</a>
+ </li>
+ </ul>
+ </div>
+ <div class="span3 bottom-description">
+ <form action="http://search-hadoop.com/?" method="get"><input value="Phoenix" name="fc_project" type="hidden"><input placeholder="Search Phoenix…" required="required" style="width:170px;" size="18" name="q" id="query" type="search"></form>
+ </div>
+ </div>
+ </div>
+ </footer>
+
+ <div class="container subfooter">
+ <div class="row">
+ <div class="span12">
+ <p class="pull-right"><a href="#">Back to top</a></p>
+ <p class="copyright">Copyright ©2017 <a href="http://www.apache.org">Apache Software Foundation</a>. All Rights Reserved.</p>
+ </div>
+ </div>
+ </div>
+
+ <!-- Le javascript
+ ================================================== -->
+ <!-- Placed at the end of the document so the pages load faster -->
+ <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
+
+ <script src="//netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/js/bootstrap.min.js"></script>
+ <script src="./js/lightbox.js"></script>
+ <script src="./js/jquery.smooth-scroll.min.js"></script>
+ <!-- back button support for smooth scroll -->
+ <script src="./js/jquery.ba-bbq.min.js"></script>
+ <script src="//yandex.st/highlightjs/7.5/highlight.min.js"></script>
+
+ <script src="./js/reflow-skin.js"></script>
+
+ </body>
+</html>
Added: phoenix/site/source/src/site/markdown/tuning_guide.md
URL: http://svn.apache.org/viewvc/phoenix/site/source/src/site/markdown/tuning_guide.md?rev=1788628&view=auto
==============================================================================
--- phoenix/site/source/src/site/markdown/tuning_guide.md (added)
+++ phoenix/site/source/src/site/markdown/tuning_guide.md Sat Mar 25 01:44:50 2017
@@ -0,0 +1,292 @@
+# Tuning Guide
+
+Tuning Phoenix can be complex, but with a little knowledge of how it works you can make significant changes to the performance of your reads and writes. The most important factor in performance is the design of your schema, especially as it affects the underlying HBase row keys. Look in “General Tips” below to find design advice for different anticipated data access patterns. Subsequent sections describe how to use secondary indexes, hints, and explain plans.
+
+# Primary Keys
+
+The underlying row key design is the single most important factor in Phoenix performance, and it's important to get it right at design time because you cannot change it later without re-writing the data and index tables.
+
+ The Phoenix primary keys are concatenated to create the underlying row key in Apache HBase. The columns for the primary key constraint should be chosen and ordered in a way that aligns with the common query patterns—choose the most frequently queried columns as primary keys. The key that you place in the leading position is the most performant one. For example, if you lead off with a column containing org ID values, it is easy to select all rows pertaining to a specific org. You can add the HBase row timestamp to the primary key to improve scan efficiency by skipping rows outside the queried time range.
+
+Every primary key imposes a cost because the entire row key is appended to every piece of data in memory and on disk. The larger the row key, the greater the storage overhead. Find ways to store information compactly in columns you plan to use for primary keys—store deltas instead of complete time stamps, for example.
+
+To sum up, the best practice is to design primary keys to add up to a row key that lets you scan the smallest amount of data.
+
+**Tip: **When choosing primary keys, lead with the column you filter most frequently across the queries that are most important to optimize. If you will use `ORDER BY` in your query, make sure your PK columns match the expressions in your `ORDER BY` clause.
+
+### Monotonically increasing Primary keys
+
+If your primary keys are monotonically increasing, use salting to help distribute writes across the cluster and improve parallelization. Example:
+
+`CREATE TABLE … ( … ) SALT_BUCKETS = N`
+
+For optimal performance the number of salt buckets should approximately equal the number of region servers. Do not salt automatically. Use salting only when experiencing hotspotting. The downside of salting is that it imposes a cost on read because when you want to query the data you have to run multiple queries to do a range scan.
+
+
+# General Tips
+
+The following sections provide a few general tips for different access scenarios.
+
+### Is the Data Random-Access?
+
+* As with any random read workloads, SSDs can improve performance because of their faster random seek time.
+
+### Is the data read-heavy or write-heavy?
+
+* For read-heavy data:
+ * 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.
+* For write-heavy data:
+ * Pre-split the table. It can be helpful to split the table into pre-defined regions, or if the keys are monotonically increasing use salting to to avoid creating write hotspots on a small number of nodes. Use real data types rather than raw byte data.
+ * Create local indexes. Reads from local indexes have a performance penalty, so it's important to do performance testing. See the [Pherf](https://phoenix.apache.org/pherf.html) tool.
+
+
+
+
+
+### Which columns will be accessed often?
+
+* Choose commonly-queried columns as primary keys. For more information, see “Primary Keys” below.
+ * 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)?
+
+* If the data is immutable or append-only, declare the table and its indexes as immutable using the `IMMUTABLE_ROWS` [option](http://phoenix.apache.org/language/index.html#options) at creation time to reduce the write-time cost. If you need to make an existing table immutable, you can do so with `ALTER TABLE trans.event SET IMMUTABLE_ROWS=true` after creation time.
+ * If speed is more important than data integrity, you can use the `DISABLE_WAL` [option](http://phoenix.apache.org/language/index.html#options). Note: it is possible to lose data with `DISABLE_WAL` if a region server fails.
+* Set the `UPDATE_CACHE_FREQUENCY` [option](http://phoenix.apache.org/language/index.html#options) 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. For more information, see “[Column Mapping and Immutable Data Encoding](https://blogs.apache.org/phoenix/entry/column-mapping-and-immutable-data)” on the Apache Phoenix blog.
+
+### Is the table very large?
+
+* Use the `ASYNC` keyword with your `CREATE INDEX` call to create the index asynchronously via MapReduce job. You'll need to manually start the job; see https://phoenix.apache.org/secondary_indexing.html#Index_Population for details.
+* If the data is too large to scan the table completely, use primary keys to create an underlying composite row key that makes it easy to return a subset of the data or facilitates [skip-scanning](https://phoenix.apache.org/skip_scan.html)—Phoenix can jump directly to matching keys when the query includes key sets in the predicate.
+
+### Is transactionality required?
+
+A transaction is a data operation that is atomic—that is, guaranteed to succeed completely or not at all. For example, if you need to make cross-row updates to a data table, then you should consider your data transactional.
+
+* If you need transactionality, use the `TRANSACTIONAL` [option](http://phoenix.apache.org/language/index.html#options). (See also http://phoenix.apache.org/transactions.html.)
+
+### Block Encoding
+
+Using compression or encoding is a must. Both SNAPPY and FAST_DIFF are good all around options.
+
+`FAST_DIFF` encoding is automatically enabled on all Phoenix tables by default, and almost always improves overall read latencies and throughput by allowing more data to fit into blockcache. Note: `FAST_DIFF` encoding can increase garbage produced during request processing.
+
+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
+
+HBase stores data in tables, which in turn contain columns grouped in column families. A row in an HBase table consists of versioned cells associated with one or more columns. An HBase row is a collection of many key-value pairs in which the rowkey attribute of the keys are equal. Data in an HBase table is sorted by the rowkey, and all access is via the rowkey.
+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. For this reason, 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
+
+If some columns are accessed more frequently than others, [create multiple column families](https://phoenix.apache.org/faq.html#Are_there_any_tips_for_optimizing_Phoenix) to separate the frequently-accessed columns from rarely-accessed columns. This improves performance because HBase reads only the column families specified in the query.
+
+
+
+## Columns
+
+Here are a few tips that apply to columns in general, whether they are indexed or not:
+
+* Keep `VARCHAR` columns under 1MB or so due to I/O costs. 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.
+* For structured objects, don't use JSON, which is not very compact. Use a format such as protobuf, Avro, msgpack, or BSON.
+* Consider compressing data before storage using a fast LZ variant to cut latency and I/O costs.
+* Use the column mapping feature (added in Phoenix 4.10), which uses numerical HBase column qualifiers for non-PK columns instead of directly using column names. This improves performance when looking for a cell in the sorted list of cells returned by HBase, adds further across-the-board performance by reducing the disk size used by tables, and speeds up DDL operations like column rename and metadata-level column drops. For more information, see “[Column Mapping and Immutable Data Encoding](https://blogs.apache.org/phoenix/entry/column-mapping-and-immutable-data)” on the Apache Phoenix blog.
+
+# Indexes
+
+A Phoenix index is a physical table that stores a pivoted copy of some or all of the data in the main table, to serve specific kinds of queries. When you issue a query, Phoenix selects the best index for the query automatically. The primary index is created automatically based on the primary keys you select. You can create secondary indexes, specifying which columns are included based on the anticipated queries the index will support.
+
+See also:
+[Secondary Indexing](https://phoenix.apache.org/secondary_indexing.html)
+
+## Secondary 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. A small number of secondary indexes is often sufficient. Depending on your needs, consider creating *[covered](http://phoenix.apache.org/secondary_indexing.html#Covered_Indexes)* indexes or *[functional](http://phoenix.apache.org/secondary_indexing.html#Functional_Indexes)* indexes, or both.
+
+If your table is large, use the `ASYNC` keyword with `CREATE INDEX` to create the index asynchronously. In this case, the index will be built through MapReduce, which means that the client going up or down won't impact index creation and the job is retried automatically if necessary. You'll need to manually start the job, which you can then monitor just as you would any other MapReduce job.
+
+Example:
+`create index if not exists event_object_id_idx_b on trans.event (object_id) ASYNC UPDATE_CACHE_FREQUENCY=60000;`
+
+See [Index Population](https://phoenix.apache.org/secondary_indexing.html#Index_Population) for details.
+
+If you can't create the index asynchronously for some reason, then increase the query timeout (`phoenix.query.timeoutMs`) to be larger than the time it'll take to build the index. If the `CREATE INDEX` call times out or the client goes down before it's finished, then the index build will stop and must be run again. You can monitor the index table as it is created—you'll see new regions created as splits occur. You can query the `SYSTEM.STATS` table, which gets populated as splits and compactions happen. You can also run a `count(*)` query directly against the index table, though that puts more load on your system because requires a full table scan.
+
+Tips:
+
+* Create [local](https://phoenix.apache.org/secondary_indexing.html#Local_Indexes) indexes for write-heavy use cases.
+* Create global indexes for read-heavy use cases. To save read-time overhead, consider creating [covered](https://phoenix.apache.org/secondary_indexing.html#Covered_Indexes) 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 due to network I/O and other bottlenecks. If you're querying a billion-row table, you want to do as much computation as possible on the server side rather than transmitting a billion rows to the client for processing. Some queries, on the other hand, must be executed on the client. Sorting data that lives on multiple region servers, for example, requires that you aggregate and re-sort on the client.
+
+## Reading
+
+* 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.
+
+See also: https://phoenix.apache.org/joins.html
+
+### Range Queries
+
+If you regularly scan large data sets from spinning disk, you're best off with GZIP (but watch write speed). Use a lot of cores for a scan to utilize the available memory bandwidth. 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.
+
+If you mostly perform large range queries you might even want to consider running HBase with a much smaller heap and size the block cache down, to only rely on the OS Cache. This will alleviate some garbage collection related issues.
+
+### 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.
+
+See also: [Hint](https://phoenix.apache.org/language/#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.
+
+Start with a batch size of 1000 and adjust as needed. Here's some pseudocode showing one way to commit records in batches:
+
+```
+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
+
+If using 4.8, consider using local indexes to minimize the write time. In this case, 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() */ …`
+
+### Anatomy of an Explain Plan
+
+An explain plan consists of lines of text that describe operations that Phoenix will perform during a query, using the following terms:
+
+* `AGGREGATE INTO ORDERED DISTINCT ROWS`—aggregates the returned rows using an operation such as addition. When `ORDERED` is used, the `GROUP BY` operation is applied to the leading part of the primary key constraint, which allows the aggregation to be done in place rather than keeping all distinct groups in memory on the server side.
+* `AGGREGATE INTO SINGLE ROW`—aggregates the results into a single row using an aggregate function with no `GROUP BY` clause. For example, the `count()` statement returns one row with the total number of rows that match the query.
+* `CLIENT`—the operation will be performed on the client side. It's faster to perform most operations on the server side, so you should consider whether there's a way to rewrite the query to give the server more of the work to do.
+* `FILTER BY` expression—returns only results that match the expression.
+* `FULL SCAN OVER` tableName—the operation will scan every row in the specified table.
+* `INNER-JOIN`—the operation will join multiple tables on rows where the join condition is met.
+* `MERGE SORT`—performs a merge sort on the results.
+* `RANGE SCAN OVER` tableName `[` ... `]`—The information in the square brackets indicates the start and stop for each primary key that's used in the query.
+* `ROUND ROBIN`—when the query doesn't contain `ORDER BY` and therefore the rows can be returned in any order, `ROUND ROBIN` order maximizes parallelization on the client side.
+* x`-CHUNK`—describes how many threads will be used for the operation. The maximum parallelism is limited to the number of threads in thread pool. The minimum parallelization corresponds to the number of regions the table has between the start and stop rows of the scan. The number of chunks will increase with a lower guidepost width, as there is more than one chunk per region.
+* `PARALLEL `x-`WAY`—describes how many parallel scans will be merge sorted during the operation.
+* `SERIAL`—some queries run serially. For example, a single row lookup or a query that filters on the leading part of the primary key and limits the results below a configurable threshold.
+
+### Example
+
+```
++------------------------------------------+
+| PLAN |
++------------------------------------------+
+| CLIENT 36-CHUNK PARALLEL 36-WAY FULL SCAN OVER exDocStoreb |
+| PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE) |
+| CLIENT 36-CHUNK PARALLEL 36-WAY RANGE SCAN OVER indx_exdocb [0,' 42ecf4abd4bd7e7606025dc8eee3de 6a3cc04418cbc2619ddc01f54d88d7 c3bf'] - [0,' 42ecf4abd4bd7e7606025dc8eee3de 6a3cc04418cbc2619ddc01f54d88d7 c3bg' |
+| SERVER FILTER BY FIRST KEY ONLY |
+| SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY ["ID"] |
+| CLIENT MERGE SORT |
+| DYNAMIC SERVER FILTER BY (A.CURRENT_TIMESTAMP, [A.ID](http://a.id/)) IN ((TMP.MCT, TMP.TID)) |
++-------------------------------------------+
+```
+
+See also:
+http://phoenix.apache.org/language/index.html#explain
+
+# Improving parallelization
+
+You can improve parallelization with the [UPDATE STATISTICS](https://phoenix.apache.org/update_statistics.html) command. This command subdivides each region by determining keys called *guideposts* that are equidistant from each other, then uses these guideposts to break up queries into multiple parallel scans.
+Statistics are turned on by default. With Phoenix 4.9, the user can set guidepost width for each table. Optimal guidepost width depends on a number of factors such as cluster size, cluster usage, number of cores per node, table size, and disk I/O.
+
+# Further Tuning
+
+For advice about tuning the underlying HBase and JVM layers, see [Operational and Performance Configuration Options](https://hbase.apache.org/book.html#schema.ops) 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.