Tags:conceptdatabasestoragephysicaldatabase Status:🟩
Database Storage Hierarchy and Physical Database Design
Summary
Database storage is organized in a hierarchy, with fast, small, and volatile memory at the top, moving down to larger, slower, and non-volatile storage for persistent data. The DBMS primarily stores data on disk and manages data transfer between primary (volatile) and secondary (non-volatile) storage, with the OS handling input/output (I/O) between them. To enhance access speed, DBMSs prioritize sequential over random access and may use memory mapping, despite challenges like transaction safety and I/O stalls. Data is stored in fixed-size pages organized by a storage manager, which tracks data usage and available space for efficient management.
Storage Hierarchy
A computer system’s memory hierarchy ranges from fast, costly, and small-capacity memory at the top to slower, cheaper, and larger-capacity memory at the bottom. The top includes volatile memory used for immediate processing tasks, while lower levels provide persistent storage that retains data even without power, crucial for housing database files and long-term data.
The I/O boundary divides primary (volatile) and secondary (non-volatile) storage, marking where data retrieval speeds significantly drop.
Above the boundary, data moves quickly enough for the CPU to wait until the data arrives. Below it, slower secondary storage requires the CPU to switch tasks / threads until the data transfers complete.
Data exchange between volatile (primary) and non-volatile (secondary) storage, known as I/O (input/output), is managed by the operating system, which signals the CPU when I/O is finished, allowing data processing to resume. This transfer process moves data from slower storage into faster, volatile memory so the CPU can access it quickly when needed.
Access times
| Access Time (ns) | Storage Type | Real Time Equivalent | Time perspective |
|---|---|---|---|
| 1 ns | L1 Cache Ref | 0,000000001 sec | 1 sec |
| 4 ns | L2 Cache Ref | 0,000000004 sec | 4 sec |
| 100 ns | DRAM | 0,0000001 sec | 100 sec |
| 16,000 ns | SSD | 0,000016 sec | 4.4 hours |
| 2,000,000 ns | HDD | 0,000002 sec | 3.3 weeks |
| 50,000,000 ns | Network Storage | 0,00005 sec | 1.5 years |
| 1,000,000,000 ns | Tape Archives | 1 sec | 31.7 years |
Disk based architecture
DBMSs assumes that the primary storage location of the database in on a non-volatile disk, and it’s components manage the movement or data between non-volatile and volatile storage.
Sequential vs. Random Access
Random access on non-volatile storage is almost always much slower than sequential access.
DBMSs try to maximize sequential access by having algorithms to try to reduce number of writes to random pages, by storing data in blocks next to each other.
Memory Mapping
The DBMS can use memory mapping to store the contents of a file into a program’s memory space.
However there can be some issues with that
- Transaction Safety: The operating system can save dirty pages (modified data in memory) at any time, which may lead to data inconsistencies during transactions.
- I/O Stalls: The DBMS cannot track which pages are currently in memory, so if a page is needed but not available, the operating system will pause the process until the page is loaded.
- Error Handling: It’s hard for the DBMS to check if the data pages are valid. Accessing a page that has issues can cause a SIGBUS (error) that the DBMS must manage.
- Performance Issues: The operating system’s data structures can compete for resources, leading to delays, especially when the Translation Lookaside Buffer (TLB) needs to be refreshed, which can further slow down operations.
DBMSs almost always wants to control things itself and can do a better job than the OS.
Storage Manager
In a disk based architecture, DBMSs stores a database as one or more files on a disk, usually in a unique format that only they can read.
The storage manager is responsible for maintaining the files of a database.
It organizes the files as a collection of pages. It tracks what data has been read and written to pages and the available space that is left within those pages.
Database Pages
A page is a fixed-size block of storage that is used to hold data in a DBMS. It can contain tuples, meta-data, indexes, log records etc. Most systems do not mix page types and some systems require a page to be self-contained, meaning that a page must include all the necessary information it needs to be processed independently.