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

asked 23 Jan, 12:55

Bjarne%20Anker's gravatar image

Bjarne Anker
470191932
accept rate: 0%

edited 24 Jan, 09:35

Volker%20Barth's gravatar image

Volker Barth
31.7k323465680


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.

permanent link

answered 24 Jan, 08:58

Breck%20Carter's gravatar image

Breck Carter
27.2k460624897
accept rate: 21%

1

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...

(24 Jan, 09:32) Volker Barth

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

permanent link

answered 24 Jan, 09:38

Bjarne%20Anker's gravatar image

Bjarne Anker
470191932
accept rate: 0%

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...

(25 Jan, 07:52) Volker Barth
1

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.

(26 Jan, 07:33) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×219
×37
×23
×13

question asked: 23 Jan, 12:55

question was seen: 109 times

last updated: 26 Jan, 07:33