Mastering MTQ with TM1 and Planning Analytics

Multi-Threaded Queries (MTQ) allow IBM TM1 and Planning Analytics to automatically load balance a single query across multiple CPU cores. In other words, TM1 is fast, MTQ makes it even faster. It has been around for a number of years but there still some frequently asked questions which need a clear answer.

IBM’s recommendation is that the MTQ value should be the maximum number of available processor cores. In our experience, it is better to NOT give ALL processor cores to MTQ but leave one or two cores to the server to make sure it has enough room to operate (MTQ=-2 or MTQ=-3 that way the Operating System and other server activities can have some processing time). MTQ=-n means that TM1 will use Total number of available cores – n + 1. For more information about configuring MTQ, you should read this IBM technote:

• IBM Tip and Technique to configure MTQ

The MTQ setting is per TM1 instance. If you are running more than one TM1 instance on the same server then the sum of all MTQ value for each instance should not be greater than the number of available cores on the server. For example, if you have two TM1 instances on the same 32 CPU server, one MTQ value could be 20 and the other one 10, the sum of the MTQ should be less than 30.

Increasing the number of cores on your server to increase the MTQ value will not necessarily make your TM1 application faster. MTQ works by splitting a query into multiple chunks and then combining the results from each chunk into a final result.

There is overhead involved in the process of splitting the query and combining the result. Depending on the size of the data there will be a point where the splitting takes more time than if it larger chunks were used.

Overall, somewhere between 32 and 64 cores, the query hits this tipping point. If your TM1 and Planning Analytics application contains only small cubes, the optimal number of cores could be even less and for applications which have bigger cubes it may be more than 64.

The only way you can really know the optimal setup is by testing.

MTQ does not trigger all the time. It will only trigger in one of the following scenario:

• Queries exceeding 10,000 cell visits.
• Creation of TM1 ‘stargate’ vew.
• Views containing the results of rule derived calculations.

To enable/disable MTQ processing when calculating a view to be used as a TM1 process data source, you need to use MTQQuery parameter in tm1s.cfg. You can choose to overwrite the MTQQuery for a specific process by using one of the following function EnableMTQViewConstruct() or DisableMTQViewConstruct().

As per IBM, “MTQ value does not specify the total # of CPUs that TM1 can leverage for multiple queries. MTQ defines the # of CPU cores that TM1 may leverage for individual queries”.

In other words, MTQ will be able to split one query into multiple chunks, but it does not manage the sequence of queries. If your queries are queuing, it has nothing to do with MTQ.

Parallel processing and MTQ are two separate things, the Q in MTQ is for query. So MTQ is only used when you are reading data from the cube, if you are running a straight load, writing data into the cube,  MTQ doesn’t have an influence.

Running a TM1 process might not always trigger MTQ. For example, if a process is using an existing view, depending on the view MTQ might not be triggered. The process would need to construct the view to trigger MTQ.

To fine tune how MTQ will behave, there are other tm1s.cfg parameters that you can use. The first four below are documented by IBM and the last four are undocumented:

Documented

• MTQ.CTreeRedundancyReducer
• MTQ.EnableParallelFacetQuery
• MTQ.OperationProgressCheckSkipLoopSize
• MTQ.SingleCellConsolidation

• MTQ.ForceParallelTxnOnMainWorkUnit
• MTQ.ImmediateCheckForSplit
• MTQ.UseThreadPrivateCacheCopyForOperationThreads
• MTQ.MultithreadStargateCreationUsesMerge
• MTQ.CTreeWorkUnitMerge
• MTQ.OperationThreadWakeUpTime

By default, MTQ does not trigger when feeders are processed. To enable MTQ for feeders, IBM introduced in Planning Analytics a new tm1s.cfg parameter MTFeeders. By turning on MTFeeders, MTQ will be triggered when:

• CubeProcessFeeders() is triggered from a TM1 process.
• A feeder statement is updated in the rules.
• Construction of feeders at startup.

MTFeeders will provide you significant improvement but you need to be aware that is does not support conditional feeders. If you are using conditional feeders where the condition clause contains a fed value, you have to turn it off.

To turn on MTFeeders during server start-up you will need to add MTFeeders.AtStartup=T.

• Tip and Technique to configure MTQ.
• Introduction to Multi-threaded Queries (MTQ) in TM1 10.2
• 10 Tips to improve your TM1 application