Physical Database Design and Storage Systems, Study notes of Deductive Database Systems

Download Physical Database Design and Storage Systems and more Study notes Deductive Database Systems in PDF only on Docsity! ER – Physical Design
Generally the Physical Design is invisible to normal developers/DB users.
The Physical Design is handled by the DBMS
Main goal of Physical Design is increased performance Review from Computer Architecture The following are ranked in order of retrieval speed to CPU – each level is several orders of magnitude slower than the one above it, and also much cheaper per unit of storage. Main Memory (Primary) SSD/Flash (modern, growing in popularity) Hard Disk (Secondary) Tapes (Tertiary)
SSD/Flash is not only faster in IOPS and throughput than spinning mechanical disks, but also is much more energy efficient.
Tapes are for archiving and not to be used for live/active data. Taps have a serious performance penalty as they are not Random Access devices. Disk Hardware A Disk is a mechanical spinning platter(s) on a single spindle. Each Platter is dived into rotational tracks, each track is divided into sectors (usually 512 bytes each). A floating head moves back and forth over the platters while the spindle rotates to read and write information to the disk. These two mechanical movements create a significant performance penalty when compared to purely electronic storage in RAM or SSD.
The latency incurred while waiting for the head to move is called Seek Time.
The latency incurred while waiting for the spindle to rotate is called Rotational Latency. A long strip of sequential data is best written to the disk on an direct sequence of sectors as it will be read off the disk with minimal mechanical delay. DBMS Storage Layout The basic storage unit of a DBMS is called a block or a page, the size of which is a fixed multiple of a disk sector. Usually this is in the range of 4KB to 16KB – this value is the smallest IO possible by the DBMS. Some DBMS allow you to change this value (Oracle does) and others do not. Tuples on Disk The tuple must be stored on disk inside these pages somehow. There are a variety of methods to handle this: 1) Fixed Length tuples An advantage of fixed length fields are that seeking the ith record does not require scanning the entire record. A disadvantage is wasted storage space. 2) Variable Sized tuples Special characters indicate when fields start/stop in the record. Also can be done using an array of field offsets at the beginning of the record, which offers a efficient way to store NULL (special ‘do not know’ character).