|
Is my observation of starting the database server dbsrv17 with the option -c 8g (for example) with -ca 0 not set, i. e. the automatic cache size tuning is enabled on a (physical) server with 32GB correct, that the database server is only assigning more memory when needed? In my test environment, running the application did not increase the cache size. It stays on 8462248, checked with SELECT PROPERTY('CurrentCacheSize'). If the server is a dedicated server running only some SQL Anywhere databases and the application server Tomcat, it seems to me the most performant way according to cache size setting. Would there be an advantage to set a static amount of cache with -ca 0? I am also wondering if there is a use for a -ch 80p (for example) setting in this case? Is it a safety setting then? I am on macOS Mojave. Regards, Robert |
|
-ca 0 turns off automatic tuning but doesn't disable the server's ability to grow the cache up to the -ch limit if it absolutely needs to (ie, if it would otherwise abort due to lack of free memory in the cache). If you don't use -ca, the server will adjust the cache size based on the needs of the server and available memory in the system as a whole. For example, if you use -cl 100m -c 1g and -ch 80g and don't set -ca 0 and you start a 200MB database, the cache will likely shrink to about 200MB. In another example with same cache parameters, suppose you start a 200GB database but only reference 200MB of it but the system as a whole is short on memory, dbsrv17 might give up cache memory down to perhaps 200MB because we're not using more than that and the system could really use it. In yet a third example with the same cache parameters, suppose you start a 100GB database and you are actively referencing 50GB of it but the system has tons of free memory (say 70GB). With -ca 0 the cache will stay at 1GB in size and database performance will be poor. Without -ca 0, we might grow the cache to 70GB because the memory is sitting there unused in the system and dbsrv17 could make good use of it. In short, you rarely want to use -ca 0 -- especially on a system shared with other memory intensive process. If you have a dedicated system for running just dbsrv17 you can set -c and -ch to the same value and not worry about dynamic cache sizing. Comment Text Removed
1
I received notice that at comment was posted that asked what happens if a query allocates a lot of temp space; however, I do not see that comment here. It's a good question. At runtime, the size of the temporary dbspace is included in "the size of the database". So, if you have a 10MB database and execute queries that generate 1GB result sets then, in the discussions above, the size of the database should be considered to be about 1GB (or 1.01GB if you want to be pedantic).
(17 Dec '20, 12:34)
John Smirnios
Very helpful explanations and examples, thanks a lot. The one thing I still wonder, do I need to set the -ch option or can I just go with the -c setting and leave the rest to the database server?
(18 Dec '20, 04:51)
robert
Replies hidden
1
The answer largely comes down to whether you are okay with the default which, for 64-bit platforms, is roughly 90% of total physical memory. If you are making, say, an address book app, embedding SQL Anywhere, you leave -ch unset, and a customer installs it on a machine with 1TB of RAM then the max cache size will be 900GB -- a bit large for an address book. Usually, there's no real impact to having that large of a max cache size. For a small database, the server just won't grow that large. Really, the server is reserving "address space", not "memory". On some UNIX systems (at least in the past -- we did what we could to mitigate the behaviour and I cannot recall for sure if this still happens), the max cache size could reserve space from your swap file but exactly how it behaves is dependent on the OS and its configuration. A customer might object to your address book reserving 900GB of swap space. Even if it is just an address space allocation (which is the typical behaviour and is not a demand on actual RAM or swap), some customers use tools that show the "memory size" of a process and report what is actually the address space size. Then you get tech support calls asking why your address book is using 900GB of RAM. Avoid that if you can. :) If you are installing SQLAnywhere on a mostly or completely dedicated machine as a server for your own business, the -ch default is probably a good one. If you are sharing a large machine with other memory-hungry services, you might want to adjust it to ensure SQLAnywhere never exceeds a certain footprint.
(18 Dec '20, 05:21)
John Smirnios
|
