Re: Speedup initial index creation

[Alexander Batyrshin]

> 2 апр. 2021 г., в 03:55, Kadir Ozdemir  написал(а):
> 
> 
> 1) I was thinking about the bulk load tool 
> (https://phoenix.apache.org/bulk_dataload.html). However, in this case, you 
> are not interested in bulk loading into the data table and its index but just 
> the index table. Now, I see that it would not work for you. You are supposed 
> to build a strongly consistent index once when you create the index. I am 
> curious why you are so concerned about its performance. 

I need minimum maintenance time window on our cluster.

> 2)  I thought you wanted to disable WAL only during index rebuild for the 
> index table, not all the time. You should be able to still use the ALTER 
> TABLE command with the new index design. Please note that in this case you 
> would disable WAL for the main table too.  Is that what you are looking for? 
> If you are willing to disable WAL, then there is no point in using strongly 
> consistent indexes because you would lose recently written data if region 
> servers crash. By the way, you can use IndexUpgradeTool to downgrade your 
> tables to the old design (to replace IndexRegionObserver with Indexer), see 
> https://phoenix.apache.org/secondary_indexing.html

I know about possibility of data loosing. But it’s not a problem if main table 
do not receive mutation during index creation (maintenance window).

Old indexes goes inconsistent too often, so it not the way.
> 
> 3) Delete markers will be added each time you run the index create command 
> whenever the data table rows have multiple versions and the versions of a row 
> have different values for indexed columns.

My table has 1 version per row after major-compaction. Also main table has no 
mutation during index creation

>> On Thu, Apr 1, 2021 at 3:28 PM Alexander Batyrshin <0x62...@gmail.com> wrote:
>> 1) How to create index old way via intermediate HFiles?
>> 
>> I see “direct” option for IndexTool but description says its disabled:
>> 
>> private static final Option DIRECT_API_OPTION = new Option("direct", 
>> "direct", false,
>> "This parameter is deprecated. Direct mode will be used whether it is 
>> set or not. Keeping it for backwards compatibility.”);
>> 
>> 
>> 2) On phoenix-4.14.2 (old indexes) WAL disabling for index table was 
>> possible by “ALTER TABLE main_table SET DISABLE_WAL=true”
>> Maybe we can add this feature to 4.16+ ?
>> 
>> 
>> 3) My main table has VERSIONS=>1. Anyway I decided to major-compacted before 
>> next run and still got Delete mutations
>> 
>> From table metrics ~ 10% of mutations is Delete
>> 
>> 
>> I checked my main table, it has loaded IndexRegionObserver:
>> 
>> coprocessor$1 => 
>> '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
>> coprocessor$2 => 
>> '|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|',
>> coprocessor$3 => 
>> '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
>> coprocessor$4 => 
>> '|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|',
>> coprocessor$5 => 
>> '|org.apache.phoenix.hbase.index.IndexRegionObserver|805306366|org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec,index.builder=org.apache.phoenix.index.PhoenixIndexBuilder'
>> 
>> 
>> By the way I split index table for more regions, increased 
>> hbase.hregion.memstore.flush.size, hbase.hstore.blockingStoreFiles and get ~ 
>> 30% speedup.
>> This is still very slow compared to old index creation.
>> 
>>> On 31 Mar 2021, at 02:55, Kadir Ozdemir  
>>> wrote:
>>> 
>>> I assume that your base table has several versions for a given row. If so, 
>>> creating a consistent index on this base table can be slower than creating 
>>> an old design index. This is because the new design creates an index row 
>>> for every data table row version.  It simply replays the mutations on a row 
>>> without updating the data table but makes necessary mutations on the index 
>>> table. It does this to make sure that if you use SCN connections to do 
>>> point-in-time queries, the index will return correct results. During these 
>>> replays, index rows will be deleted if index columns are modified. This is 
>>> the reason I think you see delete mutations on the index table. 
>>> 
>>> 1) Yes
>>> 2) No
>>> 3) No
>>> 
>>> It will be a good improvement to have an option to support (3) by just 
>>> creating indexes using the last data row versions. Please feel free to 
>

Re: Speedup initial index creation

[Alexander Batyrshin]

1) How to create index old way via intermediate HFiles?

I see “direct” option for IndexTool but description says its disabled:

private static final Option DIRECT_API_OPTION = new Option("direct", "direct", 
false,
"This parameter is deprecated. Direct mode will be used whether it is set 
or not. Keeping it for backwards compatibility.”);


2) On phoenix-4.14.2 (old indexes) WAL disabling for index table was possible 
by “ALTER TABLE main_table SET DISABLE_WAL=true”
Maybe we can add this feature to 4.16+ ?


3) My main table has VERSIONS=>1. Anyway I decided to major-compacted before 
next run and still got Delete mutations

From table metrics ~ 10% of mutations is Delete


I checked my main table, it has loaded IndexRegionObserver:

coprocessor$1 => 
'|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
coprocessor$2 => 
'|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|',
coprocessor$3 => 
'|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
coprocessor$4 => 
'|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|',
coprocessor$5 => 
'|org.apache.phoenix.hbase.index.IndexRegionObserver|805306366|org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec,index.builder=org.apache.phoenix.index.PhoenixIndexBuilder'


By the way I split index table for more regions, increased 
hbase.hregion.memstore.flush.size, hbase.hstore.blockingStoreFiles and get ~ 
30% speedup.
This is still very slow compared to old index creation.

