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

51–60 of 87 posts

Re: Sharding Pinterest: How we scaled our MySQL fleet

#51

Post by abalone »

devmach wrote: post #23

As I read on their blog, Instagram uses similar logic with PostgreSQL. If anyone interested, it's accessible on http://instagram-engineering.tumblr.com/post/10853187575/sha...

That is an excellent compare & contrast. The both use 64 bit IDs.

Instagram: 13 bit shard ID, 51 bit "local" ID consisting of 41 bit timestamp in milliseconds and 10 bit sub-millisecond ID. So this scheme supports 1024 IDs per millisecond per shard for 41 years, and 8192 shards.

Pinterest: 16 bit shard ID, 10 bit type ID(?), 36 bit local ID, 2 bits reserved. This supports 68 billion objects per shard and 65K shards, but does not represent time. So you need another field / more storage for that. Also notable is the large 10 bit type ID field which seems to be only actually used for a handful of values, leading to a large chunk of bits that don't change across IDs.

In short, Instagram's scheme is more efficient largely due to the leverage of timestamps in the ID instead of type information.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#53

Post by mappu »

techman9 wrote: post #36
taf2 wrote: post #31

Earlier quoted context omitted.

Other than Oracle being a part of the equation MySQL is still an open source database and too quote the article: "Aside: I still recommend startups avoid the fancy new stuff — try really hard to just use MySQL. Trust me. I have the scars to prove it." In many ways, Postgres is cutting edge with it's features and capabilities. I see lots of updates from Postgres that include new SQL features (json objects) - conversel…

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.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#54

Post by misiti3780 »

Can someone explain to my why people are still recommending mysql over postgres? This is a serious question, it just seems that Postgres has more features and I cant think of any good reasons mysql would scale any differently other than it has been along a little longer (so there are more blog posts + experienced engineers) ?

"MySQL is mature, stable and it just works. Not only do we use it, but it’s also used by plenty of other companies pushing even bigger scale. MySQL supports our need for ordering data requests, selecting certain ranges of data and row-level transactions. It has a hell of a lot more features, but we don’t need or use them. But, MySQL is a single box solution, hence the need to shard our data. Here’s our solution:"

what about that paragraph is not true of postgres also ?

UPDATE:

This schemaless json reminds me of this friendfeed blog post from years ago:

https://backchannel.org/blog/friendfeed-schemaless-mysql

Re: Sharding Pinterest: How we scaled our MySQL fleet

#55

Post by mappu »

boomzilla wrote: post #41
detaro wrote: post #39

Earlier quoted context omitted.

What's the problem with other bots appearing as pinterest?

Simple economics: they get some value from having their content on Pinterest. There is no value (that they could see) from having their content scraped by other bots.

That's terrible for whoever the next big Pinterest is.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#57

Post by fredliu »

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 projects, but curious to see how they do it, or alternatives.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#58

Post by morgo »

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.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#60

Post by morgo »

misiti3780 wrote: post #54

Can someone explain to my why people are still recommending mysql over postgres? This is a serious question, it just seems that Postgres has more features and I cant think of any good reasons mysql would scale any differently other than it has been along a little longer (so there are more blog posts + experienced engineers) ? "MySQL is mature, stable and it just works. Not only do we use it, but it’s also used by ple…

There are features on both sides which the other database doesn't have.

With this specific workload, I think MySQL will work pretty well. Two features in particular: innodb clustered index and compression.

(I work on the MySQL team.)

Post reply on HN