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

Sharding Pinterest: How we scaled our MySQL fleet

engineering.pinterest.com

Post reply on HN
Print view View original

61–70 of 87 posts

Re: Sharding Pinterest: How we scaled our MySQL fleet

#61

Post by numbsafari »

fredliu wrote: post #57

How are they going to solve the problem of querying into the data that's stored as json? E.g. trying to find all pins whose "link" is from, say, reddit. Just pull out all data and filter them through in client side? That's not gonna scale. Or having a sort of cron job that periodically picking out interested fields in new json data and store them in a separate table? <-- this is essentially what we do in one of our p…

I can think of two ways.

The first would be to create a mapping table as described. For a relation like "links to reddit", the cardinality is such that it would probably break their sharding scheme.

So the second approach is probably the one he mentions in the article: map reduce (more generally, separate computation). My guess is that for those sorts of "reports" they are using Hadoop. They could also be leveraging things like the HyperLogLog features of Redis.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#62

Post by fredliu »

morgo wrote: post #58
fredliu wrote: post #57

How are they going to solve the problem of querying into the data that's stored as json? E.g. trying to find all pins whose "link" is from, say, reddit. Just pull out all data and filter them through in client side? That's not gonna scale. Or having a sort of cron job that periodically picking out interested fields in new json data and store them in a separate table? <-- this is essentially what we do in one of our p…

In MySQL 5.7 this will be possible because there is a native JSON data type + indexing available via computed columns.

That's good to know. 5.7 seems a bit new though. Before 5.7, what are the common practice to query into json?

Re: Sharding Pinterest: How we scaled our MySQL fleet

#63

Post by silverlake »

wereHamster wrote: post #40
silverlake wrote: post #34

Isn't it unsafe to expose internal database IDs to external clients? I would have generated a second GUID for public view.

But how do you ensure that it is unique?

Twitter's Snowflake, though it's gone now. The principle is the same. https://github.com/twitter/snowflake.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#64

Post by brianwawok »

detaro wrote: post #33
needusername wrote: post #27

Earlier quoted context omitted.

5 TB easily fits on a single Oracle instance on a single host.

It also easily fits in a Postgres instance on a single host, if you only look at "can store X amount of data". And even if Postgres is slower, for the money you save in license costs you can buy a few beefy nodes extra.

You can easily buy 10 postgres nodes for the license cost of 1 Oracle node.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#65

Post by brianwawok »

richardwhiuk wrote: post #13
econner wrote: post #7

Earlier quoted context omitted.

Cassandra was immature when this work was started (late 2011). The team was also much more familiar with the ins and outs of operating mysql.

There's also no transactions in Cassandra, although that's questionable useful given that the unidirectional map may be on a different machine.

Cassandra has very lightweight transactions.

What he lists as his transaction use case (update if unmodified) I do right now with Cassandra and an IF clause and a timestamp...

i.e. update foo set x = y AND last_modified_timestamp = 456789 IF last_modified_timestamp = 12345

see http://www.datastax.com/dev/blog/lightweight-transactions-in...

Re: Sharding Pinterest: How we scaled our MySQL fleet

#66

Post by brianwawok »

nemothekid wrote: post #26
halayli wrote: post #6

This looks like a big hack to compensate for using the wrong tool. Cassandra would have been a better solution IMO. With Cassandra, you can set replication factors, speed up the writes, and automatically shard the data without having to manage your own "mapping tables".

Even today I'm not sure I'd recommend Cassandra for his use case. A stated requirement he needed was: > Support asking for N number of Pins in a board in a deterministic order (such as reverse creation time or user specified ordering). Same for Pinner to likes, Pinner to Pins, etc. This can be a pain to model in Cassandra. It would require denormalizing for every key you wanted to order on, and it generally makes upd…

If you only need 1 ordering, not too hard in cassandra. If you need a few different orderings, you would need a side table - which seems to be what you need here with mysql.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#67

Post by brianwawok »

meritt wrote: post #25
halayli wrote: post #6

This looks like a big hack to compensate for using the wrong tool. Cassandra would have been a better solution IMO. With Cassandra, you can set replication factors, speed up the writes, and automatically shard the data without having to manage your own "mapping tables".

While that would have made scaling easier they would then run into a different set of massive problems because Cassandra isn't a relational database.

But neither is a sharded database. You can't do fancy joins and groups across 400 shards. (Well you can, but you are writing your own code to split up requests and stick it back together.. which is the same thing you would need to do in Cassandra).

Re: Sharding Pinterest: How we scaled our MySQL fleet

#68

Post by brianwawok »

silverlake wrote: post #34

Isn't it unsafe to expose internal database IDs to external clients? I would have generated a second GUID for public view.

Depends on what the GUID lets you access, right?

If GUID got you into a bank account, opps.

If it gets someone access to an already public picture... not a huge deal, right?

Re: Sharding Pinterest: How we scaled our MySQL fleet

#69

Post by techman9 »

mappu wrote: post #53
techman9 wrote: post #36

Earlier quoted context omitted.

JSON object storage for one? I'm a little confused as to why their schema relies on storing JSON data as a TEXT field in MySQL. I don't believe this is technically incorrect, but seems to negate many of the features of relational database design. Why not just store each entry as either a traditional column or use a solution that enables native JSON storage.

>Why not just store each entry as either a traditional column Slow ALTER >or use a solution that enables native JSON storage This would help if you needed to select or join on the individual columns, but if Pinterest don't need to do that, then this falls under the "avoid the fancy new stuff" quote from the article.

> Slow ALTER

In practice, how often do schema migrations take place though?

Re: Sharding Pinterest: How we scaled our MySQL fleet

#70

Post by e12e »

forkerenok wrote: post #46
rimantas wrote: post #35

Earlier quoted context omitted.

I believe Facebook had enough talent capable to choose their DBs carefully. And I am pretty sure MySQL replication had a lot to do with it. As having people who already knew how to finetune MySQL for big loads. BTW, believe it or not, Wikipedia once run on Postgresql, but was later migrated to MySQL (and currently they use MariaDB).

Proof-link? or lie. (pg fanboy here)

I'd also like to see a reference to wikipedia running on postgresql. The mediawiki software platform supports running on postgresql[1] -- but it appears the wikipedia has been running on MySQL prior to migrating to MariaDB:

http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariad...

[1] https://www.mediawiki.org/wiki/Manual:PostgreSQL

Post reply on HN