Re: performance issue on big table join

[Alexander Behm]

You are welcome.

You can track IMPALA-3902 to see our progress on supporting fully
multi-threaded execution.

Support for multi-threaded aggregations is already available. Certain other
queries will also work in multi-threaded mode. The big limitation is that
distributed joins and unions do not yet work (local joins for nested types
are ok).
We even enable multi-threading by default for some operations like COMPUTE
STATS on Parquet.

You can play around with multi-threaded execution using the MT_DOP query
option.

On Thu, Nov 2, 2017 at 6:17 PM, 俊杰陈  wrote:

> Thanks Alex to reply again.
>
> Do we have plan to support multi-thread join/aggregation?  Or it is
> intented to be single thread to maximum query throughput?
>
>
>
> 2017-11-03 0:32 GMT+08:00 Alexander Behm :
>
>> See my response on the other thread you started. The probe side of joins
>> are are executed in a single thread per host. Impala can run multiple
>> builds in parallel - but each build uses only a single thread.
>> A single query might not be able to max out your CPU, but most realistic
>> workloads run several queries concurrently.
>>
>> On Thu, Nov 2, 2017 at 12:22 AM, Hongxu Ma  wrote:
>>
>> > Thanks LL. Your query options look good.
>> >
>> > As Xu Cheng mentioned, I also noticed that Impala do hash join slowly in
>> > some big data situations.
>> > Very curious to the root cause.
>> >
>> >
>> > 在 02/11/2017 10:00, 俊杰陈 写道:
>> >
>> > +user list
>> >
>> > 2017-11-02 9:57 GMT+08:00 俊杰陈  > >:
>> >
>> >
>> > Hi Mostafa
>> >
>> > Cheng already put the profile in thread.
>> >
>> > Here is another profile for impala release version. you can also see the
>> > attachment.
>> >
>> >
>> > 2017-11-02 9:30 GMT+08:00 Mostafa Mokhtar  <
>> mmokh...@cloudera.com>:
>> >
>> >
>> > Attaching the query profile will be most helpful to investigate this
>> > issue.
>> >
>> > If you can capture the profile from the WebUI on the coordinator node it
>> > would be great.
>> >
>> > On Wed, Nov 1, 2017 at 6:22 PM, 俊杰陈  <
>> cjjnj...@gmail.com> wrote:
>> >
>> >
>> > Thanks Hongxu,
>> >
>> > Here are configurations on my cluster,  most of them are default values.
>> > Which item do you think it may impact?
>> >
>> > ABORT_ON_DEFAULT_LIMIT_EXCEEDED: [0]
>> > ABORT_ON_ERROR: [0]
>> > ALLOW_UNSUPPORTED_FORMATS: [0]
>> > APPX_COUNT_DISTINCT: [0]
>> > BATCH_SIZE: [0]
>> > COMPRESSION_CODEC: [NONE]
>> > DEBUG_ACTION: []
>> > DEFAULT_ORDER_BY_LIMIT: [-1]
>> > DISABLE_CACHED_READS: [0]
>> > DISABLE_CODEGEN: [0]
>> > DISABLE_OUTERMOST_TOPN: [0]
>> > DISABLE_ROW_RUNTIME_FILTERING: [0]
>> > DISABLE_STREAMING_PREAGGREGATIONS: [0]
>> > DISABLE_UNSAFE_SPILLS: [0]
>> > ENABLE_EXPR_REWRITES: [1]
>> > EXEC_SINGLE_NODE_ROWS_THRESHOLD: [100]
>> > EXPLAIN_LEVEL: [1]
>> > HBASE_CACHE_BLOCKS: [0]
>> > HBASE_CACHING: [0]
>> > MAX_BLOCK_MGR_MEMORY: [0]
>> > MAX_ERRORS: [100]
>> > MAX_IO_BUFFERS: [0]
>> > MAX_NUM_RUNTIME_FILTERS: [10]
>> > MAX_SCAN_RANGE_LENGTH: [0]
>> > MEM_LIMIT: [0]
>> > MT_DOP: [0]
>> > NUM_NODES: [0]
>> > NUM_SCANNER_THREADS: [0]
>> > OPTIMIZE_PARTITION_KEY_SCANS: [0]
>> > PARQUET_ANNOTATE_STRINGS_UTF8: [0]
>> > PARQUET_FALLBACK_SCHEMA_RESOLUTION: [0]
>> > PARQUET_FILE_SIZE: [0]
>> > PREFETCH_MODE: [1]
>> > QUERY_TIMEOUT_S: [0]
>> > REPLICA_PREFERENCE: [0]
>> > REQUEST_POOL: []
>> > RESERVATION_REQUEST_TIMEOUT: [0]
>> > RM_INITIAL_MEM: [0]
>> > RUNTIME_BLOOM_FILTER_SIZE: [1048576]
>> > RUNTIME_FILTER_MAX_SIZE: [16777216]
>> > RUNTIME_FILTER_MIN_SIZE: [1048576]
>> > RUNTIME_FILTER_MODE: [2]
>> > RUNTIME_FILTER_WAIT_TIME_MS: [0]
>> > S3_SKIP_INSERT_STAGING: [1]
>> > SCAN_NODE_CODEGEN_THRESHOLD: [180]
>> > SCHEDULE_RANDOM_REPLICA: [0]
>> > SCRATCH_LIMIT: [-1]
>> > SEQ_COMPRESSION_MODE: [0]
>> > STRICT_MODE: [0]
>> > SUPPORT_START_OVER: [false]
>> > SYNC_DDL: [0]
>> > V_CPU_CORES: [0]
>> >
>> > 2017-10-31 15:30 GMT+08:00 Hongxu Ma  <
>> inte...@outlook.com>:
>> >
>> >
>> > Hi JJ
>> > Consider it only takes 3mins on SparkSQL, maybe there are some
>> >
>> > mistakes
>> >
>> > in
>> >
>> > query options.
>> > Try run "set;" in impala-shell and check all query options, e.g:
>> > BATCH_SIZE: [0]
>> > DISABLE_CODEGEN: [0]
>> > RUNTIME_FILTER_MODE: GLOBAL
>> >
>> > Just a guess, thanks.
>> >
>> > 在 27/10/2017 10:25, 俊杰陈 写道:
>> > The profile file is damaged. Here is a screenshot for exec summary
>> > [cid:ii_j999ymep1_15f5ba563aeabb91]
>> > ​
>> >
>> > 2017-10-27 10:04 GMT+08:00 俊杰陈 

