Some months back I finished a paper with Joe Hellerstein and Michael Stonebraker scheduled to be published in the next issue of Foundations and Trends of Databases. This paper is aimed at describing how current generation database management systems are implemented. I’ll post a reference to it here once it is published.
As very small part of this paper, we cover the process model used by Oracle, DB2, MySQL, SQL Server, and PostgreSQL. A process model is how a database maps the work it’s doing on behalf of multiple concurrent users onto operating system processes and/or threads. This is an important design choice in that it has fundamental impact on the number of concurrent requests that can be supported, development costs, maintainability, and code base portability amongst other issues.
These same design choices are faced by most high scale server designers and is equally applicable to mail servers, web servers, app servers, and any other application needing to service large numbers of requests in parallel. Given the importance of the topic and it’s applicability to all multi-user server systems, it’s worth covering separately here. I find it interesting to note that three of the leading DBMSs support more than one process model and one supports four variants. There clearly is no single right answer.
Summarizing the process models supported by IBM DB2, MySQL, Oracle, PostgreSQL, and Microsoft SQL Server:
1. Process per DBMS Worker: This is the most straight-forward process model and is still heavily used today. DB2 defaults to process per DBMS worker on operating systems that don’t support high quality, scalable OS threads and thread per DBMS worker on those that do. This is also the default Oracle process model but they also supports process pool as described below as an optional model. PostgreSQL runs the Process per DBMS Worker model exclusively on all operating system ports.
2. Thread per DBMS Worker: This an efficient model with two major variants in use today:
a. OS thread per DBMS Worker: IBM DB2 defaults to this model when running on systems with good OS thread support. This is the model used by MySQL as well.
b. DBMS Thread per DBMS Worker: In this model DBMS Workers are scheduled by a lightweight thread scheduler on either OS processes or OS threads both of which are explained below. This model avoids any potential OS scheduler scaling or performance problems at the expense of high implementation costs, poor development tools and debugger support, and substantial long-standing maintenance costs. There are two sub-categories of this model:
i. DBMS threads scheduled on OS Process: a lightweight thread scheduler is hosted by one or more OS Processes. Sybase uses this model and began with the thread scheduler hosted by a single OS process. One of the challenges with this approach is that, to fully exploit shared memory multi-processors, it is necessary to have at least one process per processor. Sybase has since moved to hosting DBMS threads over potentially multiple OS processes to avoid this limitation. When DBMS threads within multiple processes, there will be times when one process has the bulk of the work and other processes (and therefore processors) are idle. To make this model work well under these circumstances, DBMSs must implement thread migration between processes. Informix did an excellent job of this starting with the Version 6.0 release. All current generation systems supporting this model implement a DBMS thread scheduler that schedules DBMS Workers over multiple OS processes to exploit multiple processors.
ii. DBMS threads scheduled on OS Threads: Microsoft SQL Server supports this model as a non-default option. By default, SQL Server runs in the DBMS Workers multiplexed over a thread pool model (described below). This SQL Server option, called Fibers, is used in some high scale transaction processing benchmarks but, otherwise, is in very light use.
3. Process/Thread Pool: In this model DBMS workers are multiplexed over a pool of processes. As OS thread support has improved, a second variant of this model has emerged based upon a thread pool rather than a process pool. In this later model, DBMS workers are multiplexed over a pool of OS threads:
a. DBMS workers multiplexed over a process pool: This model is much more efficient than process per DBMS worker, is easy to port to operating systems without good OS thread support, and scales very well to large numbers of users. This is the optional model supported by Oracle and the one they recommend for systems with large numbers of concurrently-connected users. The Oracle default model is process per DBMS worker. Both of the options supported by Oracle are easy to support on the vast number of different operating systems they target (at one point Oracle supported over 80 target operating systems).
b. DBMS workers multiplexed over a thread pool: Microsoft SQL Server defaults to this model and well over 99% of the SQL Server installations run this way. To efficiently support 10’s of thousands of concurrently connected users, SQL Server optionally supports DBMS threads scheduled on OS threads.
Most current generation commercial DBMSs support intra-query parallelism, the ability to execute all or parts of query in parallel. Essentially, intra-query parallelism is the temporary assignment of multiple DBMS workers to execute a SQL query. The underlying process model is not impacted by this feature other a single client connection may, at times, have more than a single DBMS worker.
Process model selection has a substantial influence on DBMS scaling and portability. As a consequence, three of the most successful commercial systems each support more than one process model across their product line. From an engineering perspective, it would clearly be much simpler to employ a single process model across all operating systems and at all scaling levels. But, due to the vast diversity of usage patterns and the non-uniformity of the target operating systems, however, each DBMS has elected to support multiple models.
–jrh
James Hamilton, Windows Live Platform Services
Bldg RedW-D/2072, One Microsoft Way, Redmond, Washington, 98052
W:+1(425)703-9972 | C:+1(206)910-4692 | H:+1(206)201-1859 | JamesRH@microsoft.com
H:mvdirona.com | W:research.microsoft.com/~jamesrh | Msft internal blog: msblogs/JamesRH