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
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.
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