Impala query failed on memory limit

[#1]

Hi,

Any help with this impala error is much appreciated:

Memory limit exceeded

Failed to pin block for fixed-length data needed for sorting. Reducing query concurrency or increasing the memory limit may help this query to complete successfully.

the impala daemon memory is 16G on 20 servers

Memory Limit Exceeded

Query(6f4bb710145f8780:301965b0c0457a87) Limit: Consumption=2.18 GB

  Fragment 6f4bb710145f8780:301965b0c0457a88: Consumption=8.00 KB

    EXCHANGE_NODE (id=20): Consumption=0

    DataStreamRecvr: Consumption=0

  Block Manager: Limit=12.80 GB Consumption=2.17 GB

  Fragment 6f4bb710145f8780:301965b0c0457a9b: Consumption=10.53 MB

    SORT_NODE (id=11): Consumption=0

    HASH_JOIN_NODE (id=10): Consumption=10.52 MB

    EXCHANGE_NODE (id=18): Consumption=0

    DataStreamRecvr: Consumption=0

    EXCHANGE_NODE (id=19): Consumption=0

    DataStreamRecvr: Consumption=0

    DataStreamSender: Consumption=200.00 B

  Fragment 6f4bb710145f8780:301965b0c0457b07: Consumption=138.62 MB

    HASH_JOIN_NODE (id=9): Consumption=138.58 MB

    EXCHANGE_NODE (id=14): Consumption=0

    DataStreamRecvr: Consumption=0

    EXCHANGE_NODE (id=15): Consumption=0

    DataStreamRecvr: Consumption=0

    DataStreamSender: Consumption=19.41 KB

  Fragment 6f4bb710145f8780:301965b0c0457b30: Consumption=56.02 MB

    ANALYTIC_EVAL_NODE (id=4): Consumption=0

    SORT_NODE (id=3): Consumption=48.01 MB

    AGGREGATION_NODE (id=13): Consumption=8.00 MB

    EXCHANGE_NODE (id=12): Consumption=0

WARNING: The following tables are missing relevant table and/or column statistics.

default.analytics_customerinfo_v4p_tbl

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

Read 95.12 MB of data across network that was expected to be local. Block locality metadata for table 'default.analytics_customerinfo_v4p_tbl' may be stale. Consider running "INVALIDATE METADATA `default`.`analytics_customerinfo_v4p_tbl`".

[#2]

I think it was probably unable to get enough memory because of other concurrently executing queries.

This is somewhat counterintuitive, but if you set the mem_limit query option to an amount of memory that the query can reliably obtain, e.g. 2GB, then when it hits that limit spill-to-disk will kick in and the query should be able to complete (albeit slow than running fully in-memory).

We generally recommend that all queries run with a mem_limit set. You can configure a default mem_limit via the "default query options" config or by setting up memory-based admission control. We have some good docs about how to set up memory-based admission control here: https://www.cloudera.com/documentation/enterprise/latest/topics/impala_admission.html#admission_memo...

We're actively working on improving this so that it's more hands-off.