Locking Issues

[mattgoff]

I'm running into some locking issues on TM1 x64 10.2.2.3. I have a process that makes a copy of a version (i.e. a snapshot). Algorithm is:

1. Via parameter, set source version which is used to create a subset in version dim. Target version element is automatically created, name based on current datetime.
2. Walk through the cubes via indicies (backwards) looking for cubes with a version dimension.
3. When it finds one, create a view in that cube with only version dim subset (from #1). Then kick off cube copy process with new view as datasource (also passes version dim index, number of dims, and target version name) to do the copy.

The issue I have is that everything runs fine (although surprisingly slowly) on dev, but when I try to use it on production (with users doing their thing) the process runs much more slowly and users are locked for most of the time (although I see a few breathers). The version I'm trying to run against first is nearly all static-- the only rule it has running against it is to change C-level for B/S to the period-end instead of a consolidation. Users only have read access to the source and target versions, too, so no data is changing. Due to the length of the process, a replication may happen while it's running (and those accounts do have write access, but nothing should be changing).

I have two versions of the child process(es), once which copies directly and one that uses ASCIIOutput and a second child process to read file and write to the new version. The latter was my first attempt to solve this issue by trying to avoid some rules/feeders firing. It didn't improve things.

What else can I try? Ideally I'd like to eliminate the locks, but barring a bug I don't understand why they're getting set. Barring that, I'd like to expedite the run time back to at last the same performance I see on dev as I will need to do this periodically during working hours. I haven't used data reservation or bulk load modes in the past, but I'm reading up on them for another attempt. Unfortunately, since this only affects prod, my users have to be my guinea pigs....

I can post the code if that's useful.

Matt

[moby91]

What else can I try?

What happens when you execute your process in Bulk Load Mode ?
Does that make a difference regarding performance ?


http://www-01.ibm.com/support/knowledge ... 2RN_BkLdMd
TM1 Operation Guide 10.2.2
Remote Cognos TM1 Server Operations >

Using TM1 in Bulk Load Mode

Bulk Load Mode enables IBM Cognos TM1 to run in a special optimized single-user or single chore/process mode. This mode can maximize Cognos TM1 performance for dedicated tasks during times when little or no other activity is expected.

Cognos TM1 typically runs in a multi-user mode where multiple users, chores and processes can all run concurrently accessing Cognos TM1 data. In Bulk Load Mode, the Cognos TM1 server prevents concurrent activity by temporarily suspending other users, chores and processes and eliminates the overhead required by a multi-user environment.

Bulk Load Mode doesn't actually log out users, but only suspends their interaction with Cognos TM1 . As soon as Bulk Load Mode is done, any users that were previously logged in are reactivated and user-interaction with Cognos TM1 resumes.


http://www-01.ibm.com/support/knowledge ... ode_NC6749
TM1 Reference Guide 10.2.2
TM1 TurboIntegrator Functions > Server Manipulation TurboIntegrator Functions >
EnableBulkLoadMode


http://www-01.ibm.com/support/knowledge ... lkLoadMode
TM1 Reference Guide 10.2.2
TM1 TurboIntegrator Functions > Server Manipulation TurboIntegrator Functions >
DisableBulkLoadMode

[BrianL]

Have you tried enabling log4j.logger.TM1.Lock.Exception=DEBUG and diagnosing what objects are under contention?

[Steve Rowe]

Hi,

Not sure how your job is split up chore / TI wise. What I would suggest is the following.

Job 1 : This does all the "quick" preparation type stuff, from view and subset creation and population. Changing attributes and so on. This is all the stuff that runs quickly and can be prepared before the long running view copies take place. There are various activities that take place in here that could be taking the lock. Job 1 should also build all the rule dependencies that exist in your instance. In general these are built when the instance comes up but rule references to attributes are missed out (I guess this is a bug). If you add the rule dependency statements in a TI directly they execute quickly. If they are created in the background when the export is being done this takes a lock for the life of the export.

Job 2 : Export the data you want to copy to flat file. This is always non-locking but often can take a longer time to execute

Job 3 : Write the data into the cube. Writing data to a cube is supposed to be non-locking these days but not sure its 100% perfect all the time.

Keeping steps 2 and 3 distinct means that if the write operation triggers a lock it doesn't exist for the entire time the export is happening, just for the proportional shorter time that the it takes for the data to load.

They need to be set up as independent jobs, not chained together in a chore.
I've followed this general strategy and found that it helps minimise the lengths of the locks that will take place.

Also check this thread http://www.tm1forum.com/viewtopic.php?p=49645 , I don't know if the issue around user log off taking a lock exists in 10.2.2 but it (probably) can't do any harm to add the cfg.

HTH
Steve

[mattgoff]

What happens when you execute your process in Bulk Load Mode ?
Does that make a difference regarding performance ?

Every time I've tried this it hasn't gone well, and today was no exception. Added EnableBulkLoadMode as first line in Prolog and server made to it the end (based on export files) before attempting a Rollback (unknown why) which resulted in the following error: "TM1.Dimension Attempt to roll back permanent dimension 'Scenario' (0x00000000051E4A10) without a before image!". "Scenario" is the name of my version dimension. The server hung at this point, so I had to task kill it.

Have you tried enabling log4j.logger.TM1.Lock.Exception=DEBUG and diagnosing what objects are under contention?

Nope, good idea. Added.

Not sure how your job is split up chore / TI wise.

Right now it's three processes. A parent process which is run on demand (not by a chore) and creates the subset in the version dim, identifies cubes with a version dim, then fires off two child processes, one to export data to flatfile and a second to import it into the new version. Are you thinking I should maybe have two parents, one for the export and one for the import, and that might avoid locks on the export portion?

I'm going to add that cfg file entry, but chances are I can't restart the server until tonight, so testing will have to wait. Thanks for all the responses.

Thanks,
Matt

[Steve Rowe]

I'd have three, the first preparation job is the one that is most likely to trigger a lock, but if it is not chained to the export job then the lock will be very short.
The export and import jobs need to do as little as possible, almost just an asciioutput and cellputn.

gl! not a Friday afternoon job!

[mattgoff]

I'd have three, the first preparation job is the one that is most likely to trigger a lock, but if it is not chained to the export job then the lock will be very short.
The export and import jobs need to do as little as possible, almost just an asciioutput and cellputn.

gl! not a Friday afternoon job!

Worth a shot. Export and import need to have the logic to walk through the cubes, though, but since it's already written that's just copy/paste and is pretty fast in terms of execution time. I snuck in a restart before the west coast crowd got in, so I will have that flag set plus the extended logging now.

Matt

[mattgoff]

At the risk of liveblogging my efforts, here's another update. I refactored the old processes into five new processes grouped into three sets. The first process creates the new element and the subset for the source element. The next one walks through the cubes-- when it finds a cube with a version dim, it creates a view and calls the third process to do the export. The fourth and fifth walk the cubes again to do the import.

Total runtime is similar to the original process on dev, but users working during any of the three (even the import) report little to no impact to performance. No lock contentions were noted by log4j. So, it's a pain to have to run three processes, but the usability for end users is vastly improved. I did set the KeepPrivateSubsetsLoaded=T cfg file flag-- at some point I'll prob test the old process to see if that was the fix or if it was the separated processes. Either way, problem resolved.

Thanks all,
Matt

[paulsimon]

Hi Matt

Interesting results.

When you tried the approach without the intermediate file, ie cube to same cube, was that all running from the same top level process? I would guess that would maintain locks on the dimension for adding the new Version, and possibly for creating the subset and views.

Did you then split it into 3 separately triggered processes?

I am wondering if you might be able to run it all in one go using a Chore with Multiple Commit. That has certainly helped in the past. It would be good if IBM would introduce a Commit command into TI so we could control when commits happen.

I have some locking issues in 10.1.1 at the moment, where it appears to be locking cubes that do share any dimensions, and have no rule linkages.

Regards

Paul Simon

[Steve Rowe]

Just to back Paul up, believe it should stay non-locking if you put the three sets of TIs in a chore and have multiple commit turned on. This should be exactly like running each set independently.

To clarify when I said this

They need to be set up as independent jobs, not chained together in a chore

I meant, don't put all the calls in one master TI and put that in a chore...

Cheers,

[qml]

It would be good if IBM would introduce a Commit command into TI so we could control when commits happen.

They sorta already did. CubeSaveData used in a TI process commits and serialises to disk any data changes made in that TI up until that function is executed. Normally data changes are committed after all code of a given TI has been executed, but using the above function you can force earlier/multiple commits. Of course the CubeSaveData function is not without its cost, especially if your cub file is large and takes long to write to disk, but it still adds a useful trick to the toolbox.

[BrianL]

It would be good if IBM would introduce a Commit command into TI so we could control when commits happen.

They sorta already did. CubeSaveData used in a TI process commits and serialises to disk any data changes made in that TI up until that function is executed.

Sortof. You are correct that CubeSaveData will commit and serialize data changes for a single cube, HOWEVER it does not release locks. It certainly has its uses, but I wouldn't include reducing lock conflicts as one of them. Far more intriguing is the potential for a multi-threaded SaveDataAll equivalent.

[lotsaram]

Sortof. You are correct that CubeSaveData will commit and serialize data changes for a single cube, HOWEVER it does not release locks. It certainly has its uses, but I wouldn't include reducing lock conflicts as one of them. Far more intriguing is the potential for a multi-threaded SaveDataAll equivalent.

Multithreaded SaveData might be "intriguing" but it relies on having HD available where multi-threaded writing is possible. Otherwise you quickly hit a wall of I/O write limitations.

[qml]

You are correct that CubeSaveData will commit and serialize data changes for a single cube, HOWEVER it does not release locks.

What sort of locks are you talking about here? Data writing (CellPutN/CellPutS) is non-locking (i.e. does not require an exclusive lock) since the introduction of PI. Similarly SaveDataAll and CubeSaveData are non-locking these days. This means that unless you have another source of locking (like metadata changes) you can easily use TI to write data, commit it and access it from outside of the process while the process that writes the data is still running. I have done it and it works well.

[BrianL]

Multithreaded SaveData might be "intriguing" but it relies on having HD available where multi-threaded writing is possible. Otherwise you quickly hit a wall of I/O write limitations.

In my limited testing, even with "standard" hardware (non-ssd hard drive) there's still performance gains. Another complexity is that CubeSaveData doesn't truncate the transaction log. However, using CSD in parallel followed by a SaveDataAll can mitigate this IF non of the cubes are marked dirty (written to) between the CSD and the SDA. Ugly, but can be made to work.

[BrianL]

You are correct that CubeSaveData will commit and serialize data changes for a single cube, HOWEVER it does not release locks.

What sort of locks are you talking about here? Data writing (CellPutN/CellPutS) is non-locking (i.e. does not require an exclusive lock) since the introduction of PI. Similarly SaveDataAll and CubeSaveData are non-locking these days. This means that unless you have another source of locking (like metadata changes) you can easily use TI to write data, commit it and access it from outside of the process while the process that writes the data is still running. I have done it and it works well.

I brought up releasing locks since this thread was (originally) about lock contention issues. Maybe I'm just being pedantic, but PI, SaveDataAll, CubeSaveData, etc... all still require locks, they're just less obtrusive. You can see this in action if you try to run data writes concurrent with some dimension edits. One or the other will wait. In that case, you can't just use CubeSaveData to release the lock and allow the dimension edit to run. I hadn't thought of using SDA/CSD for a mid-transaction publish. I'd expect there to be a significant performance impact for large cubes though since it writes out the entire cube data to disk.

[paulsimon]

Hi

I would be wary of putting too many CubeSaveData or SaveDataAll's into code. It might be OK in a predominantly batch update environment which I would guess still exists where QML is, if he is still at the same client. However, I have currently taken on a system where there is a lot of data entry and lots of user triggered processes. If these used CubeSaveData there would be a high likelihood of concurrent attempts to write the same cube to disk. I haven't done much with CubeSaveData but it still works in the same way as SaveDataAll, ie it writes out a .cub$ file and if this completes, it deletes the .cub and renames the .cub$ to .cub. This means that you can only have one process issuing a CubeSaveData on the same cube at the same time. I don't' see that PI can do anything to avoid that. Certainly in 10.1.1 and 10.2.1 too many SaveDataAlls tended to lead to crashes or deadlocks.

The system that I am working on at the moment has so many interconnected cubes, it would be difficult to work out which cubes to save. Admittedly, if the system had been architected differently it would not need so many cubes and so many processes transferring data from one cube to another, but you have to start with what you are given.

I believe that the IBM recommendation is still to isolate a SaveDataAll into a separate Chore with no other processes. I am not entirely happy with this recommendation, as it does expose the system to the risk of data loss. However, it seems to be the only way.

I am still really looking for a statement that will commit changes and release locks, without having to wait for a process to actually end. Multi Commit Chores are a way around it but they can only run one process after another. There is no support for conditional calls, which is why I would prefer to be able to use a master process to call others. However, with a master process you have the issue that nothing is committed and no locks are released until the overall master process finishes. As QML says, CubeSaveData may do a form of commit, but then it is bound up with physically writing data to disk and that is not practical in user data entry systems.

I have 10.2.2 on my own system and I am starting to look at the ability to call TI from Java. This seems like something that I have been asking the TM1 developers to do for many years. Admittedly I would still prefer VB to Java but IBM are not going to do VB. I haven't found it yet but I would guess that there must be some sort of Commit that you can issue from Java since unlike a TI process a Java program can potentially run forever. Unfortunately the Java documentation is not great. At least it offers the prospect of a step thru debugger, find and replace, etc, which the TI editor doesn't offer.

Regards

Paul Simon

[BrianL]

I am still really looking for a statement that will commit changes and release locks, without having to wait for a process to actually end. Multi Commit Chores are a way around it but they can only run one process after another. There is no support for conditional calls, which is why I would prefer to be able to use a master process to call others.

True. However, any of the TI's included in the chore could be simple wrappers that check your condition and only call another TI if the condition is met.

I have 10.2.2 on my own system and I am starting to look at the ability to call TI from Java. This seems like something that I have been asking the TM1 developers to do for many years. Admittedly I would still prefer VB to Java but IBM are not going to do VB. I haven't found it yet but I would guess that there must be some sort of Commit that you can issue from Java since unlike a TI process a Java program can potentially run forever. Unfortunately the Java documentation is not great. At least it offers the prospect of a step thru debugger, find and replace, etc, which the TI editor doesn't offer.

IMHO, the TI java extensions are a big hack. Sure, you get to code in java and use the far more featured editors/debuggers/etc; BUT it's still bound by all the other limitations of TI. There are no new functions, all the usual TI functions are simply exposed to be called by java (sometimes with nicer parameter passing - like arrays). TI code is still the entry point into java, so you need to manage a TI and java code. The closest to "pure" java TI you can do, is to create a TI, define the datasource, then have each tab call a different java function. There is no change to what happens between tabs and there is no additional control over commit and/or locks.

[Steve Rowe]

Just curious why some of the posters want more control to hand craft commits and locks? What's the use case?

Where we are now is infinitely better than where we used to be and I've done things recently that would have been unimaginable a few years back. Admittedly it does require significant changes to how a TI is / was traditionally written.

Not sure what I'm missing...

Cheers,

[Duncan P]

IMHO, the TI java extensions are a big hack. Sure, you get to code in java ...

Here speaks a man who has never had to write a TM1 object/version management system with rule parsing, dependency analysis and documentation, all in TI.

This kind of stuff needs a programming language, not something with the same level of functionality (and editing support) as DOS batch script circa 1990.