Petabytes of Data @ Your Fingertips: How?

A database today is a vastly complex affair. The 'big' implementations

can be a cluster of thousands of database servers spread across the geography.

Yet, as enterprises scale up and grow beyond those geographical boundaries, they

continue to demand and expect that the data will always be there, at their

fingertips. The magic that works behind the scenes to make this happen is what

makes a modern relational database tick. If you consider a database deployment

as vast as those of a Google or a Yahoo, the amount of data that is there, with

the primary ambition of being found in response to any number of any manner of

queries runs into Petabytes.

How does this work? What is the secret databases engines hold that stores all

this information efficiently and in a manner that they can be queried and

processed in fractions of minutes? The technologies that apply are the very same

ones that power your enterprise. And we're going to plumb the depths of a

modern RDBMS system and see why and how it just works. Before we move on, let's

get an overview of what a modern RDBMS looks like on the inside.

Quick refresher



A modern RDBMS has a few logical sections to it, although you may not see them
as such discreet pieces (as in separate modules) in reality. Exactly how the

different pieces fit together also differs between each implementation. But

broadly speaking, they would have the following:

Storage engine: This would determine where each data element goes and

stores it in the best possible way. Sometimes, compression is also applied to

make efficient usage of storage capacity.

Buffer/Cache engine: This takes care of buffering and caching, both

data as well as instructions between the database backend server and the user

front-end (which may be over a Web connection). This is also important when

cursors several data pages long are being retrieved for reporting or analysis.

Transaction engine: RDBMS operations are atomized into transactions

and sets of transactions can be rolled back in case of failure. This is well

known. The engine that makes this happen is the transaction engine, which has

built-in atomization logic, along with the ability to detect different kinds of

failures (recoverable and irrecoverable) and handle them appropriately.

Recovery engine: In case a transaction fails or data is corrupted, the

recovery engine is called upon to get things to working order again. This means

that you get the data in a previously 'known' and 'sane' state as

against something that's completely unknown or damaged.

Query engine: This engine is responsible for fetching the queries

(from the application or user), parsing it, optimizing it and then queuing it

for execution.

Each of these engines, in turn, would use sub-engines that perform further

specialized tasks. For instance, the storage engine would need to call on one of

several discreet file system drivers to perform the storage depending on if the

storage is happening locally, over the network onto a SAN or if it is in a

distributed environment.

Now let's turn to some of the key players in the RDBMS arena and see what

technologies make them work better for us.

Databases for SOA



There is a new set of databases aimed at getting better performance out of SOA
applications that use databases. IBM's DB2 9 'VIPER' and MS SQL Server

2005 are two such SOA ready databases.

SOA enables databases to interact better with SOA applications like web

services, and are targeted at large e-commerce deployments. Regular databases

and database applications are not geared to meet the demands of SOA deployments.

To do that, an RDBMS must do the following at a minimum:

1. Be able to talk and understand XML. All SOA transactions happen via XML so

it is important for the database engine to know how these messages look and work

so to know what are the really interesting ones that it must store and what is

part of regular chatter that can be safely thrown away.

2. It must do HTTP and SOAP. Most databases simply communicate on a

predetermined TCP port (like 1433 for MS SQL Server over TDS Stream> protocol,1521 for Oracle and port 3306 for MySQL) and this has nothing

to do with regular SOA traffic. A true SOA database must communicate on the

regular channels like HTTP (port 80) atleast.

In order to act as a service provider for SOA applications or data, the RDBMS

must be further aware of SOAP authentication methods and be able to proxy that

authentication between token providers and consumers in the SOA chain. Future

databases will need to be SODA instead of just an RDBMS to serve the purposes of

modern computing.

Oracle 10g



Automatic Storage Mgmt: ASM for short, think of it as your SAN's
storage manager embedded into the kernel of your RDBMS engine. Hierarchically,

the ASM would fit in between the different database instances (see Jargon Buster

in this article for more on this) and the storage they use. Thus, it virtualizes

the storage available to Oracle databases across a clustered storage environment

and exposes them as a single available set of locations to the database. Where

the exact file (or file segments) are located and how they are laid out,

accessed or managed becomes transparent to the database engine, with the

positive side effect of removing the headache of managing storage from the DBA's

workload. ASM is used by the Oracle kernel only for the database files and

cannot be used for other storage.

A set of physical disks are grouped into 'disk groups' and a set of these

