Optimizing Cache Size

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?  

jvvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:
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
jvvAuthor Commented:
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
namasi_navaretnamCommented:
Sorry, I do not know either. Hopefully someone else can help.
0
sybasetogoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AchetonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.