Re:See Twitter's SQL.
by shimon on Sunday July 06, 2008 @10:51PM
(#238247)
The title pretty much covers it. Twitter is more like an IM network or email list service than like a typical database-backed webapp; a relational database is just the wrong platform for large-scale messaging.
I agree, of course. But before the twitter bashing starts, I wanna say, I don't think that they foresaw messaging being the primary use-case. After all, you already have IM and SMS and a gillion other messaging systems. Twitter was conceived as a blogging platform, but smaller. Messaging was how you would post to twitter, not what you would do with twitter.
Of course, that was wrong. (How annoying are the twitter users who actually "answer the question: What are you doing?") But that kind of insight only comes out once your product is exposed to real humans. It's obvious, but only in hindsight.
As it turns out, when you make LiveJournal really small, you get Jabber.
Re:See Twitter's SQL.
by amarcus on Sunday July 06, 2008 @10:03PM
(#238210)
these queries are not optimised at all. first of all, every page gives of MySQL warnings...and second, there are alot of SELECT count(*)...instead of SELECT count(1). This may not seem like it would increase speed alot but given the number of users and the amount of times that query is executed, it will speed up twitter by a bit.
There are a few other things that i have noticed...they should really clean up their sql
Re:See Twitter's SQL.
by gaius on Monday July 07, 2008 @03:04AM
(#238392)
Most likely whoever wrote it cut their teeth on MySQL 4.0 or earlier which didn't have subselects.
They don't do this so much now, but back in the day (mid-late 90s) MySQL documentation was notorious for glossing over why they didn't have features. Foreign keys were "too slow". Transactions were "too slow", etc. If you need to rollback, store the previous values in memory in your own code, they told everyone (then quietly added the feature and changed the docs).
Why roundtrip to the db for the same user info again? I have seen this type of behavior in data access "frameworks"... the optimization would be to reduce the roundtrip to the db...
Re:See Twitter's SQL.
by jrockway on Monday July 07, 2008 @12:45AM
(#238328)
s/frameworks/poorly-written frameworks/.
It's really amazing what poorly-written frameworks have done to the minds of people. When I teach classes on DBIx::Class (the Perl ORM), my students are shocked that $foo->bar->baz actually generates the SQL to follow the two relations as opposed to the "easy" "get list of results, then run query on each result, then return an array...". Actually, $foo->bar->baz, by itself, doesn't touch the database. The database is only contacted when you actually try to get information out of the result object. (And of course, the database is only queried once.)
Re:See Twitter's SQL.
by neilc on Monday July 07, 2008 @01:57AM
(#238364)
Not AFAICS; count( * ) and count( 1 ) are trivially equivalent, and this doesn't depend on the storage engine being used. It is true that count( * ) without a WHERE clause is much faster in MyISAM than in Innodb (or Postgres), but that is a different optimization (count( * ) => metadata lookup, not count( 1 ) => count( * )).
Re:See Twitter's SQL.
by fendale on Monday July 07, 2008 @07:20AM
(#238491)
Mentioned elsewhere in this thread, but count() and count(1) mean basically the same thing and are equivalent.
There is a nice optimisation in MYISAM table that makes count() from table <with no where clause> almost instant as its stored as meta-data on the table - in InnoDB, Oracle or Postgres this optimisation doesn't exist ...
Re:See Twitter's SQL.
by ashleyw on Monday July 07, 2008 @10:36AM
(#238617)
Wish I'd taken a screenshot now.
It was basically a semi-transparent box in the top left of the screen which showed you the query as well as which DB served the data (for me they came from db007, and the friend data from some shards), and two links, one for a popdown box with info about the query, and another with the file trace infomation.
Re:See Twitter's SQL.
by silpol on Tuesday July 08, 2008 @12:09AM
(#239553)
uptime was much better, and reaction was much quicker and to the point. with recent switchover to Google infra they are almost non-stop -- only Google is not that stupid to pay for your messaging ;)
Of course, that was wrong. (How annoying are the twitter users who actually "answer the question: What are you doing?") But that kind of insight only comes out once your product is exposed to real humans. It's obvious, but only in hindsight.
As it turns out, when you make LiveJournal really small, you get Jabber.
http://code.google.com/p/query-reviewer/
Grabbing the users
SELECT * FROM `users` WHERE (users.id in (<list of user ids>))
followed by queries for each user id in that list
SELECT * FROM `users` WHERE (`users`.`id` = <user id>)
Maybe there's a reason for doing that, but if there is, I can't think of it
They don't do this so much now, but back in the day (mid-late 90s) MySQL documentation was notorious for glossing over why they didn't have features. Foreign keys were "too slow". Transactions were "too slow", etc. If you need to rollback, store the previous values in memory in your own code, they told everyone (then quietly added the feature and changed the docs).
It's really amazing what poorly-written frameworks have done to the minds of people. When I teach classes on DBIx::Class (the Perl ORM), my students are shocked that $foo->bar->baz actually generates the SQL to follow the two relations as opposed to the "easy" "get list of results, then run query on each result, then return an array...". Actually, $foo->bar->baz, by itself, doesn't touch the database. The database is only contacted when you actually try to get information out of the result object. (And of course, the database is only queried once.)
http://www.mysqlperformanceblog.com/2007/04/10/count-vs-coun...
There is a nice optimisation in MYISAM table that makes count() from table <with no where clause> almost instant as its stored as meta-data on the table - in InnoDB, Oracle or Postgres this optimisation doesn't exist ...
It was basically a semi-transparent box in the top left of the screen which showed you the query as well as which DB served the data (for me they came from db007, and the friend data from some shards), and two links, one for a popdown box with info about the query, and another with the file trace infomation.
Quite intresting, shame they have removed it.
Some profile pages are being linked to the staging server.
At least maybe (big maybe) some good will come of this?
Anyway... I didnt understand that... "Kolega to bolega". What does it mean? Open the bottle and then it will talk?