> On 31 Mar 2021, at 02:55, Kadir Ozdemir  wrote:
> 
> I assume that your base table has several versions for a given row. If so, 
> creating a consistent index on this base table can be slower than creating an 
> old design index. This is because the new design creates an index row for 
> every data table row version.  It simply replays the mutations on a row 
> without updating the data table but makes necessary mutations on the index 
> table. It does this to make sure that if you use SCN connections to do 
> point-in-time queries, the index will return correct results. During these 
> replays, index rows will be deleted if index columns are modified. This is 
> the reason I think you see delete mutations on the index table. 
> 
> 1) Yes
> 2) No
> 3) No
> 
> It will be a good improvement to have an option to support (3) by just 
> creating indexes using the last data row versions. Please feel free to create 
> an improvement Jira for this.
> 
> Did you create your base table using 4.16? If not, have you upgraded it to 
> the new index design using IndexUpgradeTool? I am asking this to make sure 
> that your index actually uses the new index design. You can verify this using 
> the HBase shell by describing the data table and checking if the 
> IndexRegionObserver coproc is loaded on your  base table.
>   
> 
> On Tue, Mar 30, 2021 at 3:10 PM Alexander Batyrshin <0x62...@gmail.com 
> <mailto:0x62...@gmail.com>> wrote:
> I tried on phoenix-4.16.0
> 
> > On 31 Mar 2021, at 00:54, Alexander Batyrshin <0x62...@gmail.com 
> > <mailto:0x62...@gmail.com>> wrote:
> > 
> > Hello,
> > I tried to create new consistent index on mutable table and found out that 
> > IndexTool MapReduce works 3-5 times slower compared to old indexes on 4.14.2
> > So I have some question;
> > 
> > 1) Is it possible to create index old way via intermediate HFiles and 
> > bulk-loading?
> > 2) Is it possible to disable WAL on HBase index table for creation time?
> > 3) My main table has no updates, but I observe Delete mutations on index 
> > table. Is it possible to disable this for initial index creation time?
> > 
> 

Re: Speedup initial index creation

[Alexander Batyrshin]

I tried on phoenix-4.16.0

> On 31 Mar 2021, at 00:54, Alexander Batyrshin <0x62...@gmail.com> wrote:
> 
> Hello,
> I tried to create new consistent index on mutable table and found out that 
> IndexTool MapReduce works 3-5 times slower compared to old indexes on 4.14.2
> So I have some question;
> 
> 1) Is it possible to create index old way via intermediate HFiles and 
> bulk-loading?
> 2) Is it possible to disable WAL on HBase index table for creation time?
> 3) My main table has no updates, but I observe Delete mutations on index 
> table. Is it possible to disable this for initial index creation time?
> 

Speedup initial index creation

[Alexander Batyrshin]

 Hello,
I tried to create new consistent index on mutable table and found out that 
IndexTool MapReduce works 3-5 times slower compared to old indexes on 4.14.2
So I have some question;

1) Is it possible to create index old way via intermediate HFiles and 
bulk-loading?
2) Is it possible to disable WAL on HBase index table for creation time?
3) My main table has no updates, but I observe Delete mutations on index table. 
Is it possible to disable this for initial index creation time?

Optimisation for SELECT ... WHERE pk IN (pk1, pk2...,pkN)

[Alexander Batyrshin]

 Hello all,
I have I quite big mutable table and need to select rows by primary keys.
The problem is that "SELECT … WHERE pk IN (pk1, pk2...,pkN)" (ROUND ROBIN POINT 
LOOKUP ON $num KEYS OVER) do not use HBase bloom filter ‘ROW’.
This leads to performance degradation when more than one StoreFile per region 
exists (that is near always true on mutable tables under heavy write)

Im wondering is there any way to optimisation?
Can we force-use ROW bloomfilter directly in Phoenix server side filters? Or is 
there any way to patch HBase method StoreFileScanner.requestSeek() to use ‘ROW’ 
bloom?


Offtop: ROWCOL bloom is bad option, because it consumes too much spaces and 
doesn’t fit into RegionServers BlockCache

FAQ page is blank now

[Alexander Batyrshin]

 Looks like FAQ page at http://phoenix.apache.org/faq.html 
 is blank now

When Phoenix 5.x for HBase-2.x will be updated?

[Alexander Batyrshin]

As I see there are many bug fixes and updates (consistent indexes) for 4.x 
Phoenix branch.
So im curios when this will be available for 5.x branch?

Re: Why we change index state to PENDING_DISABLE on RegionMovedException

[Alexander Batyrshin]

Its looks premises, but we need to do write performance evaluation old indexes 
vs new one before we can go with this update.

