phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mujtaba Chohan <mujt...@apache.org>
Subject Re: Update statistics made query 2-3x slower
Date Fri, 13 Feb 2015 18:20:54 GMT
Hi Constantin,

This in useful info. Just to clarify slowdown that you see after update
statistics case, was the *update statistics* executed after initial data
load or after you upserted it again?

2. How many regions/region servers (RS) did the data end up on?

3. 30/60 seconds for count(*) seems really high. Do you see lots of disk
I/O during your count query? How wide are your rows and how much memory is
available on your RS/HBase heap?

3. Can you also send output of *explain select count(*) from tablex* for
this case?

Thanks,
Mujtaba

On Fri, Feb 13, 2015 at 12:34 AM, Ciureanu, Constantin (GfK) <
Constantin.Ciureanu@gfk.com> wrote:

> Hello Mujtaba,
>
>
>
> Don’t worry – it was just the *select count(*) from tableX* that was
> slowed down in a more than visible way.
>
> I presume all the regular queries do actually benefit from using the STATS.
>
>
>
> Some other cases where I saw slowdown for “*select count(*) from tableX*”:
>
> -       First time after loading 6 M records – the time to obtain the
> count was ~30 sec
>
> -       After loading the *same* 6 M records again – the time almost
> doubled L I imagine the data is doubled, not yet compacted in HBase
>
> -       After deleting the 6M rows (delete from …. , not truncate) and
> loading the 6M rows again – the same double time – same comment as above
>
> -       After update statistics tableX – the time was around 2x the
> original time (~60 seconds) – this I couldn’t really explain (perhaps the
> fleet I use is undersized)
>
>
>
> I need to mention that I’m using 4.2.2 but I can’t wait for 4.3 to be
> released (as it will fix some issues I have. Eg. one with SKIP SCAN:
>
> [1st part of PK between A and B] or [first part of PK between C and D] or
> [….] was understood as a full table scan = painfully slow -> but this
> worked today after I used a hint in the SELECT /*+ SKIP_SCAN */ which
> shouldn’t be mandatory in my opinion).
>
>
>
> Regards,
>
>   Constantin
>
>
>
> *From:* Mujtaba Chohan [mailto:mujtaba@apache.org]
> *Sent:* Thursday, February 12, 2015 9:20 PM
>
> *To:* user@phoenix.apache.org
> *Subject:* Re: Update statistics made query 2-3x slower
>
>
>
> Constantin - If possible can you please share your schema, approx.
> row/columns width, number of region servers in your cluster plus their heap
> size, HBase/Phoenix version and any default property overrides so we can
> identify why stats are slowing things down in your case.
>
>
>
> Thanks,
>
> Mujtaba
>
>
>
> On Thu, Feb 12, 2015 at 12:56 AM, Ciureanu, Constantin (GfK) <
> Constantin.Ciureanu@gfk.com> wrote:
>
> It worked!
>
> Without stats it’s again faster (2-3x times) – but I do understand that
> all other normal queries might benefit from the stats.
>
>
>
> Thank you Mujtaba for the info,
>
> Thank you Vasudevan for the explanations, I already used HBase and I agree
> it’s hard to have a counter for the table rows (especially if the
> tombstones for deleted rows are still there – ie. not compacted yet).
>
>
>
> Constantin
>
>
>
>
>
>
>
> *From:* Mujtaba Chohan [mailto:mujtaba@apache.org]
> *Sent:* Wednesday, February 11, 2015 8:54 PM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Update statistics made query 2-3x slower
>
>
>
> To compare performance without stats, try deleting related rows from
> SYSTEM.STATS or an easier way, just truncate SYSTEM.STATS table from HBase
> shell and restart your region servers.
>
> //mujtaba
>
>
>
> On Wed, Feb 11, 2015 at 10:29 AM, Vasudevan, Ramkrishna S <
> ramkrishna.s.vasudevan@intel.com> wrote:
>
> Hi Constantin
>
>
>
> Before I could explain on the slowness part let me answer your 2nd
> question,
>
>
>
> Phoenix is on top of HBase. HBase is a distributed NoSQL DB. So the data
> that is residing inside logical entities called regions are spread across
> different nodes (region servers).  There is nothing like a table that is in
> one location where you can keep updating the count of rows that is getting
> inserted.
>
>
>
> Which means that when you need  count(*) you may have to aggregate the
> count from every region distributed across region servers. So in other
> words a table is not a single entity it is a collection of regions.
>
>
>
> Coming to your slowness in query, the update statistics query allows you
> to parallelize the query into logical chunks on a single region.  Suppose
> there are 100K rows in a region the statistics collected would allow you to
> run a query parallely for eg say execute parallely on 10 equal chunks of
> 10000 rows within that region.
>
>
>
> Have you modified any of the parameters related to statistics like this
> one ‘phoenix.stats.guidepost.width’.
>
>
>
>
>
> Regards
>
> Ram
>
> *From:* Ciureanu, Constantin (GfK) [mailto:Constantin.Ciureanu@gfk.com]
> *Sent:* Wednesday, February 11, 2015 2:51 PM
> *To:* user@phoenix.apache.org
> *Subject:* Update statistics made query 2-3x slower
>
>
>
> Hello all,
>
>
>
> 1.     Is there a good explanation why updating the statistics:
>
> *update statistics tableX;*
>
>
>
> made this query 2x times slower?   (it was 27 seconds before, now it’s
> somewhere between 60 – 90 seconds)
>
> *select count(*) from tableX;*
>
> +------------------------------------------+
>
> |                 COUNT(1)                 |
>
> +------------------------------------------+
>
> | 5786227                                  |
>
> +------------------------------------------+
>
> 1 row selected (62.718 seconds)
>
>
>
> (If possible J ) how can I “drop” those statistics?
>
>
>
> 2. Why there is nothing (like a counter / attribute for the table) to
> obtain the number of rows in one table fast?
>
>
>
> Thank you,
>
>    Constantin
>
>
>
>
>

Mime
View raw message