6 Replies Latest reply on Mar 31, 2017 2:00 PM by 2907697

    Low hit ratio on Data Cache


      I have a BSO cube which has a hit ratio of 0.02 right now. Yesterday it was 0.16. I'm not sure what has caused the hit ratio to drop so much in just one day.


      Here are the statistics:



      I know that the block size is bit too much and it will affect the hit ratio on cache. But just in one day hit ratio has dropped more than 10 % which makes me think there is something wrong with the cube other than the block size.


      Can anyone please explain what the problem could be?

        • 1. Re: Low hit ratio on Data Cache

          It might just be that users are querying data that is not in the data cache. (The users might have queried different data sets compared to today as well)


          How many pag files do you have on the system?

          • 2. Re: Low hit ratio on Data Cache

            There are 4 pag files.. I have attached the screenshot above. But I'm not sure why data cache hit ratio is so less. We have data cache set to ~43 GB which is more than enough for 7.5 GB .pag files.

            • 3. Re: Low hit ratio on Data Cache

              Sorry missed it. There is no need of giving 4.5 GB. Technically 0.125 of the total size of pag files.

              A maximum of 50 % of size of the pag files. Beyond that performance improvement is not noticed.


              This might also be a product bug of not providing accurate ratio. Have you noticed any delay with retrievals today compared to yesterday? If not it might be giving a wrong ratio.

              • 4. Re: Low hit ratio on Data Cache

                Its a new application which is not being utilized to the fullest as of yet. That's why the Data cache has been set to far more than what is actually required(.125*total size of pag files), to accommodate the expected number of pag files which which will increase over next few months.


                The only difference that I have noticed is, 3 days back the forecast copy from working to final was taking around 2.5 hours to complete. Yesterday it took more than 5 hours. I started looking at the statistics when I came to know about the delay of that script. The Hit ratio while the script was executing was 0.16 then it dropped to .13. Eventually the script completed after 5 hours. Today when I saw the statistics, the hit ratio dropped to .02. Now its back up to .13.


                Whether it is .02 or .13.. in both the cases, based on the cache settings we have, we should not be seeing such low values on hit ratio for data cache. I fear, this could be one of the potential reasons why all of a sudden our calc script execution time has doubled.

                • 5. Re: Low hit ratio on Data Cache

                  A couple of things to bear in mind:


                  1. You said that you "have data cache set to ~43 GB which is more than enough for 7.5 GB .pag files." - no, not necessarily, and definitely not in your case; remember that the .pag files contain compressed data blocks, the data cache contains uncompressed data blocks. If you are using bitmap compression, and given your very low block density, the uncompressed size of your 7m blocks in those .pag files is much, much more than 43GB (it's in the terabytes!).


                  2. Data can only come from the cache if it's already been loaded in. As I understand it (it would be easy to test) Essbase doesn't fill up the cache as soon as the application starts - even if the uncompressed data files are smaller than the cache. So for example, the first query after a restart would never come from the cache, no matter how large it is. That goes back to MPavan's point about users querying different slices of data potentially driving the hit ratio down.


                  I think routinely recording the metrics on a cube is a great idea but I don't think reviewing them looking for a problem (unless you actually have a performance problem) is worthwhile. I'd treat your calc performance problem as a calc performance problem and not get too hung up on the data cache hit ratio. Not saying it's not related, but with your block count and density, you are unlikely to find large calcs (e.g. version copies, full aggregations) being able to keep the data cache hit ratio up.

                  • 6. Re: Low hit ratio on Data Cache

                    That makes  sense. Thanks Pawan and Tim for helping me out.