Re: hotspot in System.catalog table

[anil gupta]

We saw atleast 5x improvement in Upsert performance from our streaming app
just by altering table and adding UPDATE_CACHE_FREQUENCY=6 in all our
tables. Overall our cluster, system.catalog table and apps looks more
happy. Thanks Again!

On Thu, Apr 12, 2018 at 11:37 PM, anil gupta  wrote:

> Thanks a lot. Going to do that and see the impact.
>
> On Thu, Apr 12, 2018 at 11:33 PM, James Taylor 
> wrote:
>
>> It’s client side, but that’ll only impact newly created tables. You’ll
>> need to use the ALTER TABLE command for existing tables.
>>
>> On Thu, Apr 12, 2018 at 11:30 PM anil gupta 
>> wrote:
>>
>>> I have set phoenix.default.update.cache.frequency=6 in
>>> hbase-site.xml via ambari(we barely alter schema). Is this a client or
>>> server side property?
>>>
>>> On Thu, Apr 12, 2018 at 11:14 PM, anil gupta 
>>> wrote:
>>>
 I c. As per documentation[1], even for commits of upsert system.catalog
 is called. IMO, ALWAYS seems to be really aggressive. Is there any reason
 UPDATE_CACHE_FREQUENCY is set to ALWAYS by default? Do we plan to change
 the default value to 5 or 10 sec? Thanks for your help.


 PS: we were running into a lot of Phoenix scalability issues due to
 this.

 [1] https://phoenix.apache.org/language/index.html#options

 On Thu, Apr 12, 2018 at 11:06 PM, James Taylor 
 wrote:

> No, that won’t make a difference.
>
> On Thu, Apr 12, 2018 at 10:51 PM anil gupta 
> wrote:
>
>> Thanks for quick reply, James. We will look into
>> UPDATE_CACHE_FREQUENCY property. If we just replace PS with Statement, 
>> will
>> it fix the problem(AFAIK, Statement is not compiled)?
>>
>> On Thu, Apr 12, 2018 at 10:43 PM, James Taylor <
>> jamestay...@apache.org> wrote:
>>
>>> Try setting the UPDATE_CACHE_FREQUENCY table property (and
>>> configuring the phoenix.default.update.cache.frequency system-wide
>>> property). That'll prevent pinging the region hosting SYSTEM.CATALOG 
>>> every
>>> time a query is compiled. We've found value of even 5 seconds makes a 
>>> big
>>> difference. For more on that, see here[1] and here[2].
>>>
>>> In the future, we'll let the SYSTEM.CATALOG table span multiple
>>> regions - keep an eye on PHOENIX-3534.
>>>
>>> Thanks,
>>> James
>>>
>>> [1] https://phoenix.apache.org/#Altering
>>> [2] https://phoenix.apache.org/language/index.html#options
>>>
>>> On Thu, Apr 12, 2018 at 10:32 PM, anil gupta 
>>> wrote:
>>>
 Hi All,

 System.catalog table seems to be single region table(correct?). We
 are currently facing a problem of hotspot on System.catalog table.
 One of our app does around 4-5k select queries/sec. And, It is
 creating a new preparedstatement everytime. I suspect that while
 instantiating a new preparedstatement(contrary to Statement),
 system.catalog table is queried first. Hence, it is resulting into
 hotspotting. Is my analysis correct?

 (I have already suggested my colleagues to try using Statement
 instead of PS if they have to create a new one everytime.)

 --
 Thanks & Regards,
 Anil Gupta

>>>
>>>
>>
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>


 --
 Thanks & Regards,
 Anil Gupta

>>>
>>>
>>>
>>> --
>>> Thanks & Regards,
>>> Anil Gupta
>>>
>>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>



-- 
Thanks & Regards,
Anil Gupta

Re: hotspot in System.catalog table

[anil gupta]

Thanks a lot. Going to do that and see the impact.

On Thu, Apr 12, 2018 at 11:33 PM, James Taylor 
wrote:

> It’s client side, but that’ll only impact newly created tables. You’ll
> need to use the ALTER TABLE command for existing tables.
>
> On Thu, Apr 12, 2018 at 11:30 PM anil gupta  wrote:
>
>> I have set phoenix.default.update.cache.frequency=6 in
>> hbase-site.xml via ambari(we barely alter schema). Is this a client or
>> server side property?
>>
>> On Thu, Apr 12, 2018 at 11:14 PM, anil gupta 
>> wrote:
>>
>>> I c. As per documentation[1], even for commits of upsert system.catalog
>>> is called. IMO, ALWAYS seems to be really aggressive. Is there any reason
>>> UPDATE_CACHE_FREQUENCY is set to ALWAYS by default? Do we plan to change
>>> the default value to 5 or 10 sec? Thanks for your help.
>>>
>>>
>>> PS: we were running into a lot of Phoenix scalability issues due to
>>> this.
>>>
>>> [1] https://phoenix.apache.org/language/index.html#options
>>>
>>> On Thu, Apr 12, 2018 at 11:06 PM, James Taylor 
>>> wrote:
>>>
 No, that won’t make a difference.

 On Thu, Apr 12, 2018 at 10:51 PM anil gupta 
 wrote:

> Thanks for quick reply, James. We will look into
> UPDATE_CACHE_FREQUENCY property. If we just replace PS with Statement, 
> will
> it fix the problem(AFAIK, Statement is not compiled)?
>
> On Thu, Apr 12, 2018 at 10:43 PM, James Taylor  > wrote:
>
>> Try setting the UPDATE_CACHE_FREQUENCY table property (and
>> configuring the phoenix.default.update.cache.frequency system-wide
>> property). That'll prevent pinging the region hosting SYSTEM.CATALOG 
>> every
>> time a query is compiled. We've found value of even 5 seconds makes a big
>> difference. For more on that, see here[1] and here[2].
>>
>> In the future, we'll let the SYSTEM.CATALOG table span multiple
>> regions - keep an eye on PHOENIX-3534.
>>
>> Thanks,
>> James
>>
>> [1] https://phoenix.apache.org/#Altering
>> [2] https://phoenix.apache.org/language/index.html#options
>>
>> On Thu, Apr 12, 2018 at 10:32 PM, anil gupta 
>> wrote:
>>
>>> Hi All,
>>>
>>> System.catalog table seems to be single region table(correct?). We
>>> are currently facing a problem of hotspot on System.catalog table.
>>> One of our app does around 4-5k select queries/sec. And, It is
>>> creating a new preparedstatement everytime. I suspect that while
>>> instantiating a new preparedstatement(contrary to Statement),
>>> system.catalog table is queried first. Hence, it is resulting into
>>> hotspotting. Is my analysis correct?
>>>
>>> (I have already suggested my colleagues to try using Statement
>>> instead of PS if they have to create a new one everytime.)
>>>
>>> --
>>> Thanks & Regards,
>>> Anil Gupta
>>>
>>
>>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>

>>>
>>>
>>> --
>>> Thanks & Regards,
>>> Anil Gupta
>>>
>>
>>
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>


-- 
Thanks & Regards,
Anil Gupta

Re: hotspot in System.catalog table

[James Taylor]

It’s client side, but that’ll only impact newly created tables. You’ll need
to use the ALTER TABLE command for existing tables.

On Thu, Apr 12, 2018 at 11:30 PM anil gupta  wrote:

> I have set phoenix.default.update.cache.frequency=6 in hbase-site.xml
> via ambari(we barely alter schema). Is this a client or server side
> property?
>
> On Thu, Apr 12, 2018 at 11:14 PM, anil gupta 
> wrote:
>
>> I c. As per documentation[1], even for commits of upsert system.catalog
>> is called. IMO, ALWAYS seems to be really aggressive. Is there any reason
>> UPDATE_CACHE_FREQUENCY is set to ALWAYS by default? Do we plan to change
>> the default value to 5 or 10 sec? Thanks for your help.
>>
>>
>> PS: we were running into a lot of Phoenix scalability issues due to this.
>>
>> [1] https://phoenix.apache.org/language/index.html#options
>>
>> On Thu, Apr 12, 2018 at 11:06 PM, James Taylor 
>> wrote:
>>
>>> No, that won’t make a difference.
>>>
>>> On Thu, Apr 12, 2018 at 10:51 PM anil gupta 
>>> wrote:
>>>
 Thanks for quick reply, James. We will look into UPDATE_CACHE_FREQUENCY
 property. If we just replace PS with Statement, will it fix the
 problem(AFAIK, Statement is not compiled)?

 On Thu, Apr 12, 2018 at 10:43 PM, James Taylor 
 wrote:

> Try setting the UPDATE_CACHE_FREQUENCY table property (and configuring
> the phoenix.default.update.cache.frequency system-wide property). That'll
> prevent pinging the region hosting SYSTEM.CATALOG every time a query is
> compiled. We've found value of even 5 seconds makes a big difference. For
> more on that, see here[1] and here[2].
>
> In the future, we'll let the SYSTEM.CATALOG table span multiple
> regions - keep an eye on PHOENIX-3534.
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#options
>
> On Thu, Apr 12, 2018 at 10:32 PM, anil gupta 
> wrote:
>
>> Hi All,
>>
>> System.catalog table seems to be single region table(correct?). We
>> are currently facing a problem of hotspot on System.catalog table.
>> One of our app does around 4-5k select queries/sec. And, It is
>> creating a new preparedstatement everytime. I suspect that while
>> instantiating a new preparedstatement(contrary to Statement),
>> system.catalog table is queried first. Hence, it is resulting into
>> hotspotting. Is my analysis correct?
>>
>> (I have already suggested my colleagues to try using Statement
>> instead of PS if they have to create a new one everytime.)
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>
>


 --
 Thanks & Regards,
 Anil Gupta

