Hi
I tried to run TPC-DS query51 using Impala. It showed poor performance in some
exchange and aggregate (highlighted by yellow color) with very low CPU
utilization.
Suggested by
https://www.cloudera.com/documentation/enterprise/latest/topics/impala_mt_dop.html,
I tried MT_DOP but failed to the following error. So I am wondering whether
there exists other tuning options here. Thank you in advance.
ERROR: NotImplementedException: MT_DOP not supported for plans with
base table joins or table sinks.
+--------------------------+--------+----------+----------+---------+------------+-----------+---------------+----------------------------------------------------------------------------+
| Operator | #Hosts | Avg Time | Max Time | #Rows | Est.
#Rows | Peak Mem | Est. Peak Mem | Detail
|
+--------------------------+--------+----------+----------+---------+------------+-----------+---------------+----------------------------------------------------------------------------+
| 123:MERGING-EXCHANGE | 1 | None | None | 100 | 100
| 0 B | 0 B | UNPARTITIONED
|
| 64:TOP-N | 6 | 2.11ms | 3.36ms | 600 | 100
| 20.00 KB | 5.47 KB |
|
| 122:AGGREGATE | 6 | 16.62s | 18.25s | 2.68K | 209.10M
| 960.13 MB | 128.00 MB | FINALIZE
|
| 121:EXCHANGE | 6 | None | None | 65.88M | 209.10M
| 0 B | 0 B |
HASH(v1.item_sk,v1.d_date,v1.web_sales,v1.store_sales) |
| 63:AGGREGATE | 6 | 229.25s | 294.95s | 65.88M | 209.10M
| 992.20 MB | 128.00 MB | STREAMING
|
| 62:HASH JOIN | 6 | 159.28s | 165.79s | 10.57B | 209.10M
| 1.73 GB | 3.07 GB | INNER JOIN, PARTITIONED
|
| |--120:EXCHANGE | 6 | 816.64s | 817.04s | 65.88M | 209.10M
| 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN
v1.item_sk ELSE v1.item_sk END) |
| | 61:ANALYTIC | 6 | 17.03s | 22.40s | 65.88M | 209.10M
| 14.05 MB | 4.00 MB |
|
| | 60:SORT | 6 | 43.51s | 46.74s | 65.88M | 209.10M
| 1.04 GB | 126.00 MB |
|
| | 118:EXCHANGE | 6 | 712.27s | 716.06s | 65.88M | 209.10M
| 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN
v1.item_sk ELSE v1.item_sk END) |
| | 59:HASH JOIN | 6 | 4.14s | 4.36s | 65.88M | 209.10M
| 800.14 MB | 295.73 MB | FULL OUTER JOIN, PARTITIONED
|
| | |--117:EXCHANGE | 6 | 647.88s | 648.31s | 65.01M | 43.37M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date)
|
| | | 115:AGGREGATE | 6 | 11.76s | 13.14s | 65.01M | 43.37M
| 800.11 MB | 128.00 MB | FINALIZE
|
| | | 114:EXCHANGE | 6 | 625.11s | 627.27s | 65.01M | 43.37M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumws)
|
| | | 44:AGGREGATE | 6 | 209.38s | 276.41s | 65.01M | 43.37M
| 800.13 MB | 128.00 MB | STREAMING
|
| | | 43:HASH JOIN | 6 | 171.78s | 206.50s | 10.28B | 43.37M
| 1.07 GB | 295.73 MB | INNER JOIN, PARTITIONED
|
| | | |--113:EXCHANGE | 6 | 133.14s | 133.42s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | | | 42:ANALYTIC | 6 | 19.38s | 20.29s | 65.01M | 43.37M
| 14.05 MB | 4.00 MB |
|
| | | | 41:SORT | 6 | 37.96s | 39.38s | 65.01M | 43.37M
| 450.07 MB | 36.00 MB |
|
| | | | 111:EXCHANGE | 6 | 53.40s | 53.93s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | | | 110:AGGREGATE | 6 | 10.67s | 11.30s | 65.01M | 43.37M
| 704.10 MB | 128.00 MB | FINALIZE
|
| | | | 109:EXCHANGE | 6 | 40.79s | 41.37s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk,d_date)
|
| | | | 40:AGGREGATE | 6 | 25.50s | 26.76s | 65.01M | 43.37M
| 737.80 MB | 128.00 MB | STREAMING
|
| | | | 39:HASH JOIN | 6 | 4.94s | 5.02s | 433.69M | 43.37M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | | | |--108:EXCHANGE | 6 | 413.71ms | 519.31ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | | | 38:SCAN HDFS | 1 | 894.97ms | 894.97ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | | | 37:SCAN HDFS | 6 | 1.88s | 2.71s | 433.69M | 43.37M
| 33.86 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales
|
| | | 112:EXCHANGE | 6 | 185.91s | 251.98s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | | 36:ANALYTIC | 6 | 31.84s | 33.31s | 65.01M | 43.37M
| 14.05 MB | 4.00 MB |
|
| | | 35:SORT | 6 | 38.48s | 39.36s | 65.01M | 43.37M
| 450.07 MB | 36.00 MB |
|
| | | 107:EXCHANGE | 6 | 52.12s | 52.80s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | | 106:AGGREGATE | 6 | 10.60s | 11.74s | 65.01M | 43.37M
| 704.10 MB | 128.00 MB | FINALIZE
|
| | | 105:EXCHANGE | 6 | 39.60s | 40.54s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk,d_date)
|
| | | 34:AGGREGATE | 6 | 25.11s | 26.00s | 65.01M | 43.37M
| 737.80 MB | 128.00 MB | STREAMING
|
| | | 33:HASH JOIN | 6 | 4.93s | 4.99s | 433.69M | 43.37M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | | |--104:EXCHANGE | 6 | 406.26ms | 490.42ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | | 32:SCAN HDFS | 1 | 897.16ms | 897.16ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | | 31:SCAN HDFS | 6 | 1.84s | 2.89s | 433.69M | 43.37M
| 35.32 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales
|
| | 116:EXCHANGE | 6 | 706.14s | 708.85s | 65.88M | 165.73M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date)
|
| | 103:AGGREGATE | 6 | 11.29s | 13.94s | 65.88M | 165.73M
| 800.11 MB | 128.00 MB | FINALIZE
|
| | 102:EXCHANGE | 6 | 688.72s | 691.42s | 65.88M | 165.73M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumss)
|
| | 58:AGGREGATE | 6 | 207.71s | 285.28s | 65.88M | 165.73M
| 800.14 MB | 128.00 MB | STREAMING
|
| | 57:HASH JOIN | 6 | 170.99s | 206.82s | 10.57B | 165.73M
| 1.14 GB | 1.10 GB | INNER JOIN, PARTITIONED
|
| | |--101:EXCHANGE | 6 | 198.67s | 199.64s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| | | 56:ANALYTIC | 6 | 14.85s | 17.09s | 65.88M | 165.73M
| 14.05 MB | 4.00 MB |
|
| | | 55:SORT | 6 | 34.44s | 37.68s | 65.88M | 165.73M
| 456.07 MB | 70.00 MB |
|
| | | 99:EXCHANGE | 6 | 125.42s | 127.89s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| | | 98:AGGREGATE | 6 | 12.23s | 13.09s | 65.88M | 165.73M
| 736.10 MB | 128.00 MB | FINALIZE
|
| | | 97:EXCHANGE | 6 | 111.12s | 112.20s | 76.32M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk,d_date)
|
| | | 54:AGGREGATE | 6 | 76.74s | 80.18s | 76.32M | 165.73M
| 1.05 GB | 128.00 MB | STREAMING
|
| | | 53:HASH JOIN | 6 | 18.75s | 19.54s | 1.66B | 165.73M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | | |--96:EXCHANGE | 6 | 415.67ms | 508.18ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | | 52:SCAN HDFS | 1 | 892.80ms | 892.80ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | | 51:SCAN HDFS | 6 | 3.50s | 3.65s | 1.66B | 165.73M
| 48.44 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales
|
| | 100:EXCHANGE | 6 | 261.85s | 327.27s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| | 50:ANALYTIC | 6 | 32.10s | 35.20s | 65.88M | 165.73M
| 14.05 MB | 4.00 MB |
|
| | 49:SORT | 6 | 35.56s | 36.72s | 65.88M | 165.73M
| 456.07 MB | 70.00 MB |
|
| | 95:EXCHANGE | 6 | 125.86s | 127.32s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| | 94:AGGREGATE | 6 | 12.42s | 14.29s | 65.88M | 165.73M
| 736.10 MB | 128.00 MB | FINALIZE
|
| | 93:EXCHANGE | 6 | 111.14s | 112.02s | 76.32M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk,d_date)
|
| | 48:AGGREGATE | 6 | 77.39s | 81.46s | 76.32M | 165.73M
| 1.05 GB | 128.00 MB | STREAMING
|
| | 47:HASH JOIN | 6 | 18.78s | 19.18s | 1.66B | 165.73M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | |--92:EXCHANGE | 6 | 412.63ms | 519.04ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | 46:SCAN HDFS | 1 | 891.21ms | 891.21ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | 45:SCAN HDFS | 6 | 3.45s | 3.89s | 1.66B | 165.73M
| 47.71 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales
|
| 119:EXCHANGE | 6 | 784.38s | 826.65s | 65.88M | 209.10M
| 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN
v1.item_sk ELSE v1.item_sk END) |
| 30:ANALYTIC | 6 | 34.63s | 35.44s | 65.88M | 209.10M
| 14.05 MB | 4.00 MB |
|
| 29:SORT | 6 | 53.09s | 56.55s | 65.88M | 209.10M
| 1.04 GB | 126.00 MB |
|
| 91:EXCHANGE | 6 | 666.59s | 668.45s | 65.88M | 209.10M
| 0 B | 0 B | HASH(CASE WHEN v1.item_sk IS NOT NULL THEN
v1.item_sk ELSE v1.item_sk END) |
| 28:HASH JOIN | 6 | 4.09s | 4.41s | 65.88M | 209.10M
| 800.14 MB | 295.73 MB | FULL OUTER JOIN, PARTITIONED
|
| |--90:EXCHANGE | 6 | 602.37s | 602.60s | 65.01M | 43.37M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date)
|
| | 88:AGGREGATE | 6 | 11.59s | 13.21s | 65.01M | 43.37M
| 800.11 MB | 128.00 MB | FINALIZE
|
| | 87:EXCHANGE | 6 | 580.11s | 582.65s | 65.01M | 43.37M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumws)
|
| | 13:AGGREGATE | 6 | 213.32s | 277.19s | 65.01M | 43.37M
| 800.13 MB | 128.00 MB | STREAMING
|
| | 12:HASH JOIN | 6 | 168.51s | 178.29s | 10.28B | 43.37M
| 1.07 GB | 295.73 MB | INNER JOIN, PARTITIONED
|
| | |--86:EXCHANGE | 6 | 131.82s | 132.12s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | | 11:ANALYTIC | 6 | 18.86s | 20.27s | 65.01M | 43.37M
| 14.05 MB | 4.00 MB |
|
| | | 10:SORT | 6 | 37.87s | 40.42s | 65.01M | 43.37M
| 450.07 MB | 36.00 MB |
|
| | | 84:EXCHANGE | 6 | 51.25s | 51.93s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | | 83:AGGREGATE | 6 | 10.74s | 11.71s | 65.01M | 43.37M
| 704.10 MB | 128.00 MB | FINALIZE
|
| | | 82:EXCHANGE | 6 | 38.37s | 38.88s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk,d_date)
|
| | | 09:AGGREGATE | 6 | 24.85s | 25.47s | 65.01M | 43.37M
| 737.78 MB | 128.00 MB | STREAMING
|
| | | 08:HASH JOIN | 6 | 4.96s | 5.11s | 433.69M | 43.37M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | | |--81:EXCHANGE | 6 | 410.17ms | 513.08ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | | 07:SCAN HDFS | 1 | 900.76ms | 900.76ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | | 06:SCAN HDFS | 6 | 1.82s | 2.76s | 433.69M | 43.37M
| 35.55 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales
|
| | 85:EXCHANGE | 6 | 144.62s | 189.18s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | 05:ANALYTIC | 6 | 32.72s | 34.43s | 65.01M | 43.37M
| 14.05 MB | 4.00 MB |
|
| | 04:SORT | 6 | 38.05s | 40.47s | 65.01M | 43.37M
| 450.07 MB | 36.00 MB |
|
| | 80:EXCHANGE | 6 | 51.60s | 52.25s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk)
|
| | 79:AGGREGATE | 6 | 10.42s | 11.41s | 65.01M | 43.37M
| 704.10 MB | 128.00 MB | FINALIZE
|
| | 78:EXCHANGE | 6 | 39.27s | 40.00s | 65.01M | 43.37M
| 0 B | 0 B | HASH(ws_item_sk,d_date)
|
| | 03:AGGREGATE | 6 | 25.46s | 26.71s | 65.01M | 43.37M
| 737.80 MB | 128.00 MB | STREAMING
|
| | 02:HASH JOIN | 6 | 4.98s | 5.23s | 433.69M | 43.37M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | |--77:EXCHANGE | 6 | 404.44ms | 482.34ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | 01:SCAN HDFS | 1 | 900.50ms | 900.50ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | 00:SCAN HDFS | 6 | 1.84s | 2.95s | 433.69M | 43.37M
| 35.57 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.web_sales
|
| 89:EXCHANGE | 6 | 660.32s | 666.04s | 65.88M | 165.73M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date)
|
| 76:AGGREGATE | 6 | 11.83s | 13.32s | 65.88M | 165.73M
| 800.11 MB | 128.00 MB | FINALIZE
|
| 75:EXCHANGE | 6 | 643.03s | 644.31s | 65.88M | 165.73M
| 0 B | 0 B | HASH(v1.item_sk,v1.d_date,v1.sumss)
|
| 27:AGGREGATE | 6 | 213.04s | 277.24s | 65.88M | 165.73M
| 800.14 MB | 128.00 MB | STREAMING
|
| 26:HASH JOIN | 6 | 168.15s | 176.70s | 10.57B | 165.73M
| 1.14 GB | 1.10 GB | INNER JOIN, PARTITIONED
|
| |--74:EXCHANGE | 6 | 194.35s | 194.72s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| | 25:ANALYTIC | 6 | 15.59s | 16.71s | 65.88M | 165.73M
| 14.05 MB | 4.00 MB |
|
| | 24:SORT | 6 | 35.36s | 36.64s | 65.88M | 165.73M
| 456.07 MB | 70.00 MB |
|
| | 72:EXCHANGE | 6 | 122.33s | 123.44s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| | 71:AGGREGATE | 6 | 12.33s | 13.79s | 65.88M | 165.73M
| 736.10 MB | 128.00 MB | FINALIZE
|
| | 70:EXCHANGE | 6 | 107.43s | 108.63s | 76.32M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk,d_date)
|
| | 23:AGGREGATE | 6 | 75.89s | 78.88s | 76.32M | 165.73M
| 1.05 GB | 128.00 MB | STREAMING
|
| | 22:HASH JOIN | 6 | 18.46s | 18.94s | 1.66B | 165.73M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| | |--69:EXCHANGE | 6 | 412.65ms | 521.64ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | | 21:SCAN HDFS | 1 | 901.28ms | 901.28ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| | 20:SCAN HDFS | 6 | 3.43s | 3.73s | 1.66B | 165.73M
| 46.98 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales
|
| 73:EXCHANGE | 6 | 217.34s | 261.02s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| 19:ANALYTIC | 6 | 32.68s | 33.52s | 65.88M | 165.73M
| 14.05 MB | 4.00 MB |
|
| 18:SORT | 6 | 36.88s | 37.88s | 65.88M | 165.73M
| 456.07 MB | 70.00 MB |
|
| 68:EXCHANGE | 6 | 122.82s | 124.40s | 65.88M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk)
|
| 67:AGGREGATE | 6 | 12.05s | 12.79s | 65.88M | 165.73M
| 736.10 MB | 128.00 MB | FINALIZE
|
| 66:EXCHANGE | 6 | 109.03s | 110.25s | 76.32M | 165.73M
| 0 B | 0 B | HASH(ss_item_sk,d_date)
|
| 17:AGGREGATE | 6 | 75.88s | 79.68s | 76.32M | 165.73M
| 1.05 GB | 128.00 MB | STREAMING
|
| 16:HASH JOIN | 6 | 18.47s | 18.89s | 1.66B | 165.73M
| 3.72 MB | 1.94 MB | INNER JOIN, BROADCAST
|
| |--65:EXCHANGE | 6 | 410.20ms | 508.85ms | 366 | 7.30K
| 0 B | 0 B | BROADCAST
|
| | 15:SCAN HDFS | 1 | 900.29ms | 900.29ms | 366 | 7.30K
| 2.07 MB | 48.00 MB | tpcds_bin_partitioned_parquet_3000.date_dim
|
| 14:SCAN HDFS | 6 | 3.44s | 4.11s | 1.66B | 165.73M
| 48.45 MB | 176.00 MB | tpcds_bin_partitioned_parquet_3000.store_sales
|
+--------------------------+--------+----------+----------+---------+------------+-----------+---------------+----------------------------------------------------------------------------+
Best Regards
Ferdinand Xu
