Tuesday, September 22, 2015

Using Ramdisk with ASO

I wanted to see what Essbase ASO performance would be like if not bound by disk. So I thought I'd start out by creating a ramdisk and storing my ess*dat files on it. First of all, ramdisk is just a file system that you create using RAM rather than a hard drive. So it's fast -- turbo-chicken-fast. If you understand what RAM is then you know that when the power is turned off on a machine you lose everything in RAM. Luckily machines and power and such are much more reliable these days so it's not a huge deal to use. I wouldn't, however, recommend going and creating a whole bunch of ramdisk in production and putting all of your cubes on it. You've got to have a good design for it and I'm not in that line of business so I won't get into all of that here.

The Disclaimer

I ran a few very basic tests using ASOsamp. I understand that not all hardware is created equal so my results might not match your results. This test serves as one point of data that will hopefully further our knowledge of how Essbase behaves in different environments. This test was run on

The Digression

I run Essbase on Linux. It's very easy to create a ramdisk on Linux. I used the following command:

mount -t tmpfs -o size=1024m tmpfs /mnt/ramdisk

In order to have the database use the ramdisk, I changed the ASOsamp.Sample storage settings in EAS to point the default and temp tablespaces over to the /mnt/ramdisk directory.

The Test

My test data was generated using dbfill. The data I loaded filled the ess00001.dat file to 285MB. I captured the load times. I then ran queries in Excel using the default retrieve, zoom-in on products to bottom level, pivot products to columns, zoom-in on stores to bottom level. I then created aggregate views based upon the Essbase recommendations. I then ran the same queries again. Note: I did not use a compression dimension in the outline.

The Results

The Analysis

These results make a lot of sense to me and illustrate just how cool Aggregate Views are. Here are my thoughts on some of the results:

The Data Load isn't much different because Data Load seems to involve a high use of CPU. Anywhere that Essbase is CPU bound, the ramdisk isn't going help very much. I assume that when Essbase is writing the data that it's also doing a lot of checking to see that members have the correct names and exist in the outline and such.

The buffer commit is much faster under ramdisk because this seems to be a heavy disk use operation. The data is initially written to the /temp directory and then moved over to the /default directory. There probably isn't a lot of data validation going on during this step.

The queries without aggregations are virtually identical. When I watch server utilization during a long running Essbase query it's almost always CPU bound -- the ESSSVR process pegs a CPU at 100% most times. So this makes a lot of sense to me. Having the data file in memory doesn't help Essbase much since it's still got to do a lot of calculating.

The default agg is quicker under ramdisk as it must rely fairly heavily upon reads and writes. Again, it needs to write the Aggregate Views to the /temp directory and then move them over to the /default directory.

The last four queries are where the magic happens. Our query times with the views created effectively drop to instantaneous -- and you don't need super fast disk for this to happen. Aggregate Views are so cool! It only takes 12.55 seconds to create 13 views but that actually saves over 24 seconds on the Store dimension zoom-in. This shows how powerful Aggregate Views can be. We will really need the ability to create these in Hybrid as soon as possible.

The Conclusions

Super fast disk is great to have but it really only makes sense when you have disk bound operations. Costs and benefits need to be weighed as in most design and purchase decisions. The results might be different as the ASO size scales up so I'd encourage others to try to replicate this test with a larger data set. I need to get more RAM on my home server before I can do that.

The Question of the Day

Smart View or Excel Add-In?


Cameron Lackpour said...


Any thought of applying this to Hybrid and even BSO?

I think you'd see far greater impact on both, even Hybrid as it seems to read the .PAG file every single time for pushing the numbers to the temporary tablespace.


Cameron Lackpour

TimF said...

Absolutely, Cameron. I'm going to need to invest in some more RAM soon! Thanks for the comment and the idea. I just finished reading your chapter on Hybrid -- it was very informative about how Hybrid is handling things from an I/O perspective.