Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How FriendFeed uses MySQL to store schema-less data

bret.appspot.com

Post reply on HN
Print view View original

11–20 of 92 posts

Re: How FriendFeed uses MySQL to store schema-less data

#11

Post by sam_in_nyc »

nostrademons wrote: post #6
sam_in_nyc wrote: post #3

Really a great way to do things... thank you so much for sharing this. I can't really see any disadvantages to doing anything this way. You still obtain access to data in a relational sense, though where normally you compare columns of a table you'd now do joins. For example, you can get the unique users that have submitted a link by joining the index_link table to the entities table, then joining that to the index_u…

They explained the sharding in the text - I glossed over it on first read-through too. When they say "join" in quotation marks, they don't actually mean a join in the MySQL sense. Rather, the initial call to user_id_index.get_all reads all entity_ids for that user into the Python code (they say it consults all shards for this, but isn't the index sharded on user_id, so all entities for a given user_id live on one sha…

When they say it's sharded on "user_id" what they mean is that's the field that decides which database the record is stored. It might go something like: if the user starts with 0-8, store in DB1, otherwise, store in DB2. This is up to their Datastore controller to decide how to hash based on the user_id and the number of databases.

Re: How FriendFeed uses MySQL to store schema-less data

#12

Post by nostrademons »

sam_in_nyc wrote: post #11
nostrademons wrote: post #6

Earlier quoted context omitted.

They explained the sharding in the text - I glossed over it on first read-through too. When they say "join" in quotation marks, they don't actually mean a join in the MySQL sense. Rather, the initial call to user_id_index.get_all reads all entity_ids for that user into the Python code (they say it consults all shards for this, but isn't the index sharded on user_id, so all entities for a given user_id live on one sha…

When they say it's sharded on "user_id" what they mean is that's the field that decides which database the record is stored. It might go something like: if the user starts with 0-8, store in DB1, otherwise, store in DB2. This is up to their Datastore controller to decide how to hash based on the user_id and the number of databases.

Yeah. They actually explained that in the article too - the shard number = user_id % num_of_shards. So user 1 is on DB1, user 2 is on DB3, etc. If they have 10 shards, user 11 starts back on DB1 etc.

Re: How FriendFeed uses MySQL to store schema-less data

#13

Post by sam_in_nyc »

nostrademons wrote: post #4

This is really interesting. We did something similar for GameClay. I stored game properties as JSON-encoded dicts stored in MogileFS, then had a "regular" MySQL table that would point to the MogileFS key for the file, then the Python code would just read it out, use a JSON library to parse it, and manipulate it as a Python object. We had normal MySQL indexes on all the game metadata that appeared in the UI, so if you…

I just recently wrapped up using a similar "store serialized data" set-up myself. In my example, I'm allowing the user insert/remove/reorder items on a list. This type of operation is pain to do with SQL operations. If you have a list, A,B,C,D,E,F and you want to insert G before C, then you either have to:

  delete all, then insert A,B,G,C,D,E,F.
  set "sortnumber" on G to "3", and increment sortnumber on all >= 3.
  set "sortnumber" of G to be the average of B and C.
The latter is the cleverest, but eventually you run out of space in floating-point land, unless you have a Cron come in and clean everything up periodically. And of course, reordering (the example was an insert), and removing need to be considered as well. So it's a choice between lots of deletes and inserts (but really easy). Semi-annoying logic of reordering/removing and a few updates. Or clever hack that requires a cron to cover your ass.

What I did instead was stored the list as JSON, convert it to an array, and use array splicing functions to reorder things. Then, I convert it back to JSON and store it. It's worked extraordinarily well... It takes a fraction of the amount of time to do native data structure stuff than it does to touch the DB several times.

Re: How FriendFeed uses MySQL to store schema-less data

#14

Post by gaius »

hsiung wrote: post #8

That's reassuring, I thought I was the only crazy fool who was storing json objects in database columns =). We do something similar for some of our data models at thesixtyone.com. It's really nice for not having to bring down the site for schema upgrades.

It's really nice for not having to bring down the site for schema upgrades.

All the other popular databases let you modify the schema online. This feature has been taken for granted for over a decade.

Re: How FriendFeed uses MySQL to store schema-less data

#15

Post by sam_in_nyc »

gaius wrote: post #9
derwiki wrote: post #2

