Question Re: Service parameters cache in Technology Q&A

Service parameters cache

Former Member — Tue, 23 Jan 2018 12:55:03 GMT

Hi.

Is there any point in setting the cache limits on a server with 24 Gb, running a database on approx. 11 Gb? I'm thinking about the -c -cl and -ch parameters. And what is the point in setting "-ca 0" to disable automatic cache resize? Is there any situation where you want to use this parameter?

Regards,

Bjarne Anker

Re: Service parameters cache

Breck_Carter — Wed, 24 Jan 2018 08:58:08 GMT

Here's an excerpt from this Foxhound 4 Help topic:

Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.

Re: Service parameters cache

VolkerBarth — Wed, 24 Jan 2018 09:32:54 GMT

That being said, using the defaults may be helpful to get to know how much maximum cache size the database server will use for typical workloads... So I recommend to study the according properties and/or the according console log messages, and use the results to specify the -c option value...

Re: Service parameters cache

Former Member — Wed, 24 Jan 2018 09:38:40 GMT

Thanks for the input guys.

I've started the service with automatic cache resize (no -ca 0 parameter) and the high limit for the cache (-ch) bigger than the databasesize (12 GB for a 9 GB database). I looks very promising.

Bjarne

Re: Service parameters cache

VolkerBarth — Thu, 25 Jan 2018 07:52:49 GMT

In my understanding, the default value for the maximum cache size on Win64 is 90 % of the total physical memory of the machine, which would be about 21,6 GB, so I'm not sure you have increased the maximum cache size... Note that cache is also used for temporary file space, so whether a "somewhat-more-than-dbsize" cache is optimal or not is difficult to tell without real workloads...

I would prefer to set the initial cache size because that would default to 25 % of the total RAM here, so only 6 GB. As stated, I can't tell whether that is fitting for you, but the properties can tell you so...

Re: Service parameters cache

Breck_Carter — Fri, 26 Jan 2018 07:33:15 GMT

FWIW Foxhound provides ongoing information related to cache usage:

cache size and usage: 13M, 0% of 14G
temporary file size, usage, fragments etc: Temp 2M 48.9% 5 322G C:\\Users\\Breck\\AppData\\Local\\Temp\\sqla0001.tmp
database-level temporary space usage, rollback log size and number of uncommitted operations
connection-level temporary space usage, rollback log size and number of uncommitted operations

See "Usage 2: Runaway Memory Usage" in the white paper here.