Architecture of a database management system

-

There’s no common blueprint for database management system design. Every database is built slightly differently, and component boundaries are somewhat hard to see and define. Even if these boundaries exist on paper (e.g., in project documentation), in code seemingly independent components may be coupled because of performance optimizations, handling edge cases, or architectural decisions.

Hence in this article I will be defining a common architecture (component + interfaces between them) of DBMS, that I believe every DB vendor thrive for.

DBMS Architecture

Database management systems use a client/server model, where database system instances (nodes) take the role of servers, and application instances take the role of clients.



DBMS Components and their Interaction

So, it is not the one man job but various component/s that work together for a common goal and that is storage and retrieval of your data in a robust and consistent manner.

You can think of it like a car, who has various part/s or you can say component/s (Engine, Clutch, Gear, Tyre etc.) that work together for a common goal and that is keep you moving.

Transport Subsystem

Client requests arrive through the transport subsystem. Requests come in the form of queries, most often expressed in some query language. The transport subsystem is also responsible for communication with other nodes in the database cluster.

Query Processor

Upon receipt, the transport subsystem hands the query over to a query processor, which parses, interprets, and validates it. Later, access control checks are performed, as they can be done fully only after the query is interpreted.

The parsed query is passed to the query optimizer, which first eliminates impossible and redundant parts of the query, and then attempts to find the most efficient way to execute it based on internal statistics (index cardinality, approximate intersection size, etc.) and data placement (which nodes in the cluster hold the data and the costs associated with its transfer). The optimizer handles both relational operations required for query resolution, usually presented as a dependency tree, and optimizations, such as index ordering, cardinality estimation, and choosing access methods.

Execution Engine

The query is usually presented in the form of an execution plan (or query plan): a sequence of operations that have to be carried out for its results to be considered complete. Since the same query can be satisfied using different execution plans that can vary in efficiency, the optimizer picks the best available plan.

The execution plan is handled by the execution engine, which collects the results of the execution of local and remote operations. Remote execution can involve writing and reading data to and from other nodes in the cluster, and replication.

Storage Engine

Local queries (coming directly from clients or from other nodes) are executed by the storage engine. The storage engine has several components with dedicated responsibilities:

Transaction manager

  • This manager schedules transactions and ensures they cannot leave the database in a logically inconsistent state. So it basically handles stuff like start transaction, commit/rollback/save points.

Lock manager

  • This manager locks on the database objects for the running transactions, ensuring that concurrent operations do not violate physical data integrity. There is basically the decision of granularity like table or row level, that give a picture of how more the database will be handling simultaneous transactions at a given point of time.

Access methods (storage structures)

  • These manage access and organizing data on disk. Access methods include heap files and storage structures such as B-Trees.

Buffer manager

  • This manager caches data and/or index pages in memory.

Recovery manager

  • This manager maintains the operation log and restoring the system state in case of a failure.

Together, transaction and lock managers are responsible for concurrency control. They guarantee logical and physical data integrity while ensuring that concurrent operations are executed as efficiently as possible.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more