How has the locking model changed?

[jonathan.d]

Hi All,

I am running 941 FP3 on win server 2003 x64.

Horrible locking issues, which makes me ask the question... is this IBM's solution to the new locking model that would require a redesign of the model or is it a bad default behavior that can be changed through internal configuration parameters?

Scenario

Model- Consolidated GL cube which is fed from 6 individual GL cubes which are fed from 6 manual entry cubes.

When a large view is recalculating on the consolidated GL cube(approx time 70secs), any writes there after are waiting until the recalculate completes.
(Worse case: 3 users hit a recalculate with a 45 secs gap, any user wishing to write there after ends up waiting for a very long time)

Granted that behavior would be acceptable if data integrity is required at all costs, but what if one doesn't care about data integrity.
With writes being so fast, I would imagine it would hold on reads, complete writes and then resume reads even though they are dirty.

Has the locking model changed where in reads are held in priority over writes?
Is there a way to change this behavior? Or, is sandboxing the solution..where in even though everything is connected via rules each user can make changes in the manual GL entry cubes and see the impact in the consolidated GL cube without affecting other users doing the same? (Correct me if I am wrong about how sandboxing works)

Any advice/suggestions would be appreciated.

[LoadzaGrunt]

I would be looking to optimise the calculation first before looking to optimise a locking scenario.

What steps have you taken to see if the rules and feeders can be improved ?

[jonathan.d]

Granted, I could do a bit more to improve rules and feeders. Also, trimming down a few dimensions.
But, lets just say I have reached a fairly acceptable state when it comes to the above mentioned and the model is not used in a standard approval hierarchy method...

That would lead me back to my initial question.

[lotsaram]

When a large view is recalculating on the consolidated GL cube(approx time 70secs), any writes there after are waiting until the recalculate completes. ... With writes being so fast, I would imagine it would hold on reads, complete writes and then resume reads even though they are dirty.
Has the locking model changed where in reads are held in priority over writes?

You have to bear in mind that any change to data anywhere in the cube will invalidate all calculations. If a write was permitted in the middle of a calculation then the calculation would have to start from scratch. Seen from this point of view it makes sense that the write thread has to wait for the calculation thread to finish.

Fundamentally though I think your system is suffering from sub-optimal design. I have seen some very large and very complex GL budgeting models and 70 sec to calculate a high level view in a GL cube is much longer than I would reasonably expect. First thing to do review rules and feeders to tune performance to an acceptable level.

Second thing to look at: In respect to the locking model. If there are rules linking cubes and flowing data from the 6 data entry cubes to the main consolidation cube then they will effectively be treated and one cube as far as locking goes. If you don't want data input in the data entry cubes to be held up for calculations in the consolidation cube then you need to replace the rules with TI. If there are no rules linking cubes then users will be able to read/write or write/write to independent cubes simultaneously. This means that updates will not flow "real time" to the consolidation cube, but if you don't care so much about to the second data integrity then this would be a much better design for you.

[mykill]

We have the same situation.

were is a new parameter:

InfiniteThresholdForSingleCellStorage.

The idea is to turn off the cache so reads and writes can happen at the same time.

I can not test it, because we are still using 9.1 SP3 but it should work with TM1 9.0 SP3 U9, 9.4.1 FP3 and 9.5.

Hotfix 4 for TM1 9.0 SP3 U9 Server addresses this issue:
It introduces a new optional server configuration parameter
InfiniteThresholdForSingleCellStorage which if set prevents calculated
rules derived cube cell values from get being cached.
As they are not chached, they do not force a write lock on the Calculation
Storage.
The result will be improved multi-user concurrency / multi-thread
performance, paid for by increased (repeated) calculation operations.

InfiniteThresholdForSingleCellStorage=[True|False]

Default Setting: InfiniteThresholdForSingleCellStorage=False

[David Usherwood]

Interesting. Can you point to the IBM documentation for this? I'm very puzzled though about the versions you quote, as my understanding is that Applix more or less ripped apart the engine after 90. When they rebuilt it in 9.1, specifically to address write locking, the early versions where not good, and it has taken them an inordinately long time to bring performance back to where it was with 90. We have just tested 94FP3 and on browsing large views they have _finally_ corrected a most unpleasang performance issue.

