Comment by unoti | Hacker News Reader

Comment by unoti | Hacker News Reader

I used to work at Second Life. Their player inventory and monetary transaction system was in MySQL. Every single item a player had or made was a record in this database. If you’ve never spent time in SL then the scope of this is probably a lot bigger than you imagine. As an active creator in SL I had 10s of thousands of records in there myself for inventory, and lots more activity for financial transactions and history. The database was distributed into somewhere between 30 and 50 servers, each of which had a read only replica. User inventory was divided amongst these shards. Any transactions that could execute on the read only replica did, and transactions that needed to be placed on the main server for a user executed there.

There were always concerns about the latency involved in the replication. Whenever I did large scale batch operations that needed to write lots of records in master servers, I would monitor the replication queue length and suspend operations when it got too far behind to keep things sane and healthy.

The biggest failure in this architecture was sharing sql queries as opposed to sharing service calls. This is something that was being fixed as I left. Switching it to work with service calls gives more flexibility in the underlying implementation and opens more options for improving the system.

For most kinds of large systems I’d recommend thinking about the architecture in terms of distributed Actors hosted in something like DAPR, Orleans, Akka, or Erlang/OTP and then the technical details of the underlying database become less of a constraint. It grants you flexibility for your underlying storage that you would not otherwise have.

Fun side fact: Bos, the CTO of meta, was a key person working on making all the magic happen there back then.

> The biggest failure in this architecture was sharing sql queries as opposed to sharing service calls

noob question but what do “sharing queries” and “sharing service calls” mean?

Suppose there are several different major components of a large system that need to know something about an important entity like a user for example. A good way to share that information is to have a user service that answers those questions (in a larger system) or a user library/data provider that does this (in a smaller system). A worse way to do that is to have sql queries that different systems use to get that data.

Isn't shared queries faster though?

It’s one less layer, so yes. But going thru a service gives you the opportunity to use caching, cache invalidation, or switch the operation to different kind of data store. So potentially it can be faster sometimes. Also if the database is occasionally burning up from being overworked then survival and reliability are much more important than speed.

A service layer is also a single point of failure. So if uptime is a concern, one less component that a developer can accidentally take out and bring down the whole system is valuable.

One of the most pernicious dangers in shipping a successful project is you paradoxically both cargo cult and critique random parts of your success. Looking back, you might decide that choice A was correct, while choice B was incorrect. But you don't have the benefit of having tried all of those other options. So don't trust postmortems too much, they are single data points in a sea of failures. They could very well be random chance.

a database noob question, when you say distribute the database to 50 servers all these are shards, correct? MySQL (or any standard RDBMS) can't span more than one server, correct ?

Sharding is when you store your tables, or parts of tables, on other machines, so that queries are executed by the resources of those remote machines.

Read-only replication stores a copy of the whole database on other machines, so that any query which doesn't write can be handled by asking the remote machine. Writes are bottlenecked through the primary machine, which then sends changes to the remotes.

> when you say distribute the database to 50 servers all these are shards, correct?

Yes, these are separate server instances that are essentially separate from each other. All of the records for a given user live on a single particular server. Although MySQL doesn't do this kind of partitioning inherently, this kind of partitioning can be implemented in the application.

would be cool if there was a free open source program that did this well written once so we could all benefit from it

It's not something that you can easily abstract away from the application's concerns without a significant performance penalty. For the most basic CRUD operations, an ORM could probably do a decent job. Surely someone has already written an ORM that supports sharding?

Beyond CRUD, I'm not so sure, and any application that's big enough to use sharding probably has some pretty complicated queries. At that point you really don't want to be depending on a library to handle the low level details. You need to think about the performance implications of the access patterns and the trade-offs inherent in different design choices. If a library makes these choices for you then you are unlikely to get optimal results.

There’s Vitess that’s been mentioned on HN a lot recently. https://vitess.io/

Is there a write up somewhere on how you handled cross database transactions? E.g. Bob on db#12 gifts Alice on db#23 a "MegaPlushie(tm)"item?

Interested to know how you'd avoid duplicates and lost transactions while maintaining a comfortable level of performance/latency for an online game.

With mysql this can be handled via XA transactions https://dev.mysql.com/doc/refman/8.0/en/xa.html

> For most kinds of large systems I’d recommend thinking about the architecture in terms of distributed Actors

Did you do this at SL? Because this could be a case of "If I had to do it over again I'd make it perfect" (they say hindsight is 20/20, but it only seems to be).

Reply on news.ycombinator.com