Environment

Understanding of Usage

Your TM1 Application Server (JAVA) memory is impacted by the following applications:

• TM1Web (viewing/exporting data)
• PMPSVC (viewing/exporting data)
• Planning Analytics for Excel (PA for Excel, which uses the PMHub service on the TM1 Application Server)
• Planning Analytics Workspace (if used to display PA for Excel/Perspectives reports)
• Operations Console (obtaining server status/logging data)
   

As much as one would like to know what the best settings are or how much memory is required to host the TM1 Application Server, the answer does not come easy.  The following questions must be taken in to account and even with answers to the following questions, only load/stress testing can provide an accurate estimate to the resources required for your specific load:

• How many concurrent users are expected?
• What application(s) are users expected to use?
• Are users doing reporting only or is data entry included?
• How much data is to be consumed by a user?  (how many rows/columns/cells)
• How much data is expected to be exported?
• Do web/excel books contain more than one tab?  (is it really 1 report, or 20 reports under 1?)
   

Applications Settings / Governors

To get a better handle on your TM1 Application Server memory management, the below safeguards/settings should be introduced.  It is recommended to implement more restrictive safeguards, raising the restrictions as required and where it makes sense to do so.

TM1Web (via /tm1_64/webapps/tm1web/web-inf/configuration/tm1web_config.xml):

<!-- If specified, sets a limit for the maximum number of cells in the workbook file. It will not be possible to open workbooks with a number of cells that exceeds this limit. Leaving this blank will allow all workbooks to be opened. -->
<add key="WorkbookMaxCellCount" value="500000" />

<!-- If specified, sets a threshold for limiting of exports using the number of cells. If the set threshold is exceeded when performing an export (looped exports will reach this multiplicatively), then the export will not be allowed for that user. Is applied for all users, and for  Websheets and CubeViewer. Leaving this blank will allow for classic-style exporting with no cell count limitations. -->
<add key="ExportCellsThreshold" value="1000000" />

<!-- MaximumSheetsForExport: Maximum number of sheets allowed to Export -->
<add key="MaximumSheetsForExport" value="50" />

<!-- Determines the maximum number of exports that can occur concurrently. If less than or equal to zero, will allow virtually unlimited concurrent exports (2147483647 max). The default value is 5. -->
<add key="MaximumConcurrentExports" value="4" />

<!-- CubeViewerRowPageSize: Number of rows to fetch in a page of cubeviewer -->
<add key="CubeViewerRowPageSize" value="100" />
    
<!-- CubeViewerColumnPageSize: Number of columns to fetch in a page of cubeviewer -->
<add key="CubeViewerColumnPageSize" value="20" />
 

Planning Analytics for Excel (via Options Menu):

• Application Settings:
  • Auto spread consolidated input, recommended value: disabled
    •   If not required, disable this setting to prevent users from accidentally spreading
    •   If spreading is allowed, ensure users are educated so that a spread is not attempted on an intersection that spans too many cells
• Exploration or list settings:
  •  Data display row limit, recommended value: 5000
    •   Reduce the number of rows displayed to ensure a larger than necessary query is no executed
  •  Expand member limit, recommended value: 2000
    •   Reduce the number of rows displayed to ensure a larger than necessary query is no executed
• Planning Analytics for Excel Design Recommendations:
  • Ensure that Zero Suppression is enabled where possible, to prevent larger than necessary queries
  • Do not include too many sheets/tabs in a single book, create multiple reports where possible
  • Ensure the Excel Sheet does not contains many unnecessary blank/empty columns/rows

Tuning the JAVA Process

The TM1 Application Server sits on a Websphere Liberty Application Server, which utilizes the JAvA platform.  The details in this section can assist you with further tuning of the JAVA Process for the TM1 Application Server, however as mentioned earlier in this document - only a proper test/load plan can ensure a successful deployment.  

To fine tune the TM1 Application Server's JAVA Process, edit the jvm.options file (found in \tm1_64\wlp\usr\servers\tm1).  Consider adding the following entries, below the existing jvm.options settings (or updating any settings that may already exist).  Values should be adjusted accordingly to available resources, for example - don't allocate an Xmx of 24576m if you have less than 32GB of RAM on the server.

• -Xmx12288m
  • Xmx specifies the maximum memory allocation pool (heap) for a Java Virtual Machine (JVM)
    • Xmx is more commonly set using Cognos Configuration.  Changes to Xmx in the jvm.options file will take precedence.
    • In PA 2.0.5 and lower (using JRE7), Xmx should NOT exceed 24576m, as additional performance problems may be encountered.
    • In PA 2.0.6 and higher (using JRE8), Xmx should NOT exceed 57344m, as additional performance problems may be encountered.
• -Xms12288m
  • Xms specifies the initial memory allocation pool on process startup.
  • Xms should reflect the same value specified by the Xmx parameter.
• -Xmn6144m
  • Xmn sets the size of the new (nursery) heap.
  • Xmn should be set to half of the value specified by the Xmx parameter.
• -Xgcpolicy:balanced
  • A garbage collection policy of balanced is recommended when the Xmx exceeds 8192m
  • Depending on application usage, a different garbage collection policy may prove beneficial to performance
    • If using TM1Web, a policy of balanced is recommended
    • If Planning Analytics for Excel is the only application using the TM1 Application Server, a policy of gencon may be better suited
      • Usage of the application, size of reports, number of users all come in to effect.  Both policies should be tested accordingly.
• -Xverbosegclog:${bin_path}/verbosegc_%Y%m%d.%H%M%S.%pid.log,1,20000
  • Enabling Verbose Garbage Collection logging is recommended
  • The performance impact of having the logging enabled is negligible
  • Should any problems arise with the JAVA process, the output of the Verbose GC logs may prove beneficial to understanding root cause

Using the settings above, the entries in the jvm.options file will look like:
  -Xmx12288m
  -Xms12288m
  -Xmn6144m
  -Xgcpolicy:balanced
  -Xverbosegclog:${bin_path}/logs/verbosegc_%Y%m%d.%H%M%S.%pid.log,1,20000