Several options can have a major impact on database server performance, including:
Cache size The amount of cache memory available to the database server can be a key factor in affecting performance. The database server takes an initial amount of cache memory that is either specified by the -c option or is a default value.
The -c option controls the amount of memory that SQL Anywhere uses as a cache.
Generally speaking, the more memory made available to the database server, the faster it performs. The cache holds information that may be required more than once. Accessing information in cache is many times faster than accessing it from disk. The default initial cache size is computed based on the amount of physical memory, the operating system, and the size of the database files. On Windows and Unix operating systems, the database server automatically grows the cache when the available cache is exhausted.
The database server messages window displays the size of the cache at startup, and you can use the following statement to obtain the current size of the cache:
SELECT PROPERTY( 'CacheSize' );
For more information about performance tuning, see Improving database performance.
For more information about controlling cache size, see -c server option.
On Windows and Unix, the database server automatically takes more memory for use in the cache as needed, as determined by a heuristic algorithm. See Using the cache to improve performance.
You can use database options to configure the upper cache limit. See -ch server option.
As well, you can force the cache to remain at its initial amount. See -ca server option.
Multiprogramming level The database server's multiprogramming level is the maximum number of server tasks that can execute concurrently. In general, a higher multiprogramming level increases the overall throughput of the server by permitting more requests to execute simultaneously. However, if the requests compete for the same resources, increasing the multiprogramming level can lead to additional contention and actually increase transaction response time.
In some cases, increasing the multiprogramming level can even lower the system's throughput. You can set the server's multiprogramming level with the -gn option. See -gn server option and Setting the database server's multiprogramming level.
Number of processors If you are running on a multi-processor computer using a network database server, you can set the number of processors with the -gt option. See -gt server option and Threading in SQL Anywhere.
The number of CPUs that the database server can use may also be affected by your license or SQL Anywhere edition. See Editions and licensing.
Other performance-related options There are several options available for tuning network performance, including -gb (database process priority), and -u (buffered disk I/O). See The SQL Anywhere database server.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|