Re: question about map in impala

[Alexander Behm]

Regarding the design decisions I encourage you to look at these:
https://blog.cloudera.com/blog/2015/11/new-in-cloudera-
enterprise-5-5-support-for-complex-types-in-impala/
https://www.slideshare.net/cloudera/nested-types-in-impala-55344174

Regarding making your query faster:
1. Your query with count(1) does not scan any data, so using it as a
comparison point is not realistic. A query with count(some_column) is a
better comparison point, but still not quite fair.
2. To speed up your query you might try the MT_DOP query option. The MT_DOP
query option controls the intra-node parallelism, i.e., how many fragment
instances per node will be launched.

In the future, this type of query might benefit from data elimination
techniques like Parquet min/max filtering (assuming your predicate is
selective), but we don't fully support min/max on nested types yet. If you
are able to restructure your data such that columns with selective
predicates are at the top-level, then you could benefit from min/max data
elimination even today (with the latest Impala version).

Alex

On Thu, Nov 2, 2017 at 12:58 AM, yu feng  wrote:

> Hi, I find In impala, If I have a table T and a column attributes which
> defained as Map, I have to write SQL like this :
>
>
> select attributes.value, T.name from  T join T.attributes where attributes
> .key = 'play'.
>
> However, We query this table in hive like this :
>
> select T.attributes['play'], T.name from T
>
> And I find the execution of impala is Slow caused of the JOIN.
> If I execute :
>
> select count(1) from T;
>
> It takes 0.5 seconds and If I add join attributes, It will take 8s +.
>
> the summary like this :
>
> Operator#Hosts   Avg Time   Max Time#Rows
> Est. #Rows   Peak Mem  Est. Peak Mem  Detail
> 
> ---
> 07:AGGREGATE 1   85.020ms   85.020ms1
>  1  112.00 KB-1.00 B  FINALIZE
> 06:EXCHANGE  1  404.928us  404.928us   25
>  1  0-1.00 B  UNPARTITIONED
> 05:AGGREGATE25  104.354ms  141.573ms   25
>  18.35 MB   10.00 MB
> 01:SUBPLAN  251s218ms1s813ms  201.54M
> 668.96B8.25 MB  0
> |--04:NESTED LOOP JOIN  253s250ms4s852ms0
> 10   24.00 KB16.00 B  CROSS JOIN
> |  |--02:SINGULAR ROW SRC   250.000ns0.000ns0
>  1  0  0
> |  03:UNNEST25  688.110ms1s033ms0
> 10  0  0  uda_events.attributes
> 00:SCAN HDFS25  112.396ms  206.405ms  201.61M
> 66.90B  107.20 MB   48.00 MB  uda.uda_events
>
>
> Operator   #Hosts   Avg Time   Max Time#Rows  Est. #Rows
> Peak Mem  Est. Peak Mem  Detail
> 
> ---
> 03:AGGREGATE1   87.908ms   87.908ms1   1
> 112.00 KB-1.00 B  FINALIZE
> 02:EXCHANGE 1  321.296us  321.296us   25   1
>0-1.00 B  UNPARTITIONED
> 01:AGGREGATE   25   74.613ms   81.567ms   25   1
> 12.00 KB   10.00 MB
> 00:SCAN HDFS   25   91.512ms  135.441ms  208.47M  66.90B
> 3.09 MB  0  uda.uda_events
>
>
> I have two question :
> 1、 Why Impala implements map column like this( via join) ?
> 2、 How to speed up the speed the query like this?
>
> Thanks very much.
>

Re: performance issue on big table join

[俊杰陈]

Thanks Alex to reply again.

Do we have plan to support multi-thread join/aggregation?  Or it is
intented to be single thread to maximum query throughput?



2017-11-03 0:32 GMT+08:00 Alexander Behm :

> See my response on the other thread you started. The probe side of joins
> are are executed in a single thread per host. Impala can run multiple
> builds in parallel - but each build uses only a single thread.
> A single query might not be able to max out your CPU, but most realistic
> workloads run several queries concurrently.
>
> On Thu, Nov 2, 2017 at 12:22 AM, Hongxu Ma  wrote:
>
> > Thanks LL. Your query options look good.
> >
> > As Xu Cheng mentioned, I also noticed that Impala do hash join slowly in
> > some big data situations.
> > Very curious to the root cause.
> >
> >
> > 在 02/11/2017 10:00, 俊杰陈 写道:
> >
> > +user list
> >
> > 2017-11-02 9:57 GMT+08:00 俊杰陈  :
> >
> >
> > Hi Mostafa
> >
> > Cheng already put the profile in thread.
> >
> > Here is another profile for impala release version. you can also see the
> > attachment.
> >
> >
> > 2017-11-02 9:30 GMT+08:00 Mostafa Mokhtar  <
> mmokh...@cloudera.com>:
> >
> >
> > Attaching the query profile will be most helpful to investigate this
> > issue.
> >
> > If you can capture the profile from the WebUI on the coordinator node it
> > would be great.
> >
> > On Wed, Nov 1, 2017 at 6:22 PM, 俊杰陈  <
> cjjnj...@gmail.com> wrote:
> >
> >
> > Thanks Hongxu,
> >
> > Here are configurations on my cluster,  most of them are default values.
> > Which item do you think it may impact?
> >
> > ABORT_ON_DEFAULT_LIMIT_EXCEEDED: [0]
> > ABORT_ON_ERROR: [0]
> > ALLOW_UNSUPPORTED_FORMATS: [0]
> > APPX_COUNT_DISTINCT: [0]
> > BATCH_SIZE: [0]
> > COMPRESSION_CODEC: [NONE]
> > DEBUG_ACTION: []
> > DEFAULT_ORDER_BY_LIMIT: [-1]
> > DISABLE_CACHED_READS: [0]
> > DISABLE_CODEGEN: [0]
> > DISABLE_OUTERMOST_TOPN: [0]
> > DISABLE_ROW_RUNTIME_FILTERING: [0]
> > DISABLE_STREAMING_PREAGGREGATIONS: [0]
> > DISABLE_UNSAFE_SPILLS: [0]
> > ENABLE_EXPR_REWRITES: [1]
> > EXEC_SINGLE_NODE_ROWS_THRESHOLD: [100]
> > EXPLAIN_LEVEL: [1]
> > HBASE_CACHE_BLOCKS: [0]
> > HBASE_CACHING: [0]
> > MAX_BLOCK_MGR_MEMORY: [0]
> > MAX_ERRORS: [100]
> > MAX_IO_BUFFERS: [0]
> > MAX_NUM_RUNTIME_FILTERS: [10]
> > MAX_SCAN_RANGE_LENGTH: [0]
> > MEM_LIMIT: [0]
> > MT_DOP: [0]
> > NUM_NODES: [0]
> > NUM_SCANNER_THREADS: [0]
> > OPTIMIZE_PARTITION_KEY_SCANS: [0]
> > PARQUET_ANNOTATE_STRINGS_UTF8: [0]
> > PARQUET_FALLBACK_SCHEMA_RESOLUTION: [0]
> > PARQUET_FILE_SIZE: [0]
> > PREFETCH_MODE: [1]
> > QUERY_TIMEOUT_S: [0]
> > REPLICA_PREFERENCE: [0]
> > REQUEST_POOL: []
> > RESERVATION_REQUEST_TIMEOUT: [0]
> > RM_INITIAL_MEM: [0]
> > RUNTIME_BLOOM_FILTER_SIZE: [1048576]
> > RUNTIME_FILTER_MAX_SIZE: [16777216]
> > RUNTIME_FILTER_MIN_SIZE: [1048576]
> > RUNTIME_FILTER_MODE: [2]
> > RUNTIME_FILTER_WAIT_TIME_MS: [0]
> > S3_SKIP_INSERT_STAGING: [1]
> > SCAN_NODE_CODEGEN_THRESHOLD: [180]
> > SCHEDULE_RANDOM_REPLICA: [0]
> > SCRATCH_LIMIT: [-1]
> > SEQ_COMPRESSION_MODE: [0]
> > STRICT_MODE: [0]
> > SUPPORT_START_OVER: [false]
> > SYNC_DDL: [0]
> > V_CPU_CORES: [0]
> >
> > 2017-10-31 15:30 GMT+08:00 Hongxu Ma  <
> inte...@outlook.com>:
> >
> >
> > Hi JJ
> > Consider it only takes 3mins on SparkSQL, maybe there are some
> >
> > mistakes
> >
> > in
> >
> > query options.
> > Try run "set;" in impala-shell and check all query options, e.g:
> > BATCH_SIZE: [0]
> > DISABLE_CODEGEN: [0]
> > RUNTIME_FILTER_MODE: GLOBAL
> >
> > Just a guess, thanks.
> >
> > 在 27/10/2017 10:25, 俊杰陈 写道:
> > The profile file is damaged. Here is a screenshot for exec summary
> > [cid:ii_j999ymep1_15f5ba563aeabb91]
> > ​
> >
> > 2017-10-27 10:04 GMT+08:00 俊杰陈  > nj...@gmail.com> >:
> > Hi Devs
> >
> > I met a performance issue on big table join. The query takes more
> >
> > than 3
> >
> > hours on Impala and only 3 minutes on Spark SQL on the same 5 nodes
> > cluster. when running query,  the left scanner and exchange node are
> >
> > very
> >
> > slow.  Did I miss some key arguments?
> >
> > you can see profile file in attachment.
> >
> > [cid:ii_j9998pph2_15f5b92f2cf47020]
> > ​
> > --
> > Thanks & Best Regards
> >
> >
> >
> > --
> > Thanks & Best Regards
> >
> >
> > --
> > Regards,
> > Hongxu.
> >
> >
> >
> >
> > --
> > Thanks & Best Regards
> >
> >
> >
> >
> > --
> > Thanks & Best Regards
> >
> >
> >
> >
> > --
> > Regards,
> 

