Impala 0.7 sometimes hangs on query

Barry,

Could you give us some details about the query you ran to better diagnose
the problem? Specifically, the query itself, size of the tables, the result
of describe tables.

Thanks,

-- Ishaan

The query is
select * from sales_fact_500m join pa_product on (sales_fact.material_id
= product.material_id) where sales_fact.TXN_DATE = '20100601' and
sales_fact.SOLDTO_CUSTOMER_ID = '0000150538' and product.SUB_CLASS1 =
'Other Products-5055030'

The tables are big. sales_fact has 500 million rows. Product as about 1
million.
I will check to see if I can send the result of describe tables. There are
57 columns all types are either string or double.

Barry,

Could you also send us the query plan, it should be accesible via the
debug webpage, http://<impalad_host>:2500, look under queries.
Additionally, any logs that you can give us right before the hang would
also be useful.

Thanks,

-- Ishaan

I've looked at that url, but it does not show any query plans (will look
more). Here is what is the log however:

Log file created at: 2013/04/17 16:15:43
Running on machine: ip-XX-XXX-XX-XX7
Log line format: [IWEF]mmdd hh:mm:ss.uuuuuu threadid file:line] msg
I0417 16:15:43.248311 22466 daemon.cc:34] impalad version 0.7 RELEASE
(build 62a2db93eb04c36e5becab5fdcaf06b53a839238)
Built on Mon, 15 Apr 2013 08:26:23 PST
I0417 16:15:43.268839 22466 daemon.cc:35] Using hostname:
ip-XX-XXX-XX-XXX.us-west-1.compute.internal
I0417 16:15:43.269245 22466 logging.cc:76] Flags (see also /varz are on
debug webserver):
--dump_ir=false
--module_output=
--be_port=22000
--hostname=ip-xxx.us-west-1.compute.internal
--keytab_file=
--mem_limit=-1
--planservice_host=localhost
--planservice_port=20000
--principal=
--exchg_node_buffer_size_bytes=10485760
--max_row_batches=0
--randomize_splits=false
--num_disks=0
--num_threads_per_disk=1
--read_size=8388608
--enable_webserver=true
--state_store_host=ip-xxx.us-west-1.compute.internal
--state_store_subscriber_port=23000
--use_statestore=true
--nn=ip-xxx.us-west-1.compute.internal
--nn_port=8020
--serialize_batch=false
--status_report_interval=5
--compress_rowbatches=true
--abort_on_config_error=true
--be_service_threads=64
--beeswax_port=21000
--default_query_options=
--fe_service_threads=64
--heap_profile_dir=
--hs2_port=21050
--load_catalog_at_startup=false
--log_mem_usage_interval=0
--log_query_to_file=true
--query_log_size=25
--use_planservice=false
--statestore_subscriber_timeout_seconds=10
--state_store_port=24000
--statestore_max_missed_heartbeats=5
--statestore_num_heartbeat_threads=10
--statestore_suspect_heartbeats=2
--kerberos_reinit_interval=60
--sasl_path=/usr/lib/sasl2:/usr/lib64/sasl2:/usr/local/lib/sasl2:/usr/lib/x86_64-linux-gnu/sasl2
--web_log_bytes=1048576
--log_filename=impalad
--periodic_counter_update_period_ms=500
--rpc_cnxn_attempts=10
--rpc_cnxn_retry_interval_ms=2000
--enable_webserver_doc_root=true
--webserver_doc_root=/opt/cloudera/parcels/IMPALA-0.7-1.p0.306/lib/impala
--webserver_interface=
--webserver_port=25000
--flagfile=/var/run/cloudera-scm-agent/process/179-impala-IMPALAD/impala-conf/impalad_flags
--fromenv=
--tryfromenv=
--undefok=
--tab_completion_columns=80
--tab_completion_word=
--help=false
--helpfull=false
--helpmatch=
--helpon=
--helppackage=false
--helpshort=false
--helpxml=false
--version=false
--alsologtoemail=
--alsologtostderr=false
--drop_log_memory=true
--log_backtrace_at=
--log_dir=/var/log/impalad
--log_link=
--log_prefix=true
--logbuflevel=-1
--logbufsecs=30
--logemaillevel=999
--logmailer=/bin/mail
--logtostderr=false
--max_log_size=200
--minloglevel=0
--stderrthreshold=2
--stop_logging_if_full_disk=false
--symbolize_stacktrace=true
--v=1
--vmodule=
I0417 16:15:43.269922 22466 mem-info.cc:66] Physical Memory: 7.29 GB
I0417 16:15:43.270910 22466 daemon.cc:43] Cpu Info:
Model: Intel(R) Xeon(R) CPU E5507 @ 2.27GHz
Cores: 2
L1 Cache: 32.00 KB
L2 Cache: 256.00 KB
L3 Cache: 2.00 MB
Hardware Supports:
ssse3
sse4_1
sse4_2
I0417 16:15:43.270963 22466 daemon.cc:44] Disk Info:
Num disks 3: xvde, xvdf, xvdg
I0417 16:15:43.270987 22466 daemon.cc:45] Mem Info: 7.29 GB
I0417 16:15:48.217349 22466 impala-server.cc:1740] Default query
options:TQueryOptions {
01: abort_on_error (bool) = false,
02: max_errors (i32) = 0,
03: disable_codegen (bool) = false,
04: batch_size (i32) = 0,
05: num_nodes (i32) = 0,
06: max_scan_range_length (i64) = 0,
07: num_scanner_threads (i32) = 0,
08: max_io_buffers (i32) = 0,
09: allow_unsupported_formats (bool) = false,
10: default_order_by_limit (i64) = -1,
11: debug_action (string) = "",
12: mem_limit (i64) = 0,
13: abort_on_default_limit_exceeded (bool) = false,
}
WARN0417 16:15:48.466000 main org.apache.hadoop.conf.Configuration]
mapred.max.split.size is deprecated. Instead, use
mapreduce.input.fileinputformat.split.maxsize
WARN0417 16:15:48.472000 main org.apache.hadoop.conf.Configuration]
mapred.min.split.size is deprecated. Instead, use
mapreduce.input.fileinputformat.split.minsize
WARN0417 16:15:48.472000 main org.apache.hadoop.conf.Configuration]
mapred.min.split.size.per.rack is deprecated. Instead, use
mapreduce.input.fileinputformat.split.minsize.per.rack
WARN0417 16:15:48.472000 main org.apache.hadoop.conf.Configuration]
mapred.min.split.size.per.node is deprecated. Instead, use
mapreduce.input.fileinputformat.split.minsize.per.node
WARN0417 16:15:48.472000 main org.apache.hadoop.conf.Configuration]
mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
WARN0417 16:15:48.472000 main org.apache.hadoop.conf.Configuration]
mapred.reduce.tasks.speculative.execution is deprecated. Instead, use
mapreduce.reduce.speculative
WARN0417 16:15:48.971000 main org.apache.hadoop.conf.Configuration]
org.apache.hadoop.hive.conf.LoopingByteArrayInputStream@361cb7a1:an attempt
to override final parameter:
mapreduce.job.end-notification.max.retry.interval; Ignoring.
WARN0417 16:15:48.997000 main org.apache.hadoop.conf.Configuration]
org.apache.hadoop.hive.conf.LoopingByteArrayInputStream@361cb7a1:an attempt
to override final parameter: mapreduce.job.end-notification.max.attempts;
Ignoring.
WARN0417 16:15:49.036000 main org.apache.hadoop.hive.conf.HiveConf]
DEPRECATED: Configuration property hive.metastore.local no longer has any
effect. Make sure to provide a valid value for hive.metastore.uris if you
are connecting to a remote metastore.
INFO0417 16:15:49.194000 main hive.metastore] Trying to connect to
metastore with URI thrift://ip-xxx.us-west-1.compute.internal:9083
:
INFO0417 16:15:53.417000 main hive.metastore] Waiting 1 seconds before next
connection attempt.
INFO0417 16:15:54.417000 main hive.metastore] Connected to metastore.
I0417 16:15:56.271982 22466 impala-server.cc:2003] Impala Beeswax Service
listening on 21000
I0417 16:15:56.272064 22466 impala-server.cc:2014] Impala HiveServer2
Service listening on 21050
I0417 16:15:56.272091 22466 impala-server.cc:2022] ImpalaInternalService
listening on 22000
I0417 16:15:56.278708 22466 thrift-server.cc:365] ThriftServer 'backend'
started on port: 22000
I0417 16:15:56.278766 22466 exec-env.cc:143] Starting global services
I0417 16:15:56.278870 22466 exec-env.cc:164] Using global memory limit:
0.00
I0417 16:15:56.278923 22466 webserver.cc:118] Starting webserver on all
interfaces, port 25000
I0417 16:15:56.278959 22466 webserver.cc:128] Document root:
/opt/cloudera/parcels/IMPALA-0.7-1.p0.306/lib/impala
I0417 16:15:56.291616 22466 webserver.cc:167] Webserver started
I0417 16:15:56.291733 22466 simple-scheduler.cc:98] Starting simple
scheduler
I0417 16:15:56.291769 22466 state-store-subscriber.cc:124] Starting
subscriber
I0417 16:15:56.292191 22466 thrift-server.cc:365] ThriftServer
'StateStoreSubscriber' started on port: 23000
I0417 16:15:56.292247 22466 client-cache.cc:98] CreateClient(): adding new
client for ip-xxx.us-west-1.compute.internal:24000
I0417 16:15:56.297287 22466 thrift-server.cc:365] ThriftServer
'beeswax-frontend' started on port: 21000
I0417 16:15:56.301013 22466 thrift-server.cc:365] ThriftServer
'hiveServer2-frontend' started on port: 21050
I0417 16:15:56.301084 22466 impalad-main.cc:101] Impala has started.
I0417 16:16:02.226348 22668 impala-server.cc:1591] ExecPlanFragment()
instance_id=9c50dcfe27524b5f:bd91afc3a6f5a318
coord=ip-xxx.us-west-1.compute.internal:22000 backend#=0
I0417 16:16:02.226532 22668 plan-fragment-executor.cc:83] Prepare():
query_id=9c50dcfe27524b5f:bd91afc3a6f5a316
instance_id=9c50dcfe27524b5f:bd91afc3a6f5a318
I0417 16:16:02.469959 22668 plan-fragment-executor.cc:114] descriptor table
for fragment=9c50dcfe27524b5f:bd91afc3a6f5a318
tuples:
Tuple(id=0 size=664 slots=[Slot(id=0 type=STRING col=1 offset=264
null=(offset=4 mask=1)), Slot(id=2 type=STRING col=0 offset=280
null=(offset=4 mask=2)), Slot(id=3 type=STRING col=2 offset=296
null=(offset=4 mask=4)), Slot(id=4 type=STRING col=3 offset=312
null=(offset=4 mask=8)), Slot(id=5 type=STRING col=4 offset=328
null=(offset=4 mask=10)), Slot(id=6 type=STRING col=5 offset=344
null=(offset=4 mask=20)), Slot(id=7 type=STRING col=6 offset=360
null=(offset=4 mask=40)), Slot(id=8 type=STRING col=7 offset=376
null=(offset=4 mask=80)), Slot(id=9 type=STRING col=8 offset=392
null=(offset=5 mask=1)), Slot(id=10 type=STRING col=9 offset=408
null=(offset=5 mask=2)), Slot(id=11 type=STRING col=10 offset=424
null=(offset=5 mask=4)), Slot(id=12 type=STRING col=11 offset=440
null=(offset=5 mask=8)), Slot(id=13 type=STRING col=12 offset=456
null=(offset=5 mask=10)), Slot(id=14 type=STRING col=13 offset=472
null=(offset=5 mask=20)), Slot(id=15 type=STRING col=14 offset=488
null=(offset=5 mask=40)), Slot(id=16 type=DOUBLE col=15 offset=8
null=(offset=0 mask=1)), Slot(id=17 type=DOUBLE col=16 offset=16
null=(offset=0 mask=2)), Slot(id=18 type=DOUBLE col=17 offset=24
null=(offset=0 mask=4)), Slot(id=19 type=DOUBLE col=18 offset=32
null=(offset=0 mask=8)), Slot(id=20 type=DOUBLE col=19 offset=40
null=(offset=0 mask=10)), Slot(id=21 type=DOUBLE col=20 offset=48
null=(offset=0 mask=20)), Slot(id=22 type=DOUBLE col=21 offset=56
null=(offset=0 mask=40)), Slot(id=23 type=DOUBLE col=22 offset=64
null=(offset=0 mask=80)), Slot(id=24 type=DOUBLE col=23 offset=72
null=(offset=1 mask=1)), Slot(id=25 type=DOUBLE col=24 offset=80
null=(offset=1 mask=2)), Slot(id=26 type=DOUBLE col=25 offset=88
null=(offset=1 mask=4)), Slot(id=27 type=DOUBLE col=26 offset=96
null=(offset=1 mask=8)), Slot(id=28 type=DOUBLE col=27 offset=104
null=(offset=1 mask=10)), Slot(id=29 type=DOUBLE col=28 offset=112
null=(offset=1 mask=20)), Slot(id=30 type=DOUBLE col=29 offset=120
null=(offset=1 mask=40)), Slot(id=31 type=DOUBLE col=30 offset=128
null=(offset=1 mask=80)), Slot(id=32 type=DOUBLE col=31 offset=136
null=(offset=2 mask=1)), Slot(id=33 type=DOUBLE col=32 offset=144
null=(offset=2 mask=2)), Slot(id=34 type=DOUBLE col=33 offset=152
null=(offset=2 mask=4)), Slot(id=35 type=DOUBLE col=34 offset=160
null=(offset=2 mask=8)), Slot(id=36 type=DOUBLE col=35 offset=168
null=(offset=2 mask=10)), Slot(id=37 type=DOUBLE col=36 offset=176
null=(offset=2 mask=20)), Slot(id=38 type=DOUBLE col=37 offset=184
null=(offset=2 mask=40)), Slot(id=39 type=DOUBLE col=38 offset=192
null=(offset=2 mask=80)), Slot(id=40 type=DOUBLE col=39 offset=200
null=(offset=3 mask=1)), Slot(id=41 type=DOUBLE col=40 offset=208
null=(offset=3 mask=2)), Slot(id=42 type=DOUBLE col=41 offset=216
null=(offset=3 mask=4)), Slot(id=43 type=DOUBLE col=42 offset=224
null=(offset=3 mask=8)), Slot(id=44 type=DOUBLE col=43 offset=232
null=(offset=3 mask=10)), Slot(id=45 type=DOUBLE col=44 offset=240
null=(offset=3 mask=20)), Slot(id=46 type=STRING col=45 offset=504
null=(offset=5 mask=80)), Slot(id=47 type=STRING col=46 offset=520
null=(offset=6 mask=1)), Slot(id=48 type=STRING col=47 offset=536
null=(offset=6 mask=2)), Slot(id=49 type=STRING col=48 offset=552
null=(offset=6 mask=4)), Slot(id=50 type=STRING col=49 offset=568
null=(offset=6 mask=8)), Slot(id=51 type=STRING col=50 offset=584
null=(offset=6 mask=10)), Slot(id=52 type=STRING col=51 offset=600
null=(offset=6 mask=20)), Slot(id=53 type=STRING col=52 offset=616
null=(offset=6 mask=40)), Slot(id=54 type=DOUBLE col=53 offset=248
null=(offset=3 mask=40)), Slot(id=55 type=STRING col=54 offset=632
null=(offset=6 mask=80)), Slot(id=56 type=STRING col=55 offset=648
null=(offset=7 mask=1)), Slot(id=57 type=DOUBLE col=56 offset=256
null=(offset=3 mask=80))])
Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])
I0417 16:16:02.537744 22668 exchange-node.cc:49] Exch id=4
input_desc=Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])

output_desc=Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])
I0417 16:16:03.936625 22670 plan-fragment-executor.cc:212] Open():
instance_id=9c50dcfe27524b5f:bd91afc3a6f5a318
I0417 16:16:03.937850 22668 impala-server.cc:1620] CancelPlanFragment():
instance_id=9c50dcfe27524b5f:bd91afc3a6f5a318
I0417 16:16:03.937911 22668 plan-fragment-executor.cc:400] Cancel():
instance_id=9c50dcfe27524b5f:bd91afc3a6f5a318
I0417 16:16:03.937954 22668 data-stream-mgr.cc:280] cancelling all streams
for fragment=9c50dcfe27524b5f:bd91afc3a6f5a318
I0417 16:16:03.937990 22668 data-stream-mgr.cc:168] cancelled stream:
fragment_id=9c50dcfe27524b5f:bd91afc3a6f5a318 node_id=4
I0417 16:16:03.993186 22670 hash-join-node.cc:201] HashJoinNode(node_id=2)
using llvm codegend function for building hash table.
I0417 16:16:04.016794 22670 hash-join-node.cc:214] HashJoinNode(node_id=2)
using llvm codegend function for probing hash table.
I0417 16:16:04.018528 22670 client-cache.cc:98] CreateClient(): adding new
client for ip-xxx.us-west-1.compute.internal:22000
I0417 16:16:04.438062 22670 data-stream-mgr.cc:257] DeregisterRecvr():
fragment_id=9c50dcfe27524b5f:bd91afc3a6f5a318, node=4
I0417 16:16:05.566387 22668 impala-server.cc:1591] ExecPlanFragment()
instance_id=f08071080394c90:a03c5450f60188dd
coord=ip-xxx.us-west-1.compute.internal:22000 backend#=0
I0417 16:16:05.566553 22668 plan-fragment-executor.cc:83] Prepare():
query_id=f08071080394c90:a03c5450f60188db
instance_id=f08071080394c90:a03c5450f60188dd
I0417 16:16:05.574394 22668 plan-fragment-executor.cc:114] descriptor table
for fragment=f08071080394c90:a03c5450f60188dd
tuples:
Tuple(id=0 size=664 slots=[Slot(id=0 type=STRING col=1 offset=264
null=(offset=4 mask=1)), Slot(id=2 type=STRING col=0 offset=280
null=(offset=4 mask=2)), Slot(id=3 type=STRING col=2 offset=296
null=(offset=4 mask=4)), Slot(id=4 type=STRING col=3 offset=312
null=(offset=4 mask=8)), Slot(id=5 type=STRING col=4 offset=328
null=(offset=4 mask=10)), Slot(id=6 type=STRING col=5 offset=344
null=(offset=4 mask=20)), Slot(id=7 type=STRING col=6 offset=360
null=(offset=4 mask=40)), Slot(id=8 type=STRING col=7 offset=376
null=(offset=4 mask=80)), Slot(id=9 type=STRING col=8 offset=392
null=(offset=5 mask=1)), Slot(id=10 type=STRING col=9 offset=408
null=(offset=5 mask=2)), Slot(id=11 type=STRING col=10 offset=424
null=(offset=5 mask=4)), Slot(id=12 type=STRING col=11 offset=440
null=(offset=5 mask=8)), Slot(id=13 type=STRING col=12 offset=456
null=(offset=5 mask=10)), Slot(id=14 type=STRING col=13 offset=472
null=(offset=5 mask=20)), Slot(id=15 type=STRING col=14 offset=488
null=(offset=5 mask=40)), Slot(id=16 type=DOUBLE col=15 offset=8
null=(offset=0 mask=1)), Slot(id=17 type=DOUBLE col=16 offset=16
null=(offset=0 mask=2)), Slot(id=18 type=DOUBLE col=17 offset=24
null=(offset=0 mask=4)), Slot(id=19 type=DOUBLE col=18 offset=32
null=(offset=0 mask=8)), Slot(id=20 type=DOUBLE col=19 offset=40
null=(offset=0 mask=10)), Slot(id=21 type=DOUBLE col=20 offset=48
null=(offset=0 mask=20)), Slot(id=22 type=DOUBLE col=21 offset=56
null=(offset=0 mask=40)), Slot(id=23 type=DOUBLE col=22 offset=64
null=(offset=0 mask=80)), Slot(id=24 type=DOUBLE col=23 offset=72
null=(offset=1 mask=1)), Slot(id=25 type=DOUBLE col=24 offset=80
null=(offset=1 mask=2)), Slot(id=26 type=DOUBLE col=25 offset=88
null=(offset=1 mask=4)), Slot(id=27 type=DOUBLE col=26 offset=96
null=(offset=1 mask=8)), Slot(id=28 type=DOUBLE col=27 offset=104
null=(offset=1 mask=10)), Slot(id=29 type=DOUBLE col=28 offset=112
null=(offset=1 mask=20)), Slot(id=30 type=DOUBLE col=29 offset=120
null=(offset=1 mask=40)), Slot(id=31 type=DOUBLE col=30 offset=128
null=(offset=1 mask=80)), Slot(id=32 type=DOUBLE col=31 offset=136
null=(offset=2 mask=1)), Slot(id=33 type=DOUBLE col=32 offset=144
null=(offset=2 mask=2)), Slot(id=34 type=DOUBLE col=33 offset=152
null=(offset=2 mask=4)), Slot(id=35 type=DOUBLE col=34 offset=160
null=(offset=2 mask=8)), Slot(id=36 type=DOUBLE col=35 offset=168
null=(offset=2 mask=10)), Slot(id=37 type=DOUBLE col=36 offset=176
null=(offset=2 mask=20)), Slot(id=38 type=DOUBLE col=37 offset=184
null=(offset=2 mask=40)), Slot(id=39 type=DOUBLE col=38 offset=192
null=(offset=2 mask=80)), Slot(id=40 type=DOUBLE col=39 offset=200
null=(offset=3 mask=1)), Slot(id=41 type=DOUBLE col=40 offset=208
null=(offset=3 mask=2)), Slot(id=42 type=DOUBLE col=41 offset=216
null=(offset=3 mask=4)), Slot(id=43 type=DOUBLE col=42 offset=224
null=(offset=3 mask=8)), Slot(id=44 type=DOUBLE col=43 offset=232
null=(offset=3 mask=10)), Slot(id=45 type=DOUBLE col=44 offset=240
null=(offset=3 mask=20)), Slot(id=46 type=STRING col=45 offset=504
null=(offset=5 mask=80)), Slot(id=47 type=STRING col=46 offset=520
null=(offset=6 mask=1)), Slot(id=48 type=STRING col=47 offset=536
null=(offset=6 mask=2)), Slot(id=49 type=STRING col=48 offset=552
null=(offset=6 mask=4)), Slot(id=50 type=STRING col=49 offset=568
null=(offset=6 mask=8)), Slot(id=51 type=STRING col=50 offset=584
null=(offset=6 mask=10)), Slot(id=52 type=STRING col=51 offset=600
null=(offset=6 mask=20)), Slot(id=53 type=STRING col=52 offset=616
null=(offset=6 mask=40)), Slot(id=54 type=DOUBLE col=53 offset=248
null=(offset=3 mask=40)), Slot(id=55 type=STRING col=54 offset=632
null=(offset=6 mask=80)), Slot(id=56 type=STRING col=55 offset=648
null=(offset=7 mask=1)), Slot(id=57 type=DOUBLE col=56 offset=256
null=(offset=3 mask=80))])
Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])
I0417 16:16:05.626904 22668 exchange-node.cc:49] Exch id=4
input_desc=Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])

