Solved

Optimizing Cache Size

Posted on 2003-11-10
7
970 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Title # Comments Views Activity
SyBase SQL Syntax 4 662
connect to sybase then query 1 758
HOW to reterive specific datetime values from sybase iq 4 1,982
sql statement error 18 83
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

838 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