The Architecture of Schemaless, Uber Engineering’s Trip Datastore Using MySQL | Hacker News Reader

The Architecture of Schemaless, Uber Engineering’s Trip Datastore Using MySQL

eng.uber.com

Color me skeptical, that looks like a pretty strange design to me, a database on top of a database [1].

Agreed, the design immediately reminded me of the following ancient DailyWTF article, one of the few ones that stuck in my head ever since I read it. They called it the "Inner-Platform Effect" - might apply here.

http://thedailywtf.com/articles/The_Inner-Platform_Effect

I've been following these Uber engineering articles, and I think this is a very neat architecture. Append only + boring technology = solid stuff.

I'm curious to know how many shards per storage cluster they use and how this mapping is done. Is it fixed or can it change? I imagine a startup trying to use a similar setup could start with a few storage clusters, but then add more clusters as needs grow...

They say they use 4096 shards (presumably generated based on some part of `row_key` which is the trip id), but I'm not sure this is a generally-applicable strategy. e.g. if sharding in a social netowrk website is performed based on `user_id` then won't be able to do joins across `user_id`s.

Anyone want to weigh in on whether or not Postgres is a viable option for this?

Might be a case of MySQL natively supporting multi-master replication, whereas with Postgres you have to use a third-party/commercial solution.

Especially if you're not using any particularly advanced Postgres features, the operational simplicity of having built-in multimaster replication might outweigh any PG benefits.

Are they actually using multi-master replication? I gleaned from the article series that they have many small independent MySQL clusters, each with one master and two slaves, and that their worker layer handles requesting data from the correct cluster for whatever shard a request routes to. Writes to a cluster can only go the master. So this seems more like a single-master design, just replicated a bunch but without the masters communicating with each other.

That said, I haven't used MySQL much at all. Am I just missing something or misunderstanding what multi-master means in the context of MySQL?

Only semi-related, I've discerned a Postgres > MySQL/MariaDB sentiment on HN for the last year or so. Is that just my imagination? If not, why is that? MySQL in my experience is a very powerful RDBMS, maybe I just haven't run up against its limitations.

There's probably a couple reasons, but I'll summarize my experiences with them.

I haven't dug into MySQL for a few years (last I really worked with it was 5.5), but out of the box, it does a lot of things that are pretty unsafe or encourage bad practices, such as truncating data when it's longer than the column size, inserting the zero-value for a NOT NULL column rather than erroring when a NULL is inserted, confusing timestamp column behavior, no DDL-level transactionality, etc. Additionally, and not necessarily a bad thing, but it has made some odd implementation and feature decisions that can be (IMHO) counter-intuitive or have a large impact, particularly with regards to how foreign keys get implemented, but also with things like not having schemas (database > tables, rather than database > schemas > tables), not having a boolean type, its datatype specification (int(1) means an integer that displays only a single digit, rather than denoting storage sizes), or the fact that every ALTER TABLE causes a complete on-disk table rewrite.

PostgreSQL, on the other hand, makes every attempt to keep 100% data integrity at all times, has a lot of killer features (probably the best date/time math implementation I've ever used, typesafe operators, etc), is generally very extensible, and most importantly, is extremely predictable. True, it doesn't have the same scalability features out of the box that MySQL does, but that's getting better every release, and as mentioned elsewhere, there's plenty of adequate third-party tooling available (e.g. Slony).

I think MySQL is very much so the PHP of the RDBMS world - it does a lot of silly stupid stuff - mostly for historical reasons - but in the hands of someone who knows how to use it properly, it can be an extremely useful tool. Postgres just defaults to being an extremely useful tool out of the box without needing to know all the gotchas that come with it.

> I haven't dug into MySQL for a few years (last I really worked with it was 5.5), but out of the box, it does a lot of things that are pretty unsafe or encourage bad practices, such as truncating data when it's longer than the column size, inserting the zero-value for a NOT NULL column rather than erroring when a NULL is inserted, [..]

This is no longer the default in MySQL 5.7.

The short version is, Postgres is developed under the "do it right, then make it fast" philosophy, for the most part. Early versions of MySQL were like, "ACID? Pbbbbbbbbbt." So they treated your data like it was essentially worthless and it could lose it at about any time. For a lot of applications built on hardware at the time and the needs at the time, the data really was kinda worthless and so people built stuff on it anyway. But a lot of people remember those early days when MySQL was a pretty crappy database that just happened to be fast and scale out well. MySQL has mostly caught up to Postgres in the whole "not losing data" sense, and is catching up in terms of SQL features as well (although I wish it had CTEs, among many other things). Postgres has made a lot of advances in speed and scalability too, but still lacks some of what MySQL has in terms of clustering and the like without using plugins or add-ons.

This may be relevant:

"I'm hearing a lot about how Uber uses MySQL at web scale but not about their migration away from PostgreSQL." https://twitter.com/xaprb/status/688149033991340033

Part 1 of the article said: "Our new solution needed to be able to linearly add capacity by adding more servers, a property our Postgres setup lacked."

From that, I think we can presume that it was more straightforward to go with MySQL multimaster replication rather than the 3rd-party solutions required for PostGreSQL which are not as mature.

Can you please stop? Postgres is great and all. But people use MySQL for valid reasons, web scale is one. And InnoDB is a very very good DB engine for that task.

"web scale is one"

Does "web scale" actually mean anything? I thought it was just a piss take from the "MongoDb is web scale" cartoon.

i don't think people actually use the expression "web scale" seriously, but i can't tell if they're joking. poe's law or something.

He sounded pretty serious in that post. Maybe I missed the joke.

> Since Schemaless uses MySQL asynchronous replication, the write will be lost if a master receives a write request, persists the request, and then fails before it has replicated the write to the minions (e.g., in a hard drive failure). To solve this problem we use a technique called buffered writes. Buffered writes minimize the chance of losing data by writing it to multiple clusters. If a master is down, the data is not readily available for subsequent reads but has nevertheless been persisted.

Postgres has the same replication semantics as MySQL async replication. So yes, if you want to build something like this, you could.

However, the question you should be asking yourself isn't "could you" but "should you".

The answer is probably not.

Multi-master setups are extremely difficult to build reliably and unless you understand them extremely well you can frequently shoot yourself in the foot with something that seems right but really is only right for a handful of use cases.

https://news.ycombinator.com/item?id=10894047

Apologies if you're just linking that for reference, but this is a 2nd article in the series.

Read that as a drunk saying shemales...

sober and i read the same

Reply on news.ycombinator.com