Skip to main content
C
camagruder
Newcomer
December 15, 2021
Solved

Import Cache Page Size and Cache Pages-In-Memory Limits Best Practices

  • December 15, 2021
  • 2 replies
  • 0 views

We are looking at the performance of several huge base data imports.  What are the best practices for setting cache page size and cache pages-in-memory limits for the best possible import performance?    We have broken the data load up into >40 separate imports but we still have some imports taking over 40 minutes.  I am hoping there might be other ways to improve the import time without having to break the data imports up further.  

Best answer by NickKroppe

Hi,

As Jon mentioned, trial and error is almost always necessary. That being said, here are some general guidelines that are documented in the OneStream Foundation Handbook on pages 379-380.

 

Cache page size and cache pages in memory guidance:

  • Activate detailed logging on the import workflow to determine the exact step(s) in the import that are causing the performance bottleneck.
  • Default settings of 20,000 x 200 gives you a 4M record data processing in memory limit.
  • If you are getting timeouts on a larger data load - break up the load.
    • If it's not an option - increase the Cache Page Size to 50,000 or 100,000
  • More than 200K rows: Increase Cache Page Size to 50,000 or 100,000
  • 100K-200K rows: Leave the default settings alone.
  • 20K-100K rows: Decrease the Cache Page Size to 10,000
  • Less than 20K rows: Decrease the Cache Page Size to 2,000 and increase the Cache Pages-In-Memory Limit to 2,000

 

Nick Kroppe

OneStream Software

2 replies

T
OneStream Employee
TheJonGOneStream Employee
OneStream Employee
December 15, 2021

Perhaps someone else can provide a better method, but I have always used good ole trial and error to find the optimal settings. Keep adjusting until you find the sweet spot. I know this can be cumbersome on a 40 minute data load as you have to wait for it to finish each time but that's the only way I know.

N
OneStream Employee
NickKroppeOneStream EmployeeAnswer
OneStream Employee
December 16, 2021

Hi,

As Jon mentioned, trial and error is almost always necessary. That being said, here are some general guidelines that are documented in the OneStream Foundation Handbook on pages 379-380.

 

Cache page size and cache pages in memory guidance:

  • Activate detailed logging on the import workflow to determine the exact step(s) in the import that are causing the performance bottleneck.
  • Default settings of 20,000 x 200 gives you a 4M record data processing in memory limit.
  • If you are getting timeouts on a larger data load - break up the load.
    • If it's not an option - increase the Cache Page Size to 50,000 or 100,000
  • More than 200K rows: Increase Cache Page Size to 50,000 or 100,000
  • 100K-200K rows: Leave the default settings alone.
  • 20K-100K rows: Decrease the Cache Page Size to 10,000
  • Less than 20K rows: Decrease the Cache Page Size to 2,000 and increase the Cache Pages-In-Memory Limit to 2,000

 

Nick Kroppe

OneStream Software

    Terms & ConditionsAccessibility statement