> On 11 Sep 2019, at 01:15, Geoffrey Jacoby  wrote:
> 
> Just wanted to add that in the new index architecture recently introduced in 
> Phoenix 4.14.3 and the forthcoming 4.15, the index stays in ACTIVE state even 
> if there's a write failure, and the index will be transparently repaired the 
> next time someone reads from the affected keyrange. From the client 
> perspective indexes will always be in sync. Indexes created using the older 
> index framework will still work, but will need to be upgraded to the new 
> framework with the IndexUpgradeTool in order to benefit from the new 
> behavior. 
> 
> We'll be updating the docs on the website soon to reflect that; in the 
> meantime you can look at PHOENIX-5156 and PHOENIX-5211 if you'd like more 
> details. 
> 
> Geoffrey
> 
> On Tue, Sep 10, 2019 at 3:02 PM Vincent Poon  <mailto:vincentp...@apache.org>> wrote:
> Normally you're right, this should get retried at the HBase layer and would 
> be transparent.  However as part of PHOENIX-4130, we have the hbase client 
> only try the write once, so there's no chance to retry.  We did that to avoid 
> tying up rpc handlers on the server.
> Instead, we retry the entire Phoenix mutation from the client side.  The 
> index is put into "PENDING_DISABLE", so that if the next write succeeds, it 
> can flip back to "ACTIVE".
> 
> On Tue, Sep 10, 2019 at 2:29 PM Alexander Batyrshin <0x62...@gmail.com 
> <mailto:0x62...@gmail.com>> wrote:
> As I know RegionMovedException is not a problem at all, its just notification 
> that we need to update meta information about table regions and retry.
> Why we do extra work with changing state of index?
> 
> 2019-09-10 22:35:00,764 WARN  [hconnection-0x4a63b6ea-shared--pool10-t961] 
> client.AsyncProcess: #41, table=IDX_TABLE, attempt=1/1 failed=1ops, last 
> exception: org.apache.hadoop.hbase.exceptions.RegionMovedException: Region 
> moved to: hostname=prod023 port=60020 startCode=1568139705179. As
>  of locationSeqNum=93740117. on prod027,60020,1568142287280, tracking started 
> Tue Sep 10 22:35:00 MSK 2019; not retrying 1 - final failure
> 2019-09-10 22:35:00,789 INFO  
> [RpcServer.default.FPBQ.Fifo.handler=170,queue=10,port=60020] 
> index.PhoenixIndexFailurePolicy: Successfully update INDEX_DISABLE_TIMESTAMP 
> for IDX_TABLE due to an exception while writing updates. 
> indexState=PENDING_DISABLE
> org.apache.phoenix.hbase.index.exception.MultiIndexWriteFailureException:  
> disableIndexOnFailure=true, Failed to write to multiple index tables: 
> [IDX_TABLE]
> at 
> org.apache.phoenix.hbase.index.write.TrackingParallelWriterIndexCommitter.write(TrackingParallelWriterIndexCommitter.java:236)
> at 
> org.apache.phoenix.hbase.index.write.IndexWriter.write(IndexWriter.java:195)
> at 
> org.apache.phoenix.hbase.index.write.IndexWriter.writeAndKillYourselfOnFailure(IndexWriter.java:156)
> at 
> org.apache.phoenix.hbase.index.write.IndexWriter.writeAndKillYourselfOnFailure(IndexWriter.java:145)
> at 
> org.apache.phoenix.hbase.index.Indexer.doPostWithExceptions(Indexer.java:614)
> at org.apache.phoenix.hbase.index.Indexer.doPost(Indexer.java:589)
> at 
> org.apache.phoenix.hbase.index.Indexer.postBatchMutateIndispensably(Indexer.java:572)
> at 
> org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost$37.call(RegionCoprocessorHost.java:1048)
> at 
> org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost$RegionOperation.call(RegionCoprocessorHost.java:1711)
> at 
> org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.execOperation(RegionCoprocessorHost.java:1789)
> at 
> org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.execOperation(RegionCoprocessorHost.java:1745)
> at 
> org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.postBatchMutateIndispensably(RegionCoprocessorHost.java:1044)
> at 
> org.apache.hadoop.hbase.regionserver.HRegion.doMiniBatchMutation(HRegion.java:3677)
> at 
> org.apache.hadoop.hbase.regionserver.HRegion.batchMutate(HRegion.java:3138)
> at 
> org.apache.hadoop.hbase.regionserver.HRegion.batchMutate(HRegion.java:3080)
> at 
> org.apache.hadoop.hbase.regionserver.RSRpcServices.doBatchOp(RSRpcServices.java:916)
> at 
> org.apache.hadoop.hbase.regionserver.RSRpcServices.doNonAtomicRegionMutation(RSRpcServices.java:844)
> at 
> org.apache.hadoop.hbase.regionserver.RSRpcServices.multi(RSRpcServices.java:2406)
> at 
> org.apache.hadoop.hbase.protobuf.generated.Clie

Why we change index state to PENDING_DISABLE on RegionMovedException

[Alexander Batyrshin]

As I know RegionMovedException is not a problem at all, its just notification 
that we need to update meta information about table regions and retry.
Why we do extra work with changing state of index?

2019-09-10 22:35:00,764 WARN  [hconnection-0x4a63b6ea-shared--pool10-t961] 
client.AsyncProcess: #41, table=IDX_TABLE, attempt=1/1 failed=1ops, last 
exception: org.apache.hadoop.hbase.exceptions.RegionMovedException: Region 
moved to: hostname=prod023 port=60020 startCode=1568139705179. As
 of locationSeqNum=93740117. on prod027,60020,1568142287280, tracking started 
