BFT: Our case for 100% de-normalization.

As a long time DBA and Database Architect this idea is repugnant – make a database 100% de-normalized; one table and except for the one query, retrieval by primary key, nothing else works.  And yet we have had great success using this kind of database.

This does not replace the original normalized database, rather it is more like a permeant cache fed from the main database.  It is a MySQL database which has certain advantages over Memcached or other true caches such as it is permanent until our processes replace it.

Consider what it might take to build a simple web page: get a request, process it which might take many queries and some significant processing then send back your html.

In building a typical web page you can have dozens or queries

In building a typical web page you can have dozens or queries

If you display a page more often than it changes (read more than you write) a BFT (Big Fat Table) can be quite effective.  The idea is simple enough; for each record in our database do all the joins, processing, XML tag it, and anything else then save it back into a table that is wide (it has text fields for all that processed data) and deep (one row for each record).  So that when we need it we can retrieve and use it in our application with minimal processing.

We can make this work because of the proxy (we use Spockproxy) which shards the database into smaller pieces.  Without sharding the BFT would be too big to fit on a single server; in our case a single record on the BFT is about 3k, twice the size of what it takes to store the record in the normalized database (figured by the total data size divided by the number of rows in the primary table).

Writes are slow.  Not only do you have to write the record to the main database you have to then recreate the BFT record and ‘REPLACE’ it (remember the BFT is only for reads so we don’t need to worry if a record is new or replacing an old one).  If you need to see saved results instantly you will need to do something tricky in your application since they will not be in the BFT immediately after you write them to the main database.

But the reads shine, particularly if your application is mostly reads, say 90% or more and you alway ask for detailed records by primary key.  If you have some complicated work to do on your data before you display it, compute it’s popularity and convert to XML for example, you can get very good throughput with little contention. 

Tags: , ,

3 Responses to “BFT: Our case for 100% de-normalization.”

  1. Roland Bouman Says:

    Hi!

    interesting post. What I am wondering about is this…

    #1 what exactly is “XML tagging” in this context?
    #2 What is the benefit of storing it in a database table? Have you tried storing it simply as files, (perhaps with some additional directory structure in order to keep the number of files per dir small enough to get decent performance?)

    Jut curious, I am currently considering to do something like this for an existing app.

  2. Bjorn Says:

    Take a look at Amazon’s persistent, scalable, redundant key-value store Dynamo. They use it for all sorts of stuff instead of a traditional RDBMS. Really interesting read:

    http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html

    A bunch of open source alternatives are emerging, such as scalaris:

    http://code.google.com/p/scalaris/

    I haven’t tried it myself, but why don’t you? :)

  3. Frank Flynn Says:

    To answer Roland:

    #1 what exactly is “XML tagging” in this context?

    I mean taking the data and wrapping it in XML (also sorting, formatting, truncating as needed) before writing it to the BFT. If you took the data from the main DB and converted it directly to XML and never sorted or changed it besides converting to XML that might not be much processing but I’ve found there’s always more to it. So do everything you can up front so on the page view there is very little processing to be done.

    #2 What is the benefit of storing it in a database table? Have you tried storing it simply as files,

    The benefits I use are:
    1 – I use replication and it’s a very easy way to get a set of duplicate data on another machine. True enough there are other ways to duplicate data for files and such but MySQL replication is very easy.

    2 – There is essentially no limit to the ID name space (bigint is really big), with files can run into issues and have to use different directories – not the hugest problem but using a DB is very simple.

    3 – MySQL, and SQL in general, have several built in features that are very handy for finding / limiting individual records which might be causing problems. For example monitor the slow query log to show when you need to add more servers; or find all the record ID’s where the length of a field is > 64k – this is a slow query that you run on a slave but it could show you that your app is creating web pages that are too big for most purposes.

    4 – MySQL has a caches so assuming you have lots of RAM your popular data gets cached like memcache but if a requested record is not in the cache it’s not a big deal to get it.

    True enough there are other ways to do this – but I’ve been very please with our results doing it this way.