Solved

Optimizing Cache Size

Posted on 2003-11-10
7
975 Views
Last Modified: 2008-03-17
I am busy tuning performance of a large database. (5Gb)
What will be the best cache size? I have 1Gb Ram.
Is it a good idea to take the maximum cache size of 90%?
There are no other processes on this machine, so it only run that dbsrv6.exe. Yes, still version 6 :-((

Or is it better to have a smaller cache, and leave more memory for queries to be prcessed?
I've read somewhere that it is a good idea to take 25% of memory, so 256Mb. Is that true?  

0
Comment
Question by:jvv
7 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9715304
This is the Sybase recommendation:

The initial, minimum, and maximum cache sizes are all controllable from the database server command line.

Initial cache size     You can control the initial cache size by specifying the database server -c command-line option. The default value is as follows:

Windows CE     The formula is as follows:

max( 600K, min( dbsize , physical-memory ) )
where dbsize is the total size of the database file or files started, and physical-memory is 25% of the physical memory on the machine.

Windows NT, Windows 95/98, NetWare     The formula is as follows:

max( 2M, min( dbsize , physical-memory ) )
where dbsize is the total size of the database file or files started, and physical-memory is 25% of the physical memory on the machine.

UNIX     At least 8 Mb.

For information about UNIX initial cache size, see Dynamic cache sizing (UNIX).

Maximum cache size     You can control the maximum cache size by specifying the database server -ch command-line option. The default is based on an heuristic that depends on the physical memory in your machine.

Minimum cache size     You can control the minimum cache size by specifying the database server -cl command-line option. By default, the minimum cache size is the same as the initial cache size.

You can also disable dynamic cache sizing by using the -ca command-line option.

------------------------------------------------------------------------------------------------------------

Here is some info on UNIX Dynamic Caching

Dynamic cache sizing
Adaptive Server Anywhere provides automatic resizing of the database cache. The capabilities are different on different operating systems. On Windows NT, Windows 95/98, and UNIX operating systems, the cache grows and shrinks. On other operating systems, the cache can increase in size, but not decrease. Details are provided in the following sections.

Full dynamic cache sizing helps to ensure that the performance of your database server is not impacted by allocating inadequate memory. The cache grows when the database server can usefully use more as long as memory is available, and shrinks when cache is not required, so that the database server does not unduly impact other applications on the system. The effectiveness of dynamic cache sizing is limited, of course, by the physical memory available on your system.

Dynamic cache sizing removes the need for explicit configuration of database cache in many situations, making Adaptive Server Anywhere even easier to use.

There is no cache resizing on Windows CE or Novell NetWare

HTH

Namasi
0
 

Author Comment

by:jvv
ID: 9715581
I've read the manuual, but I want to know the answer from experienced users.

+++++++++++
Windows NT, Windows 95/98, NetWare     The formula is as follows:

max( 2M, min( dbsize , physical-memory ) )
where dbsize is the total size of the database file or files started, and physical-memory is 25% of the physical memory on the machine.
+++++++++++

That's what the online help days, but I do not believe that.
I have a Win2K server with 1Gb RAM. I can easily set the cache to -c 760M and that works. But I do not know what is the optimal cache size.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9715743
Sorry, I do not know either. Hopefully someone else can help.
0
 
LVL 4

Accepted Solution

by:
sybasetogo earned 63 total points
ID: 9751616
I would really try ASE.
In general, run your system without database and see how much memory consumed.
Add static overhead required for ASA software to be loaded and rest of ASA memory structures.
You can determine that as difference between memory used and cache configured for ASA process.

Make sure you are not exeeding physical memory in any case.
0
 
LVL 2

Assisted Solution

by:Acheton
Acheton earned 62 total points
ID: 9914348
If you are optmising for performance we have found that the factor which makes the largest difference is not cache size or processor power but page size. What page size are you running with?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Read  the original post  on Monitis Blog. Hi.  My name is Erik Dietrich, and this is the first time I’ve posted on the Monitis blog.  By way of introduction, I thought it would make sense to talk about my initial experience with Monitis.   Befo…
Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question