Tue Sep 10 22:35:00 MSK 2019; not retrying 1 - final failure
2019-09-10 22:35:00,789 INFO  
[RpcServer.default.FPBQ.Fifo.handler=170,queue=10,port=60020] 
index.PhoenixIndexFailurePolicy: Successfully update INDEX_DISABLE_TIMESTAMP 
for IDX_TABLE due to an exception while writing updates. 
indexState=PENDING_DISABLE
org.apache.phoenix.hbase.index.exception.MultiIndexWriteFailureException:  
disableIndexOnFailure=true, Failed to write to multiple index tables: 
[IDX_TABLE]
at 
org.apache.phoenix.hbase.index.write.TrackingParallelWriterIndexCommitter.write(TrackingParallelWriterIndexCommitter.java:236)
at 
org.apache.phoenix.hbase.index.write.IndexWriter.write(IndexWriter.java:195)
at 
org.apache.phoenix.hbase.index.write.IndexWriter.writeAndKillYourselfOnFailure(IndexWriter.java:156)
at 
org.apache.phoenix.hbase.index.write.IndexWriter.writeAndKillYourselfOnFailure(IndexWriter.java:145)
at 
org.apache.phoenix.hbase.index.Indexer.doPostWithExceptions(Indexer.java:614)
at org.apache.phoenix.hbase.index.Indexer.doPost(Indexer.java:589)
at 
org.apache.phoenix.hbase.index.Indexer.postBatchMutateIndispensably(Indexer.java:572)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost$37.call(RegionCoprocessorHost.java:1048)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost$RegionOperation.call(RegionCoprocessorHost.java:1711)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.execOperation(RegionCoprocessorHost.java:1789)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.execOperation(RegionCoprocessorHost.java:1745)
at 
org.apache.hadoop.hbase.regionserver.RegionCoprocessorHost.postBatchMutateIndispensably(RegionCoprocessorHost.java:1044)
at 
org.apache.hadoop.hbase.regionserver.HRegion.doMiniBatchMutation(HRegion.java:3677)
at 
org.apache.hadoop.hbase.regionserver.HRegion.batchMutate(HRegion.java:3138)
at 
org.apache.hadoop.hbase.regionserver.HRegion.batchMutate(HRegion.java:3080)
at 
org.apache.hadoop.hbase.regionserver.RSRpcServices.doBatchOp(RSRpcServices.java:916)
at 
org.apache.hadoop.hbase.regionserver.RSRpcServices.doNonAtomicRegionMutation(RSRpcServices.java:844)
at 
org.apache.hadoop.hbase.regionserver.RSRpcServices.multi(RSRpcServices.java:2406)
at 
org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java:36621)
at org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2380)
at org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:124)
at 
org.apache.hadoop.hbase.ipc.RpcExecutor$Handler.run(RpcExecutor.java:297)
at 
org.apache.hadoop.hbase.ipc.RpcExecutor$Handler.run(RpcExecutor.java:277)

Re: Any reason for so small phoenix.mutate.batchSize by default?

[Alexander Batyrshin]

> On 3 Sep 2019, at 19:45, Alexander Batyrshin <0x62...@gmail.com> wrote:
> 
> I observer that there is some extra mutations in batch for every my UPSERTs
> For example if app call executeUpdate() only 5 times then on commit there 
> will be "DEBUG MutationState:1046 - Sent batch of 10"
> Can’t figure out where this extra mutations comes from and why.
> 
> This is mean that “useful” batch size is phoenix.mutate.batchSize / 2.


Extra mutation is Mutation.Delete because I have NULL value in UPSERT statement

Re: Any reason for so small phoenix.mutate.batchSize by default?

[Alexander Batyrshin]

I observer that there is some extra mutations in batch for every my UPSERTs
For example if app call executeUpdate() only 5 times then on commit there will 
be "DEBUG MutationState:1046 - Sent batch of 10"
Can’t figure out where this extra mutations comes from and why.

This is mean that “useful” batch size is phoenix.mutate.batchSize / 2.

> * What does your table DDL look like?

CREATE TABLE IF NOT EXISTS TABLE_CODES (
"id" VARCHAR NOT NULL PRIMARY KEY,
"d"."tg" VARCHAR,
"d"."drip" VARCHAR,
"d"."s" UNSIGNED_TINYINT,
"d"."se" UNSIGNED_TINYINT,
"d"."rle" UNSIGNED_TINYINT,
"d"."dme" TIMESTAMP,
"d"."dpa" TIMESTAMP,
"d"."p" VARCHAR,
"d"."pt" UNSIGNED_TINYINT,
"d"."x" VARCHAR,
"d"."pn" VARCHAR,
"d"."b" VARCHAR,
"d"."hc" VARCHAR ARRAY,
"d"."ns" VARCHAR(16),
"d"."tv" VARCHAR(10),
"d"."vcp" VARCHAR,
"d"."et" UNSIGNED_TINYINT,
"d"."xoa" BINARY(16),
"d"."j" VARCHAR
) SALT_BUCKETS=30, COLUMN_ENCODED_BYTES=NONE;

CREATE INDEX "IDX_CIS_O" ON "TABLE_CODES" ("d"."x", "d"."dme") 
INCLUDE("d"."tg", "d"."rle", "d"."pt" ... ) SALT_BUCKETS=30;
CREATE INDEX "IDX_CIS_PRID" ON "TABLE_CODES" ("d"."drip", "d"."dme") 
INCLUDE("d"."tg", "d"."rle", "d"."pt" ...) SALT_BUCKETS=30;

For my case SALT_BUCKET=30 every batch with default settings will carry only 50 
“useful” rows and they will be splitted across 30 servers, so every server will 
get only 1-2 rows.

> * How large is one mutation you're writing (in bytes)?

Any idea how to calculate it?
https://phoenix.apache.org/metrics.html 
<https://phoenix.apache.org/metrics.html> will give me total mutations count 
and total size in bytes of batch. But as I mentioned before there is “extra” 
mutation that will corrupt statistics

