Sybase 12.4.2 manual Setting buffer cache sizes, 427, Methods of adjusting buffer cache sizes

CHAPTER 12 Managing System Resources

				Memory available
		Memory available		using "cooked" file
		using raw partitions		systems
	Amount
Memory Use	Used	Queries	Inserts	Queries	Inserts
Memory remaining for the main and		675	828	397	550
temp buffer caches
Main_Cache_Memory_Mb setting:		405	497	238	330
60% of memory remaining for
buffer caches
Temp_Cache_Memory_Mb setting:		270	331	159	220
40% of memory remaining for
buffer caches

aMinimum operating system use for Windows NT is 30MB

As shown in the table, you should have one set of values for your buffer caches when primarily inserting into the database, another set when primarily querying the database, each differing from a typical mixed load of inserting and querying. To change the cache sizes, see “Setting buffer cache sizes”. Remember that the cache size options do not take effect until you stop and restart the database.

Setting buffer cache sizes

By default, Adaptive Server IQ sets the size of the main and temporary buffer caches to 8MB and 4MB respectively. Most applications will require much higher values (limited by the total amount of physical memory).

Several options and server switches can affect buffer cache sizes:

	Table 12-2: Methods of adjusting buffer cache sizes
		How long the setting is	For more
Method	When to use it	effective	information, see
MAIN_CACHE_MEMORY_	Normal way to set buffer	From the next time the	“Setting buffer cache
MB and	cache sizes. Database	database is restarted, until	size database options”
TEMP_CACHE_MEMORY_	must be open to set these	you reset these options, or	on page 428
MB database options	values.	temporarily override them
		with server switches

427