Re: impala::Mutex

[Zoltan Borok-Nagy]

Thanks for the recommendation, the whole abseil library looks cool.

abls::Mutex is especially interesting, since it conceptually differs from
traditional mutexes. Although, this means a bit more work if we want to use
it, because it won't work as a drop-in replacement.

Zoli


On Tue, Oct 31, 2017 at 8:47 PM, Todd Lipcon  wrote:

> BTW I should have linked the following in my earlier email:
> https://abseil.io/about/design/mutex
>
> -Todd
>
> On Tue, Oct 31, 2017 at 10:21 AM, Todd Lipcon  wrote:
>
> > Yea, we use our own mutex which is a simple wrapper around pthreads, but
> > also adds some instrumentation and debugging.
> >
> > We have recently been thinking about switching to absl::Mutex from
> Google.
> > It has adaptive spinning, faster wake up for condition waiters, and a
> nice
> > API which avoids missed notify bugs. It also has the same or better
> > instrumentation support.
> >
> > If you are going to switch I'd take a look at that implementation as
> well.
> >
> > On Oct 31, 2017 10:12 AM, "Tim Armstrong" 
> wrote:
> >
> >> The main case where we still use boost::mutex is when it's pair with a
> >> condition variable - we don't have any condition variable that
> integrates
> >> with SpinLock.
> >>
> >> Mutex does provide some stronger fairness guarantees, I think, but afaik
> >> we
> >> don't rely on that anywhere.
> >>
> >> On Tue, Oct 31, 2017 at 9:54 AM, Daniel Hecht 
> >> wrote:
> >>
> >> > We have impala::SpinLock, which works with lock_guard, unique_lock,
> etc.
> >> > It's adaptive so it will block after spinning for a little bit, and
> >> seems
> >> > to work well in most cases. It's built on gutil, which also has some
> >> > tracing we could enable. Any reason not to stick with that?
> >> >
> >> > I haven't looked at Kudu's implementation in a while and am not
> opposed
> >> to
> >> > using it if there's a good reason to switch.  But I think maybe we
> >> should
> >> > first figure out how to better share code with Kudu so that we don't
> >> just
> >> > continue to fork general purpose utility code (which is what we're
> >> > currently doing).
> >> >
> >> > On Tue, Oct 31, 2017 at 9:47 AM, Zoltan Borok-Nagy <
> >> > borokna...@cloudera.com>
> >> > wrote:
> >> >
> >> > > Hi Everyone,
> >> > >
> >> > > I started to review the usage of synchronization primitives in
> Impala
> >> and
> >> > > created this JIRA issue: https://issues.apache.org/
> >> > jira/browse/IMPALA-6125
> >> > >
> >> > > After some chat with Tim, we talked about the possibilities of
> >> reducing
> >> > the
> >> > > dependence of boost. Since now we are using C++11, there are
> >> std::thread,
> >> > > std::mutex, and std::condition_variable in the standard library.
> >> > >
> >> > > On the other hand, the standard library likes to use exceptions in
> >> case
> >> > of
> >> > > errors, and AFAIK we don't like exceptions in Impala. Also, we
> already
> >> > have
> >> > > utility classes like ConditionVariable in the code base. In the
> >> kudu/util
> >> > > directory, there is a Mutex class which is more conform to the
> Impala
> >> > code
> >> > > conventions than boost and the standard library. It also checks the
> >> > > ownership of the mutex in debug mode, which can be useful for
> >> detecting
> >> > > bugs.
> >> > >
> >> > > Do you think it would be a good idea to create our own Mutex
> >> > implementation
> >> > > based on kudu/util/mutex?
> >> > >
> >> > > BR,
> >> > > Zoltan
> >> > >
> >> >
> >>
> >
>
>
> --
> Todd Lipcon
> Software Engineer, Cloudera
>

