7 ms·

I would argue that most of these Postgres "flaws" are actually advantages over MySQL when you look at them holistically rather than the very specific Uber use-c

• fjordan 10y ago
  Tools such as the percona toolkit (https://www.percona.com/software/mysql-tools/percona-toolkit https://www.percona.com/software/mysql-tools/percona-toolkit) provide the ability to perform safe and performant online alters for MySQL. Behind the scenes it actually creates a new table with the new schema and utilizes triggers to apply writes to the new table as the original data is being copied. Once it completes the table is renamed and the triggers are removed. Percona also recommends using this tool to safely perform alters for any Galera-based replication product (Percona XtraDB Cluster, MariaDB Galera Cluster, etc.)
  • verelo 10y ago
    While this is true, i don't personally think Percona tools alone makes these changes 'safe'. You can create scenarios where performance is so degraded that you introduce replication delay or service interruptions (and yes the tools have ways to work around that, but it's a significant challenge to tune this correctly on production), data can be lost during the table switch over, and the complication of managing a product / database with a table in an extended migration is very difficult. This space is far from done, until we can change data structures on the fly...we're going to find there to be a constant struggle between schema and schema-less and neither side will be right or wrong. Solution: Quantum states; We suspect every possible scenario is happening already, so we should just migrate our database to the appropriate quantum state, where the data is in the structure we desire. Sounds insane, it probably is.
    • spotman 10y ago
      Your absolutely correct that it is not risk free, however, if you do not use foreign keys (which no huge scale mysql install like schemaless does) and you know what your doing (read: calculating the expected load, adding throttles and sanity checks, backups, etc) online alters are extremely feasible with mysql and folks have been doing them for years. I know first hand shops like FB use a similar method in production. Also, big note, the whole point of "schemaless" (And other things, like fb's friend feed architecture) is that you don't make schema changes.
  • chucky_z 10y ago
    online schema change is absolutely riddled with crippling bugs if you use foreign keys. Just a heads up, we've had to basically take everything it does and make an in-house version. The idea and execution are great, when it actually works.
    • barrkel 10y ago
      Foreign keys are a major problem in MySQL if you do lots of writes, especially bulk writes. As is auto-increment (table-lock for load data infile, yay!). We allocate our own IDs, and disable foreign keys for bulk loading.
• return0 10y ago
  > the very specific Uber use-case Other than the "schemaless" layer, the issues mentioned are fairly common.
• busterarm 10y ago
  Can't agree with this post enough. I find their whole writeup to be terribly myopic. When they started their service, Postgres was almost certainly the right choice for what they were building and their MySQL setup was not. Now Postgres is less effective for them. These kind of tech switches are _inevitable_ if you're making the right choices for your organization. This strikes me as very similar to the article where Twitter ditched Rails. The focus should be inward... how they chose a tool that didn't support their use case and how they solved the problem, but instead they're about the flaws (that aren't really flaws) of the tool. It's always the craftsman.
  • _Codemonkeyism 10y ago
    "1B+ rows in it with mysql?" Been there, really no fun.
    • midom 10y ago
      As long as you can shard (across multiple instances, or even within same instance, to avoid B-Tree latching), 1B+ rows within MySQL is piece of cake. Also, MySQL* is getting LSM-Tree support lately, which makes high performance data ingestion combined with OLTP workload quite feasible. * https://github.com/facebook/mysql-5.6/tree/webscalesql-5.6.27.75/storage/rocksdb https://github.com/facebook/mysql-5.6/tree/webscalesql-5.6.2...
      • _Codemonkeyism 10y ago
        "I wonder how Uber adds an index to a table that already has 1B+ rows in it with mysql?" "As long as you can shard " Not sure how sharding helps with 1B+ tables when adding indices, care to share?
        • Swannie 10y ago
          This is: "Split the index in your application code." And then do your joins, in your application code.
    • deleted 10y ago
• jobu 10y ago
  > It sounds like Uber is using MySQL as just a data bucket with primary keys They have a couple posts about "Schemaless", but I still don't understand why they used MySQL as the data store instead of something like Cassandra. ( https://eng.uber.com/schemaless-part-one/ https://eng.uber.com/schemaless-part-one/ ) From that post it looks like they basically built a no-sql database on top of a relational database. The only reason given was operational trust ( "If we get paged at 3 am when the datastore is not answering queries and takes down the business, would we have the operational knowledge to quickly fix it?" ). The project took nearly a year to roll out, and in that time the operation knowledge could surely be trained, hired, or contracted.
  • MikeKusold 10y ago
    I was at MesosCon and ended up talking to some Uber people. They are currently using Cassandra in prod. I can't speak as to why they use MySQL the way they do though.
    • mandeepj 10y ago
      Thanks for sharing this. I was wondering - the limitations they have listed could be easily overcome with cassandra
    • verma7 10y ago
      I gave a talk at MesosCon about how we (are starting to) run Cassandra across multiple datacenters at Uber (https://www.youtube.com/watch?v=U2jFLx8NNro https://www.youtube.com/watch?v=U2jFLx8NNro, https://schd.ws/hosted_files/mesosconna2016/60/mesoscon-uber.pdf https://schd.ws/hosted_files/mesosconna2016/60/mesoscon-uber...).
  • dastbe 10y ago
    So arguably, they are using mysql as a storage engine rather than as a database. They don't explicitly answer the question "Why didn't you use InnoDB/WiredTiger/etc. for your dataplane?", but you get the idea that they were very happy with the specific characteristics of MySQL for their use case and so they built on top of it. It also sounds like they had some deadlines (specifically, the death of their datastore) that they had to meet :).
• madhusudhan000 10y ago
  Well, for one thing, if they say schemaless, then you are using the wrong tool for the job. Sure postgres has JSONB (comparing to mongo db), Key value store such as HStore, but they do well if they fit on one machine. The moment you hit that scale you have to realize that there are tools specifically built for this. There is the phoenix project https://phoenix.apache.org/ https://phoenix.apache.org/ that salesforce is using for scaling. Definitely worth a try. But again, the title of the article sounded nothing more than a rant to me.
  • peatmoss 10y ago
    The term schemaless within Uber is more of a product name. It's kind of a joke with many of the engineers, that it's anything but devoid of schema.
  • deleted 10y ago
    [deleted]
• grogers 10y ago
  Support for online schema changes has been getting a lot better in recent releases of MySQL. No idea if it is totally bulletproof right now though, we are still in the "stone ages" of doing failover based schema changes, not even pt-online-schema-change. It seems that the replication arguments in the post are pretty weak (migrating to 9.4+ to allow heterogeneous versions would still have less downtime than migrating to MySQL), but clustered indexes seem like a huge win for their use case of updating only a subset of each row. Whether that is reason enough to totally ditch postgres, I don't know.
• carterehsmith 10y ago
  I just want to add that newer versions of MySQL can add indexes online. In fact, it looks like all DDL can be done online, even though some if it (like adding a column) may require the table to be rebuilt, which can take time. Note that the master is still available for read and write during this. Replicas will lag though.
  • njs12345 10y ago
    From the MySQL docs: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html http://dev.mysql.com/doc/refman/5.7/en/alter-table.html > Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. While the master may be technically up for writes during this period, it's not much a goer if your table is large and has any write traffic at all, as anything writing will stall for what may be an extended period.
    • carterehsmith 10y ago
      It should never be an extended period. Everyone everywhere will advise to keep your transactions as short as possible. Sure this is sometimes boring additional work - eg you don't delete 1M records with one statement, you break it into 1,000 statements each deleting 1,000 records. Sucks, but keeps your db and your users happy. BTW this is true for PostgreSQL and MySQL and Oracle and every db that allows concurrent DML.
      • njs12345 10y ago
        No, the migration itself will cause transactions to stall. For example, if you have a table with 1M user records, and you run a migration to add a column in MySQL, then any updates to the table will be stalled while the table is rewritten to add the extra column (which may take a while). This is independent of how many records it touches - even if the transaction only touched 1 record and would take 10ms to execute, if the migration takes 10 minutes it may be stalled for up to 10 minutes. In Postgres you can add a nullable column, and the table will only be locked for a very short amount of time, independent of the size of the table.
• ComodoHacker 10y ago
  >can have open read transactions for a VERY long time without impacting other parts of the system Unless you're on a replica.