Solved

Optimizing Cache Size

Posted on 2003-11-10
7
940 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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, I do not know either. Hopefully someone else can help.
0
 
LVL 4

Accepted Solution

by:
sybasetogo earned 63 total points
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now