Multi-threaded queries

You can improve the processing performance of queries by allowing queries to be split into multiple processing threads.

Multi-threaded queries allow Planning Analytics to automatically load balance the application of cores by executing each query on a separate core. This multiple processing can improve efficiency and processing time for large queries and rules.

Example

  • Query 1 runs and receives the parent thread and the 7 worker threads (8 total).
  • Query 2 runs. Upon completing the sub task in the worker thread, query 2 returns to be queued, and 4 of the threads are now assigned to Query 2, resulting in a 4 to 4 split of threads.
  • Query 3 runs. The threads once again complete their sub task on the threads that are inflight, and then reassign to the new query. That causes a split of Query 1 into 3 threads, Query 2. Query 3 into 3 threads and Query 3 into 2 threads for a total of 8 threads. This process continues until eventually all threads are evenly divided.
  • If Query 1 completes while Query 2 and Query 3 are still inflight, the threads that are assigned to Query 1 are assigned to Query 2 and Query 3, so they each maintain 4 threads.

Details

By default, multi-threaded queries are enabled. The number of threads is set to the maximum number of cores available on a database. The result is a dynamic system setting that consumes all cores. You can fine-tune the configuration by using the following database configuration parameters:
  • MTQ: Sets the maximum number of threads per Planning Analytics query.
  • MTQ.OperationProgressCheckSkipLoopSize: Specifies the number of cells to be processed before checking whether multi-threaded splits are needed.
  • MTQ.SingleCellConsolidation: Disallow multi-threaded query processing for single cell consolidations.

Only databases running at less than 100% capacity can benefit from multi-threaded query improvements. For example, if you had 8 cores processing 8 concurrent queries, none could leverage multi-threaded queries. The addition of a 9th concurrently processing query would result in the query processing threads being split across multiple cores.

Multi-threaded queries can improve performance on numeric cubes, where consolidation is optimized. As Planning Analytics does not consolidate string values, multi-threaded queries have no impact on the performance of string cubes.

Note: Planning Analytics database multi-threaded queries are no longer limited to 64 CPU cores. Microsoft Windows Platforms with more than 64 processors can take full advantage of this feature and its performance benefits.

Logging

To enable logging for multi-threaded queries, enter the following lines in the tm1s-log.properties file located in the same location as your tm1s.cfg file:

  • To capture Stargate creation times add: log4j.logger.TM1.Cube.Stargate=DEBUG
  • To capture work unit splitting: log4j.logger.TM1.Parallel=DEBUG
  • To capture the event of operation threads picking work units: log4j.logger.TM1.OperationThread=DEBUG