>>>
>>
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>

Re: hotspot in System.catalog table

[James Taylor]

No, that won’t make a difference.

On Thu, Apr 12, 2018 at 10:51 PM anil gupta  wrote:

> Thanks for quick reply, James. We will look into UPDATE_CACHE_FREQUENCY
> property. If we just replace PS with Statement, will it fix the
> problem(AFAIK, Statement is not compiled)?
>
> On Thu, Apr 12, 2018 at 10:43 PM, James Taylor 
> wrote:
>
>> Try setting the UPDATE_CACHE_FREQUENCY table property (and configuring
>> the phoenix.default.update.cache.frequency system-wide property). That'll
>> prevent pinging the region hosting SYSTEM.CATALOG every time a query is
>> compiled. We've found value of even 5 seconds makes a big difference. For
>> more on that, see here[1] and here[2].
>>
>> In the future, we'll let the SYSTEM.CATALOG table span multiple regions -
>> keep an eye on PHOENIX-3534.
>>
>> Thanks,
>> James
>>
>> [1] https://phoenix.apache.org/#Altering
>> [2] https://phoenix.apache.org/language/index.html#options
>>
>> On Thu, Apr 12, 2018 at 10:32 PM, anil gupta 
>> wrote:
>>
>>> Hi All,
>>>
>>> System.catalog table seems to be single region table(correct?). We are
>>> currently facing a problem of hotspot on System.catalog table.
>>> One of our app does around 4-5k select queries/sec. And, It is creating
>>> a new preparedstatement everytime. I suspect that while instantiating a new
>>> preparedstatement(contrary to Statement), system.catalog table is queried
>>> first. Hence, it is resulting into hotspotting. Is my analysis correct?
>>>
>>> (I have already suggested my colleagues to try using Statement instead
>>> of PS if they have to create a new one everytime.)
>>>
>>> --
>>> Thanks & Regards,
>>> Anil Gupta
>>>
>>
>>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>

Re: hotspot in System.catalog table

[anil gupta]

Thanks for quick reply, James. We will look into UPDATE_CACHE_FREQUENCY
property. If we just replace PS with Statement, will it fix the
problem(AFAIK, Statement is not compiled)?

On Thu, Apr 12, 2018 at 10:43 PM, James Taylor 
wrote:

> Try setting the UPDATE_CACHE_FREQUENCY table property (and configuring
> the phoenix.default.update.cache.frequency system-wide property). That'll
> prevent pinging the region hosting SYSTEM.CATALOG every time a query is
> compiled. We've found value of even 5 seconds makes a big difference. For
> more on that, see here[1] and here[2].
>
> In the future, we'll let the SYSTEM.CATALOG table span multiple regions -
> keep an eye on PHOENIX-3534.
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/#Altering
> [2] https://phoenix.apache.org/language/index.html#options
>
> On Thu, Apr 12, 2018 at 10:32 PM, anil gupta 
> wrote:
>
>> Hi All,
>>
>> System.catalog table seems to be single region table(correct?). We are
>> currently facing a problem of hotspot on System.catalog table.
>> One of our app does around 4-5k select queries/sec. And, It is creating a
>> new preparedstatement everytime. I suspect that while instantiating a new
>> preparedstatement(contrary to Statement), system.catalog table is queried
>> first. Hence, it is resulting into hotspotting. Is my analysis correct?
>>
>> (I have already suggested my colleagues to try using Statement instead of
>> PS if they have to create a new one everytime.)
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>
>


-- 
Thanks & Regards,
Anil Gupta

Re: hotspot in System.catalog table

[James Taylor]

Try setting the UPDATE_CACHE_FREQUENCY table property (and configuring
the phoenix.default.update.cache.frequency system-wide property). That'll
prevent pinging the region hosting SYSTEM.CATALOG every time a query is
compiled. We've found value of even 5 seconds makes a big difference. For
more on that, see here[1] and here[2].

In the future, we'll let the SYSTEM.CATALOG table span multiple regions -
keep an eye on PHOENIX-3534.

Thanks,
James

[1] https://phoenix.apache.org/#Altering
[2] https://phoenix.apache.org/language/index.html#options

On Thu, Apr 12, 2018 at 10:32 PM, anil gupta  wrote:

> Hi All,
>
> System.catalog table seems to be single region table(correct?). We are
> currently facing a problem of hotspot on System.catalog table.
> One of our app does around 4-5k select queries/sec. And, It is creating a
> new preparedstatement everytime. I suspect that while instantiating a new
> preparedstatement(contrary to Statement), system.catalog table is queried
> first. Hence, it is resulting into hotspotting. Is my analysis correct?
>
> (I have already suggested my colleagues to try using Statement instead of
> PS if they have to create a new one everytime.)
>
> --
> Thanks & Regards,
> Anil Gupta
>