Re: performance issue on big table join

[Alexander Behm]

See my response on the other thread you started. The probe side of joins
are are executed in a single thread per host. Impala can run multiple
builds in parallel - but each build uses only a single thread.
A single query might not be able to max out your CPU, but most realistic
workloads run several queries concurrently.

On Thu, Nov 2, 2017 at 12:22 AM, Hongxu Ma  wrote:

> Thanks LL. Your query options look good.
>
> As Xu Cheng mentioned, I also noticed that Impala do hash join slowly in
> some big data situations.
> Very curious to the root cause.
>
>
> 在 02/11/2017 10:00, 俊杰陈 写道:
>
> +user list
>
> 2017-11-02 9:57 GMT+08:00 俊杰陈  :
>
>
> Hi Mostafa
>
> Cheng already put the profile in thread.
>
> Here is another profile for impala release version. you can also see the
> attachment.
>
>
> 2017-11-02 9:30 GMT+08:00 Mostafa Mokhtar  
> :
>
>
> Attaching the query profile will be most helpful to investigate this
> issue.
>
> If you can capture the profile from the WebUI on the coordinator node it
> would be great.
>
> On Wed, Nov 1, 2017 at 6:22 PM, 俊杰陈   
> wrote:
>
>
> Thanks Hongxu,
>
> Here are configurations on my cluster,  most of them are default values.
> Which item do you think it may impact?
>
> ABORT_ON_DEFAULT_LIMIT_EXCEEDED: [0]
> ABORT_ON_ERROR: [0]
> ALLOW_UNSUPPORTED_FORMATS: [0]
> APPX_COUNT_DISTINCT: [0]
> BATCH_SIZE: [0]
> COMPRESSION_CODEC: [NONE]
> DEBUG_ACTION: []
> DEFAULT_ORDER_BY_LIMIT: [-1]
> DISABLE_CACHED_READS: [0]
> DISABLE_CODEGEN: [0]
> DISABLE_OUTERMOST_TOPN: [0]
> DISABLE_ROW_RUNTIME_FILTERING: [0]
> DISABLE_STREAMING_PREAGGREGATIONS: [0]
> DISABLE_UNSAFE_SPILLS: [0]
> ENABLE_EXPR_REWRITES: [1]
> EXEC_SINGLE_NODE_ROWS_THRESHOLD: [100]
> EXPLAIN_LEVEL: [1]
> HBASE_CACHE_BLOCKS: [0]
> HBASE_CACHING: [0]
> MAX_BLOCK_MGR_MEMORY: [0]
> MAX_ERRORS: [100]
> MAX_IO_BUFFERS: [0]
> MAX_NUM_RUNTIME_FILTERS: [10]
> MAX_SCAN_RANGE_LENGTH: [0]
> MEM_LIMIT: [0]
> MT_DOP: [0]
> NUM_NODES: [0]
> NUM_SCANNER_THREADS: [0]
> OPTIMIZE_PARTITION_KEY_SCANS: [0]
> PARQUET_ANNOTATE_STRINGS_UTF8: [0]
> PARQUET_FALLBACK_SCHEMA_RESOLUTION: [0]
> PARQUET_FILE_SIZE: [0]
> PREFETCH_MODE: [1]
> QUERY_TIMEOUT_S: [0]
> REPLICA_PREFERENCE: [0]
> REQUEST_POOL: []
> RESERVATION_REQUEST_TIMEOUT: [0]
> RM_INITIAL_MEM: [0]
> RUNTIME_BLOOM_FILTER_SIZE: [1048576]
> RUNTIME_FILTER_MAX_SIZE: [16777216]
> RUNTIME_FILTER_MIN_SIZE: [1048576]
> RUNTIME_FILTER_MODE: [2]
> RUNTIME_FILTER_WAIT_TIME_MS: [0]
> S3_SKIP_INSERT_STAGING: [1]
> SCAN_NODE_CODEGEN_THRESHOLD: [180]
> SCHEDULE_RANDOM_REPLICA: [0]
> SCRATCH_LIMIT: [-1]
> SEQ_COMPRESSION_MODE: [0]
> STRICT_MODE: [0]
> SUPPORT_START_OVER: [false]
> SYNC_DDL: [0]
> V_CPU_CORES: [0]
>
> 2017-10-31 15:30 GMT+08:00 Hongxu Ma  
> :
>
>
> Hi JJ
> Consider it only takes 3mins on SparkSQL, maybe there are some
>
> mistakes
>
> in
>
> query options.
> Try run "set;" in impala-shell and check all query options, e.g:
> BATCH_SIZE: [0]
> DISABLE_CODEGEN: [0]
> RUNTIME_FILTER_MODE: GLOBAL
>
> Just a guess, thanks.
>
> 在 27/10/2017 10:25, 俊杰陈 写道:
> The profile file is damaged. Here is a screenshot for exec summary
> [cid:ii_j999ymep1_15f5ba563aeabb91]
> ​
>
> 2017-10-27 10:04 GMT+08:00 俊杰陈  nj...@gmail.com> >:
> Hi Devs
>
> I met a performance issue on big table join. The query takes more
>
> than 3
>
> hours on Impala and only 3 minutes on Spark SQL on the same 5 nodes
> cluster. when running query,  the left scanner and exchange node are
>
> very
>
> slow.  Did I miss some key arguments?
>
> you can see profile file in attachment.
>
> [cid:ii_j9998pph2_15f5b92f2cf47020]
> ​
> --
> Thanks & Best Regards
>
>
>
> --
> Thanks & Best Regards
>
>
> --
> Regards,
> Hongxu.
>
>
>
>
> --
> Thanks & Best Regards
>
>
>
>
> --
> Thanks & Best Regards
>
>
>
>
> --
> Regards,
> Hongxu.
>
>

