7 ms·

AdWords was run on MySQL up until two years ago. The vast majority of developers working on "web-scale" projects won't get to handle projects with larger requir

AdWords was run on MySQL up until two years ago. The vast majority of developers working on "web-scale" projects won't get to handle projects with larger requirements than AdWords. In that perspective the whole NoSQL trend makes very little sense, especially given the fact that e.g. PostgreSQL and MySQL have a lot of needed features that many NoSQL databases don't, and that the most "hip" NoSQL database a couple of years ago was a database that doesn't even scale that well (MongoDB).

threeseed 12y ago
You do understand how MySQL et al are used in those cases right ? They are treated as dumb key value stores and sharded horizontally with joins done in the application layer. They are NOT your typical SQL deployment and the features you talk about are often meaningless. And you are 100% wrong about MongoDB not scaling well. The stories you hear of people switching are never going back to PostgreSQL or MySQL they are going to the next level in scalability e.g. HBase or Cassandra.
- h1karu 12y ago
  Like I said because the relational database doesn't scale horizontally you are forced to build a sharding layer into your application which introduces complexity into the application layer and limits your ability to use the relational features of the relational database. At that point you might as well just be using a key-value store that does the sharding for you and offers greater flexibility.
  - baibaichen 12y ago
    there is still transaction in one shard beyond SQL. I believe that ACID is the main benefit comparing with NoSQL store
    - h1karu 12y ago
      My NoSQL store of choices gives me document-level ACID semantics along with eventual consistency, MapReduce, and replication. Here's an example that explains how to get transaction-like guarantees from this kind of NoSQL data-store: http://guide.couchdb.org/editions/1/en/recipes.html http://guide.couchdb.org/editions/1/en/recipes.html https://en.wikipedia.org/wiki/BigCouch https://en.wikipedia.org/wiki/BigCouch
      - baibaichen 12y ago
        Interesting, that isn't transaction, it is just a workaround, and I don't think you can design your app like that which treat document as a transaction log. And then using view to generate the real information.
        h1karu 12y ago
        that's exactly how I design the parts of my app that need a transactional nature. Map/reduce views make it a breeze to query for a consistent aggregate view of the world. This is not something new it's a well established technique from the relational world called "event sourcing" http://www.martinfowler.com/eaaDev/EventSourcing.html http://www.martinfowler.com/eaaDev/EventSourcing.html when everything is a write you don't have to worry about conflicts or locks, so that's nice, plus couch is really good at scaling write thoughput with small documents.
    - deleted 12y ago
      [deleted]
  - g8oz 12y ago
    It's only a problem if your app needs cross-shard reporting. And things like Gearman can help you out with the application layer complexity of horizontal scaling.
- twic 12y ago
  Urban Airship switched from MongoDB to PostgreSQL. I see they have since switched from PostgreSQL to Cassandra, though.
h1karu 12y ago
AdWords was designed with sharding built into it's application layer. This essentially means that the developers were forced to implement a custom app-specific persistence layer that rides on top of MySQL. This limits your ability to write SQL queries because you can only query within a particular shard and that decreases the usefulness of mysql and makes it feel more like a nosql data-store. At that point you find yourself asking why didn't we just use a NoSQL store ? And often times the answer is "because we wanted to use something we were already familiar with". Sometimes people are willing to add a lot more complexity to their application just to allow themselves to avoid having to learn something new.
- balfirevic 12y ago
  "At that point you find yourself asking why didn't we just use a NoSQL store" Because you still have the full ability to write general SQL within the shard. For many types of application this is useful.
  - threeseed 12y ago
    Sure. But only if ALL the data within that query exists on the shard. It is rare that this would happen if you have anything resembling a normalised schema. In which case you would still be doing a lot of joins in your application layer. IMHO Sharded MySQL very much belongs in the NoSQL camp.
    - twic 12y ago
      I don't know that this is true. Imagine you're LeanKit, or Fog Creek, and you run a kanban board as a service. Or a bug tracker, CMS, whatever. You have many customers, each of whom has no more than thousands of users and millions of items. There are many relationships between objects belonging to a given customer, but precisely zero relationships between objects belonging to different customers. Shard using the customer identity as a key, and you have nicely spread-out data and the ability to do any query the application might need to, while still having a normalised schema. There are plenty of other application whose schemas have this property, or almost have it. In my company, we make financial applications, and a lot of the data has very similar siloed ownership structure. The one thing you can't do is reporting queries across your customers. That doesn't seem like a killer, though - it's normal to farm that stuff out to an offline reporting database even in single-server environments.