Also....
Why would you _not_ want calculated values to be cached?!?! What would the point of easy updates if all calculations took ages, every time?

[jonathan.d]

I am with you on that one David.

However, mykill could you be referring to this CalculationThresholdForStorage parameter.

Straight from the documentation is what I have posted below.

Could someone shed some light on this parameter as to what it really means? I think the default parameter of 50 is high, if they refer to number of calculations as those that exist between cubes as in GL cube -> Mid level GL Cube -> Entry Cube(in this case 2).

Am i right?

Has anybody tried tweaking this parameter and seen performance increase?


CalculationThresholdForStorage
This parameter is optional.

Defines a minimum number of rule calculations required for a single cell or Stargate view, beyond which the TM1 server stores the calculations for use during the current server session.

For example, when a user requests rule-derived values from a TM1 server, either from a single cell or a Stargate view, the server usually has to perform multiple rule calculations to arrive at the requested rule-derived values.

CalculationThresholdForStorage has a direct effect on memory consumption and performance. A high parameter value results in decreased memory consumption and slower performance. A low parameter value results in increased memory consumption and faster performance.

If you do not include CalculationThresholdForStorage in Tm1s.cfg, the default calculation threshold is 50.

[mykill]

Also....
Why would you _not_ want calculated values to be cached?!?! What would the point of easy updates if all calculations took ages, every time?

In a planning system every time you change data - the cache becomes invalid. Sometimes (many CPU cores) it can be faster to calculate every time.

[John Hammond]

We are running 941 FP1 on win server 2003 x64.

We just had two instances where a long running read only query locked out users while it was running. Once the query was killed users could resume as normal. One query was a simple query on the logs. We could not even go into tm1top.

Obviously this is very worrying in that TM1 is acting like an old style batch processing system and processing one request after another instead of time slicing them. Or it could be that the query has its hands on a resource that everyone needs and thus everyone is placed into a wait state.

The question is what resource needed by everybody can be locked by a single process. Locking is usually associated with a write. I tried putting logging onto the system tables to see if there was any writing going on and I could find none.

The alternative is that there is a problem with 9.4 in that it is overlocking as johnathan.d suggests.

Does anyone have any ideas, please, as this could be a showstopper in that any long running jobs have to go in at the evenings only.

[lotsaram]

READ activity is multi-threaded but all CALCULATION on a TM1 server is still single threaded. Many (if not most) read queries will need to perform calculations (consolidations, rules ...). Therefore "read" activity can still cause queuing.

[John Hammond]

Thanks Lotsaram

One thing that occurred to me is that we are logging all cubes including the system cubes because we want to be able to trace backwards if we have a data problem to find what caused it from the logs.

Could the bottlenect be the log file?

It seems to act as a psuedo cube yet parts of it are hived off into separate tm1sdatetime.log file (with no break in the numbers making them tedious to read - please note IBM). Certainly the log query feature is OLAP like but very slow.

Regards

John

[John Hammond]

Finally have found time to test this and switching logging off all cubes including system cubes

It makes no difference to locking.

My read only process is still locking other processes by taking a read lock. I assume that anything issuing a write intent to the same cube or component of the cube cannot get the lock because TM1 is unable to build a view against a changing cube.

Initially I thought this solved the problem so sorry if I put you on a bum steer.

[adityashah]

Hey Guys,

very interesting topic...We are running into mysterious locking issues... we have reporting cubes (users dont input anything though they have Write access) on 941 fp2 hf16. normal cube size is 16-19 dimensions, 6k-8k max elements per dim.

whenever user runs RECALC on excel slice to refresh data, i see other users with RECALC on their excel slice queuing up... so first user will have R lock, while i see others with Wait: IXC or Wait:IXCur in tm1 top. now i know the fact that our users are trying to refresh their excel slice and no writing to cubes, they use pvt subsets and sometimes psudo susbsets in their views. i dont see any reason for intent-to-write locks when users are just refreshing their excel slice (F9), correct me if i am wrong.

i removed all expressions from all public subsets except 2-3 subsets. so during the data refresh process there is no dynamic subset process running. our users are not trained to create subsets using expressions. i dont think of anything else which would have IX locks, my understanding is, it should be all R locks.

does it make sense? is there something we can do to avoid waits for F9s? any information on this would be helpful...

Thanks,

