Tuning the dbCacheSize in Operations Manager 4.0

[reide]

The Operations Manager Best Practices Guide recommends setting the "dbCacheSize" parameter to 1024 (1 GB of memory) to improve performance by reducing disk access on the Ops Mgr server.

          dbCacheSize: amount of memory, in MB, to allocate to the database cache. In general, a larger cache gives better performance.

However, there is no way to tell what the default "dbCacheSize" is or how much of the cache is utilized.

- What is the default size of the dbCacheSize when Operations Manager is installed?

- Is there a way to report on cache utilization (to determine if your cache is too big or small)?

- Is there a way to report on cache hit/miss rates?

[benjstarratt]

I'm curious about the answer too.  Thanks for raising the subject.  My Ops Mgr 4.0 server has 4 GB of RAM with more than 2 GB free.  The dbsrv10.exe & dfmserver.exe processes together use about 250MB of RAM.  If the GUI would be more responsive with a larger database memory size--I would certainly appreciate it.  I tried the dfm database get dbCacheSize command and it returns no value for the option which I would guess is what you are seeing also.

[reide]

benjstarratt,

If you've got that much RAM available and free, I'd strongly suggest you set your dbCacheSize to at least 1024.  Clearly you can see that the database is not consuming that much RAM today, so you are not full utilizing the RAM available to your Operations Manager server. This caching helps to avoid going to disk to get information from the DFM database.  The improvement in GUI performance will depend on how many disk accesses are avoided by using the larger cache.  In small DFM environments, you may not notice the difference.

Make sure you stop the dfm services before you change the dbCacheSize.

# dfm service stop

# dfm database set dbCacheSize=1024

# dfm service start

[rshiva]

Hi all,

Have you tried to check the sybase.log file? It's located in the log folder under the installation directory of DFM server. This value keeps changing when db check point happens:

I. 10/18 05:44:08. 1 physical processor(s) detected.
I. 10/18 05:44:08. Per-processor licensing model. The server is licensed to use 232 physical processor(s).
I. 10/18 05:44:08. This server is licensed to:
I. 10/18 05:44:08.     DFM User
I. 10/18 05:44:08.     NetApp
I. 10/18 05:44:08. Running Linux 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 on X86 (X86_64)
I. 10/18 05:44:08. Server built for X86 processor architecture
I. 10/18 05:44:09. Authenticated Server licensed for use with Authenticated Applications only
I. 10/18 05:44:09. 14696K of memory used for caching
I. 10/18 05:44:09. Minimum cache size: 8192K, maximum cache size: 1834880K
I. 10/18 05:44:09. Using a maximum page size of 8192 bytes

This value keeps changing when for every DB checkpoint process:

I. 10/18 05:44:21. Now accepting requests
I. 10/18 05:44:25. Cache size adjusted to 14016K
I. 10/18 05:44:26. Cache size adjusted to 13896K
I. 10/18 05:45:36. Cache size adjusted to 20152K
I. 10/18 05:49:22. Cache size adjusted to 22952K
I. 10/18 05:50:12. Starting checkpoint of "monitordb" (monitordb.db) at Mon Oct 18 2010 05:50
I. 10/18 05:50:14. Finished checkpoint of "monitordb" (monitordb.db) at Mon Oct 18 2010 05:50

Now, I've assigned cache size of 1 GB to DFM running on a VM with 2 GB of memory. Here's the portion of sybase log:

I. 10/19 15:06:57. Running Linux 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 on X86 (X86_64)
I. 10/19 15:06:57. Server built for X86 processor architecture
I. 10/19 15:06:57. Authenticated Server licensed for use with Authenticated Applications only
I. 10/19 15:06:57. 1048576K of memory used for caching
I. 10/19 15:06:57. Minimum cache size: 1048576K, maximum cache size: 1834880K
I. 10/19 15:06:57. Using a maximum page size of 8192 bytes
I. 10/19 15:06:57. Starting database "monitordb" (/opt/NTAPdfm/data/monitordb.db) at Tue Oct 19 2010 15:06
I. 10/19 15:06:59. Transaction log: monitordb.log

As you can notice - It affects the minimum cache size. So If the requirement has to go beyond 1 GB - Then the value would be changed dynamically.

Hope that helps

Thanks and regards

Shiva Raja

[reide]

Thanks Shiva.

So it appears that by default Operations Manager lets Sybase dynamically size its cache.  I found it odd that the maximum cache size was 1,834,880K (1.75 GB).   I wonder if that is a hard limit for Sybase, or if its based on the amount of available RAM on your particular system (2 GB RAM - OS and other).

I think the correct answer here is to read-up on the Sybase SQL Anywhere ver. 10 documentation.  Its likely going to tell me what I'm looking for, and have utilities that can report on utilization and hit/miss rates.

Reid

[pascalduk]

Even on my linux machine with 16 GB RAM the cache does not go beyond 1.75 GB.

[adaikkap]

That's because dfm is a 32 bit application and each process or service of dfm cant use more than 2G.

Regards

adai

[reide]

This is more information about the DFM database cache than you probably want to know....

When the DFM option dbCacheSize is set to the default value of (null), SQL Anywhere uses its dynamic cache sizing.  This feature dynamically grows the cache as its needed (which is good).  However, it also shrinks the cache if other applications on the server are requesting memory (which is bad).  Generally, dynamic cache sizing assesses cache requirements at the rate of approximately once per minute. If it determines a lot of new data has been inserted into the database, the cache may be resized every five seconds for thirty seconds. To avoid cache size oscillations, the database server increases the cache size incrementally. Rather than immediately adjusting the cache size to the target value, each adjustment modifies the cache size by 75% of the difference between the current and target cache size.

The query processing engine of the database uses available cache memory to process queries in-memory for faster responses.  If it determines there is not enough free space in the cache, it resorts to much slower disk-based processing. 

This is why NetApp Best Practice is to set the dbCacheSize to at least 1024 (1 GB RAM). Doing so tells SQL Anywhere to reserve 1 GB of system memory just for cache and disables dynamic cache sizing.  This prevents the cache from shrinking due to other processes on the server requesting memory.  In most situations, it provides enough excess cache memory to support in-memory query processing.  In addition, the database doesn't have to wait for the cache to incrementally grow over time.  The cache is available as soon as the database starts.

The document "Diagnosing Application Performance Issues with SQL Anywhere" does outline how to monitor database cache utilization.  However, this is not a functionality provided by DFM or NetApp, so you're on your own here.

As mentioned in other parts of this discussion, the maximum cache size appears to be 1.75 GB of RAM.

Unfortunately, I do not have a DFM environment large enough in my lab to show any noticable performance difference after setting the cache to 1 GB of RAM.  I may be able to provide this later this year as I work with some customers.