are available to a storage node. An ASM manages storage at the node level and

different nodes are formed into a storage cluster. ASMs among different nodes in

a cluster can communicate among themselves to manage storage between them.

At start up, the kernel would fire up the ASM which would mount the disk

groups after making sure everything is ok (or performing maintenance on them).

It is only after this that the DB instances are started up, which in turn mount

the databases.

MySQL Server allows DBAs to choose from a set of storage engines on the fly, each engine is optimized for a specific app area from archival to cluster computing

Cluster File System: When an Oracle RAC (Real Application Cluster) is

spread across several cluster-nodes, a robust and high performance CFS (Cluster

File System) is required to efficiently access and store data across the

cluster. Oracle's open source OCFS (Oracle Cluster File System) is a

contribution in this direction. Unlike what we said about the ASM above, OCFS

lets the database store data files, log files as well as archives. But, regular

files other than Oracle database files cannot be stored on the OCFS and this

support is planned for OCFS 2.0 (current release is 1.0.14-1 at the time of

going to press). The OCFS can work only on a SAN or a DAS where local servers

have direct control over the storage. The system runs on a RHEL Advanced Server

based server, and standard Linux commands (FSCK, MKFS, etc) are available to

manage and maintain OCFS storage.

MS SQL Server



Automatic Consistency Checking: Since version 7.0, SQL Server has
supported file based databases. This lets a single database be extended among

different storage units and managed together from a single interface. SQL Server

2000 removed the need to run DBCC (consistency checks) on databases prior to

backup, since the engine anyway performs these checks at regular intervals.

Also, as and when the storage engine encounters a problem, it is handled without

needing a separate management operation. With the 2005 edition, running a DBCC

CHECK (although still available) is no longer a necessary step, as it is

completely automated.

Page-wise Checksum: Instead of checksums being computed at the file

level, SQL Server 2005 checksums pages of data, which can increase data

protection. This can, for instance catch problems that are not reported by the

OS or I/O hardware --- an ALTER DATABASE option enables page level checksums for

databases.

Read-only Database Compression: Another improvement in this edition is that

when you have compressed NTFS volumes, you can place a database file group that's

been marked 'read only' onto that compressed volume. Since the data in these

files are not subject to change (since they are read only), the file does not

grow and hence compression can help better utilize its space.

Quick Initialization: To improve performance, SQL Server 2005 permits

creating or extending a database's data files (not log files) without first

zeroing the space allocated to it. But, to be able to do this, the SQL Server

instance needs the SE_MANAGE_VOLUME_NAME privilege, which is not available to

the Network Service account (default for the service) and hence by default this

facility is turned off. This kind of quick-initialization potentially exposes

uninitialized portions of the disk that may contain previous data to the

application or user. But, the storage engine itself restricts access only to

those portions where data has been written to by the database engine, preventing

such leaks. In a broad manner of speaking, this is similar to the 'quick

format' option for disks available in major OSs.

Snapshots: Point-in-time backups can be taken using database

snapshots. This uses NTFS Sparse File technology to efficiently manage the space

used. Pages of data are copied from the database to the snapshot on a

copy-on-write basis, letting the snapshot file attain similar sizes as the

original database.

Row-wise versioning: And imagine having different versions of each row

in a database! That's also now supported with SQL Server 2005's row-level

versioning. This also works on a copy-on-write basis, but stamps each row with

its transaction ID to



enable a trace back.

Data partitioning: Data in the latest version of this RDBMS can be

partitioned into independent segments and accessed and modified separately as

long as they are assigned to different file groups. Thus, the storage engine in

SQL Server 2005 also handles file groups (actually data partitions), along with

switching partitions between file groups and aligning them to different

boundaries.

MySQL



Pluggable Engines: This well-known open source enterprise class database uses a
pluggable storage architecture. While about eight different types are shipped

with different editions of MySQL, about five engines are also available from

their partners and the MySQL community. Each storage engine is specifically

written and optimized for a particular application area. Some handle

read-intensive operations more suited to Web based deployments, while others are

raw OLTP, clustering and warehousing optimized. The default storage engine is

called 'MyISAM'. Other native engines include: Cluster, Federated, Archive,

Merge, Memory, CSV and BlackHole. Oracle/InnoDB's own InnoDB engine is tasked

with transactional data storage with row-level locking and referential

integrity. PrimeBase XT, Open OLAP and Berkeley DB (Oracle) are among the

community developed contributions.