> * How much data ends up being sent to a RegionServer in one RPC?
Where I can get this metric?


> On 3 Sep 2019, at 17:19, Josh Elser  wrote:
> 
> Hey Alexander,
> 
> Was just poking at the code for this: it looks like this is really just 
> determining the number of mutations that get "processed together" (as opposed 
> to a hard limit).
> 
> Since you have done some work, I'm curious if you could generate some data to 
> help back up your suggestion:
> 
> * What does your table DDL look like?
> * How large is one mutation you're writing (in bytes)?
> * How much data ends up being sent to a RegionServer in one RPC?
> 
> You're right in that we would want to make sure that we're sending an 
> adequate amount of data to a RegionServer in an RPC, but this is tricky to 
> balance for all cases (thus, setting a smaller value to avoid sending batches 
> that are too large is safer).
> 
> On 9/3/19 8:03 AM, Alexander Batyrshin wrote:
>>  Hello all,
>> 1) There is bug in documentation - http://phoenix.apache.org/tuning.html
>> phoenix.mutate.batchSize is not 1000, but only 100 by default
>> https://github.com/apache/phoenix/blob/master/phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java#L164
>> Changed for https://issues.apache.org/jira/browse/PHOENIX-541
>> 2) I want to discuss this default value. From PHOENIX-541 
>> <https://issues.apache.org/jira/browse/PHOENIX-541> I read about issue with 
>> MR and wide rows (2MB per row) and it looks like rare case. But in most 
>> common cases we can get much better write perfomance with batchSize = 1000 
>> especially if it used with SALT table

Any reason for so small phoenix.mutate.batchSize by default?

[Alexander Batyrshin]

 Hello all,

1) There is bug in documentation - http://phoenix.apache.org/tuning.html 

phoenix.mutate.batchSize is not 1000, but only 100 by default
https://github.com/apache/phoenix/blob/master/phoenix-core/src/main/java/org/apache/phoenix/query/QueryServicesOptions.java#L164
 

Changed for https://issues.apache.org/jira/browse/PHOENIX-541 



2) I want to discuss this default value. From PHOENIX-541 
 I read about issue with MR 
and wide rows (2MB per row) and it looks like rare case. But in most common 
cases we can get much better write perfomance with batchSize = 1000 especially 
if it used with SALT table

Re: java.io.IOException: Added a key not lexically larger than previous

[Alexander Batyrshin]

Looks like problem begun with 4.14.2
Maybe somehow https://issues.apache.org/jira/browse/PHOENIX-5266 
<https://issues.apache.org/jira/browse/PHOENIX-5266> can re-apply mutations to 
main table with bugs?

> On 20 Jun 2019, at 04:16, Alexander Batyrshin <0x62...@gmail.com> wrote:
> 
> Hello,
> Are there any ideas where this problem comes from and how to fix?
> 
> Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,348 WARN  
> [MemStoreFlusher.0] regionserver.HStore: Failed flushing store file, retrying 
> num=9
> Jun 18 21:38:05 prod022 hbase[148581]: java.io.IOException: Added a key not 
> lexically larger than previous. Current cell = 
> \x0D100395583733fW+,WQ/d:p/1560882798036/DeleteColumn/vlen=0/seqid=30023231, 
> lastCell = \x0D100395583733fW+,WQ/d:p/1560882798036/Put/vlen=29/seqid=30023591
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.io.hfile.AbstractHFileWriter.checkKey(AbstractHFileWriter.java:204)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.io.hfile.HFileWriterV2.append(HFileWriterV2.java:279)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.io.hfile.HFileWriterV3.append(HFileWriterV3.java:87)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.StoreFile$Writer.append(StoreFile.java:1053)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.StoreFlusher.performFlush(StoreFlusher.java:139)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.DefaultStoreFlusher.flushSnapshot(DefaultStoreFlusher.java:75)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HStore.flushCache(HStore.java:969)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HStore$StoreFlusherImpl.flushCache(HStore.java:2484)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2622)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.access$900(MemStoreFlusher.java:76)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher$FlushHandler.run(MemStoreFlusher.java:264)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> java.lang.Thread.run(Thread.java:748)
> Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,373 FATAL 
> [MemStoreFlusher.0] regionserver.HRegionServer: ABORTING region server 
> prod022,60020,1560521871613: Replay of WAL required. Forcing server shutdown
> Jun 18 21:38:05 prod022 hbase[148581]: 
> org.apache.hadoop.hbase.DroppedSnapshotException: region: 
> TBL_C,\x0D04606203096428+jaVbx.,1558885224779.b4633aee06956663b05e8322ce34b0a3.
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2675)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlus

Re: java.io.IOException: Added a key not lexically larger than previous

[Alexander Batyrshin]

> Since you're using a global index, which stores the index data in a separate 
> table (and hence different regions, each of which has a different MemStore / 
> set of HFiles), and the error's happening to the base table, I'd be surprised 
> if Phoenix indexing is related. 

AFAIK Phoenix handle mutable secondary global index server side via coprocessor 
on HBase put. So maybe this coprocessor some how brake MemStore consistency for 
example by releasing locks/etc.


> What coprocessors (Phoenix and otherwise) are loaded on the table?


