Problems with RDBMS
Date: Sat, 8 Sep 2001 12:07:07 -0700
From: David Jeske
To: Max
Subject: DB performance
Hey Max,
I'm interested in continuing that discussion we started about
databases and performance. I find that the routes I take with
relational databases are often performance compromises because
it is not easy to get them to use the data orginization which
would be most efficient.
Here are the two biggest examples:
1) Relational databases are not very good at data _partitioning_.
We ran into this problem with Yahoo Groups. We had > 1M groups, each
of which had between 2 and 400k subscription rows. Those subscription
rows were all in a single subscription table (actually, there were
multiple databases, but we'll ignore that). The way Oracle works,
every time we wanted to send an email to a group, and we fetched out
the 30k subscription rows for that group, it had to do 30k seeks and
load 30k blocks. Most of that data was wasted, because there was
really only one useful row per-block. Obviously, with caching it
didn't actually have to load 30k blocks, but even to load 10k blocks,
and do 10k seeks is way too much.
Oracle supports the idea of a partition key, but as far as we could
figure out, one has to pre-create a partition and tablespace for every
_value_ of the column that needs a partition. It definetly could not
handle us making > 1M of these tablespaces and partition keys.
We ended up building a flat-file database we called the
"RepDB". Everytime data changed in the oracle database, triggers would
put that change into an export into our RepDB. The RepDB was really
just a big directory tree, with one file per group. That file
contained all the subscription rows for the group, and allowed us to
get out 30k rows instantly. The files were in sorted order chunks. New
records were added by just rewriting the linear chunk the new record
belonged in. It also had indexed access via the Sleepcat/BDB stuff. As
you can see, RepDB was basically "very partitioned" to make up for
Oracle's lack of partitioning.
Since the middle-ware we used on Oracle and RepDB talked exactly the
same wire-protocol, we had always considered putting the rest of our
Oracle data into RepDB and just dropping Oracle.
Do you know how to easily get Oracle to do something like this?
[[ The answer is to use "Index-Organized Tables (IOT)", although
there are some limitations:
http://www.oracle-base.com/Articles/8i/IndexOrganizedTables.asp
http://www.dba-oracle.com/art_9i_indexing.htm
http://www.jlcomp.demon.co.uk/ch_14.html
]]
2) Relational Databases are not very good at real-time substring searches and
sort-orders.
The same subscription data was also presented in a UI on the
web. Users could page through their members and do substring searches
on them. Any basic SQL knowledge will tell you that fetching out the
4000 - 4030 rows of a sorted query (which is out of cache) requires
the database to load and sort all the rows over again. When that
involves doing 10k seeks because there are 30k members, 10k out of
cache) it's just not viable to put it in an interactive application.
Substring searching is just as bad, partially because the rows are located
all over the place, and partially because the DB is slow at doing row unpacking.
As a result, we built these features into another server, called
MemberSearch. Substring searching over 2M members in a file on Linux
always took < 1.5s on a loaded system. Since it cached substring
search results, paging around in those results was instant. Likewise
for sort-orders. MemberSearch could quickly build a sort-order,
because it could quickly load all of the data out of a single
file. Once it built the sort order, it could keep it around and keep
it updated when changes came in -- until it decided to kill it off.
The kind of sort-order I'm talking about is very different from
building an index. Here are three major differences:
- In these large-data interactive applications, it's
generally okay for there to be a delay from when something is
added to the table and when it's added to every one of the sort-orders
used for interactive display. This means that adding or changing rows
in the database is always fast and never has to update 6 indicies.
- If a 'partition' of data has a small number of rows (for example,
an eGroups group with only 10 subscribers), then the sort order never gets
stored (and thus never needs to be updated).
- The sort-order storage is designed to be able to do the query
"give me items 4000-4010" with 1 seek, and 1 read. One might have
to then look up each one of those rows in the "live" table to
get other data about the row, but at least that was 11 seeks and 11
reads, instead of 10k seeks/reads and a bunch of sorting work.
Do you know any way to get Oracle to do this kind of stuff?
------
We're (expectedly) running into the same kinds of problems with
RDBMS/SQL for our CRM product. My overall feeling is that SQL was
never built for interactive applications which need access to lots of
data, and it's just no good at it. Do you know of any solutions which
are?
--
David Jeske (N9LCA) + http://www.chat.net/~jeske/ + jeske@...