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

71–80 of 87 posts

Re: Sharding Pinterest: How we scaled our MySQL fleet

#71

Post by philsnow »

> We only interact with the master in production. You never want to read/write to a slave in production. Slaves lag, which causes strange bugs.

These can be worked through, with discipline. You probably only need a fully consistent few on a surprisingly small number of pages.

Generally you'll want to read from the master right after a user changes something (if you have a model where user clicks a button -> you go to a different page). User changes setting on a pin, clicks 'save', you render a new page showing their updated pin. This page view should probably come from the master, or else you risk the user's change not showing up, causing confusion.

Reads from slaves are fine, as long as you're not using something that was read out of a slave as an input to a database write somewhere else (which you shouldn't be doing anyway!). If you render a page that lets a user change their profile (say site.com/edit_profile), the user data can come from a slave, but if you take _all_ the field values and blindly write those into the master, that's where you run into "time travel" bugs. You just need to find out what the user changed and only make those changes in the master.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#72

Post by philsnow »

> To edit a Pin, we read-modify-write the JSON under a MySQL transaction:

I've seen a different approach where you keep a version number on the row, do your read, modify in memory on the app server, then do your write like this

    UPDATE db03429.pins SET blob=’’ WHERE local_id=7075733 AND version=53
then look at the result and make sure that it modified one row. If it returned zero rows, you retry (or show a failure to the user, whichever is appropriate for your use case).

The reason you'd do this is so you can't ever have the row locked for a long period of time. A lot of people don't think about database scalability so _even if they know_ that the code they're writing runs while a transaction is held open, they don't care that that transaction is blocking anything else that is trying to read the row they're working on.

This can lead to row lock bloat over time, which can cause scalability / availability issues as app servers wait longer and longer to get read (or write) locks on all the rows they care about for their current request. This is mitigated a bit if you're requiring / encouraging people to read from slaves instead of master, though.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#73

Post by mappu »

techman9 wrote: post #69
mappu wrote: post #53

Earlier quoted context omitted.

>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?

Looking over the source history at $DAYJOB, we run a migration containing DDL statements on average every two days. Mostly for adding new features on small(er) metadata tables where the ALTER doesn't hurt - but occasionally on our bigger XX-GB log tables, where it takes all morning.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#74

Post by fredliu »

fredliu wrote: post #62
morgo wrote: post #58

Earlier quoted context omitted.

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?

Not sure why I'm down voted, that was a genuine question...

Re: Sharding Pinterest: How we scaled our MySQL fleet

#75

Post by misiti3780 »

morgo wrote: post #60
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.)

thank you!

Re: Sharding Pinterest: How we scaled our MySQL fleet

#76

Post by needusername »

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.

And your engineers work for free.

Re: Sharding Pinterest: How we scaled our MySQL fleet

#77

Post by georgiecasey »

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

> 'We had several NoSQL technologies, all of which eventually broke catastrophically'

Re: Sharding Pinterest: How we scaled our MySQL fleet

#79

Post by ScottWhigham »

tapirl wrote: post #78

Too many configs and maintenance. NoSQL is really better than SQL to scale your databases. It is just sad there is no one open source NoSQL db as good as Google BitTable.

NoSQL is really better than SQL to scale your databases.

Downvoted for what is essentially your opinion presented as a fact. If you want to make such a statement, you'll need to give more context - either in the form of proof, experience, or "other". Just something would suffice really - anything. You'll find a lot of people agreeing with you if you add such context.

Without context, it's nothing more than "Vanilla is better than chocolate - hands down."

Re: Sharding Pinterest: How we scaled our MySQL fleet

#80

Post by joantune »

a minor con of this approach is that you have to add an extra layer to your application to do these operations, i.e. to abstract them. But probably the speed & other gains surpass the cons, and in such a big team, i'm sure they will easily handle such an abstraction layer.

One good idea is to open source it, so if other people can take advantage of it, they will also help you maintain it and find bugs for it.

A question: If you started this now, would you consider using Postgres-XL AFAIK it supports similar shardings, in a more transparent manner for the developers. Any thoughts on this?

Post reply on HN