Twitter, PayPal reveal database performance

http://www.itnews.com.au/News/317811,twitter-paypal-reveal-database-performance.aspx

by werner • 14 years ago

119 48 14 years ago

itnews.com.au

aidos• 14 years ago

"get a timestamp error if one node attempts to read data that was written by another node in the future (due to data moving across datelines)"

Honest question: Which part of a replication/db system would be using time stamps that weren't universal?

donavanm• 14 years ago

Just the concept of using time for the logical clock is sketchy. It's not monotonically increasing, it drifts, it can go decrease, etc... Curious what their cross host/region ordering and consistency looks like.

sanxiyn• 14 years ago

"An atomic clock is not that expensive." -- http://research.google.com/archive/spanner.html

blrgeek• 14 years ago

And that's the part that Google Spanner gets around with Atomic clocks and GPS clocks.

ericcholis4• 14 years ago

Not surprising, especially if you've ever used their interface or API. I'm reasonably certain that they don't have any indexes.

Slowest API ever....

ch0wn2• 14 years ago

The sandbox they provide is even worse in terms of response times.

nopassrecover• 14 years ago

And lacks documentation. Or the documentation that is there is wrong. Or it's there but missing fundamental details. Or it's right but you question how it could possibly be a good design decision.

TazeTSchnitzel• 14 years ago

Heck, Bitcoin, a decentralised peer-to-peer "currency", feels faster. Even if it takes ten minutes to confirm a transaction, and longer to be certain.

taligent3• 14 years ago

Reasonably certain based on what evidence ? Because its API is slow ?

eli• 14 years ago

Oh, please. I think a lot of their core backend is still in C++ so maybe we should blame C++ for the slow API?

robryan2• 14 years ago

The speed of their transaction search actively discourages me using it. It is extremely slow even when searching by the transaction Id which you would assume at least that would have a decent index on it.

taligent3• 14 years ago

Have you EVER actually worked on a reasonably sized system ?

Because there are a lot more contributors to request/response performance than just database indexes. Especially when you are at the scale of Paypal.

drm237• 14 years ago

I have, and I can tell you that the speed at which they are able to search for transactions is disturbing. Regardless of if it's related to indexes or another cause, it's still a huge issue for people who have to use PayPal. That appears to be the point the parent comment was trying to make.

Your comment had the potential to be constructive and educational, but instead you took a condescending approach. That's unfortunate.

robryan2• 14 years ago

The point I was trying to make was that of all possible transaction searches you can make, one made on the transaction id should be the easiest one to make fast.

VMG• 14 years ago

That's surprising to me. I didn't expect financial transactions to create that much data. Also wouldn't most of the data be archived and only rarely be accessed?

ericcholis4• 14 years ago

In some cases they store more data than just transaction information. Some people are using PayPal to manage their entire shopping cart. Meaning, that they store basic item information for orders.

conradfr• 14 years ago

Every action you do on your PayPal account is logged, that must account for something.

Firehed• 14 years ago

Financial transactions tend to create a lot of data for the logging and recoverability they require, and the logging isn't terribly interesting.

