In hindsight, the solution was simple, but it took us a while to figure out how to move our auto_increment PK tables into shards and still guarantee uniqueness of the PK ids across shards.

Also, since we (similarly) couldn’t enforce foreign keys across shards, we had to write code to check references periodically.

More details (if you care) at http://oracle2mysql.wordpress.com/2007/08/23/scale-out-notes-on-sharding-unique-keys-foreign-keys/ .

Thanks!

#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.

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?

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.