Outline

• Finish up Record and Block Formats
• Client-Server Systems
• Logical and Structured Addresses
• Pointer Swizzling
• Quiz
• Returning Blocks to Disk
• Pinned Records and Blocks
• Records with Variable Length Fields
• Records with Repeating Fields
• Formats

Introduction

• Last day, we were looking at secondary storage.
• We considered different failure modes for disks and considered how to mitigate them by using multiple disks arranged in one of the RAID levels we discussed.
• We then looked at how relations are stored on disk.
• In particular, we looked at how to store data belonging to a SQL column type, how to arrange this field data into tuples (we so far looked at how to do this for CHAR, VARCHAR and DATE),
• We now continue our discussion of this... We'll then discuss how to organize records to blocks, how blocks are addressed, etc...

Representing Data Elements IV

• Bits - The SQL1999 and after have a type BINARY(n) that is used when one wants sequences of `n` bits.
  • To store this we pack the bits into `|~ n/8 ~|` (round up) consecutive bytes.
  • Unused bits in last byte are ignored.
  • For example, suppose we want to store a BINARY(6) value `001100`. We might represent this with `00110000`.
  • Note sometimes store a single bit as `00000000` or `11111111`. Sqlite, I am looking at you :_(
• Enumerated Types - {RED,GREEN,YELLOW}. These can be represented using numbers 0, 1, 2 and stored in an integer.

Fixed-Length Records

• Let's take a first-pass at how a MovieStar tuple from our CREATE TABLE of last day could be stored:

  Record:	name	address	gender	birthdate
  Byte positions:	0	30	286	287	297

• Note this may be inefficient to process by 32 or 64 bit processors. Sometimes one reorganizes records so that each field divisible by 4 or 8 to help the processor.

Record Headers

Sometimes we need to store additional information besides the value of each field. This data could be things like:

1. The record schema or a pointer to it.
2. The length of the record
3. Timestamp indicating when the data was last modified or read.

Schema Info

• When a CREATE TABLE is done the database stores:
  1. the attributes of the relation
  2. their types
  3. the order in which the attributes appear in the tuple
  4. any constraints on the attributes.
• This info is usually stored in the catalog/dictionary of the database.
• Each record then for a given relation keeps a pointer to where in the dictionary the information is stored.

MovieStar Record Layout Example

Packing Fixed-Length Records into Blocks

• A block might look like:

  header

  Rec `1`

  ...

  Rec `n`

• The header above is called a block header and might store:
  • Links to other blocks.
  • Role played by this block in network of blocks.
  • Info about which relation tuples in this block belong to
  • A directory giving offsets to records in this block
  • A block ID, and a timestamp for when this block was last read/modified.

Block and Record Addresses

• We often use an addressing mechanism to refer to a particular block or record.
• We will work with addresses a lot when talk about access structures such as indexes.
• When a block is in the buffer the address can be taken to be the location of its first byte in virtual memory.
• However, when the block is in secondary storage, its address depends on characteristics of the DBMS filesystem such as device id, cylinder number, etc.
• A record can be referred to by it block ID, together with an offset into the block.
• To better understand how blocks are referred to in secondary storage, we next look at DBMSs as client server systems, as a DBMS might be managing data on more than one machine.

Client-Server Systems

• A DBMS typically consists of a server that provides data from secondary storage to one or more client processes that manipulate the data.
• The server's data lives in a database address space.

Client Server Systems (cont'd)

There are several ways to represent addresses in the database address space:

• Physical Addresses - byte string that let's one figure out where in the secondary storage the data lives. Might consist of:
  • The host to which storage is attached
  • An identifier for the disk or device
  • The cylinder number
  • The track within the cylinder
  • The block within the track
  • The offset to the beginning of the record
• Logical Addresses - in this scheme each block or record has a logical address which is a string of bytes of some fixed length. Then a map table is used to look up where on the disk this corresponds to.

Quiz

Which of the following statements is true?

1. Rotational latency is the time to position head to correct cylinder.
2. Checksums can be used to prevent intermittent disk failures causing bogus data from being used.
3. RAID 5 uses a dedicated parity disk.

Logical Addresses

• Having logical addresses means one also needs a map table.
• Why would one want to use logical addresses?
  • Easier to move records/blocks around.
  • This is especially useful when have access structures and so might have several pointers to the same block. It allows one to update just one place.

Structured Addresses

• Often one uses a logical address for the block and then have a physical address (a byte offset) for the record.
• This is called a structured address.
• The start of a block then usually has an offset table that holds the offsets for the record within the block:

Advantages of this Scheme

• We can move the record around within the block by adjusting the offset.
• This is good if we have variable length records.
• If we have room, this can allow for records to move between blocks by using a forwarding address for the record.
• We can have a tombstone indicator in the offset to indicate if a record had been deleted.

Pointer Swizzling

• Recall the address for a block can be either in the database address space (slow) or in the memory address space (faster).
• Want to use the latter address if possible.
• The techniques to do this generally go under the name of pointer swizzling, a pointer being what the DBMS uses to look up an address.
• The rough idea is to have a table that contains the addresses of all blocks in memory together with their database address.
• When a block is on disk we use the DB addresses, when it is read into memory we use the table to replace all the addresses in the block to their in-memory equivalents that currently exist in the table.
• To implement this, a pointer consists of a bit to indicate whether the address is a DB or memory one, followed by the address itself.

Pointer Swizzling Example

• The left hand side of the image above show the DB address within two blocks before the first first block has been read into memory.
• The right hand side shows the swizzled addresses within the first block after it has been read in.
• Note when a DB address is not in the table, it is not swizzled in the block.

Types of Swizzling

• Automatic Swizzling - when read in a block, update the translation table, and swizzle all pointers to this block.
• Swizzling on Demand - when we read in a block, update the translation table, and only swizzle a pointer when try to de-reference it.
• No swizzling - when read in a block, update the translation table. If try to de-reference a pointer check if in table, if yes, go to where table says, else read in block.
• Programmer Controlled Swizzling - sometimes app programmer allowed to force a swizzle.

Returning Blocks to Disk

• When a block is moved back to disk all the pointers that reference into it must be unswizzled, that is, converted to DB addresses rather than memory ones.
• The translation table can now be used in reverse to put back into the pointer the database address given the memory address.
• To keep things fast, the translation table is often a hash table.

Pinned Records and Blocks

• A block is said to be pinned if it cannot at the moment be safely returned to disk.
• A bit in the header of the block is often used to say whether the block is currently pinned or not.
• A swizzled pointer might require us to pin a block.
• This is because a block might have within it a swizzled reference to a block we are trying to move to disk.
• If the referring block hasn't been updated by the time we try to move the referred to block to disk, it will point to an invalid block in memory.
• What needs to be pinned can be tracked by augmenting our db-address, mem-address table with an additional column pointing to a linked list of the blocks that uses that particular swizzling (see figure above).
• Swizzle pointers in this set-up are augmented with an additional component pointing to the next use of the swizzling.
• Another reason to pin blocks that will be discussed later is related to the DBMS recover system.

Records with Variable Length Fields

• So far, we have assumed that fields of a given type are always represented using the same number of bytes, and that, in turn, records always use the same number of bytes (for example, look at how we said to implement VARCHAR's).
• This can be very wasteful if most of the time the data item requires substantially less than the allotted storage, so let's see how we can handle records with variable length fields.
• If a record has one or more fields of variable length, then the record must contain enough info to find each of its fields. To do this we can place in its header:
  • The length of the record
  • Pointers to each of the fields.

Records with Repeating Fields

• Suppose have a record with a lot of DATE's in it.
• Can group multiple occurrences together then in the header store a pointer to the first.

Variable Format Records

• Sometimes we use records whose format changes from record to record.
• For example, XML or JSON data.
• We can store these as a sequence of tagged fields.
• Each field consists of:
  • attribute name
  • the type of the field
  • the length of the field
  • the value of the field

Records that do not fit in Blocks

• Sometimes we have records with many fields or which contain things such as images or video or audio clips which are large. So the record is too big to fit into one block.
• In which case, we can use spanned records.
• These kinds of records consist of a sequence of record fragments which do fit into a block.
• Each record or fragment then has a header which says:
  • If its is spanned or not.
  • If it is the first or last fragment in a record
  • If it is a middle fragment where the next and previous fragments are.