Steve (steve) wrote in lj_dev,

Database distribution

On the mailing lists, Domando and Brad mentioned some things about distributing database load. I had asked about this in the developer community, but didn't really get any conclusive answers, so I went out and did some research on how all this works, both with Oracle and SQL Server because they both use competing technologies.. I decided I'd post it here instead of to the mailing list so more people could see it. Whether this can be applied to LJ will be up to you guys since I'm not so familiar with MySQL, how it replicates, and how we have the capability to play with it.

Oracle distributes itself by using a shared disk approach. This is basically one array of drives or even a storage area network (SAN) which is accessed by multiple computers, each with its own OS, processors, and memory. Basically then the database load is distributed among the servers evenly. Since there is only repository of data, it doesn't matter which server you hit. This approach is in parallel with the general Oracle architecture, which is to use fewer but massive servers (basically Sun machines, as part of the scale up, not scale out approach). They also have a new feature in 9i called Cache Fusion, but since it's a proprietary feature they developed, it's not really something we can deploy. This has to do with how the cache across the servers is managed.

I have a bit more info on MS than I do Oracle, since I'm much more familiar with that system. Microsoft uses the shared nothing model, where each server has its own set of disks to hold data. Each server has a section of the database stored. This partitioned view is not by table since having one table could itself be too much load. The view is a horizontal section of the data, i.e. a section of the table or tables. The sections are managed using a COM+ manager built into SQL Server, so that the application can act like all the data is stored locally. To further scale and provide hot backups, each segment of the data can be replicated across a smaller cluster, where writes go to the main server and the distributes to the slaves, like what LJ uses now.

Obviously these are enterprise solutions costing several thousand, if not million, dollars. Microsoft's approach of segmenting the tables is much closer to LiveJournal's current setup than Oracle's. It also can be assembled with far cheaper hardware. With LJ in mind, I suggest that we somehow figure out a way to build the same sort of solution without having to modify the schema of the database. One way I envision this is having a master server which has no data on it. That server does, however, have a map of where to send each request. How this works with different types of joins, etc...that I don't know. But there must be some way to hack it together using a central manager, similar to the COM+ object that SQL Server uses.
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded