Database System Architectures

• Covers database system architectures
• Includes: centralized and client-server systems, server system architectures, parallel systems, distributed systems, and network types.

Centralized Systems

• Run on a single computer and do not interact with other computer systems
• The general-purpose computer system includes few CPUs and device controllers connected through a common bus.
• Access to shared memory is provided by the common bus.
• A single-user system is typically a desktop unit with one CPU, one or two hard disks, and supports only one user.
• A multi-user system has more disks, memory, CPUs, uses a multi-user OS, and serves many users via terminals.
• Multi-user systems are often called server systems.

Client-Server Systems

• Server systems satisfy requests from m client systems.
• Database functionality in client-server systems can be divided into back-end and front-end components
• Back-end manages access structures, query evaluation/optimization, concurrency control, and recovery.
• Front-end consists of tools such as forms, report-writers, and graphical user interfaces.
• SQL or an application program interface provides the interface between the front-end and back-end.
• Replacing mainframes with networks of workstations or personal computers connected to back-end server machines provide better functionality for the cost.
• Client-server systems have flexibility in locating resources, expanding facilities, has better user interfaces and easier maintenance

Server System Architecture

• Server systems are broadly categorized into transaction servers and data servers
• Widely used in relational database systems transaction servers
• Data servers are used in object-oriented database systems.

Transaction Servers

• Also called query server systems or SQL server systems.
• Clients send requests to the server which executes transactions, and then ships the results back to the client.
• Requests are specified in SQL and communicated via a remote procedure call (RPC) mechanism.
• Transactional RPC allows many RPC calls to form a transaction.
• Open Database Connectivity (ODBC) is a C language API from Microsoft used to connect to a server, send SQL requests, and receive results.
• JDBC standard for Java is similar to ODBC

Transaction Server Process Structure

• A transaction server consists of multiple processes accessing data in shared memory.
• Server processes receive user queries (transactions), execute them, and send results back.
• Server processes may be multithreaded, allowing a single process to execute several user queries concurrently
• A lock manager process is used.
• A database writer process outputs modified buffer blocks to disks continually

Transaction Server Processes

• Log writer processes add log records to a log record buffer and outputs them to stable storage.
• Checkpoint processes perform periodic checkpoints
• Process monitor processes monitor other processes
• Process monitor processes take recovery actions if processes fail, such as aborting transactions and restarting processes.
• Shared memory contains shared data in: the buffer pool, lock table, log buffer, and cached query plans
• All database processes can access shared memory
• Database systems implement mutual exclusion using operating system semaphores or atomic instructions to prevent two processes from accessing the same data structure simultaneously.
• To avoid interprocess communication overhead for lock request/grant, each database process operates directly on the lock table.
• The lock manager process is still used for deadlock detection

Data Servers

• Utilized in high-speed LANs when clients have comparable processing power to the server and the tasks are compute-intensive.
• Data is shipped to clients for processing, and results are sent back to the server.
• This architecture requires full back-end functionality at the clients.
• Used in many object-oriented database systems where issues include: page-shipping vs item-shipping, locking, data caching, and lock caching

Data Servers - Page-Shipping vs Item-Shipping

• Page-shipping involves a larger unit but fewer messages.
• Item-shipping uses a smaller unit, thus involves more messages
• Worth prefetching related items along with requested items.
• Page shipping can be thought of as prefetching

Data Servers - Locking

• Overhead of requesting and getting locks from the server is high due to message delays.
• Locks can be granted on requested and prefetched items
• With page shipping, the transaction is granted a lock on the whole page.
• Locks on prefetched items can be called back by the server and returned by the client if not used.
• Locks on a page can be deescalated to locks on items when lock conflicts occur, and locks from unused items can then be returned to the server.

Data Servers - Data Caching

• Data can be cached at the client, even in between transactions.
• Check that data is up-to-date before being used (cache coherency).
• Checking can be done when requesting a lock on a data item.

Data Servers - Lock Caching

• Locks can be retained by the client system, even between transactions.
• Transactions can acquire cached locks locally, without contacting the server.
• The server calls back locks from clients when it receives conflicting lock requests.
• The client returns the lock once no local transaction is using it and is similar to de-escalation, but across transactions.

Parallel Systems

• Contain multiple processors and disks connected by a fast interconnection network.
• A coarse-grain parallel machine has few powerful processors
• A massively parallel or fine-grain parallel machine utilizes thousands of smaller processors.
• Throughput, the tasks completed in a time interval, and response time, the time to complete a task, are the 2 main performance measures

Speed-Up and Scale-Up

• Speedup involves giving a fixed-size problem executing on a small system to a system that is N-times larger.
• Speedup is measured by dividing small system elapsed time by large system elapsed time, and is linear if this equation equals N.
• Scaleup involves increasing the size of both the problem and the system: an N-times larger system used to perform an N-times larger job.
• Scaleup is measured by dividing small system small problem elapsed time by big system big problem elapsed time, and is linear if the equation equals 1.

Batch and Transaction Scaleup

• A single large job is batch scaleup which is typical of most decision support queries and scientific simulations.
• Batch scaleup uses an N-times larger computer on an N-times larger problem.
• Transaction scaleup involves numerous small queries from independent users to a shared database.
• Transaction scaleup involves N-times as many users submitting requests to an N-times larger database on an N-times larger computer
• Transaction scaleup is well-suited to parallel execution.

Factors Limiting Speedup and Scaleup

• Speedup and scaleup commonly are often sublinear due to start-up costs, interference and skew
• Startup costs: the costs of starting up multiple processes may dominate computation time
• Interference: competing processes accessing shared resources spend more time waiting than performing useful work.
• Skew: increased parallelism increases the variance in service times of parallel tasks, and execution time depends on the slowest task.

Interconnection Network Architectures

• Bus: System components send data on and receive it from a single communication bus.
  • Does not scale well with increasing parallelism.
• Mesh: Components arranged as nodes, connect to adjacent components.
  • Communication links grow with components and scales better.
  • May require 2√n hops to send message to a node or √n with wraparound​.
• Hypercube: Components are numbered in binary; components connect if binary representations differ by one bit.
  • N components connect to log(n) other components and can reach each other via log(n) links, which reduces communication delays.

Parallel Database Architectures

• Shared memory: processors share a common memory
• Shared disk: processors share a common disk
• Shared nothing: processors share neither memory nor disk
• Hierarchical: hybrid of shared memory, disk, and nothing

Parallel Database Architectures - Shared Memory

• Processors and disks access a common memory, typically via a bus or interconnection network.
• There is efficient communication between processors
• Shared memory can be accessed by any processor without having software move it
• Architecture is not scalable past 32 or 64 processors because the bus becomes a bottleneck
• Shared Memory Widely used for lower degrees of parallelism (4 to 8).

Parallel Database Architectures - Shared Disk

• All processors can directly access all disks via an interconnection network, but processors have private memories.
• The memory bus is not a bottleneck and provides a degree of fault tolerance
• If a processor fails, others take over its tasks since the database has disks that are accessible from all processors
• Shared disks systems can scale to a somewhat larger number of processors, but the communication between processors is slower
• Examples include: IBM Sysplex and DEC clusters (now part of Compaq) running Rdb (now Oracle Rdb), which were early commercial users; its weakness now is that there is a bottleneck to the disk subsystem

Parallel Database Architectures - Shared Nothing

• A node consist of processor, memory, and one or more disks. Nodes communicate via the interconnection network.
• The node functions as the server for the data on its disk or disks.
• Data accessed from local disks does not pass through the interconnection minimizing the interference of resource sharing
• Shared-nothing multiprocessors can be scaled up to thousands of processors without interference.
• Cost of communication and non-local disk access are the main drawback
• Examples include: Teradata, Tandem, Oracle-n CUBE

Parallel Database Architectures - Hierarchical

• Combines characteristics of shared-memory, shared-disk, and shared-nothing architectures
• Top level is a sharded-nothing architecture where nodes use an interconnection network but do not share disks or memory
• Each node of the system could be a shared-memory system with a few processors
• Alternately, each node could be a shared-disk system, and each of the systems sharing a set of disks could share a shared-memory system, but the complexity leads to distributed virtualmemory architectures also called non-uniform memory architectures
• Reduces the programming complexity in distributed virtual-memory architectures which can also be called non-uniform memory architecture (NUMA).

Distributed Systems

• Data spreads over multiple machines, also called sites or nodes, connected by a network.
• Data is shared by users on multiple machines.

Distributed Databases

• In homogeneous distributed databases, all sites have same software/schema, and data is partitioned among sites.
• The goal is to provide a single database view, hiding distribution details.
• In heterogeneous distributed databases, different sites use different software/schema.
• The goal is to integrate existing databases to provide useful functionality
• A local transaction accesses data in a single initiating site
• A global transaction accesses data in a different site than the initiating site or in multiple sites

Trade-offs in Distributed Systems

• Sharing data allows users at one site to access data at other sites.
• Each site retains a degree of control over locally stored data, referred to as autonomy.
• Higher system availability from data replication at remote sites, even when one site fails.
• Coordination complexity, development cost, bug potential, and processing overhead are all disadvantages.

Implementation Issues for Distributed Databases

• Atomicity guarantees must update data at multiple sites.
• The two-phase commit protocol (2PC) ensures atomicity.
• Each site executes the transaction until right before the commit and leaves the final decision to a coordinator.
• Each site must follow the coordinator's verdict, even if failures occur while waiting.
• 2PC is not always appropriate.
• Other transactions that can be used include: persistent messaging, and workflows
• Distributed concurrency control (and deadlock detection) is required
• Data items may be replicated to improve data availability
• Details are in Chapter 22

Network Types

• Local-area networks (LANs) are composed of processors distributed over small areas like a single building, or a few buildings
• Wide-area networks (WANs) are composed of processors distributed over a large geographical area.

Networks Types

• WANs with a continuous connection (e.g., the Internet) are needed for implementing distributed database systems
• Groupware applications like Lotus notes can work on WANs with discontinuous connections through data that is periodically updated.
• Copies of data may be updated independently
• This can result in non-serializable executions due to different order of operations being executed.
• Resolution is application dependent.

Explanation of database system architectures. Covers centralized, client-server, parallel, and distributed systems. Explains network types and how servers handle requests from multiple clients.