3 ms·
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
by andrewcooke 14y 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.
- taligent 14y agoYes 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.
- deleted 14y ago[deleted]
- pyre 14y agoAssume 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.
- andrewcooke 14y agoit certainly seems like you would need to aggregate keys somewhere (if it's not the typo i assumed). that might just be possible in postgres these days (update: a quick google suggests it's in development for 9.3), but i am pretty sure not in mysql. on the other hand, for all i know, at this scale, they may be treating mysql as some kind of distributed hashtable and doing a lot more work in higher layers... [update: I completely screwed up the maths; looks like this is not rows per tweet, but support data.]
- pyre 14y ago> for all i know, at this scale, they may be treating > mysql as some kind of distributed hashtable and doing > a lot more work in higher layers... If MySQL is just used as hash table, and all of the interesting stuff happens at higher layers, then talking about the number of rows isn't that meaningful.
- shawabawa3 14y ago4629 (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.
- andrewcooke 14y agoargh. you are right - i cannot do simple arithmetic. sorry. [and now it is too late to alter or delete the comment]
- jeremycole 14y agoHowdy. 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 14y agoThanks 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.