Introduction
The database management system is the name of the system which is used to manage databases. For example MySQL, Oracle, and PostgreSQL. It mainly implements tasks like data definition, data retrieval, and user administration.
DBMS architecture mainly has these parts: process manager, Client Communication Manager, Relational Query Processor, Transactional Storage Manager, and Shared Components and Utilities. I will only discuss the Process Manager and Client Communication Manager parts in this blog; the others will be included in the following articles.
Client Communications Manager
The client calls an API, this process will establish a connection between the client and the Client Communications Manager. Typically, it will be established via ODBC or JDBC connectivity protocol.
Process Models
*Operating System Thread: It is an OS program execution unit without additional private OS context and without a private address space.
*Operating System Process: It is an OS program execution unit with an address space private to it.
Several leading DBMSs are using the architecture described below, and also each of the leading database systems today is using the extension or enhancement of at least one of the models below at its core:
1. Process Per Connection
Database server mapped to one process for each client connection. This model is mainly supported by IBM DB2, PostgreSQL, and Oracle.
The advantage of it is easy to implement since the database server is mapped directly to OS processes. The OS protection facilities can help the programmer to solve some bugs like memory overruns. And also programming tools are well-suited to this model.
The disadvantages are it first needs OS-supported shared memory and is not good for scalable. Since the process will have more state than thread, it will use more memory.
2. Thread Per Connection
The thread per database server model is using a single multi-threaded process to host all the DBMS worker activity. Each client connection will allocate a new thread instead of a process, these threads will execute the corresponding SQL requests. The threads will run in the DBMS process. This model is used in some DBMS, including IBM DB2, SQL Server, MySQL, etc.
Since the threads are lighter weight than processes, it will use less memory than the first model. Correspondingly this model scales well to a large number of concurrent connections compares to the process per connection model.
But this model will also meet most of the multi-threaded programming challenges.
3. Process Pool
The process pool model is a variant of the process per connection model. Instead of giving a full process to one client connection, one central process will allocate one process from the process pool to the client connection when it sends a SQL request. After the SQL request is complete, that process will be returned to the pool and wait to be allocated to the next request.
Admission Control
As the workload in any multi-user system increases, throughput will increase up to some maximum. Beyond this point, it will begin to decrease radically as the system starts to thrash. The admission control will help the DBMS avoid thrashing.
It can be implemented in two places:
- In the dispatcher process: to limit the number of client connection
- In query processor: Execute after the query is parsed and optimized and before query execution to determine whether delay queries.
Thanks For Reading!