Data Management Systems Access Methods Data representation in - PowerPoint PPT Presentation

Data Management Systems • Access Methods Data representation in memory • Pages and Blocks • Indexing Slotted pages • Access Methods in context Records Compression Gustavo Alonso Institute of Computing Platforms Department of Computer Science ETH Zürich Pages and Blocks 1

Architecture of a database Relations, views Application Queries, Transactions (SQL) Logical data (tables, schemas) Logical view (logical data) Record Interface Logical records (tuples) Access Paths Record Access Physical records Physical data in memory Page access Page structure Pages in memory File Access Storage allocation Blocks, files, segments Cloud storage and file system (Amazon S3) Pages and Blocks 2

Goals of the lecture • Discuss how data is represented within blocks • Block/Page organization • Slotted pages • Record identifiers (tuple identifiers) • Header information for blocks and tuples • Column vs Row storage • Compression Pages and Blocks 3

In perspective • Data in a database is stored in blocks • Blocks are parts of extents • Extents are part of segments • The notion of pages in a database • Hardware pages (the atomic unit to write to storage, usually 4KB) • OS Page (the unit used by the OS to implement virtual memory, usually 4KB) • Database Page = a block, anywhere between 512B and 32 KB; examples: Oracle 2KB to 32KB (typical 8KB), MS SQL Server 8KB, MySQL 16KB • Trend is towards larger block sizes (incurs less overhead) Pages and Blocks 4

Blocks and pages Pages and Blocks 5

Finding the page you need • Segment – Extent – Block/Page • The pages allocated to a given object (a table, an index, and captured in a segment) are managed through lists • These lists are stored as part of the segment header • See “Segments and File Storage” slide 28 Block ID (Extent,Offset) USED Block ID (Extent,Offset) FREE Space Available Pages and Blocks 6

Potential bottleneck • The free and used lists of a segment are a potential bottleneck, especially for transactions that will result in modifications • How these lists are implemented affects performance: • Use several free lists so that concurrent transactions can look for free space in parallel rather than conflict on access to the free list • Make the traversal of the free list fast and keep the list small (shorter the larger the pages, sort the free list by available size, cache positions …) • Make sure holes can be efficiently found (store the available space in each page in incremental steps by using a small amount of bits) Pages and Blocks 7

Finding tuples within a page HEADER • Slotted pages: • Each page has a header (checksum, version, transaction visibility, compression information, utilization, etc.) • Each tuple gets an id (typically, block ID and offset) • The page maintains a list of the “slots” storing tuples in a page by storing a pointer (offset) to the beginning of each tuple • Needed to store tuples of different sizes Pages and Blocks 8

Why slotted pages • To support variable length tuples • Tuples get a permanent tuple id (record id) that does not change • When data does not change, it uses the space very efficiently (this is the most common case) • When data changes, it requires careful management: • If a tuple is modified and becomes larger, use the original space to store a pointer to the new location of the tuple (can be in another block) • If a tuple is deleted, just remove the pointer • If a tuple is modified and becomes smaller, just leave the unnecessary space empty • For insertion, look for a page with enough free space and compact the page if needed Pages and Blocks 9

Block structure: slotted pages • A block is structured as follows: • Header: address and type of segment (index, table, etc.) • Table directory: schema of the table stored in the block • Row directory: pointers to the actual tuples stored in the block • Free space • Row data (tuples) stored in the block • The directory grows downwards, the space for tuples is used upwards Pages and Blocks 10

Optimizing the use of blocks I • Percentage Free • Determines how much space in each block is reserved for updating tuples instead of using it for storing new tuples • This is needed because an update can result in a bigger tuple than the original one UPDATE T SET Adress = “ AStreetWithAVeryLongName ” WHERE LegiNr = 12345678 Pages and Blocks 11

Percentage Free • The use of “ P ercentage Free” is to avoid the fragmentation that would occur if pages do not have enough space to modify a tuple that becomes larger: • Every modification will result in an indirection to another page • A few of them are acceptable, too many would slow down access • This space is reserved for growing tuples rather than for inserting Pages and Blocks 12

