Suppose that the database server is now multithreaded. Each thread handles one transaction at a time and there is a maximum number of threads. If all threads are busy serving requests, incoming transactions must queue for an available thread. An important tuning decision is to determine the the optimal number of threads for a given arrival rate so that the system SLAs are met.
In this situation, there are two opposing effects. One, called software contention, represents the time that a transaction needs to wait for an available thread. The other, called physical contention, is the time spent by a transaction waiting to use physical resources (e.g., CPU and disk).
As the number of threads m increases, software contention decreases but physical contention increases because more transactions will be contending for the CPU and disk. Thus, response time may increase or decrease as a function of m, depending on which factor (software contention or physical contention) dominates, as illustrated in Fig. 2.8. The picture shows two graphs of response time vs. number of software threads for two values of the average arrival rate (l1 and l2) where l1 < l2. The curve for l1 shows the optimal value, m*, for the number of threads that minimizes the average response time for l1.
Figure 2.8. Response time vs. number of threads for two values of the average arrival rate (l1 and l2).
Figure 2.9 illustrates the queue for m software threads. The figure also indicates the QN that models the physical resources used by the busy threads. Methods for solving QNs that represent software contention are given in [6, 8] and are presented in Chap. 15.
Figure 2.9. Database server with software contention.