MT_DOP Query Option

Sets the degree of intra-node parallelism used for certain operations that can benefit from multithreaded execution. You can specify values higher than zero to find the ideal balance of response time, memory usage, and CPU usage during statement processing.

Note:

The Impala execution engine is being revamped incrementally to add additional parallelism within a single host for certain statements and kinds of operations. The setting MT_DOP=0 uses the “old” code path with limited intra-node parallelism.

Currently, MT_DOP support varies by statement type:

  • COMPUTE [INCREMENTAL] STATS. Impala automatically sets MT_DOP=4 for COMPUTE STATS and COMPUTE INCREMENTAL STATS statements on Parquet tables.

  • SELECT statements. MT_DOP is 0 by default for SELECT statements but can be set to a value greater than 0 to control intra-node parallelism. This may be useful to tune query performance and in particular to reduce execution time of long-running, CPU-intensive queries.

  • DML statements. MT_DOP values greater than zero are not currently supported for DML statements. DML statements will produce an error if MT_DOP is set to a non-zero value.

  • In Impala 3.4 and earlier, not all SELECT statements support setting MT_DOP. Specifically, only scan and aggregation operators, and local joins that do not need data exchanges (such as for nested types) are supported. Other SELECT statements produce an error if MT_DOP is set to a non-zero value.

Type: integer

Default: 0

Because COMPUTE STATS and COMPUTE INCREMENTAL STATS statements for Parquet tables benefit substantially from extra intra-node parallelism, Impala automatically sets MT_DOP=4 when computing stats for Parquet tables.

Range: 0 to 64

Examples:

Note:

Any timing figures in the following examples are on a small, lightly loaded development cluster. Your mileage may vary. Speedups depend on many factors, including the number of rows, columns, and partitions within each table.

The following example shows how to run a COMPUTE STATS statement against a Parquet table with or without an explicit MT_DOP setting:

  2. -- Explicitly setting MT_DOP to 0 selects the old code path.
  3. set mt_dop = 0;
  4. MT_DOP set to 0
  6. -- The analysis for the billion rows is distributed among hosts,
  7. -- but uses only a single core on each host.
  8. compute stats billion_rows_parquet;
  9. +-----------------------------------------+
  10. | summary                                 |
  11. +-----------------------------------------+
  12. | Updated 1 partition(s) and 2 column(s). |
  13. +-----------------------------------------+
  15. drop stats billion_rows_parquet;
  17. -- Using 4 logical processors per host is faster.
  18. set mt_dop = 4;
  19. MT_DOP set to 4
  21. compute stats billion_rows_parquet;
  22. +-----------------------------------------+
  23. | summary                                 |
  24. +-----------------------------------------+
  25. | Updated 1 partition(s) and 2 column(s). |
  26. +-----------------------------------------+
  28. drop stats billion_rows_parquet;
  30. -- Unsetting the option reverts back to its default.
  31. -- Which for COMPUTE STATS and a Parquet table is 4,
  32. -- so again it uses the fast path.
  33. unset MT_DOP;
  34. Unsetting option MT_DOP
  36. compute stats billion_rows_parquet;
  37. +-----------------------------------------+
  38. | summary                                 |
  39. +-----------------------------------------+
  40. | Updated 1 partition(s) and 2 column(s). |
  41. +-----------------------------------------+

The following example shows the effects of setting MT_DOP for a query on a Parquet table:

  2. set mt_dop = 0;
  3. MT_DOP set to 0
  5. -- COUNT(DISTINCT) for a unique column is CPU-intensive.
  6. select count(distinct id) from billion_rows_parquet;
  7. +--------------------+
  8. | count(distinct id) |
  9. +--------------------+
  10. | 1000000000         |
  11. +--------------------+
  12. Fetched 1 row(s) in 67.20s
  14. set mt_dop = 16;
  15. MT_DOP set to 16
  17. -- Introducing more intra-node parallelism for the aggregation
  18. -- speeds things up, and potentially reduces memory overhead by
  19. -- reducing the number of scanner threads.
  20. select count(distinct id) from billion_rows_parquet;
  21. +--------------------+
  22. | count(distinct id) |
  23. +--------------------+
  24. | 1000000000         |
  25. +--------------------+
  26. Fetched 1 row(s) in 17.19s

The following example shows how queries that are not compatible with non-zero MT_DOP settings produce an error when MT_DOP is set:

  2. set mt_dop=1;
  3. MT_DOP set to 1
  5. insert into a1
  6. select * from a2;
  7. ERROR: NotImplementedException: MT_DOP not supported for DML statements.

Related information:

COMPUTE STATS Statement, Impala Aggregate Functions

Parent topic: Query Options for the SET Statement