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

81–90 of 92 posts

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

#81

Post by hsiung »

srini wrote: post #74
hsiung wrote: post #24

Earlier quoted context omitted.

Actually we're on postgres... It just makes me nervous when the migration is taking upwards of 10 minutes to complete since some of our tables have millions of rows and lots of concurrent read/writes are happening.

I don't think executing long-running DDL with concurrent read/writes is that bad, especially if the DDL is additive and doesn't lock the whole table. Concurrency - isn't that what a DB is supposed to do well?

If the site isn't taken down during an update, some parts of it will always be broken during a schema migration since our ORM expects each table corresponding to a data model to be in sync after a code update. I guess you could always version your data models and handle the pre-upgrade and post-upgrade cases, but that seems like a lot of work in our situation.

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

#82

Post by d13hard »

yesimahuman wrote: post #61

Reading this makes me realize how naive my DB setup is for my startup.

don't go too gaga over this hash-on-the-db appraoch, or its non-rdbms based solutions at all the major web companies. you get lots of scaling, great performance, but you have zero features. you can't even really query.

these models have superficial appeal until you realize that all the querying you would do in the db model now has to be done out-of-band into some sort of materialized view...e.g. if you want to have a group of rows that meet some criteria, you need to store it somewhere else out of band...the database can't help you with realtime queries because there is nothing to query on

in general i would avoid this model unless you have a huge scaling issue

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

#83

Post by d13hard »

joshu wrote: post #73

This is great. I sketched out a similar design for the Delicious backend, but the team that built it went in a completely different and stupid direction. Oh well. Mine also had: - some way to do server locality (I don't think FF needs this) - some way to do table locality (I don't think FF needs this either) - per blob versioning and type - a virtual shard layer, so new machines can be added inexpensively Sorry for a…

given that you were at y, i'm wondering if you were proposing a udb fork plus some features....

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

#85

Post by DenisM »

simonw wrote: post #41
gaius wrote: post #35

Earlier quoted context omitted.

If you can't hash join then you can't join over large datasets anyway, so sharding costs you nothing in that respect. Right now, using off the shelf kit and doing nothing particularly clever, running a major commercial RDBMS you could do 10,000 commits/sec and handle 100T of data on a single instance. Sure it would cost you a pretty penny, but the thing is, unless running a database is the one competitive advantage y…

Which commercial database? Any idea which web scale applications are using it, and why the other ones aren't?

MySpace runs on Microsoft SQL Server.

http://blog.de-hao.com/2007/11/17/myspacecom-running-a-megas...

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

#86

Post by ezmobius »

antirez wrote: post #47

"However, none of them seemed widely-used enough by large sites to inspire confidence. In the tests we read about and ran ourselves, none of the projects were stable or battle-tested enough for our needs" Ok, just some hour ago I released the beta-3 of Redis ( http://code.google.com/p/redis/ if you care) and I'm near to feature-freeze with exactly with this goal. To make it rock solid (I'm going to use it in my start…

I added consistent ring hashing and a rediscloud class to the ruby client library today. so you can now use a cluster of redis servers and have the client use ring hashing to determine the server for a given key.

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

#88

Post by sam_in_nyc »

jsrn wrote: post #30
sam_in_nyc wrote: post #13

Earlier quoted context omitted.

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 "…

While your approach to do this in JSON is cool, I think you have overlooked the 'direct' solution to do this in a RDBMS - with a linked list. Here is a quick suggestion (works in Postgres): create table l ( id char primary key references l(prev) deferrable initially deferred, prev char unique not null references l(id) deferrable initially deferred, mydata text not null ); then I populate the table with your example i…

There's one more thing I forgot to mention...

The data I'm having the user order is not only order-specific, but is recursive. That is, one of the items on the list, rather than be a letter like "B" can be a list in and of itself. It's basically like "files and folders"

A major problem isn't only the UI, which took me days to make (drag and drop for files/folders ... anybody seen anything like this done before in HTML+JS?), but mostly is in the updating/validation. I receive information, like "moved item: /path/to/item to before /some/other/path" and I now need to make sure this is a valid action (eg: can't put a folder into one of its own subfolders, can't put a file in a file, etc), and also update the database to reflect this.

I chose to use JSON to encode objects in the DB, then decode them into an array, and do some easy/fast array stuff to perform the action they requested. Just judging on the array code, which does several "isset()" calls and splices, I'd imagine doing this with a DB would be a major heartache... but I have no doubt you'd be able to come up with some brilliant way to do it.

To each their own :)

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

#89

Post by [deleted] »

wayne wrote: post #7

Thanks for sharing. That was very educational. We store data in a really similar way at Spock, pointing all our reads at a big MySQL table which essentially just has an id column and a really big text column containing all the values, JSON-encoded, w/roughly the same number of rows as the FriendFeed table. Our DBA wrote a blog post about it a while back: http://www.frankf.us/wp/?p=17 The way you guys create/delete in…

[deleted]

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

#90

Post by jsrn »

sam_in_nyc wrote: post #88
jsrn wrote: post #30

Earlier quoted context omitted.

While your approach to do this in JSON is cool, I think you have overlooked the 'direct' solution to do this in a RDBMS - with a linked list. Here is a quick suggestion (works in Postgres): create table l ( id char primary key references l(prev) deferrable initially deferred, prev char unique not null references l(id) deferrable initially deferred, mydata text not null ); then I populate the table with your example i…

There's one more thing I forgot to mention... The data I'm having the user order is not only order-specific, but is recursive. That is, one of the items on the list, rather than be a letter like "B" can be a list in and of itself. It's basically like "files and folders" A major problem isn't only the UI, which took me days to make (drag and drop for files/folders ... anybody seen anything like this done before in HTM…

> To each their own :)

yeah, of course! And if it works, then, by definition, it's good for your customers and thus for you! Also, your approach is probably more flexible (no fixed schema for the recursive list structure), which makes it easier and faster for you to iterate and react to customer feedback.

If at some time you want to have a more fixed structure or let the database do some of the server-side validation work of your tree-like structure, here is a good writeup by Phil Greenspun showing how to model and query tree-like datastructures with an RDBMS: http://philip.greenspun.com/sql/trees.html (the rest of the document 'SQL for Web Nerds' is also quite good stuff: http://philip.greenspun.com/sql )

he uses 'connect by', which is a non-standard Oracle extension, the same thing can be achieved with 'with recursive'-queries, which is standard SQL (1999) and part of PostgreSQL 8.4 Also, Joe Celko's Book 'SQL for Smarties' has a good chapter covering hierarchical data structures in SQL (he also has a separate book about 'trees and hierarchies' in SQL).

Oh, and good luck and much success with your startup!

Post reply on HN