Re: Buckets VS regions

2019-08-19 Thread jesse
Yes, that seems to be a trick, the issue is only associated a region.

What could be the causes of guidposts not updated?


On Mon, Aug 19, 2019, 6:40 PM venkata subbarayudu 
wrote:

> Did you try with updating table statistics, it may be because some times
> table guideposts are out of sync
>
> Below is the SQL to update table stats
>  Update statistics table
> By default above executes asynchronously, hence it may take some time to
> update depending on table size
>
> On Tue 20 Aug, 2019, 6:34 AM jesse,  wrote:
>
>> And the table is simple and has no index set up.
>>
>> On Mon, Aug 19, 2019, 6:03 PM jesse  wrote:
>>
>>> we got some trouble, maybe someone could shed some light on this.
>>>
>>> Table has primary key c1, c2 and c3.
>>> Table is set with SALT_BUCKETS=12. Now it has 14 regions.
>>>
>>> The table has a record with c1='a', c2='b', c3='c'
>>>
>>> If Phoenix query is like:
>>> select * from t where c2='b', it returns some results.
>>>
>>> select * from t where c1='a', it returns empty
>>> select * from t where c2='b' and c1='a', it returns empty
>>>
>>> select * from t where c3='c', it returns right results
>>> select * from t where c2='b' and c3='c', it returns results
>>>
>>> What the heck is going wrong? The system used to work fine.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Mon, Aug 19, 2019, 5:33 PM James Taylor 
>>> wrote:
>>>
 It’ll start with 12 regions, but those regions may split as they’re
 written to.

 On Mon, Aug 19, 2019 at 4:34 PM jesse  wrote:

> I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is this
> right?
>
> Thanks
>
>
>


Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread you Zhuang
You are right, thanks so much, but it’s so limited that I can’t include all 
columns due to hundreds of columns existing in one table.

I think covered columns must be queried in global indexes is reasonable, local 
index isn’t.

Because we query rowkey from local index first , then get actual row from data 
table. 

Thus we have no necessity to limit local index usage including all queried 
columns.  



