Performance drop between 2.0.9.1 and 2.0.9.15

[ScoobyM]

Hi all,

We are in the process of upgrading from 2.0.9.1 (tm1s.exe 11.8.00000.33) to 2.0.9.15 (tm1s.exe 11.8.01700.1) but are encountering performance issues with large datasets on the new version. All objects were copied from old to new so the cubes, dimensions, TI processes, etc. are identical on both versions.

The new server is higher spec than the old server.
Old
CPU: Intel Xeon E5-2660 v4 x 2
RAM: 128Gb
New
CPU: Intel Xeon Gold 6134 x 2
RAM: 150Gb

Small/medium data sets are OK and run more quickly - as expected with the improved server specs.
But large datasets - via ODBO (MDX queries) or large CSVs - take significantly longer.

One ODBO process takes c.6mins on the old version but c.7.5hrs on the new one! The source is an external cube hosted by our EPOS provider and accessed via a web address. The MDX query, like the rest of the process, is identical in both versions.

Our largest CSV (c.16m rows) is located on the actual servers (2 identical copies at the moment, one on each server) and takes c.1hr on the old version but I killed it after c11.5hrs because it hadn't finished on the new one.

I have tried searching for this issue on the internet and have checked the cube properties with regards to VMM per IBM help pages but, as with all the objects, the cube properties are also identical for both versions. VMM is blank for all cubes. Nothing else jumps out on a Google search.

Both TM1 servers are virtualised and exist in the same server room, albeit on different physical servers, with the same internet connectivity utilised by the ODBO processes targetting the external cube.

Has anyone else experienced an unexpected drop in performance between these (or any) versions with large datasets or has any ideas of what might be causing it?

I appreciate that ODBO has been deprecated between those versions (surely the performance can't have tanked THAT badly), but we aren't in a position to be able to change the source in the short term.
But that doesn't explain the drop in performance loading from large CSVs located on the same server.

Any help or advice would be greatly appreciated.

Martin

[Wim Gielis]

What are you loading those data to ? Regular cube cells ? Cells in attribute cubes ?

Did you try limiting the TI processes to only an AsciiOutput ?
Is connecting to the source taking a long time or processing the data/metadata tabs ?

[JohnO]

We are in the process of upgrading from 2.0.9.1 (tm1s.exe 11.8.00000.33) to 2.0.9.15 (tm1s.exe 11.8.01700.1)

Start with this and change your plans: https://www.ibm.com/support/pages/node/6856457

In terms of your questions I recall there were changes around .12 or .13 regarding text file loads and unicode which caused issues. And changes for ODBC handling. Maybe they made changes for ODBO handling?

[ScoobyM]

Hi Wim.

Thanks for your input.

Data in both cases is loaded into regular cubes.

I haven't tried limiting the TI processes to ASCIOutput. Sorry for the naive question but, how would that help? The processes are identical in both old and new servers. They work as expected on the old server, but become unimaginably slow on the new server.

For the ODCO data source, both servers connect to the source in a similar time but things change drastically once the processes reach the Metadata and Data tabs. The old server processes c15-20k records/sec throughout until completion. The new server processes c.6-7k records/sec at first but this rapidly (within 5-10 seconds) drops to well below 1k records/sec.
The process updates 2 of the cubes 8 dimensions (to accommodate new elements) in the Metadata tab before loading the data in the Data tab.

For the large CSV, both servers access the CSV which is saved locally on the actual server - we have two identical copies of the data file, one on each server. The old server processes c5-6k records/sec throughout until completion. The new server processes c3-4k records/sec at first, but this also rapidly drops to well below 1k records/sec.
The process creates a view based on the dates included in the source data in the Metadat tab which is then zeroed out, then the Data tab just loads the data, albeit with a bit of logic wrapped around it.

[Wim Gielis]

I understand all of that, but it’s a repetition of what you wrote. If you want to narrow down the problem you will need to comment out code and see it in more detail using asciioutput or similar. On both machines. Then compare and narrow down even further if needed.

[Steve Rowe]

Just in case you've been misinformed somewhere, is there a reason you are going to 2.0.9.15? It's over a year old now, 2.0.9.18 is probably what you should be moving too.

Around the 15 release there was a lot going wrt to ODBC that may have impacted other areas.

Given that it's fairly trivial to flip to TM1 versions, I would just put 18 in place and re-test, rather than spending a lot of time figuring out what was wrong with an old release.

If you are on 18 and still have issues you'll get a lot more traction with IBM Support as well.

[ScoobyM]

Hi JohnO,

Thanks for bringing this to my attention.

[lotsaram]

I can only agree that there is absolutely no point in testing an upgrade to 2.0.9.15 as this is already a year old. Go with the latest release:

1. as you will get better support if needed from IBM or your BP rather than being helpfully asked to upgrade
2. if you are working for a organization with glacial IT and live systems seem to get frozen in place, then if you go through the effort of upgrading, it may as well be the latest possible release as the next upgrade likely won't be for a few years.

I also 2nd Wim's question that is very much depends on WHAT you are updating and not just via WHAT MECHANISM. 2.0.9.15 introduced a pretty bad bug where TI updates to any dimensions which had rule-based attributes became thousands of times slower (look up PH50635 in case it applies to your model.) I believe this was fixed in 2.0.9.17 or possibly even 2.0.9.16 but it still isn't as fast as it was before, there's still a noticeable drop in throughput if the model has rule-based aliases (especially if also localized) but maybe 10x slower not 1000x slower.

As for a OLEDB (ODBO) connection, this is something I simply wouldn't trust in a production environment. It has always been flaky at best and if performance has been degraded this isn't something in IBM will give any priority to addressing. You are much better off to change this to either CSV export/import or go direct to the source RDBMS (assuming it is SSAS) with ODBC (if possible and there aren't members and calculations which exist only in MDX.)