output_desc=Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])
I0417 16:16:07.046962 22678 plan-fragment-executor.cc:212] Open():
instance_id=f08071080394c90:a03c5450f60188dd
I0417 16:16:07.055644 22678 hash-join-node.cc:201] HashJoinNode(node_id=2)
using llvm codegend function for building hash table.
I0417 16:16:07.079422 22678 hash-join-node.cc:214] HashJoinNode(node_id=2)
using llvm codegend function for probing hash table.
I0417 16:16:07.122617 22668 impala-server.cc:1591] ExecPlanFragment()
instance_id=f08071080394c90:a03c5450f60188e5
coord=ip-xxx.us-west-1.compute.internal:22000 backend#=8
I0417 16:16:07.122795 22668 plan-fragment-executor.cc:83] Prepare():
query_id=f08071080394c90:a03c5450f60188db
instance_id=f08071080394c90:a03c5450f60188e5
I0417 16:16:07.130527 22668 plan-fragment-executor.cc:114] descriptor table
for fragment=f08071080394c90:a03c5450f60188e5
tuples:
Tuple(id=0 size=664 slots=[Slot(id=0 type=STRING col=1 offset=264
null=(offset=4 mask=1)), Slot(id=2 type=STRING col=0 offset=280
null=(offset=4 mask=2)), Slot(id=3 type=STRING col=2 offset=296
null=(offset=4 mask=4)), Slot(id=4 type=STRING col=3 offset=312
null=(offset=4 mask=8)), Slot(id=5 type=STRING col=4 offset=328
null=(offset=4 mask=10)), Slot(id=6 type=STRING col=5 offset=344
null=(offset=4 mask=20)), Slot(id=7 type=STRING col=6 offset=360
null=(offset=4 mask=40)), Slot(id=8 type=STRING col=7 offset=376
null=(offset=4 mask=80)), Slot(id=9 type=STRING col=8 offset=392
null=(offset=5 mask=1)), Slot(id=10 type=STRING col=9 offset=408
null=(offset=5 mask=2)), Slot(id=11 type=STRING col=10 offset=424
null=(offset=5 mask=4)), Slot(id=12 type=STRING col=11 offset=440
null=(offset=5 mask=8)), Slot(id=13 type=STRING col=12 offset=456
null=(offset=5 mask=10)), Slot(id=14 type=STRING col=13 offset=472
null=(offset=5 mask=20)), Slot(id=15 type=STRING col=14 offset=488
null=(offset=5 mask=40)), Slot(id=16 type=DOUBLE col=15 offset=8
null=(offset=0 mask=1)), Slot(id=17 type=DOUBLE col=16 offset=16
null=(offset=0 mask=2)), Slot(id=18 type=DOUBLE col=17 offset=24
null=(offset=0 mask=4)), Slot(id=19 type=DOUBLE col=18 offset=32
null=(offset=0 mask=8)), Slot(id=20 type=DOUBLE col=19 offset=40
null=(offset=0 mask=10)), Slot(id=21 type=DOUBLE col=20 offset=48
null=(offset=0 mask=20)), Slot(id=22 type=DOUBLE col=21 offset=56
null=(offset=0 mask=40)), Slot(id=23 type=DOUBLE col=22 offset=64
null=(offset=0 mask=80)), Slot(id=24 type=DOUBLE col=23 offset=72
null=(offset=1 mask=1)), Slot(id=25 type=DOUBLE col=24 offset=80
null=(offset=1 mask=2)), Slot(id=26 type=DOUBLE col=25 offset=88
null=(offset=1 mask=4)), Slot(id=27 type=DOUBLE col=26 offset=96
null=(offset=1 mask=8)), Slot(id=28 type=DOUBLE col=27 offset=104
null=(offset=1 mask=10)), Slot(id=29 type=DOUBLE col=28 offset=112
null=(offset=1 mask=20)), Slot(id=30 type=DOUBLE col=29 offset=120
null=(offset=1 mask=40)), Slot(id=31 type=DOUBLE col=30 offset=128
null=(offset=1 mask=80)), Slot(id=32 type=DOUBLE col=31 offset=136
null=(offset=2 mask=1)), Slot(id=33 type=DOUBLE col=32 offset=144
null=(offset=2 mask=2)), Slot(id=34 type=DOUBLE col=33 offset=152
null=(offset=2 mask=4)), Slot(id=35 type=DOUBLE col=34 offset=160
null=(offset=2 mask=8)), Slot(id=36 type=DOUBLE col=35 offset=168
null=(offset=2 mask=10)), Slot(id=37 type=DOUBLE col=36 offset=176
null=(offset=2 mask=20)), Slot(id=38 type=DOUBLE col=37 offset=184
null=(offset=2 mask=40)), Slot(id=39 type=DOUBLE col=38 offset=192
null=(offset=2 mask=80)), Slot(id=40 type=DOUBLE col=39 offset=200
null=(offset=3 mask=1)), Slot(id=41 type=DOUBLE col=40 offset=208
null=(offset=3 mask=2)), Slot(id=42 type=DOUBLE col=41 offset=216
null=(offset=3 mask=4)), Slot(id=43 type=DOUBLE col=42 offset=224
null=(offset=3 mask=8)), Slot(id=44 type=DOUBLE col=43 offset=232
null=(offset=3 mask=10)), Slot(id=45 type=DOUBLE col=44 offset=240
null=(offset=3 mask=20)), Slot(id=46 type=STRING col=45 offset=504
null=(offset=5 mask=80)), Slot(id=47 type=STRING col=46 offset=520
null=(offset=6 mask=1)), Slot(id=48 type=STRING col=47 offset=536
null=(offset=6 mask=2)), Slot(id=49 type=STRING col=48 offset=552
null=(offset=6 mask=4)), Slot(id=50 type=STRING col=49 offset=568
null=(offset=6 mask=8)), Slot(id=51 type=STRING col=50 offset=584
null=(offset=6 mask=10)), Slot(id=52 type=STRING col=51 offset=600
null=(offset=6 mask=20)), Slot(id=53 type=STRING col=52 offset=616
null=(offset=6 mask=40)), Slot(id=54 type=DOUBLE col=53 offset=248
null=(offset=3 mask=40)), Slot(id=55 type=STRING col=54 offset=632
null=(offset=6 mask=80)), Slot(id=56 type=STRING col=55 offset=648
null=(offset=7 mask=1)), Slot(id=57 type=DOUBLE col=56 offset=256
null=(offset=3 mask=80))])
Tuple(id=1 size=248 slots=[Slot(id=1 type=STRING col=0 offset=8
null=(offset=0 mask=1)), Slot(id=58 type=STRING col=1 offset=24
null=(offset=0 mask=2)), Slot(id=59 type=STRING col=2 offset=40
null=(offset=0 mask=4)), Slot(id=60 type=STRING col=3 offset=56
null=(offset=0 mask=8)), Slot(id=61 type=STRING col=4 offset=72
null=(offset=0 mask=10)), Slot(id=62 type=STRING col=5 offset=88
null=(offset=0 mask=20)), Slot(id=63 type=STRING col=6 offset=104
null=(offset=0 mask=40)), Slot(id=64 type=STRING col=7 offset=120
null=(offset=0 mask=80)), Slot(id=65 type=STRING col=8 offset=136
null=(offset=1 mask=1)), Slot(id=66 type=STRING col=9 offset=152
null=(offset=1 mask=2)), Slot(id=67 type=STRING col=10 offset=168
null=(offset=1 mask=4)), Slot(id=68 type=STRING col=11 offset=184
null=(offset=1 mask=8)), Slot(id=69 type=STRING col=12 offset=200
null=(offset=1 mask=10)), Slot(id=70 type=STRING col=13 offset=216
null=(offset=1 mask=20)), Slot(id=71 type=STRING col=14 offset=232
null=(offset=1 mask=40))])
I0417 16:16:07.215806 22685 plan-fragment-executor.cc:212] Open():
instance_id=f08071080394c90:a03c5450f60188e5
:
I0417 16:16:18.962846 22699 thrift-util.cc:85] TSocket::read() recv()
Connection reset by peer
I0417 16:16:19.328047 22699 thrift-util.cc:85] TThreadedServer client died:
ECONNRESET
I0417 16:16:29.123160 22693 thrift-util.cc:85] TSocket::read() recv()
Connection reset by peer
I0417 16:16:29.123340 22693 thrift-util.cc:85] TThreadedServer client died:
ECONNRESET
I0417 16:16:36.298084 22536 state-store-subscriber.cc:153]
ip-xxx.us-west-1.compute.internal:22000: Connection with state-store lost,
entering recovery mode
I0417 16:16:36.298210 22536 state-store-subscriber.cc:156] Trying to
register...
I0417 16:16:36.299015 22536 state-store-subscriber.cc:159] Reconnected to
state-store. Exiting recovery mode
I0417 16:16:46.299271 22536 state-store-subscriber.cc:153]
ip-xxx.us-west-1.compute.internal:22000: Connection with state-store lost,
entering recovery mode