> On Aug 20, 2019, at 12:32 AM, Josh Elser  wrote:
> 
> http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used 
> 
> 
> On 8/19/19 6:06 AM, you Zhuang wrote:
>> Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
>> hbase-version: 1.4.6
>> Table:
>> CREATE TABLE test_phoenix.app (
>> dt integer not null,
>> a bigint not null ,
>> b bigint not null ,
>> c bigint not null ,
>> d bigint not null ,
>> e bigint not null ,
>> f bigint not null ,
>> g bigint not null ,
>> h bigint not null ,
>> i bigint not null ,
>> j bigint not null ,
>> k bigint not null ,
>> m decimal(30,6) ,
>> n decimal(30,6)
>> CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
>> ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;
>> Index:
>> CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
>> (Has been filled data with bulkload and index is active)
>> Query:
>> select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
>> TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;
>> The first query will use index local_c_h_index and result shortly, the 
>> second query won’t , and response slowly.
>> The explain plan is weird, all showing without using index.
>>> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka >>  >> >> wrote:
>>> 
>>> We have no problems with that. I mean indexes are used even without hints, 
>>> if they're suitable for a query.
>>> Maybe you can share your Phoenix version, query, index definition and exec 
>>> plan ?
>>> 
>>> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang >>  >> >> wrote:
>>> 
>>>Yeah, I mean no hint , use appropriate index automatically. I
>>>create a local index  and a query with corresponding index column
>>>filter in where clause. But the query doesn’t use index, with
>>>index hint it uses it.
>>> 
>>> 
>>> 
>>> -- 
>>> Aleksandr Saraseka
>>> DBA
>>> 380997600401
>>> > *•* asaras...@eztexting.com 
>>>  >> > *•* eztexting.com 
>>> >>  
>>> >
>>>  
>>> 
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >
>>>  
>>> >>  
>>> >



Re: Buckets VS regions

2019-08-19 Thread venkata subbarayudu
Did you try with updating table statistics, it may be because some times
table guideposts are out of sync

Below is the SQL to update table stats
 Update statistics table
By default above executes asynchronously, hence it may take some time to
update depending on table size

On Tue 20 Aug, 2019, 6:34 AM jesse,  wrote:

> And the table is simple and has no index set up.
>
> On Mon, Aug 19, 2019, 6:03 PM jesse  wrote:
>
>> we got some trouble, maybe someone could shed some light on this.
>>
>> Table has primary key c1, c2 and c3.
>> Table is set with SALT_BUCKETS=12. Now it has 14 regions.
>>
>> The table has a record with c1='a', c2='b', c3='c'
>>
>> If Phoenix query is like:
>> select * from t where c2='b', it returns some results.
>>
>> select * from t where c1='a', it returns empty
>> select * from t where c2='b' and c1='a', it returns empty
>>
>> select * from t where c3='c', it returns right results
>> select * from t where c2='b' and c3='c', it returns results
>>
>> What the heck is going wrong? The system used to work fine.
>>
>>
>>
>>
>>
>>
>>
>>
>> On Mon, Aug 19, 2019, 5:33 PM James Taylor 
>> wrote:
>>
>>> It’ll start with 12 regions, but those regions may split as they’re
>>> written to.
>>>
>>> On Mon, Aug 19, 2019 at 4:34 PM jesse  wrote:
>>>
 I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is this
 right?

 Thanks





Re: Buckets VS regions

2019-08-19 Thread jesse
And the table is simple and has no index set up.

On Mon, Aug 19, 2019, 6:03 PM jesse  wrote:

> we got some trouble, maybe someone could shed some light on this.
>
> Table has primary key c1, c2 and c3.
> Table is set with SALT_BUCKETS=12. Now it has 14 regions.
>
> The table has a record with c1='a', c2='b', c3='c'
>
> If Phoenix query is like:
> select * from t where c2='b', it returns some results.
>
> select * from t where c1='a', it returns empty
> select * from t where c2='b' and c1='a', it returns empty
>
> select * from t where c3='c', it returns right results
> select * from t where c2='b' and c3='c', it returns results
>
> What the heck is going wrong? The system used to work fine.
>
>
>
>
>
>
>
>
> On Mon, Aug 19, 2019, 5:33 PM James Taylor  wrote:
>
>> It’ll start with 12 regions, but those regions may split as they’re
>> written to.
>>
>> On Mon, Aug 19, 2019 at 4:34 PM jesse  wrote:
>>
>>> I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is this
>>> right?
>>>
>>> Thanks
>>>
>>>
>>>


Re: Buckets VS regions

2019-08-19 Thread jesse
we got some trouble, maybe someone could shed some light on this.

Table has primary key c1, c2 and c3.
Table is set with SALT_BUCKETS=12. Now it has 14 regions.

The table has a record with c1='a', c2='b', c3='c'

If Phoenix query is like:
select * from t where c2='b', it returns some results.

select * from t where c1='a', it returns empty
select * from t where c2='b' and c1='a', it returns empty

select * from t where c3='c', it returns right results
select * from t where c2='b' and c3='c', it returns results

What the heck is going wrong? The system used to work fine.








On Mon, Aug 19, 2019, 5:33 PM James Taylor  wrote:

> It’ll start with 12 regions, but those regions may split as they’re
> written to.
>
> On Mon, Aug 19, 2019 at 4:34 PM jesse  wrote:
>
>> I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is this
>> right?
>>
>> Thanks
>>
>>
>>


Re: Buckets VS regions

2019-08-19 Thread James Taylor
It’ll start with 12 regions, but those regions may split as they’re written
to.

On Mon, Aug 19, 2019 at 4:34 PM jesse  wrote:

> I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is this
> right?
>
> Thanks
>
>
>


Buckets VS regions

2019-08-19 Thread jesse
I have a table is  SALT_BUCKETS = 12, but it has 14 regions, is this right?

Thanks


Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread Josh Elser

http://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used

On 8/19/19 6:06 AM, you Zhuang wrote:

Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT
hbase-version: 1.4.6
Table:
CREATE TABLE test_phoenix.app (
dt integer not null,
a bigint not null ,
b bigint not null ,
c bigint not null ,
d bigint not null ,
e bigint not null ,
f bigint not null ,
g bigint not null ,
h bigint not null ,
i bigint not null ,
j bigint not null ,
k bigint not null ,
m decimal(30,6) ,
n decimal(30,6)
CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;

Index:
CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h) ASYNC;
(Has been filled data with bulkload and index is active)

Query:
select /*+ INDEX(test_phoenix.app local_c_h_index) */ * from 
TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

select * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

The first query will use index local_c_h_index and result shortly, the 
second query won’t , and response slowly.


The explain plan is weird, all showing without using index.



On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka 
mailto:asaras...@eztexting.com>> wrote:


We have no problems with that. I mean indexes are used even without 
hints, if they're suitable for a query.
Maybe you can share your Phoenix version, query, index definition and 
exec plan ?


On Mon, Aug 19, 2019 at 12:46 PM you Zhuang > wrote:


Yeah, I mean no hint , use appropriate index automatically. I
create a local index  and a query with corresponding index column
filter in where clause. But the query doesn’t use index, with
index hint it uses it.



--
Aleksandr Saraseka
DBA
380997600401
 *•* asaras...@eztexting.com 
 *•* eztexting.com 
 



 
 
 
 
 
 







Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread you Zhuang
Phoenix-version: 4.14.3-HBase-1.4-SNAPSHOT 
hbase-version: 1.4.6
Table:
CREATE TABLE test_phoenix.app (
   dt  integer not null,
   a  bigint not null  ,
   b  bigint not null  ,
   c  bigint not null  ,
   d  bigint not null  ,
   e  bigint not null  ,
   f  bigint not null  ,
   g  bigint not null  ,
   h  bigint not null  ,
   i  bigint not null  ,
   j  bigint not null  ,
   k  bigint not null  ,
   m  decimal(30,6) ,
   n  decimal(30,6) 
  
  CONSTRAINT pk PRIMARY KEY (dt, a,b,c,d,e,f,g,h,i,j,k)
  ) SALT_BUCKETS = 3,UPDATE_CACHE_FREQUENCY = 30;

Index: 
CREATE local INDEX local_c_h_index ON test_phoenix.app (c,h)   ASYNC;
(Has been filled data with bulkload and index is active)

Query:
select /*+ INDEX(test_phoenix.app local_c_h_index) */  * from TEST_PHOENIX.APP 
where c=2 and h = 1 limit 5;
select  * from TEST_PHOENIX.APP where c=2 and h = 1 limit 5;

The first query will use index local_c_h_index and result shortly, the second 
query won’t , and response slowly.

The explain plan is weird, all showing without using index.



> On Aug 19, 2019, at 5:54 PM, Aleksandr Saraseka  
> wrote:
> 
> We have no problems with that. I mean indexes are used even without hints, if 
> they're suitable for a query. 
> Maybe you can share your Phoenix version, query, index definition and exec 
> plan ?
> 
> On Mon, Aug 19, 2019 at 12:46 PM you Zhuang  > wrote:
> Yeah, I mean no hint , use appropriate index automatically. I create a local 
> index  and a query with corresponding index column filter in where clause. 
> But the query doesn’t use index, with index hint it uses it.
> 
> 
> -- 
>   Aleksandr Saraseka
> DBA
> 380997600401
>   •  asaras...@eztexting.com 
>   •  eztexting.com 
> 
>   
>  
> 
>
> 
> 
> 
>   
> 
>  
> 
>  
> 
> 
> 



Re: Is there any way to using appropriate index automatically?

2019-08-19 Thread Aleksandr Saraseka
We have no problems with that. I mean indexes are used even without hints,
if they're suitable for a query.
Maybe you can share your Phoenix version, query, index definition and exec
plan ?

On Mon, Aug 19, 2019 at 12:46 PM you Zhuang 
wrote:

> Yeah, I mean no hint , use appropriate index automatically. I create a
> local index  and a query with corresponding index column filter in where
> clause. But the query doesn’t use index, with index hint it uses it.



-- 
Aleksandr Saraseka
DBA
380997600401
 *•*  asaras...@eztexting.com  *•*  eztexting.com










Is there any way to using appropriate index automatically?

2019-08-19 Thread you Zhuang
Yeah, I mean no hint , use appropriate index automatically. I create a local 
index  and a query with corresponding index column filter in where clause. But 
the query doesn’t use index, with index hint it uses it.