This post was originally written by Glenn Paulley and published to his blog on Sybase.com in April of 2008
When a database system is embedded in an application as part of an installable package, it cannot normally use all of the machine's resources. Rather, it must co-exist with other software and system tools whose configuration and memory usage vary from installation to installation, and from moment to moment. It may be possible to perform workload analysis of the application to determine database configuration parameters such as the server's multiprogramming level, but it is difficult to predict the system load or the amount of memory that will be available at any point in time.
One of the issues I mentioned in an earlier post is that in a self-managing database system, the entire system architecture must be centered around the self-management concept. That is, it is difficult to provide self-management technology "add-ons" when the underlying system architecture cannot effectively support them. As an example, with SQL Anywhere, the server's memory architecture is what makes a number of other self-management technologies possible. 
SQL Anywhere uses the following approach to buffer pool management: rather than attempting to 'tune' buffer pool memory in isolation, the server tunes buffer pool allocation on-the-fly to fit the overall system requirements. It does this by using a feedback control mechanism with the OS working set size as one input (see figure). The OS working set size, which is polled every minute, is the operating system's amount of real memory in use by the process. Using this feedback control loop, the server's buffer pool grows or shrinks on demand, depending on system-wide resource usage and the memory management policy of the operating system.In addition to the two reference inputs in the feedback control loop the OS working set size and the amount of free physical memory the buffer pool manager also monitors the buffer miss rate. If there are no buffer pool misses between polling times, the buffer pool governor will not permit the buffer pool to grow. A lack of buffer pool page replacements may mean the server is largely idle, or that the working set of database pages is entirely resident in the buffer pool; either situation makes it unnecessary to increase its size. However, the buffer pool is always allowed to shrink, regardless of buffer pool activity, if the new target buffer pool size is smaller than the current size.
The novel thing about SQL Anywhere is that buffer pool pages resident in memory are used for four types of objects:
(1) data structures that represent catalog objects (tables, views, stored procedures);
(2) heap memory used to store data structures for requests, connections, and connection-specific objects;
(3) query memory, working memory used for memory-intensive operators such as sorting and duplicate elimination; and
(4) database pages themselves (tables, indexes, materialized views).
The buffer pool manager determines dynamically how much memory of each type to keep in the buffer pool, depending on the current workload and the types of requests being executed. The heterogeneous contents of the pool is the reason that all pages in a SQL Anywhere database have to be the same size. The memory governor, in particular, apportions query memory to a select number of memory-intensive requests - if this number is exceeded, new memory-intensive requests are queued to prevent swamping the server with (conflicting) query memory demands. SQL Anywhere also permits heap memory to be paged to the temporary file. This permits the server to swap out waiting connections, lowering overall memory demand.
The variability of the buffer pool size has implications for both query optimization and query execution. In such an environment, it makes little sense for fixed (static) access plans (think DB2 packages), since the server's operating context may change from moment to moment. Hence queries need to be optimized frequently, if not each and every time the server receives a request. That means that the optimization process must itself be cheap, so that the overhead of optimizing each query is as little as possible. Query execution plans themselves must, in turn, adapt to execution-time changes in the amount of available physical memory. Consequently, SQL Anywhere access plans permit the dynamic adjustment of physical access methods and some query processing operators at run time, either in response to a change in the server's operating environment, or an estimation error by the optimizer.
In summary, SQL Anywhere's architecture permits dynamic adjustments to a variety of query processing mechanisms that work in tandem, with little or no tuning by the DBA: no need to allocate specific buffer pools for sorting, for example. These mechanisms automatically adjust for changes to both server operating context and server workload, and permit the server's buffer pool to grow and shrink as required.
- SAP Managed Tags:
