The DisclaimerI 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 18.104.22.168.002.
The DigressionI 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 TestMy 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 AnalysisThese 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.
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.
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.
Post a Comment