rsynnott 12y ago
The usual thing, and I believe what was done with AdWords, was application-level sharding. This is in effect implementing your own database using MySQL as a glorified key-value store (you will NOT be doing any interesting joins, and you will not have cross-shard transactions unless you do those yourself too), and is thus not really a great argument for relational databases.

threeseed 12y ago

You do understand how MySQL et al are used in those cases right ? They are treated as dumb key value stores and sharded horizontally with joins done in the application layer. They are NOT your typical SQL deployment and the features you talk about are often meaningless. And you are 100% wrong about MongoDB not scaling well. The stories you hear of people switching are never going back to PostgreSQL or MySQL they are going to the next level in scalability e.g. HBase or Cassandra.

h1karu 12y ago
Like I said because the relational database doesn't scale horizontally you are forced to build a sharding layer into your application which introduces complexity into the application layer and limits your ability to use the relational features of the relational database. At that point you might as well just be using a key-value store that does the sharding for you and offers greater flexibility.
- baibaichen 12y ago
  there is still transaction in one shard beyond SQL. I believe that ACID is the main benefit comparing with NoSQL store
  - h1karu 12y ago
    My NoSQL store of choices gives me document-level ACID semantics along with eventual consistency, MapReduce, and replication. Here's an example that explains how to get transaction-like guarantees from this kind of NoSQL data-store: http://guide.couchdb.org/editions/1/en/recipes.html http://guide.couchdb.org/editions/1/en/recipes.html https://en.wikipedia.org/wiki/BigCouch https://en.wikipedia.org/wiki/BigCouch
    - baibaichen 12y ago
      Interesting, that isn't transaction, it is just a workaround, and I don't think you can design your app like that which treat document as a transaction log. And then using view to generate the real information.
      - h1karu 12y ago
        that's exactly how I design the parts of my app that need a transactional nature. Map/reduce views make it a breeze to query for a consistent aggregate view of the world. This is not something new it's a well established technique from the relational world called "event sourcing" http://www.martinfowler.com/eaaDev/EventSourcing.html http://www.martinfowler.com/eaaDev/EventSourcing.html when everything is a write you don't have to worry about conflicts or locks, so that's nice, plus couch is really good at scaling write thoughput with small documents.
  - deleted 12y ago
    [deleted]
- g8oz 12y ago
  It's only a problem if your app needs cross-shard reporting. And things like Gearman can help you out with the application layer complexity of horizontal scaling.
twic 12y ago
Urban Airship switched from MongoDB to PostgreSQL. I see they have since switched from PostgreSQL to Cassandra, though.

h1karu 12y ago

AdWords was designed with sharding built into it's application layer. This essentially means that the developers were forced to implement a custom app-specific persistence layer that rides on top of MySQL. This limits your ability to write SQL queries because you can only query within a particular shard and that decreases the usefulness of mysql and makes it feel more like a nosql data-store. At that point you find yourself asking why didn't we just use a NoSQL store ? And often times the answer is "because we wanted to use something we were already familiar with". Sometimes people are willing to add a lot more complexity to their application just to allow themselves to avoid having to learn something new.

balfirevic 12y ago
"At that point you find yourself asking why didn't we just use a NoSQL store" Because you still have the full ability to write general SQL within the shard. For many types of application this is useful.
- threeseed 12y ago
  Sure. But only if ALL the data within that query exists on the shard. It is rare that this would happen if you have anything resembling a normalised schema. In which case you would still be doing a lot of joins in your application layer. IMHO Sharded MySQL very much belongs in the NoSQL camp.
  - twic 12y ago
    I don't know that this is true. Imagine you're LeanKit, or Fog Creek, and you run a kanban board as a service. Or a bug tracker, CMS, whatever. You have many customers, each of whom has no more than thousands of users and millions of items. There are many relationships between objects belonging to a given customer, but precisely zero relationships between objects belonging to different customers. Shard using the customer identity as a key, and you have nicely spread-out data and the ability to do any query the application might need to, while still having a normalised schema. There are plenty of other application whose schemas have this property, or almost have it. In my company, we make financial applications, and a lot of the data has very similar siloed ownership structure. The one thing you can't do is reporting queries across your customers. That doesn't seem like a killer, though - it's normal to farm that stuff out to an offline reporting database even in single-server environments.

rsynnott 12y ago

The usual thing, and I believe what was done with AdWords, was application-level sharding. This is in effect implementing your own database using MySQL as a glorified key-value store (you will NOT be doing any interesting joins, and you will not have cross-shard transactions unless you do those yourself too), and is thus not really a great argument for relational databases.