TABLE_ATTRIBUTES => {
MAX_FILESIZE => '32212254720',
coprocessor$1 => 
'|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|',
coprocessor$2 => 
'|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|',
coprocessor$3 => 
'|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|',
coprocessor$4 => 
'|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|',
coprocessor$5 => 
'|org.apache.phoenix.hbase.index.Indexer|805306366|org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec,index.builder=org.apache.phoenix.index.PhoenixIndexBuilder'
}

Re: java.io.IOException: Added a key not lexically larger than previous

[Alexander Batyrshin]

> On 15 Aug 2019, at 21:27, Josh Elser  wrote:
> 
> Short-answer, it's possible that something around secondary indexing in 
> Phoenix causes this but not possible to definitively say in a vaccuum.


As I see region-server crashes on main table (not index) memstore flush.
How can I help to provide more information?
Maybe I should increase logging level for some specific java class of Phoenix 
or HBase?

Re: java.io.IOException: Added a key not lexically larger than previous

[Alexander Batyrshin]

Im using global index.
HBase-1.4.10
Phoenix-4.14.2

I constantly get this issues today after increasing write load.

> On 15 Aug 2019, at 21:27, Josh Elser  wrote:
> 
> Are you using a local index? Can you share the basics please (HBase and 
> Phoenix versions).
> 
> I'm not seeing if you've shared this previously on this or another thread. 
> Sorry if you have.
> 
> Short-answer, it's possible that something around secondary indexing in 
> Phoenix causes this but not possible to definitively say in a vaccuum.
> 
> On 8/15/19 1:19 PM, Alexander Batyrshin wrote:
>> Is is possible that Phoenix is the reason of this problem?
>>> On 20 Jun 2019, at 04:16, Alexander Batyrshin <0x62...@gmail.com> wrote:
>>> 
>>> Hello,
>>> Are there any ideas where this problem comes from and how to fix?
>>> 
>>> Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,348 WARN  
>>> [MemStoreFlusher.0] regionserver.HStore: Failed flushing store file, 
>>> retrying num=9
>>> Jun 18 21:38:05 prod022 hbase[148581]: java.io.IOException: Added a key not 
>>> lexically larger than previous. Current cell = 
>>> \x0D100395583733fW+,WQ/d:p/1560882798036/DeleteColumn/vlen=0/seqid=30023231,
>>>  lastCell = 
>>> \x0D100395583733fW+,WQ/d:p/1560882798036/Put/vlen=29/seqid=30023591
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.io.hfile.AbstractHFileWriter.checkKey(AbstractHFileWriter.java:204)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.io.hfile.HFileWriterV2.append(HFileWriterV2.java:279)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.io.hfile.HFileWriterV3.append(HFileWriterV3.java:87)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.StoreFile$Writer.append(StoreFile.java:1053)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.StoreFlusher.performFlush(StoreFlusher.java:139)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.DefaultStoreFlusher.flushSnapshot(DefaultStoreFlusher.java:75)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HStore.flushCache(HStore.java:969)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HStore$StoreFlusherImpl.flushCache(HStore.java:2484)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2622)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.access$900(MemStoreFlusher.java:76)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.MemStoreFlusher$FlushHandler.run(MemStoreFlusher.java:264)
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> java.lang.Thread.run(Thread.java:748)
>>> Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,373 FATAL 
>>> [MemStoreFlusher.0] regionserver.HRegionServer: ABORTING region server 
>>> prod022,60020,1560521871613: Replay of WAL required. Forcing server shutdown
>>> Jun 18 21:38:05 prod022 hbase[148581]: 
>>> org.apache.hadoop.hbase.DroppedSnapshotException: region: 
>>> TBL_C,\x0D04606203096428+jaVbx.,1558885224779.b4633aee06956663b05e8322ce34b0a3.
>>> Jun 18 21:38:05 prod022 hbase[148581]: at 
>>> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2675)
>>> Jun 18 21:38:

Re: java.io.IOException: Added a key not lexically larger than previous

[Alexander Batyrshin]

Is is possible that Phoenix is the reason of this problem?

> On 20 Jun 2019, at 04:16, Alexander Batyrshin <0x62...@gmail.com> wrote:
> 
> Hello,
> Are there any ideas where this problem comes from and how to fix?
> 
> Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,348 WARN  
> [MemStoreFlusher.0] regionserver.HStore: Failed flushing store file, retrying 
> num=9
> Jun 18 21:38:05 prod022 hbase[148581]: java.io.IOException: Added a key not 
> lexically larger than previous. Current cell = 
> \x0D100395583733fW+,WQ/d:p/1560882798036/DeleteColumn/vlen=0/seqid=30023231, 
> lastCell = \x0D100395583733fW+,WQ/d:p/1560882798036/Put/vlen=29/seqid=30023591
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.io.hfile.AbstractHFileWriter.checkKey(AbstractHFileWriter.java:204)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.io.hfile.HFileWriterV2.append(HFileWriterV2.java:279)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.io.hfile.HFileWriterV3.append(HFileWriterV3.java:87)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.StoreFile$Writer.append(StoreFile.java:1053)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.StoreFlusher.performFlush(StoreFlusher.java:139)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.DefaultStoreFlusher.flushSnapshot(DefaultStoreFlusher.java:75)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HStore.flushCache(HStore.java:969)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HStore$StoreFlusherImpl.flushCache(HStore.java:2484)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2622)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.access$900(MemStoreFlusher.java:76)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher$FlushHandler.run(MemStoreFlusher.java:264)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> java.lang.Thread.run(Thread.java:748)
> Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,373 FATAL 
> [MemStoreFlusher.0] regionserver.HRegionServer: ABORTING region server 
> prod022,60020,1560521871613: Replay of WAL required. Forcing server shutdown
> Jun 18 21:38:05 prod022 hbase[148581]: 
> org.apache.hadoop.hbase.DroppedSnapshotException: region: 
> TBL_C,\x0D04606203096428+jaVbx.,1558885224779.b4633aee06956663b05e8322ce34b0a3.
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2675)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStoreFlusher.access$900(MemStoreFlusher.java:76)
> Jun 18 21:38:05 prod022 hbase[148581]: at 
> org.apache.hadoop.hbase.regionserver.MemStore