Optimizing the use of Blocks II • Percentage used • Determines how much space needs to be free in a block before the free space can be used to insert new tuples • Blocks are unavailable to inserting new tuples until they have the given amount of free space • It is needed because if updates can make tuples smaller (freeing up space), they can also make them bigger (needing space). The combination of both parameters avoids thrashing on the page Pages and Blocks 13

Percentage Used • The use of “ P ercentage Used” is to Space for one avoid having to constantly move a tuple block from the used list to the free list • The block keeps track of how much space it has used and it is free INSERT • Only when enough free can it be used for Tuple 1 inserting (probably for several inserts) Tuple 2 • Otherwise a block can constantly go Tuple 3 … from FREE to USED to FREE with single tuple deletes and inserts, adding overhead in managing the lists Pages and Blocks 14

Pages and Blocks 15

Record Layout Pages and Blocks 16

Structure of a record (tuple) ID Name Dept. Sem. • A tuple contains: 1 John D-INFK 3 • Header (validity flags for deletion, visibility info for concurrency control, 2 Mary D-ITET 5 bit map of null values, …) Schema information • Attributes • Data for each non-null attribute (or a STRING STRING INTEGER pointer to the data) • Relational engines do not store representation schema information in the tuple length data length data int (types of the attributed are Actual tuples in memory known), schema-less systems need to store the structure of the tuple 4 John 6 D-INFK 3 since everyone can be different 4 Mary 6 D-ITET 5 Pages and Blocks 17

Optimizing the record layout Intuitive serial representation but linear time to access each attribute 4 John 6 D-INFK 3 Instead of length, store offsets. That way the record has a fixed sized part at the beginning and a varied sized part at the tail. Pointers 4 6 3 John D-INFK point to tail of attribute. Each attribute can be accessed in constant time Reorder the attributes, place variable length data at the end. 3 4 6 John D-INFK Better performance. Pages and Blocks 18

Data Types • Integer Numbers • Usually represented the same format as C or C++ (standard in hardware architectures) • Real Numbers • IEEE-754 standard for variable precision • Fixed point representations for fixed precision (e.g., Oracle numbers). Avoids rounding errors, variable length by storing all digits plus where the decimal point is (not stored as a string) • Strings and BLOBS • Length and data • Time, coordinates, points … • System specific Pages and Blocks 19

Corner cases • When tuples are very big or some attribute is very big, instead of storing the whole tuple, one stores the fixed part of the tuple and a pointer to the variable, large size of the tuple (potentially in some other page) • Used for BLOBs (Binary Large Objects) • What is a large object is typically in reference to the page size (larger than some size, a page, half a page, more than one page …) • Usually, those large attributes are not processed by queries, putting them somewhere else speeds up scanning of the page as we do not need to scan the large object • Examples: the attribute is a piece of text (a long string) or a photo • BLOBs can be very large (more than one block!) Pages and Blocks 20

Several options • Store as a BLOB on another block(s) • It can be read with the data • It does not affect scans • Accessible form the database • Store the name of a file where the BLOB is • Does not take space on the database • It does not affect scans • Requires to go somewhere else to retrieve DLN DOB EXP F. Name L. Name M. Name Photo Street # 99999999 08/04/75 08/05/23 Janice SAMPLE Ann Main Street 123 BLOB Pages and Blocks 21

Row vs column store Pages and Blocks 22

Row-store (or n-ary storage model) • Row store: • Tuples are stored as described so far, all their attributes together • A tuple can be quickly accessed and retrieved TABLE SLOTTED PAGE ID Name Dept. Sem. HEADER 4 John 6 D-INFK 3 1 John D-INFK 3 HEADER 4 Mary 6 D-ITET 5 2 Mary D-ITET 5 Pages and Blocks 23

“Data page layouts for relational databases on deep memory hierarchies”; The VLDB Journal, November 2002 Pages and Blocks 24