Monday, August 18, 2008

Locking the ILM Database

Seems that most blog entries trumpet the brilliance of the blogger. This post, not so much.

In short:
never create files in the .\Extensions folder unelss you have a VERY good reason. Doing so can make ILM very busy since it watches this folder for changes. Anytime the folder contents change, ILM sucks up the folder contents into the ILM database. Do it a couple times and you'll probably get away with it. Do this a lot, and you'll see locks and retries, and things will perform miserably. Make it happen on every password set operation, and you're just asking for it.

Longer story:
My favorite tool is my handy XMA and this time I have managed to use it to bring ILM to its knees. Oops. An XMA I'm working on was happily running imports, exports and password sync but the constraints changed forcing me to do some of the import work during my export routines. This work introduced a neat little bug which I'll desribe here in hopes of somebody not doing the same thing. If I get to do another ILM extensibility talk next year, I think this little nugget will make my 'worst practices' slide.

So my Export routines had to be changed to produce the Import file (another blog post). All worked fine, so it seemed. The exports ran and did their work while also producing the delta import file.

Things started to go wrong when we saw another file appear in the Extensions folder. A search of the Export code turned up no reference to this file, so I thought my XMA was innocent. Also, all of our tests were passing without issue. Performance was a little slow at times on perf tests but we could easily blame the web service since it was the obvious choke point. A while later, we saw this file getting created so much that it had to be generated by my XMA.

Another look at the code and pouring over trace logs revealed the file was being created by our XMA's password extension. Even worse, it was getting created on every password set. So if created 5000 objects, that file was getting created 5000 files, which was asking ILM to copy the Extensions folder to the ILM database 5000 times. Bob Tucker was kind enough to show me the SQL Profiler log indicating that this was happening, A LOT.

The fix was simple, but the lesson an embarassing one. No trumpeting for me, just a dunce cap this time around.


David Lundell said...

Go Bob! Putting SQL Profiler to work.

Go Craig! Thanks for showing this issue. This is quite interesting. Thanks for having the humility to show your flubs.

Joe Stepongzi said...

Thats why we have an MAData

Profiler is a true ILMn'ers best friend.....