SQL Anywhere 12.0.1 cache settings explained in layman terms

[former_member998316]

Hi all,

we are using SQL Anywhere 12.0.1 and need someone to explain the difference in the cache settings -c and -ch.

I have tried all official documents and done online searches but all they contain is the official explanation.

-c sets the initial maximum cache, and the minimum cache if -cl is not included.

-ch sets the maximum cache, just like -c, but doesnt affect the minimum cache.

If we want to set a different upper and lower limit on the cache, say 30GB and 40GB, is it better to set -cl 30G -ch 40G or -cl 30G -c 40G?

Thanks

The following is taken from the "dbsrv12 /?":

-c states "make initial cache a maximum of <size> bytes"

-ch states "set maximum cache size of <size> bytes"

The following is taken from "http://dcx.sybase.com/1201/en/dbusage/limiting-using-perform.html":

-c You can specify the initial cache size for the database server by using the -c database server option. If you do not specify the -c option, the database server calculates the initial cache allocation.

-ch You can control the maximum cache size by specifying the database server -ch option. The default is based on a heuristic that depends on the physical memory in your computer. On other non-Unix computers, this is approximately the lower of the maximum non-AWE cache size and 90% of the physical memory of the computer. [removed text relating to non Windows x64 OSes].

[VolkerBarth]

when will the cache drop from 35G to 30G?

Well, when the database engine thinks it should do so - please have a look at that doc topic: Dynamic cache sizing. According to that, it usually checks at least once every minute if a different size is reasonable. Note, you can also use option "-ca 0" to disable dynamic cache sizing.

The database console log (dbsrv12 -o ...) will show entries when and to which size the cache is resized.

I guess within the description of dbsrv -? for the -c option ("make initial cache a maximum of <size> bytes") the word "maximum" is related to the fact that you can specify a higher value that what is available as cache, so the engine tries to reserve that value as initial cache if it can get as much cache, which may have to do with how many RAM is already in use by the OS and other applications/processess.

[VolkerBarth]

For the record: I turned that comment into an answer just because I had the impression it would possibly go unnoticed by David otherwise.

[former_member998316]

Thanks Volker. The DBSRV12 /? description of -c "initial maximum" did confuse me.

Can you also define "initial"? In other words when does the initial cache size (-c) stop enforced?

For example if we set -c 35G -cl 30G -ch 40G when will the cache drop from 35G to 30G?

Thanks

[VolkerBarth]

-c sets the "initial cache size", not something like the "initial maximum cache size". (The docs are correct here, dbsrv12 -? seems somewhat irritating...), so the database server will try to reserve that much cache when it starts.

-cl and -ch define lower and upper bounds for the cache size which the server may use while it is running.If -cl is not specified, the value of -c will be used as lower bound.

If we want to set a different upper and lower limit on the cache, say 
30GB and 40GB, is it better to set -cl 30G -ch 40G or -cl 30G -c 40G?

If you want to specify a maximum size, you need to specify -ch (unless the value happens to meet the default maximum cach, as quoted in your question).

If you want to use a cache between 30G and 40G and want to start with 30G, I'd use: "-c 30G -ch 40G"

If you want to use a cache between 30G and 40G and want to start with 35G, I'd use: "-c 35G -cl 30G -ch 40G"

If you want to use a cache between 30G and 40G and want to start with 40G, I'd use: "-c 40G -cl 30G -ch 40G"