Aditya

[Armand Mizan]

Hi All,

I am running 941 FP3 on win server 2003 x64.

Horrible locking issues, which makes me ask the question... is this IBM's solution to the new locking model that would require a redesign of the model or is it a bad default behavior that can be changed through internal configuration parameters?

.

Hi all

Has anyone experienced these sorts of issues on 9.5.? We are running 9.4 on the server,(128gb of ram) with very few models containing complex rules and experience the same locking issues that Jonathan has

Thanks in advance for your advice
Armand

[Steve Rowe]

Can I just encourage people to raise these issues direct with IBM too, the forum can help and offer advice, but if you think the locking model for TM1 is broken or destroying the USP of the product then please raise with IBM as well. Thanks

[John Hammond]

whenever user runs RECALC on excel slice to refresh data, i see other users with RECALC on their excel slice queuing up... so first user will have R lock, while i see others with Wait: IXC or Wait:IXCur in tm1 top. now i know the fact that our users are trying to refresh their excel slice and no writing to cubes, they use pvt subsets and sometimes psudo susbsets in their views. i dont see any reason for intent-to-write locks when users are just refreshing their excel slice (F9), correct me if i am wrong.

i removed all expressions from all public subsets except 2-3 subsets. so during the data refresh process there is no dynamic subset process running. our users are not trained to create subsets using expressions. i dont think of anything else which would have IX locks, my understanding is, it should be all R locks.

does it make sense? is there something we can do to avoid waits for F9s? any information on this would be helpful...

Aditya we are seeing exactly the same behaviour.

A slice can be considered as performing write based actions if it contains an intersection of dimensions with values that are all at n level. Perhaps TM1 is not smart enough to distinguish based on the formulae used in the slice as to whether write intent is there.

Possibly it may have been before active forms where you could not drill down further it could do this.

The slices/active forms seem to be coded up using DBRWs and there is an implied write intent in using that function as described in the manual.

Code: Select all

DBRW
This is a TM1 worksheet function, valid only in worksheets.
This function retrieves a value from a specified TM1 cube. When all element arguments (e1, e2, etc.) to the function are leaf elements, the DBRW function can also be used to write values to the specified cube, provided that the user has appropriate access privileges to the relevant cube, dimensions, elements, and/or cells.
This function works the same as the DBR function, with one major difference; DBRW reduces network traffic and may improve performance on wide area networks. 
In worksheets with a large number of TM1 functions, DBRW forces TM1 to execute functions in "bundles" rather than individually. Normal DBR functions are executed individually during a worksheet recalculation. DBRW functions force TM1 to execute two passes over the worksheet. In the first pass, all changed values in cells containing DBRW functions are sent in a single bundle to the cube. In the second pass, cube values are sent in a single bundle back to the worksheet. Consequently, the worksheet recalculates twice when DBRW functions are executed.

In fact it seems that all formulae for Excel DBR, DBRW and DBS, DBSW that display values have an implicit write back. There is no function to say retrieve and do not update further. If IBM could come up with a new ROXX (read only) set of functions then we might avoid all this locking with spreadsheets.

That said it still does not explain when locked not only does it freeze on submission of the sheet to return to the menu but also when building the sheet in the first place.

I am clutching a bit at straws here but maybe someone with better excel knowledge could help here.

[lotsaram]

A slice can be considered as performing write based actions if it contains an intersection of dimensions with values that are all at n level. Perhaps TM1 is not smart enough to distinguish based on the formulae used in the slice as to whether write intent is there.

Possibly it may have been before active forms where you could not drill down further it could do this.

The slices/active forms seem to be coded up using DBRWs and there is an implied write intent in using that function as described in the manual.

Hi John,

I don't really have time right now to write a more detailed post but you are a bit misguided here. A write action only happens when a user actually keys a value into a cell containing a DBR(W) formula. The ability for a user to write back is managed by security and cube lock status. The fact that a user can write to a cube does not place a write lock during a calculation, this only happens when a user actually does do a write action.

[jonathan.d]

The locking model has changed where readers always bypass writers, and no way to change this except to design/redesign the model keeping this mind.

Correct me if I am wrong...

[Yash]

Hi Jonathan,

Did you ever found a solution to the 'Locking' issue you had ?

I am running into the same problem.

Thanks
Yash