Actually, I just tried it again and was able to get the query profile this
time (before it crashed). Here it is:



Impala
/
/backends
/catalog
/logs
/memz
/metrics
/queries
/sessions
/varz

Query (id=86fa2b6cee904627:a6f6892849e42093):
- PlanningTime: 5s859ms
Summary:
Default Db: default
Impala Version: impalad version 0.7 RELEASE (build
62a2db93eb04c36e5becab5fdcaf06b53a839238)
Built on Mon, 15 Apr 2013 08:26:23 PST
Plan:
----------------
Plan Fragment 0
UNPARTITIONED
EXCHANGE (4)
TUPLE IDS: 0 1

Plan Fragment 1
RANDOM
STREAM DATA SINK
EXCHANGE ID: 4
UNPARTITIONED

HASH JOIN
JOIN OP: INNER JOIN
HASH PREDICATES:
pa_sales_fact_500m.material_id = pa_product.material_id
TUPLE IDS: 0 1
SCAN HDFS table=default.pa_sales_fact_500m #partitions=1
size=166.86GB (0)
PREDICATES: pa_sales_fact_500m.TXN_DATE = '20100601',
pa_sales_fact_500m.SOLDTO_CUSTOMER_ID = '0000150538'
TUPLE IDS: 0
EXCHANGE (3)
TUPLE IDS: 1