Re: Phoenix Index Scrutiny Tool

[Alexander Batyrshin]

I have familiar question - how to partially rebuild indexes by timestamps 
interval like many MapReduce has —starttime/—endttime

> On 9 Aug 2019, at 17:21, Aleksandr Saraseka  wrote:
> 
> Hello community!
> I'm testing scrutiny tool to check index consistency.
> I hard-deleted from HBase a couple of rows from global index, then ran 
> Scrutiny tool, it showed me some output like:
> 
> 
> SOURCE_TABLE
> TARGET_TABLE
> SCRUNITY_EXECUTE_TIME
> SOURCE_ROW_PK_HASH
> SOURCE_TS
> TARGET_TS
> HAS_TARGET_ROW
> INDEX_TABLE
> DATA_TABLE
> 1565358267566
> 8a74d1f8286a7ec7ce99b22ee0723ab1
> 1565358171998
> -1
> false
> INDEX_TABLE
> DATA_TABLE
> 1565358267566
> a2cfe11952f3701d340069f80e2a82b7
> 1565358135292
> -1
> false
> so, let's imagine that I want to repair my index and don't want to run full 
> rebuild (huge table).
> What's the best option ?
> Two things came to my mind:
> - Find a row in data table, and upset necessary data to index table.
> - Find a row in data table,  export it then drop it, and then insert it again.
> And the main question - how can I get a value from data or index table by 
> Primary Key hash ?
> 
> -- 
>   Aleksandr Saraseka
> DBA
> 380997600401
>   •  asaras...@eztexting.com 
>   •  eztexting.com 
> 
>   
>  
> 
>
> 
> 
> 
>   
> 
>  
> 
>  
> 
> 
> 

Re: Alter Table throws java.lang.NullPointerException

[Alexander Batyrshin]

HBase - 1.4.8
Phoenix - 4.14.1

Looks like there is very similar issue in jira - 
https://issues.apache.org/jira/browse/PHOENIX-5210 
<https://issues.apache.org/jira/browse/PHOENIX-5210>

> On 24 Jul 2019, at 16:46, Josh Elser  wrote:
> 
> Please start by sharing the version of Phoenix that you're using.
> 
> Did you search Jira to see if there was someone else who also reported this 
> issue?
> 
> On 7/23/19 4:24 PM, Alexander Batyrshin wrote:
>> Hello all,
>> Got this:
>> alter table TEST_TABLE SET APPEND_ONLY_SCHEMA=true;
>> java.lang.NullPointerException
>> at 
>> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3240)
>> at 
>> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3221)
>> at 
>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1432)
>> at 
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:408)
>> at 
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>> at 
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)
>> at 
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
>> at 
>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
>> at sqlline.Commands.execute(Commands.java:822)
>> at sqlline.Commands.sql(Commands.java:732)
>> at sqlline.SqlLine.dispatch(SqlLine.java:813)
>> at sqlline.SqlLine.begin(SqlLine.java:686)
>> at sqlline.SqlLine.start(SqlLine.java:398)
>> at sqlline.SqlLine.main(SqlLine.java:291)
>> Any ideas how to fix?

Alter Table throws java.lang.NullPointerException

[Alexander Batyrshin]

Hello all,

Got this:

alter table TEST_TABLE SET APPEND_ONLY_SCHEMA=true;
java.lang.NullPointerException
at 
org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3240)
at 
org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3221)
at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1432)
at 
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:408)
at 
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at 
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)
at 
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
at 
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:813)
at sqlline.SqlLine.begin(SqlLine.java:686)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:291)


Any ideas how to fix?

RS Index Handler got regionserver.MultiVersionConcurrencyControl: STUCK

[Alexander Batyrshin]

Hello all,
Any ideas how to avoid situations like this:

Jul 18 01:55:30 prod005 hbase[227531]: 2019-07-18 01:55:30,042 WARN  
[RpcServer.Index.handler=4,queue=0,port=60020] 
regionserver.MultiVersionConcurrencyControl: STUCK: 
MultiVersionConcurrencyControl{readPoint=55042096, writePoint=55042098}

Because at this point RS is blocked for any RPC

Re: Query optimization

[Alexander Batyrshin]

Is it possible not to full scan table1 for ’table1.col = ?’, but do this check 
only on subset table1.pk IN (…)?

