Outline

• Client-Server Systems
• Logical and Structured Addresses
• Pointer Swizzling
• In-Class Exercise
• 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 fixed length tuples with record headers), how to arrange tuples into blocks (blocks having headers), and how to store the whole relation as a sequence of blocks (hopefully on one cylinder).
• We now continue our discussion on blocks and records...

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.

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.
• Good if have variable length records
• If have room 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 dereference 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.

In-Class Exercise

• Suppose blocks are used to store index records, where the index is implemented as a binary search tree.
• Each record in the index stores two child structured address (which might be null), a key value and a record db address. In the case of a leaf, the child structured addresses are null.
• Suppose each block can hold at most two index records or a single table record.
• When the index was constructed the following keys were inserted: 22, 10, 55, 35, 70, 5 in the order.
• Assume initially no blocks are in memory, the memory can hold 10 blocks, show the blocks that would be read in and swizzling that would happen (assume on automatic swizzling) if we do a index lookup of the table record correspond to the key 5.
• Post your solution to the Feb 5 In-Class Exercise Thread.

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 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 last day).
• 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, XMLdata.
• 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 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.

Blobs

• A record which has a field that has a value that forces spanning (such as a JPG, MPEG, etc) is called a blob (binary large object).
• Blobs often need to be retrieved quickly so we want to store them in sequential blocks or stripe across different disks.
• Blobs also often need special index structures to be able to do things like retrieve say the 40 minute of a movie.
• Sometimes it makes sense not to store the blob in the database at all, but use the filesystem instead.
• This is because databases were originally optimize for small records and often incur a fair bit of overhead compared to the raw filesystem for supporting insert, delete, update, operations on larger objects.
• In these cases, we may just store the filesystem path to the object in the database.
• Sears, van Ingen, and Gray (2006) consider various workloads and when the overheads of keeping a blob in the database exceed the benefits.