Plan Fragment 2
RANDOM
STREAM DATA SINK
EXCHANGE ID: 3
UNPARTITIONED

SCAN HDFS table=default.pa_product #partitions=1 size=247.18MB (1) compact
PREDICATES: pa_product.SUB_CLASS1 = 'Other Products-5055030'
TUPLE IDS: 1
----------------
Query State: CREATED
Query Type: QUERY
Sql Statement: select * from pa_sales_fact_500m join pa_product on
(pa_sales_fact_500m.material_id = pa_product.material_id) where
pa_sales_fact_500m.TXN_DATE = '20100601' and
pa_sales_fact_500m.SOLDTO_CUSTOMER_ID = '0000150538' and
pa_product.SUB_CLASS1 = 'Other Products-5055030'
Start Time: 2013-04-17 16:45:14
User: pros_user
Query 86fa2b6cee904627:a6f6892849e42093:(2s177ms 0.00%)
Aggregate Profile:
- FinalizationTimer: 0ns
Coordinator Fragment:(5.737us 0.00%)
- RowsProduced: 0
CodeGen:
- CodegenTime: 0ns
- CompileTime: 80.268ms
- LoadTime: 7.637ms
- ModuleFileSize: 70.02 KB
EXCHANGE_NODE (id=4):(1.211us 0.00%)
- BytesReceived: 0.00
- ConvertRowBatchTime: 1.394us
- DataArrivalWaitTime: 0ns
- DeserializeRowBatchTimer: 0ns
- FirstBatchArrivalWaitTime: 0ns
- MemoryUsed: 0.00
- RowsReturned: 0
- RowsReturnedRate: 0
- SendersBlockedTotalTimer: 0ns
- SendersBlockedWallTimer: 0ns
Averaged Fragment 2:
split sizes: min: 5.15 MB, max: 128.00 MB, avg: 61.80 MB, stddev:
52.04 MB
Averaged Fragment 1:
split sizes: min: 18.44 GB, max: 18.65 GB, avg: 18.54 GB, stddev:
67.42 MB
Fragment 1:
Fragment 2:

And here is the result of describe table (which you asked for earlier)

Query: describe pa_sales_fact

Query finished, fetching results ...

+---------------------------+--------+---------+
+---------------------------+--------+---------+

Returned 57 row(s) in 7.82s

Just wanted to confirm some details on this issue:

This query runs to competition:
select *
from sales_fact_500m
join product on (sales_fact_500m.material_id = product.material_id)
order by sales_fact_500m.material_id asc
limit 100

However this one does not:
select *
from pa_sales_fact_500m
join pa_product on (pa_sales_fact_500m.material_id =
pa_product.material_id)
where pa_sales_fact_500m.TXN_DATE = '20100601'
and pa_sales_fact_500m.SOLDTO_CUSTOMER_ID = '0000150538'
and pa_product.SUB_CLASS1 = 'Other Products-5055030'

Questions:
- I see that in one case the tables are prefaced by "pa_", are these the
same tables and same data sets in both queries just different names?
- Each node seems to have 8GB of RAM, how many data nodes/impalad nodes are
there in total?
- Can you change "select *" to "select count(*)" and see if that query runs
to completion and get the number of rows that would be returned?

I ran the select count(*) version a couple more times and it says
Error!
Cancelled
I think this is because we recently set the mem_limit option to 7000000
(7G) so it will fail instead of crashing the daemon.
Even the old select * version gives this Cancelled error, so that convinces
me it is because of the mem_limit change.
Also tried it from impala shell. In that case it says: Query aborted,
unable to fetch data

