You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@phoenix.apache.org by Matt Kowalczyk <ma...@cloudability.com> on 2015/10/14 21:29:12 UTC

stats table causing slow queries

Hi,

I have a large phoenix table with ~1800 regions. After running a
compaction, I noticed degraded query performance. After purging the
system.stat table, query performance returned back to normal.

Surprisingly, using sqlline.py does not cause this slow down. Here are some
numbers,

(before and after purging system.stat):

sqlline query took: 1 row selected (2.869 seconds)

after compaction, before purging system.stat table:

              mean = 47536.55 milliseconds
            stddev = 2657.65 milliseconds

after purging system.stat table:

              mean = 57.63 milliseconds
            stddev = 39.82 milliseconds

Purging the system.stat table removed about ~1755 records.

I have the following related configuration in my hbase-site.xml:

  <property>
    <name>phoenix.stats.guidepost.per.region</name>
    <value>10</value>
  </property>

Any thoughts?

Thanks!

Re: stats table causing slow queries

Posted by James Taylor <ja...@apache.org>.

Thanks for the details, Matt. As a work around (which you probably already
know), you can disable the stats collection by setting that config
parameter to 1 or setting phoenix.stats.guidepost.width to a very large
value.

Would you mind opening a JIRA and we can continue the discussion there? In
particular, I'd be curious about your schema (in particular your primary
key constraint, how big your row keys are, and whether or not your table is
salted) and the queries over which you're seeing performance degradation.
Do your queries have limit clauses on them? Also, are you querying from a
JVM that stays up (i.e. like sqlline or an app server) or is the JVM being
spun up each time? When the query times degrade, do you get a sense of
where the bottleneck is (client vs server CPU)?

Another useful piece of information would be the total number of guideposts
which you can get through a query like this:
    SELECT SUM(GUIDE_POSTS_ROW_COUNT) FROM SYSTEM.STATS
WHERE PHYSICAL_NAME='your_table_name';

Thanks,
James

On Wed, Oct 14, 2015 at 12:30 PM, Matt Kowalczyk <ma...@cloudability.com>
wrote:

> Oh yeah,
>
> hbase-1.1.2
> phoenix-4.5.1
>
> Thanks!
>
> On Wed, Oct 14, 2015 at 12:29 PM, Matt Kowalczyk <ma...@cloudability.com>
> wrote:
>
>> Hi,
>>
>> I have a large phoenix table with ~1800 regions. After running a
>> compaction, I noticed degraded query performance. After purging the
>> system.stat table, query performance returned back to normal.
>>
>> Surprisingly, using sqlline.py does not cause this slow down. Here are
>> some numbers,
>>
>> (before and after purging system.stat):
>>
>> sqlline query took: 1 row selected (2.869 seconds)
>>
>> after compaction, before purging system.stat table:
>>
>>               mean = 47536.55 milliseconds
>>             stddev = 2657.65 milliseconds
>>
>> after purging system.stat table:
>>
>>               mean = 57.63 milliseconds
>>             stddev = 39.82 milliseconds
>>
>> Purging the system.stat table removed about ~1755 records.
>>
>> I have the following related configuration in my hbase-site.xml:
>>
>>   <property>
>>     <name>phoenix.stats.guidepost.per.region</name>
>>     <value>10</value>
>>   </property>
>>
>> Any thoughts?
>>
>> Thanks!
>>
>
>

Re: stats table causing slow queries

Posted by Matt Kowalczyk <ma...@cloudability.com>.

Oh yeah,

hbase-1.1.2
phoenix-4.5.1

Thanks!

On Wed, Oct 14, 2015 at 12:29 PM, Matt Kowalczyk <ma...@cloudability.com>
wrote:

> Hi,
>
> I have a large phoenix table with ~1800 regions. After running a
> compaction, I noticed degraded query performance. After purging the
> system.stat table, query performance returned back to normal.
>
> Surprisingly, using sqlline.py does not cause this slow down. Here are
> some numbers,
>
> (before and after purging system.stat):
>
> sqlline query took: 1 row selected (2.869 seconds)
>
> after compaction, before purging system.stat table:
>
>               mean = 47536.55 milliseconds
>             stddev = 2657.65 milliseconds
>
> after purging system.stat table:
>
>               mean = 57.63 milliseconds
>             stddev = 39.82 milliseconds
>
> Purging the system.stat table removed about ~1755 records.
>
> I have the following related configuration in my hbase-site.xml:
>
>   <property>
>     <name>phoenix.stats.guidepost.per.region</name>
>     <value>10</value>
>   </property>
>
> Any thoughts?
>
> Thanks!
>