mysql viability (was: shells and bells)

[Mike Bilow]

On 2000-05-04 at 09:15 -0400, Niall Kavanagh wrote:

> Here's a better one. Linux does not have a journaling file system (oh,
> they exist, in development and AFAIK they don't come with any
> commericially support distros). 

SuSE is shipping (and sponsoring development of Reiser.

> Does this mean that Linux is not suitable for some tasks? Certainly.
> 
> Does this mean that Linux should not be used for anything serious?
> Certainly not.
> 
> Does this mean that Linux is not a real operating system? Damn right it
> doesn't. ;)

Journaling in a filesystem and journaling in a database are completely
unrelated concepts.  Journaling in a filesystem really means that there
are guarantees that the directory information in i-nodes will be
maintained in a way that is synchronized with the data files to which they
point.  In a sense, a filesystem is a simply a singly-indexed
tree-structured database, but all modern filesystems (except, maybe, FAT)
can reconstruct most of the lost directory information about a file
directly from the file using a tool such as fsck.

In a SQL database, the interrelationships are much more complicated.  
Many different files can be involved in a single table, which means that
the number of potential interactions grows expoentially.  There is also,
because most tables are not singly-indexed, no possible way of
guaranteeing closure of any search unless the database is reindexed from
scratch in a forward manner.  Put more simply, there is no way to know
algorithmically that there are no infinite loops which result from
following the indices unless you either (1) check every possible indexed
search, which would consume exponential time on the order of decades in a
typical database, or (2) rebuild all of the indices from scratch.

A simple example is the operation of deleting an entry.  When you delete a
file on a filesystem, the only possible things which can point to it are
i-nodes.  (Symbolic links do not point to i-nodes in a standard Unix
filesystem, but rather hold the name of the target -- which is why they
are called "symbolic."  This allows a symbolic link to point anywhere,
even crossing between a local and network filesystem.)  The file header
holds a reference count of i-nodes which point to it, and this is simply
decremented each time an i-node is removed.  If the reference count hits
zero, then the file space is marked free.

In a database, the situation is far more complicated.  Referential
integrity -- the technical term -- requires that every field in every
table which references any record in any table must be updated when the
referenced record is removed.  This is done by using a "primary key" to
reference a record uniquely, and the only practical way to keep track of
this is to maintain updated and current indices of all linked tables.  The
notion of a backward deletion is completely foreign to a filesystem
because all deletions on a filesystem are forward: you delete the i-node
and see if the file header reference count has reached zero, but you never
care about finding all of the other i-nodes which reference the file.  (If
you do, the only approach is a from-scratch reindexing tool like fsck.)

In other words, a filesystem without journaling can always be fixed up in
reasonable time, but a generic SQL database has to be completely reindexed
if anything goes wrong.  There is at least an order of magnitude
complexity difference between these two operations.

-- Mike


-
Subcription/unsubscription/info requests: send e-mail with
"subscribe", "unsubscribe", or "info" on the first line of the
message body to discuss-request at blu.org (Subject line is ignored).