We are currently trying the query on a quadruple XL cluster to see if it
will work with the additional data.

Barry, could you also post the 'describe' output for the product table?

Marcel

Here it is:
[localhost:21000] > describe pa_product;
Query: describe pa_product
Query finished, fetching results ...
+--------------------+--------+---------+
+--------------------+--------+---------+
Returned 15 row(s) in 0.04s

By the way, the query ran successfully when we tried it on a cluster with 3
Quadruple Extra Large nodes with 64G of memory each.

You said there's about 1M rows in that table, correct? Do you have an
idea how long each of the string values is, on average, per column?

There are exactly 500 million rows in the sales_fact table and about 1
million rows in the product table. None of the string valued columns are
excessively long, but there may be some fields that are on the order of
20-80 characters.

I verified that all of the columns in the fact and product table appear to
be pretty well behaved. Most string valued columns are of length 3-30. The
longest column has values in the range 20-70. In the past we have looked at
this data using Tableau and there did not seem to be any unusual or
problematic values.

In my initial post I said that it hangs, but it seems that most of the time
it crashes for this particular query. We have seen hangs too, but I'm not
sure it was this specific query that caused the hang. This query does seem
to always produce a failure. As far as we can tell it looks like the
failure is caused by one or more nodes running out of memory. Here is an
image of the memory behavior on one of the nodes at the time of the query
failure.

<https://lh4.googleusercontent.com/-kasPzoJMxtQ/UXA6wNIPpKI/AAAAAAAAAEo/hu1aN-W55ko/s1600/memory_ganglie.PNG>
Its interesting that another similar query like

select * from sales_fact_500m join product on
(sales_fact_500m.material_id = product.material_id) order by
sales_fact_500m.material_id asc limit 100

woks fine and does not fail.
-Barry