Improving processing performance with Multi-threaded Queries
Multi-threaded queries allow IBM® Cognos® TM1® 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.
For 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.
Best practice is to set MTQ to the highest available combination of physical and multi-threaded cores. For example, set MTQ=8 for 4 hyper-threaded cores or MTQ=16 for 16 physical cores.
Only servers 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.
To allow the server to process multi-threaded queries, specify the maximum number of threads to use when processing queries by adding MTQ=n to the Tm1s.cfg, where n represents the maximum overall number of threads per query.
You can track multi-threaded query execution using the Enhanced object contention report in Cognos TM1 Operation Console.
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