3 ms·

Based on what I have read, "kv store" is a major over-simplification. A number of companies have built special-case storage services/APIs on top of MySQL. This

Based on what I have read, "kv store" is a major over-simplification. A number of companies have built special-case storage services/APIs on top of MySQL. This is not the same thing as writing your own database. In any case, it shows the strength and stability of MySQL for high-volume OLTP use-cases. Also I don't think "very long running transactions" were the singular core of Uber's problem. InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. While the impact of that is less severe in InnoDB than in Postgres, it's still very bad and will lead to performance degradation and undo-log size bloat.

jimktrains2 7y ago
> InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. Remember that what they moved to isn't directly MySQL eitger, but only uses it underneath.
- evanelias 7y ago
  It's still MySQL/InnoDB under the hood. I don't follow your point -- why does it matter whether an application is talking directly to MySQL, vs using an intermediate service with a non-SQL API? If the intermediate service supports long-running transactions, you'll hit the same underlying issue regardless... If your point is that they also happened to move to a new access pattern that eliminated long-running transactions, and conflated that access pattern change with their migration off of pg, that's a fair criticism if true. Hopefully someone from Uber can clarify this aspect. Also from what I recall (been a while since I read Uber's posts about this), Uber's specific MVCC issues related to pg's use of physical replication and locking impact on MVCC; and also separately its storage of old row versions "inline" vs InnoDB's use of a separate undo log. And on a different point, I remember some aspects of InnoDB's clustered index design being advantageous to Uber's workload as well. In total, this is why I suspect simple "long-running transactions" alone were not the primary/singular reason for the switch (at least if "long" means "minutes or hours" in order to negatively affect InnoDB).
  - jimktrains2 7y ago
    Yes, sorry, I didn't mean to imply that long running transactions were the only cause, but I believe I remember. I just need to go back and read the conversation on the pg-general list, it was very good in that it did discuss that some of the issues mentioned by Uber were real. https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma... and perhaps some other blogs from around the time this was being discussed.

jimktrains2 7y ago

> InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. Remember that what they moved to isn't directly MySQL eitger, but only uses it underneath.

evanelias 7y ago
It's still MySQL/InnoDB under the hood. I don't follow your point -- why does it matter whether an application is talking directly to MySQL, vs using an intermediate service with a non-SQL API? If the intermediate service supports long-running transactions, you'll hit the same underlying issue regardless... If your point is that they also happened to move to a new access pattern that eliminated long-running transactions, and conflated that access pattern change with their migration off of pg, that's a fair criticism if true. Hopefully someone from Uber can clarify this aspect. Also from what I recall (been a while since I read Uber's posts about this), Uber's specific MVCC issues related to pg's use of physical replication and locking impact on MVCC; and also separately its storage of old row versions "inline" vs InnoDB's use of a separate undo log. And on a different point, I remember some aspects of InnoDB's clustered index design being advantageous to Uber's workload as well. In total, this is why I suspect simple "long-running transactions" alone were not the primary/singular reason for the switch (at least if "long" means "minutes or hours" in order to negatively affect InnoDB).
- jimktrains2 7y ago
  Yes, sorry, I didn't mean to imply that long running transactions were the only cause, but I believe I remember. I just need to go back and read the conversation on the pg-general list, it was very good in that it did discuss that some of the issues mentioned by Uber were real. https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma... and perhaps some other blogs from around the time this was being discussed.