> On 19 Jun 2019, at 23:31, Vincent Poon  wrote:
> 
> 'table1.col = ?' will be a full table scan of table1 unless you have a 
> secondary index on table.col
> Check the explain plan to see if it's working as expected
> 
> On Wed, Jun 19, 2019 at 7:43 AM Alexander Batyrshin <0x62...@gmail.com 
> <mailto:0x62...@gmail.com>> wrote:
>  Hello,
> We have 2 tables:
> 
> Table1 - big one (2000M+ rows):
> 
> CREATE TABLE table1 (
> pk varchar PRIMARY KEY,
> col varchar
> );
> 
> Table2 - small one (300K rows):
> 
> CREATE TABLE table2 (
> pk varchar PRIMARY KEY,
> other varchar
> );
> 
> Query like this work fast (~ 30sec):
> SELECT table1.pk <http://table1.pk/>,  table1.col
> FROM table1
> WHERE table1.pk <http://table1.pk/> IN ( SELECT table2.pk <http://table2.pk/> 
> FROM table2 )
> 
> But query like this work quite slow (>10min):
> SELECT table1.pk <http://table1.pk/>
> FROM table1
> WHERE table1.col = ? AND table1.pk <http://table1.pk/> IN ( SELECT table2.pk 
> <http://table2.pk/> FROM table2 )
> 
> Also query below work slow:
> SELECT *
> FROM (
> SELECT table1.pk <http://table1.pk/>,  table1.col
> FROM table1
> WHERE table1.pk <http://table1.pk/> IN ( SELECT table2.pk 
> <http://table2.pk/> FROM table2 )
> ) AS s
> WHERE s.col = ?
> 
> Is there any HINT that can optimize query?

java.io.IOException: Added a key not lexically larger than previous

[Alexander Batyrshin]

 Hello,
Are there any ideas where this problem comes from and how to fix?

Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,348 WARN  
[MemStoreFlusher.0] regionserver.HStore: Failed flushing store file, retrying 
num=9
Jun 18 21:38:05 prod022 hbase[148581]: java.io.IOException: Added a key not 
lexically larger than previous. Current cell = 
\x0D100395583733fW+,WQ/d:p/1560882798036/DeleteColumn/vlen=0/seqid=30023231, 
lastCell = \x0D100395583733fW+,WQ/d:p/1560882798036/Put/vlen=29/seqid=30023591
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.io.hfile.AbstractHFileWriter.checkKey(AbstractHFileWriter.java:204)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.io.hfile.HFileWriterV2.append(HFileWriterV2.java:279)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.io.hfile.HFileWriterV3.append(HFileWriterV3.java:87)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.StoreFile$Writer.append(StoreFile.java:1053)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.StoreFlusher.performFlush(StoreFlusher.java:139)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.DefaultStoreFlusher.flushSnapshot(DefaultStoreFlusher.java:75)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HStore.flushCache(HStore.java:969)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HStore$StoreFlusherImpl.flushCache(HStore.java:2484)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2622)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher.access$900(MemStoreFlusher.java:76)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher$FlushHandler.run(MemStoreFlusher.java:264)
Jun 18 21:38:05 prod022 hbase[148581]: at 
java.lang.Thread.run(Thread.java:748)
Jun 18 21:38:05 prod022 hbase[148581]: 2019-06-18 21:38:05,373 FATAL 
[MemStoreFlusher.0] regionserver.HRegionServer: ABORTING region server 
prod022,60020,1560521871613: Replay of WAL required. Forcing server shutdown
Jun 18 21:38:05 prod022 hbase[148581]: 
org.apache.hadoop.hbase.DroppedSnapshotException: region: 
TBL_C,\x0D04606203096428+jaVbx.,1558885224779.b4633aee06956663b05e8322ce34b0a3.
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.internalFlushCacheAndCommit(HRegion.java:2675)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2352)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.internalFlushcache(HRegion.java:2314)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.flushcache(HRegion.java:2200)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.HRegion.flush(HRegion.java:2125)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:512)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher.flushRegion(MemStoreFlusher.java:482)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher.access$900(MemStoreFlusher.java:76)
Jun 18 21:38:05 prod022 hbase[148581]: at 
org.apache.hadoop.hbase.regionserver.MemStoreFlusher$FlushHandler.run(MemStoreFlusher.java:264)
Jun 18 21:38:05 prod022 hbase[148581]: at 
java.lang.Thread.run(Thread.java:748)
Jun 18 21:38:05 prod022 hbase[148581]: Caused by: java.io.IOException: Added a 
key not lexically larger than previous. Current cell = 
\x0D100395583733fW+,WQ/d:p/1560882798036/DeleteColumn/vlen=0/seqid=30023231, 
lastCell = \x0D100395583733fW+,WQ/d:p/1560882798036/Put/vlen=29/seqid=30023591
Jun 18 21:38:05 prod022 hbase[148581]: at 

Query optimization

[Alexander Batyrshin]

 Hello,
We have 2 tables:

Table1 - big one (2000M+ rows):

CREATE TABLE table1 (
pk varchar PRIMARY KEY,
col varchar
);

Table2 - small one (300K rows):

CREATE TABLE table2 (
pk varchar PRIMARY KEY,
other varchar
);

Query like this work fast (~ 30sec):
SELECT table1.pk,  table1.col
FROM table1
WHERE table1.pk IN ( SELECT table2.pk FROM table2 )

But query like this work quite slow (>10min):
SELECT table1.pk
FROM table1
WHERE table1.col = ? AND table1.pk IN ( SELECT table2.pk FROM table2 )

Also query below work slow:
SELECT *
FROM (
SELECT table1.pk,  table1.col
FROM table1
WHERE table1.pk IN ( SELECT table2.pk FROM table2 )
) AS s
WHERE s.col = ?

Is there any HINT that can optimize query?