New Features and Upgrading Guide
CHAPTER 3. New Features in Adaptive Server Anywhere 6.0
Previous releases of this software, most recently SQL Anywhere 5.5, are multi-user database management systems with full support for concurrent use by multiple connections. This section describes what is changed in Adaptive Server Anywhere to enable multi-processor support and improved multi-tasking.
SQL Anywhere Version 5 and other previous releases of the software have been able to handle multiple connections concurrently, but query processing has been carried out on only a single operating system thread. Consequently, even on multi-processor machines, only a single processor was used for query processing.
The data processing engine in Adaptive Server Anywhere can now run using multiple operating-system threads, and hence can take advantage of multiple processors. By default, Adaptive Server Anywhere uses the same number of operating system threads as the machine has CPU's.
Multi-processor support in Adaptive Server Anywhere enables transactions from separate connections to run simultaneously on separate CPU's. A single connection uses a single thread, and no single requests are split among CPUs.
Goals of multi-processor supportMulti-processor support is implemented primarily to improve the performance of multi-user databases under a heavy load. It increases the size of database, number of users, and workload that Adaptive Server Anywhere can handle.
Larger databases Workgroup and personal databases are becoming larger and larger. SQL Anywhere Version 5 has been used with databases of up to several GB. Multi-processor support raises the size of database that can be effectively handled.
More users As requests from different connections can be processed on different processors, more users can be handled by Adaptive Server Anywhere Version 6 than by Version 5, or multiple threads can be used from a single application.
Faster response In any environment with more than one user, the response time for each user will be improved, as requests can be handled in parallel.
Larger SQL Remote installations In many SQL Remote installations, the performance bottleneck is the application of messages from remote users at the consolidated database. The SQL Remote Message Agent can use multiple worker threads, each applying messages simultaneously. Adaptive Server Anywhere can process these threads in parallel.
In short, multi-processor support raises the size of system that Adaptive Server Anywhere can handle.
How threading worksTo understand how multi-processor support works, you need to understand threads and requests.
Suppose Adaptive Server Anywhere is being used concurrently by two users, or two connections from a single application. Each connection submits a query (or other SQL statement) to Adaptive Server Anywhere. Each of these SQL statements is a separate request to the server.
Internally Adaptive Server Anywhere, like previous versions of the software, has the ability to schedule these requests in a round robin fashion to handle multiple connections concurrently.
The figure shows a schematic illustration of two requests being processed by a single thread.
Unlike SQL Anywhere Version 5, Adaptive Server Anywhere can also take advantage of machines that have several CPU's. Requests to Adaptive Server Anywhere are shared out among the available threads, and these operating system threads can run on the different processors at the same time.
For example, with three tasks and two threads you may get an execution schedule as follows:
No single request can be running on more than one thread at a time, but a request is not tied to a single thread. Thus, over time the load is shared evenly among the available threads.
On UNIX machines, each task is its own thread. That is, each thread runs just one task.
Controlling threading behaviorThere are three command-line options for the database server that control threading behavior. Not all are needed on every platform.
-gn This controls the number of tasks.
-gx This controls the number of threads. On UNIX, where each task is its own thread, this is not needed. The number of threads is always the number of tasks.
-gt The number of CPUs to use. If you have more than one CPU, you can control how many CPUs the threads exploit. By default, all CPUs available on the machine are used.
Threads in SQL Anywhere Version 5The scheduling of individual requests is a form of internal threading. In SQL Anywhere Version 5, the word threads was used to describe this internal scheduling. However, there was no multi-processor support for query processing in Version 5. The THREAD_COUNT option in Version 5 described the number of requests that could be processed concurrently.
SQL Anywhere Version 5 did use separate threads to handle network connections. However, the query-processing engine was not multi-threaded. In Version 6 the CPU-intensive query processing tasks are multi-threaded.
Impact on single-processor machinesThe changes that have been made to enable multi-processor support also have an impact on single-processor machines.
Databases being used by a single connection will see little effect from the multi-processor changes.
Multi-user databases on a single CPU will see two main effects:
More efficient multi-tasking The extra information that is stored for each execution thread enables switching between threads with less work than before. This makes for more efficient multi-tasking.
Increase in peak memory requirements As the execution threads are more independent from each other, more information needs to be held for each thread, as opposed to being held in a common place. This can lead to an increase in peak memory requirements. Each active request uses more memory than it did in version 5.