The trick is realizing that things can and do fail in any way for any reason, and being able to automatically recover from any point in that process. Our (WePay's) system currently requires, at an absolute minimum, seven inserts across six tables, though more commonly 9-10 rows across seven, plus updates on several others, and significantly more if we can't authorize the payment on the first attempt (e.g., mistyped ZIP code on your billing address). Only one of those tables is for logging (account_history, which is functionally identical to a check ledger).

I've completely ignored both the logging of state changes (largely redundant at this point, it was more for early debugging a couple years ago, but still sometimes useful for figuring out why something stalled) and recording of fraud-detection data which can easily be a hundred rows, albeit very small ones (80 bytes or so). It's also interesting to look at what updates need to be performed in a transaction and which ones don't, although that's of course irrelevant to the actual amount of data produced.

That doesn't account for the product side of payments at all - recording contents of shopping carts, donations, ticket purchases, etc. That's at best one stateless row, but use your imagination about the data layout for various money-collecting tools.

Archival is interesting and something I'm sure we'll look at more in the future, but right now it would tend to create more problems than it solves -- we have automated data integrity checks running multiple times per day to ensure nothing is out of line, and kicking data out to an archive somewhere would complicate that significantly. We also of course don't have nearly as much data as PayPal being significantly newer, so it's less of a problem.

conflicts/row locks being one of the more common, but that's easy to deal with. It's when an external call to a processor dies halfway through that things get tricky.

andrewcooke3• 14 years ago

curiously, 4629 tweets a second is 4 million a day[1], which is more than the number of rows per day (3 million).

this must be someone not thinking, right? i don't see how they can have less rows than tweets. even with de-duplication of repeated text they still need a key (and timestamp) per tweet.

oh! and it gets worse. the next line says "400 million new tweets a day". that one must be plain wrong (it's a rate 100x higher than the number per second), given that the other two are (comparatively) consistent (it would also mean an average of 3 tweets per day per active user (140 million), and i suspect they define active user to be anyone over 1 tweet per month...).

so the section "by the numbers" contains four items, but only two independent values. and appears to be inconsistent twice.

[1] almost exactly - presumably the per second value is derived from a rough figure for the daily value.

taligent3• 14 years ago

Yes because finding ways to not insert a new row for each tweet is a classic sign of someone not thinking.

From the UI Twitter only shows tweets to the minute so you could imagine that there is an optimisation there for merging retweets with the same text within a 60 second period.

pyre2• 14 years ago

Assume they normalized the timestamp, and only stored something on the level of "these people tweeted/retweeted this message sometime during this 60 second interval."

The only way to avoid adding a new row per account that tweeted/retweeted would be to store the list of account in a single row, and keep updating it on every re-tweet. This seems like it would be a less than optimal solution.

shawabawa3• 14 years ago

4629 (tweets per second) * 86400 (seconds per day) = 399,945,600 (400 million tweets per day)

So that checks out... I'm guessing the number of new rows per day is excluding tweets.

andrewcooke3• 14 years ago

argh. you are right - i cannot do simple arithmetic. sorry. [and now it is too late to alter or delete the comment]

jeremycole2• 14 years ago

Howdy. I was the presenter from Twitter.

Aside from your bad math above, the reporter heard million when I said billion. More than 3 billion rows per day. That math is easy enough to come by if you do any math on the rest of the numbers: 400M (Tweets per day) * 4 (replication) = 1.6B rows per day to store the Tweets, plus the same amount for an entry in a timeline. So that's 3.2B right there. And there are a lot of other types of indexes.

the_cat_kittles• 14 years ago

Thanks for clearning that up, I was very curious how you could make so few rows with so many tweets. Alas, no magic, just bad reporting.

dkhenry5• 14 years ago

I always knew the company I work for does cool things with MySQL, but I never knew how far we really have pushed the envelope until I see how much more we push MySQL then the twitters and facebooks of the world. Doing some napkin math I would say our largest cluster of MySQL does about 320 Billion inserts a Day across 80 nodes and some of our nodes can peak at about 200K queries per second ( that would be 16 Million QPS if all the nodes were fully loaded ). Meaning at one of our installs we do the same volume as two facebooks !

ericcholis4• 14 years ago

Out of curiosity, what industry do you work in?

dkhenry5• 14 years ago

Performance Monitoring.

ddorian43• 14 years ago

Curious what type of servers, what type of writes, do you use compression, how you shard, what type of indexes? Write an article for highscalability and get exposure?

trustfundbaby• 14 years ago

It'd be great to read about people's experiences using postgres on a comparable scale to that discussed in this article ... does that even happen?

d0ugal• 14 years ago

Yahoo had a huge postgres database a few years ago. No idea what they are doing now. http://www.computerworld.com/s/article/9087918/Size_matters_...

And Skype sounds pretty interesting too. http://www.slideshare.net/adorepump/small-overview-of-skype-...

keyz• 14 years ago

What I would really like to see is a guesstimate of the volume of user behavior/analytics data that FB/Twitter generate. It would be interesting to see the average amount of data per user-interaction.

hendler• 14 years ago

Does the "commercial instance of MySQL" refer to Percona, something else, or is it simply the MySQL commercial license?

jeremycole2• 14 years ago

The article author is confused. I clearly stated in the presentation that we're developing our own branch of MySQL, which is available on Github:

https://github.com/twitter/mysql

spudlyo• 14 years ago

If anyone is interested in watching Jeremy's presentation online it can be found at:

http://medianetwork.oracle.com/video/player/1873920417001

gogetter• 14 years ago

Twitter, Paypal don't really need to be that fast. Nor does Facebook.

But do you think major banks on Wall Street tracking billions of trades a day in near real-time would be satifisfied with these numbers?

It all depends on what your users demand and what they will put up with.

Twitter, PayPal reveal database performance

Related Stories