I like the trick for getting around index creation and deletion, I wasn't aware that MySQL required a full table lock. I checked the docs for DB2, and it allows read/write during index creating -- is this a MySQL only limitation or do the other major databases impose the same restriction?

Pretty much it's MySQL only. Most of the MySQL posts here are actually about clever ways to do things most DBAs and developers have taken for granted for years. Do you wonder why no-one seems to be writing blog posts about "sharding" Sybase or DB2 or Oracle or Postgres...?

Excuse my ignorance, but what's the Postgres solution to sharding?

Re: How FriendFeed uses MySQL to store schema-less data

#16

Post by fauigerzigerk »

We're doing something very similar to store RDF data. Our reason for doing it this way isn't performance but rather RDF's pretty intricate schema requirements (and opportunities). The drawback of using this scheme is that it roughly doubles the amount of data stored.

Contrary to Friendfeed, we have to use joins a lot because analysing data is the purpose of our application. We tried to do it with mysql, but mysql turns out to be completely unsuitable for the task due to its lack of merge or hash joins.

I'm quite surprised that someone like Friendfeed would change their entire data model for performance reasons instead of considering a stronger RDBMS (of which there are many). Their problem with index maintainance isn't exactly new. It's a solved problem that needs no wheel reinventing and doesn't merit the increased complexity of asynchronous index updates in my view.

Re: How FriendFeed uses MySQL to store schema-less data

#17

Post by gaius »

sam_in_nyc wrote: post #15
gaius wrote: post #9

Earlier quoted context omitted.

Pretty much it's MySQL only. Most of the MySQL posts here are actually about clever ways to do things most DBAs and developers have taken for granted for years. Do you wonder why no-one seems to be writing blog posts about "sharding" Sybase or DB2 or Oracle or Postgres...?

Excuse my ignorance, but what's the Postgres solution to sharding?

It's to write efficient hash joins, partitioning and row versioning into your core database engine.

Re: How FriendFeed uses MySQL to store schema-less data

#18

Post by bdr »

gaius wrote: post #14
hsiung wrote: post #8

That's reassuring, I thought I was the only crazy fool who was storing json objects in database columns =). We do something similar for some of our data models at thesixtyone.com. It's really nice for not having to bring down the site for schema upgrades.

It's really nice for not having to bring down the site for schema upgrades. All the other popular databases let you modify the schema online. This feature has been taken for granted for over a decade.

Without locking tables?

Re: How FriendFeed uses MySQL to store schema-less data

#19

Post by gaius »

bdr wrote: post #18
gaius wrote: post #14

Earlier quoted context omitted.

It's really nice for not having to bring down the site for schema upgrades. All the other popular databases let you modify the schema online. This feature has been taken for granted for over a decade.

Without locking tables?

Yes.

Re: How FriendFeed uses MySQL to store schema-less data

#20

Post by paul »

gaius wrote: post #9
derwiki wrote: post #2

I like the trick for getting around index creation and deletion, I wasn't aware that MySQL required a full table lock. I checked the docs for DB2, and it allows read/write during index creating -- is this a MySQL only limitation or do the other major databases impose the same restriction?

Pretty much it's MySQL only. Most of the MySQL posts here are actually about clever ways to do things most DBAs and developers have taken for granted for years. Do you wonder why no-one seems to be writing blog posts about "sharding" Sybase or DB2 or Oracle or Postgres...?

Actually, they do, e.g. http://highscalability.com/skype-plans-postgresql-scale-1-bi...

MySQL is popular with startups though, so it's not surprising that there's a lot written about it.

It's true that MySQL has some lame limitations, but I don't believe that there are any silver bullets out there. Google tried to switch their ads system from MySQL to a "real" database once, and it was basically a disaster and had to be abandoned in favor of MySQL (I wasn't working on it, so I can't really give all the details).

Another problem we had with MySQL that Bret didn't mention was that it would try to be "smart", and sometimes it would "randomly" (from our perspective) choose a very inefficient strategy, and we would have to waste a lot of time figuring out what it was doing and how to force it to do the right thing. The approach Bret describes basically avoid any MySQL "smarts" and treats it as a dumb, but fast and well tested B-tree. This gives us fairly reliable and predictable performance characteristics because we know exactly what it's doing (mostly).

Post reply on HN