3 ms·

What if it is the difference between your schema working under high transaction rates or not? Or, the difference between buying much more database hardware and

by spotman 11y ago

What if it is the difference between your schema working under high transaction rates or not? Or, the difference between buying much more database hardware and having smaller shards to avoid index contention. (which, is $$) Facebooks mysql architecture (at least used to, likely still is) based on this: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql Note the simple schema, and lack of FKs. FKs are rarely present in extremely high transactions per second systems that operate on the same few tables. This is due to index contention and locking. I say this with lots of experience working on different production systems that see hundreds of thousands of transactions per second. I have yet to see one arrive at these kind of numbers using foreign keys, unless it's something like a giant shared hosting platform, that is operating on hundreds and hundreds of different tables. (therefore, less index contention) Often, in these shops, data correctness is not validated in realtime in this way, but often in a way that is not in the critical path of answering queries. (more like, eventually consistent). Some places have entire teams for this. To your point on correctness, my experience ranges a lot in areas where correctness can be eventually consistent. For something like healthcare or banking, you would rather spend the money on way more hardware, because you can't afford correctness to be off. However, with things like globally scaled social apps, this is just not the case. Most people tend to still use Oracle in those situations. Which is legions slower , and legions more expensive than mysql or pgsql.

ak39 11y ago
>To your point on correctness, my experience ranges a > lot in areas where correctness can be eventually consistent. "Eventually consistent"? Explain. (Do you mean in an ACID transaction?)
- spotman 11y ago
  Let me explain better. As eventually consistent is probably the wrong term for what I mean. In things like a large social media app where the reality is no FKs, and 100k queries-per-second, for instance, you would write into your application logic how to deal with child rows and parent rows, and not rely on FKs to raise an exception, or perform cascading deletes and things. So if ( when ) there is either a bug, hiccup, or a variety of other reasons that can as you mention make things question correctness (or specifically: referential integrity, if we are going to nitpick), these are usually cleaned up out of band. So you may have a table you have to slowly iterate through later and remove rows in which their parent rows no longer exist anymore, as an example. In something like the friend feed schema used in the previous example, read up on how that works, as you will see they sort of turn SQL on its head a bit, to make it more flexible and deal with these shortcomings.
  - Pamar 11y ago
    On the other hand, if you deal with money, or physical products (like a car) or anything slightly more "important" than your "likes" count on a social site, eventual consistency would get you laughed out of the room pretty quickly.
    - spotman 11y ago
      Agreed overall, see my comment above which addresses this. These types of systems typically will never see as many transactions/sec as their “likes” counterparts, for obvious reasons. Having said that, I have worked on some credit card infrastructure that does get up there, and it too, does not have FKs. Having or not having FKs does not magically mean your data will be accurate or in-accurate. ACID, as well as traditional database transactions, still apply, and keep your data integrity high. In conjunction with well designed application logic, and proper planning, FKs are not needed to maintain accurate data whatsoever. Also, there is commerce related things (Amazon) that work on truly eventually consistent data stores, like DynamoDB. Don’t see amazon getting laughed at much. (they use it for their shopping cart, source: http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html http://www.allthingsdistributed.com/2007/10/amazons_dynamo.h... , this paper is also a great read fwiw) Anyways, I think we are getting a bit into the weeds here, as the OPs link is about a mysql bug. Cheers.
      - jeltz 11y ago
        Well, the shopping carts do not contain data which must be correct so your example does not contradict Pamar.
        spotman 11y ago
        I agree with you in spirit, but it's simply inaccurate to suggest that a shopping cart does not deal with mission critical data. the fact is, Amazon does use Dynamo for a mission critical financial purpose. If Dynamo lost data Amazon would not use it for arguably one of the key pieces of their success. the article I linked covers how they do this, and how it stays accurate, scalable, and robust. anyways cheers, I hope we have not hijacked this thread about MySQL bugs too much.

by spotman 11y ago

ak39 11y ago

>To your point on correctness, my experience ranges a > lot in areas where correctness can be eventually consistent. "Eventually consistent"? Explain. (Do you mean in an ACID transaction?)

spotman 11y ago
Let me explain better. As eventually consistent is probably the wrong term for what I mean. In things like a large social media app where the reality is no FKs, and 100k queries-per-second, for instance, you would write into your application logic how to deal with child rows and parent rows, and not rely on FKs to raise an exception, or perform cascading deletes and things. So if ( when ) there is either a bug, hiccup, or a variety of other reasons that can as you mention make things question correctness (or specifically: referential integrity, if we are going to nitpick), these are usually cleaned up out of band. So you may have a table you have to slowly iterate through later and remove rows in which their parent rows no longer exist anymore, as an example. In something like the friend feed schema used in the previous example, read up on how that works, as you will see they sort of turn SQL on its head a bit, to make it more flexible and deal with these shortcomings.
- Pamar 11y ago
  On the other hand, if you deal with money, or physical products (like a car) or anything slightly more "important" than your "likes" count on a social site, eventual consistency would get you laughed out of the room pretty quickly.
  - spotman 11y ago
    Agreed overall, see my comment above which addresses this. These types of systems typically will never see as many transactions/sec as their “likes” counterparts, for obvious reasons. Having said that, I have worked on some credit card infrastructure that does get up there, and it too, does not have FKs. Having or not having FKs does not magically mean your data will be accurate or in-accurate. ACID, as well as traditional database transactions, still apply, and keep your data integrity high. In conjunction with well designed application logic, and proper planning, FKs are not needed to maintain accurate data whatsoever. Also, there is commerce related things (Amazon) that work on truly eventually consistent data stores, like DynamoDB. Don’t see amazon getting laughed at much. (they use it for their shopping cart, source: http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html http://www.allthingsdistributed.com/2007/10/amazons_dynamo.h... , this paper is also a great read fwiw) Anyways, I think we are getting a bit into the weeds here, as the OPs link is about a mysql bug. Cheers.
    - jeltz 11y ago
      Well, the shopping carts do not contain data which must be correct so your example does not contradict Pamar.
      - spotman 11y ago
        I agree with you in spirit, but it's simply inaccurate to suggest that a shopping cart does not deal with mission critical data. the fact is, Amazon does use Dynamo for a mission critical financial purpose. If Dynamo lost data Amazon would not use it for arguably one of the key pieces of their success. the article I linked covers how they do this, and how it stays accurate, scalable, and robust. anyways cheers, I hope we have not hijacked this thread about MySQL bugs too much.