Re: performance issue on big table join

[Hongxu Ma]

Thanks LL. Your query options look good.

As Xu Cheng mentioned, I also noticed that Impala do hash join slowly in some 
big data situations.
Very curious to the root cause.

在 02/11/2017 10:00, 俊杰陈 写道:

+user list

2017-11-02 9:57 GMT+08:00 俊杰陈 :



Hi Mostafa

Cheng already put the profile in thread.

Here is another profile for impala release version. you can also see the
attachment.


2017-11-02 9:30 GMT+08:00 Mostafa Mokhtar 
:



Attaching the query profile will be most helpful to investigate this
issue.

If you can capture the profile from the WebUI on the coordinator node it
would be great.

On Wed, Nov 1, 2017 at 6:22 PM, 俊杰陈 
 wrote:



Thanks Hongxu,

Here are configurations on my cluster,  most of them are default values.
Which item do you think it may impact?

ABORT_ON_DEFAULT_LIMIT_EXCEEDED: [0]
ABORT_ON_ERROR: [0]
ALLOW_UNSUPPORTED_FORMATS: [0]
APPX_COUNT_DISTINCT: [0]
BATCH_SIZE: [0]
COMPRESSION_CODEC: [NONE]
DEBUG_ACTION: []
DEFAULT_ORDER_BY_LIMIT: [-1]
DISABLE_CACHED_READS: [0]
DISABLE_CODEGEN: [0]
DISABLE_OUTERMOST_TOPN: [0]
DISABLE_ROW_RUNTIME_FILTERING: [0]
DISABLE_STREAMING_PREAGGREGATIONS: [0]
DISABLE_UNSAFE_SPILLS: [0]
ENABLE_EXPR_REWRITES: [1]
EXEC_SINGLE_NODE_ROWS_THRESHOLD: [100]
EXPLAIN_LEVEL: [1]
HBASE_CACHE_BLOCKS: [0]
HBASE_CACHING: [0]
MAX_BLOCK_MGR_MEMORY: [0]
MAX_ERRORS: [100]
MAX_IO_BUFFERS: [0]
MAX_NUM_RUNTIME_FILTERS: [10]
MAX_SCAN_RANGE_LENGTH: [0]
MEM_LIMIT: [0]
MT_DOP: [0]
NUM_NODES: [0]
NUM_SCANNER_THREADS: [0]
OPTIMIZE_PARTITION_KEY_SCANS: [0]
PARQUET_ANNOTATE_STRINGS_UTF8: [0]
PARQUET_FALLBACK_SCHEMA_RESOLUTION: [0]
PARQUET_FILE_SIZE: [0]
PREFETCH_MODE: [1]
QUERY_TIMEOUT_S: [0]
REPLICA_PREFERENCE: [0]
REQUEST_POOL: []
RESERVATION_REQUEST_TIMEOUT: [0]
RM_INITIAL_MEM: [0]
RUNTIME_BLOOM_FILTER_SIZE: [1048576]
RUNTIME_FILTER_MAX_SIZE: [16777216]
RUNTIME_FILTER_MIN_SIZE: [1048576]
RUNTIME_FILTER_MODE: [2]
RUNTIME_FILTER_WAIT_TIME_MS: [0]
S3_SKIP_INSERT_STAGING: [1]
SCAN_NODE_CODEGEN_THRESHOLD: [180]
SCHEDULE_RANDOM_REPLICA: [0]
SCRATCH_LIMIT: [-1]
SEQ_COMPRESSION_MODE: [0]
STRICT_MODE: [0]
SUPPORT_START_OVER: [false]
SYNC_DDL: [0]
V_CPU_CORES: [0]

2017-10-31 15:30 GMT+08:00 Hongxu Ma 
:



Hi JJ
Consider it only takes 3mins on SparkSQL, maybe there are some


mistakes


in


query options.
Try run "set;" in impala-shell and check all query options, e.g:
BATCH_SIZE: [0]
DISABLE_CODEGEN: [0]
RUNTIME_FILTER_MODE: GLOBAL

Just a guess, thanks.

在 27/10/2017 10:25, 俊杰陈 写道:
The profile file is damaged. Here is a screenshot for exec summary
[cid:ii_j999ymep1_15f5ba563aeabb91]
​

2017-10-27 10:04 GMT+08:00 俊杰陈 
>:
Hi Devs

I met a performance issue on big table join. The query takes more


than 3


hours on Impala and only 3 minutes on Spark SQL on the same 5 nodes
cluster. when running query,  the left scanner and exchange node are


very


slow.  Did I miss some key arguments?

you can see profile file in attachment.

[cid:ii_j9998pph2_15f5b92f2cf47020]
​
--
Thanks & Best Regards



--
Thanks & Best Regards


--
Regards,
Hongxu.






--
Thanks & Best Regards









--
Thanks & Best Regards









--
Regards,
Hongxu.