Till now, this architecture allowed MySQL DBAs to select the best available

storage engine for a MySQL instance. From version 5.1, the architecture has been

changed to allow database developers change the storage engine on the fly using

what MySQL terms as a 'clap-on



clap-off' approach with special LOAD and UNLOAD statements.

Other enhancements include the transactional engine, OLAP engine and

multi-dimensional engine. The MySQL community also got its long standing wish to

be able to do data partitioning (range, hash, key, list, composite and

sub-partition) on its databases with the latest version, although the support

for this is rather shaky and under development.

Falcon: This new database engine for MySQL was originally supposed to

enter public beta testing in summer this year, has not yet come into the open

even with broad specifications at the time of this going to press. All that is

known about this engine is that it is based on the Netfrastructure engine by Jim

Starkey that makes better use of modern processor and RAM capabilities. More

cores in a processor means the engine would use them to marshal data from one

place to another more efficiently and go multi-threaded to do so. Larger

memories mean more caching, but Falcon wants to do better than that by using

in-memory databases that fill up from disk-based content. Tuning databases are

also rumored to be a thing of the past with Falcon handling your database.

Multiple versions of a table will exist in memory while there would be just a

single version on the disk,



making versioning faster and more consistent. Similarly, transactions are also
performed in memory with the outputs flowing to the disk. For the rest, we'll

have to wait till a version of Falcon actually hits the beta servers.

IBM DB2 VIPER



Not just relational DBMS: This is version 9 of the IBM DB2 and was almost
named “DB3” given the wide ranging changes to the DB2 system. IBM says that

as of Viper, DB2 is no longer a pure relational environment but provides an XML

plus relational environment.

pureXML: DB2 Viper is intensely an XML environment. XML documents can

be stored in a database using several methods: you could break up the document

into its constituent parts and store each one separately or, store entire XML as

a single piece. Both have their pros and cons. When you break up the file, you

can store it more efficiently, replacing duplicates with pointers to the already

stored copy. But, this has a performance penalty when a document has to be

reconstructed in response to a query. DB2 Viper stores XML documents as a single

LOB (Large Object) entity, making it compliance friendly, as when you break up

something into pieces and recombine it later, you do not end up with the

original (for eg, digital certificates will fail to verify as the original

document). Storing XML as a LOB overcomes this problem as well.

SOA friendly: Having a database that can directly do XML is great.

But, having it do a part of SOA for you as well would make for a great deal.

This is what DB2 Viper also enables with its pureXML. So, instead of having

separate DB2 instances for each platform or application that needs the database,

you can have just a single DB2 deployment that can serve data via XML to the

various applications, thus complying with the primary requirement of SOA:

disconnectedness.

Tokenized Compression: To compress data effectively, DB2 v9 replaces

duplicate data even within columns with tokens. This reduces redundancy. DB2

will look for duplication across the database and replace the duplicates with

numeric tokens each time it appears. A central token 'dictionary' helps

refresh the data item with the actual information when it is retrieved. Numeric

values, LOB, XML and indexes are not currently tokenized.

Optimized for SAP: It not onl detects its deployed environment on

installation, but can also detect a SAP deployment in that environment. It can

discover SAP's configuration and use these values to work better with that

application. DB2 can be deployed in a special 'silent install' mode as a

part of the SAP installation. The information picked up from SAP is used for

database tuning, indexing and multi-dimensional clustering.

Security partitioning: This is a form of data partitioning (see Jargon

Buster) where DB2 lets you partition data based on security tokens. This means

you can put confidential information in one partition, top secret data in

another and see-this-and-die information in a third one. You can do partitioning

using an ALTER TABLE command. This function is useful when you need to perform

decision-support analysis (what-if, scenario building, etc) where you may need

to temporarily 'delete' data for the analysis query period.

Replicated partitions: This is a feature that is a part of data

warehouse appliances and may soon find its way into DB2. Partition replication

is useful in parallel processing environments where you can replicate a

partition onto another instance.

Self-Tuning: In both memory and storage, the database engine can

automatically detect application workloads (especially SAP) and tune itself to

better performance. This is done by redistributing memory among the different

DB2 processes to optimize the usage.

ILM: The DMS (Data Managed Storage) module of Viper lets you have

disks of different speeds in the same system and it will automatically allocate

data to each of them based on their age. That is, older data will go onto slower

disks and current data on faster ones. Thus, DB2 has a built-in ILM module as

well.