12 ms·

Jepsen: MySQL 8.0.34

364 points by aphyr 3y ago

adontz 3y ago
Serious question. I have this question for, like 20 years already. Why would anyone start a new project with MySQL? Is it really superior in anything? I'm in industry for 20+ years and as far as I remember MySQL was always the worst and most popular RDBMS at any given moment.
- willvarfar 3y ago
  Not up to date, but a decade ago MySQL supported pluggable storage engines and so had had some good non-default choices. It was possible to fit really big databases onto small boxes using tokudb, for example. This doesn't explain why it was so popular for starting new small projects, but people were also choosing mongodb at that time too, so ymmv :) Nowadays postgres has grown a lot of features but I believe it is still behind on built-in compression? Added: this old blog post of mine is still getting traffic 10 years later; probably still valid https://williame.github.io/post/25080396258.html https://williame.github.io/post/25080396258.html
- Keyframe 3y ago
  Once upon a time it was easi-ish to scale out, and was simple to use and fast. There was Percona as well. These days, who knows? Might still be true.
- WJW 3y ago
  - It works well enough. - It scales up fine for 99+% of companies, and for the ones that need to scale beyond that there are battle tested solutions like Vitess. - It is what people know already so they don't have to learn anything new. Same reason why people still make new websites in PHP I guess. It's not fancy but it works fine and won't bring any unwelcome surprises.
- gog 3y ago
  We use it, we know it and can troubleshoot it if needed, it satisfies our needs and it works. What more do you need? It also works for others, Github for example. The only thing I am missing at the moment is a native UUID type so I don't have to write functions that convert 16bit binary to textual representation and back when examining the data manually on the server.
  - dijit 3y ago
    I strongly dislike how people look to github as an example, its the highest appeal to authority. I know facebook uses mysql, but I also know that it is a bastardised custom version that has known constraints and has limited use (no foreign keys for example). I spoke to the DBA who first deployed MySQL at Github and the vibe I got from him immediately was that he had doubled down on his prejudice: which is fine, but its not ok to ignore that it can be a lot of effort to work around issues with any given technology. For a great example of what I mean: most people wouldn’t choose PHP for a new project (despite it having improved majorly) - the appeal to authority there is to say “it works for Facebook” without mentioning “Hack” or the myriad of internal processes to avoid the warts of PHP. That a large headcount company can use something does not make it immune from criticism.
    - capableweb 3y ago
      > most people wouldn’t choose PHP for a new project Is this really true? I used to be a full-time PHP developer but I personally don't touch that language anymore. But it's still very popular around the world, I've seen multiple projects start this year use PHP, because that's the language the founders/most developers in the company are familiar with. Probably depends a lot on where in the world you're located. Last Stack Overflow survey had ~20% of the people answering the survey saying that they still use PHP in some capacity.
      - deleted 3y ago
        [deleted]
      - sroussey 3y ago
        The beauty of PHP is that it is stateless and the end of the run, everything is freed. It is difficult to have memory leaks. Personally, I like using Typescript/Javascript on both front end and backend, but I don’t look down at PHP backends at all. And it’s come a long way as a language. I’ve been a fan of rolling your own stdlib as the semantics there are old and weird, but vscode tells you so who cares anymore.
    - stephenr 3y ago
- endorphine 3y ago
  As opposed to what? Postgres? Isn't InnoDB most performant for read-heavy apps?
  - dijit 3y ago
    MyISAM is actually considerably faster (than InnoDB) for read heavy apps. InnoDB is comparatively slow, but you get much better transactionality (IE; something that is much closer to ACID compliance). Row level locking is faster for inserts than table level locking, but table level locking is faster for reads than row level locking. Regardless: Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. (not that Postgresql is the only alternative btw).
    - VintageCool 3y ago
      There is no excuse to be using MyISAM instead of InnoDB in 2023. It was a scarcely forgivable mistake in 2013. The read performance advantages of MyISAM are solved better by using SSDs instead of HDDs. MyISAM will cost you dearly when performing actions like "trying to create a new replica of an existing database".
    - menaerus 3y ago
      MyISAM is not a transactional storage engine even to begin with, so saying that you get "much better transactionality with InnoDB" or "MyISAM is actually considerably faster" is either wrong or at best comparing apples to oranges. > Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. Strange take since a deadlock is rather an exceptional event you want never to occur so deadlocking, in algorithm design, wouldn't be considered a reason one would say that the implementation does not "scale with the core count". Whether or not the algorithm scales with the core count is for many other different reasons but not deadlocks. Considering the "scale with the core count" design problem, Postgres process-per-connection architecture makes it a much less viable option than, say, MySQL so this is wrong as well.
      - 3y ago
- Topgamer7 3y ago
  MySQL has some aggregation performance over postgres. Having done a recent migration of an application two things that come to mind are: - its case insensitive by default, which can make filtering simpler, without having to deal with a duplicate column where all values are lower/upper cased. - MySQL implements loose index scan and index skip scan, which improves performance of a number of join aggregation operations (https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan)
  - dpratt 3y ago
    | its case insensitive by default This is obviously up for debate, but subjectively I find this to be an absolutely terrible design decision.
    - hu3 3y ago
      I agree it's debatable. And not intuitive at first. With that said, in all my years and thousands of tables across multiple jobs, I have yet to see a single case where I had to change a table to be case sensitive. So I guess for me it is a sensible default.
- dissident_coder 3y ago
  First, MySQL is the "devil you know". If you've spent a decade working exclusively with MySQL quirks, you're just gonna be more comfortable with it regardless of quality. MySQL also tends to be faster for read-heavy workloads and simple queries. Also replication is easier to setup with MySQL in my (outdated) experience, even though it's gotten better with Postgres recently and I haven't really been able to compare them myself since I'm just using Amazon RDS Postgres these days and haven't had the need to setup master-master replication (which is the pain point in postgres, and was pretty straightfoward with mysql the last time I worked with it). Setting up read-replicas with postgres is still ezpz. Postgres specific features tend to be much better than MySQL ones, Postgresql JSON(b) support blows MySQL out of the water. And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. If you don't need that kind of stuff, you might be fine - but I would hate to hit a wall in my application where I want to reach for it and it's not there. MySQL used to be the only game in town, so it was the "default" choice - but IMO postgres has surpassed it.
  - darrenf 3y ago
    > And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. Do generated column indexes meet this need? CREATE TABLE json_with_id_index ( json_data JSON, id INT GENERATED ALWAYS AS (json_data->"$.id"), INDEX id (id) ) https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/8.0/en/create-table-seconda...
    - simcop2387 3y ago
      Looks like that would work as an expression index, though i can't tell at a glance if this requires the column to also be stored which would increase storage size (but probably isn't a huge problem if it is). But that likely won't work for dealing with the partial index case where you're only wanting to keep the ones that aren't null in the index to reduce the size (and speed up null/not null checks).
- Thaxll 3y ago
  Your memory is failing you maybe you don't remember not too long ago when PG did not have any replications built-in.
  - mrkeen 3y ago
    Not too long ago MySQL didn't have transactions. Edit: I would just love a comment from the person who thinks 'missing feature in the past' is wrong, unfair or irrelevant as a reply to a 'missing feature in the past' comment.
    - evanelias 3y ago
      There's a non-trivial nine-year difference between the things you're describing: the InnoDB storage engine was released in 2001. Postgres gained built-in replication in 2010. That said, personally I wouldn't describe either of these as "not too long ago". Technology rapidly changes and many things from either 2001 or 2010 are considered rather old.
- Freeaqingme 3y ago
  Also from an operations point of view it's quite easy to manage. I'm not that experienced with Postgresql, but my understanding is that until recently you had to vacuum it every once in a while. Besides, it's also using some kind of threading model that most people handle by putting a proxy in front of Postgres to keep connections open. Also, Mysql has had native replication for a very long time, including Galera which does two-step commit in a multimaster cluster. Although Postgres is making some headway in this regard, it is my impression that this is only quite recent and not yet fully up to par with Mysql yet.
  - stonemetal12 3y ago
    >my understanding is that until recently you had to vacuum it every once in a while. You still do. The auto Vacuum daemon was added in 2008ish, so it isn't too bad. Just more complexity to manage. > it's also using some kind of threading model It does a process per connection just like web servers did back in the day when C10k was a thing. A lot of the buffers are configured per connection so you can get bigger buffers if you keep the number of connections small.
  - dgellow 3y ago
    I think what you reference is known as Transaction ID Wraparound, Postgres still needs to be vacuumed to avoid that problem: https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql https://www.crunchydata.com/blog/managing-transaction-id-wra...
- adontz 3y ago
  Just want to add, that comparing to postgresql is a very modern view. There were other databases, not popular today, but quite popular back in the day. To name a few: DB2, InterBase, Firebird, Paradox, Access, SQL Server Compact. MySQL was a really shitty database in early 2000s, still THE most popular.
- viraptor 3y ago
  Others mentioned a few reasons already, but compared to postgres (because typically that's the other option) I'll add index selection. Even with the available plugins and stats and everything, I don't want to in an emergency situation spend time trying to indirectly convince postgres that it should use a different index. "A query takes 20x the time and you can't force it back immediately" is a really bad failure mode.
- deleted 3y ago
  [deleted]
- turtles3 3y ago
  This is a great question, and if the choice is between mysql and postgres, I would like to make the case that despite the current popular momentum behind postgres, mysql is a better default. Please note I'm not saying mysql is better, but in the absence of any other criteria, I would suggest stating with mysql. I have a few reasons for this view, but they mostly revolve around operational complexity. From a developer's point of view postgres is fantastic. Far saner SQL dialect, tons of great features. When it comes to operations though, that's where mysql has the edge, and ops is half of using a database - it's an important facet for a business to consider. As other commenters have mentioned, postgres requires careful tuning of the autovacuum process, otherwise it can't keep up as the workload grows. Postgres has a far more advanced query planner, but it comes at the cost of potentially blowing up your app at 3am, and it gives you no tools to patch in a quick fix while you address the root cause. This frankly ignores the reality of operating a business. Sometimes you need a quick fix, even if that might lead to users developing bad habits. Yes there is the pg_hint_plan extension, but that still only helps you later after the problem had happened. You can't pin a query plan. To me the ideal situation would be for postgres to continue to use the old query plan, but emit some structured log to tell you it thinks it's now suboptimal. But I digress. Thirdly, postgres has no way to have an index clustered table. This lets you trade a small cost on write for greater page locality when reading related rows. Postgres let's you do this as a one time operation that takes the table offline for the duration, which isn't sufficient if you need it. Fourthly, mysql is still easier to upgrade. You will need to upgrade your database at some point. Mysql has great support for upgrade in place, as well as using replication to build a new db. Mysql replication has always been logical replication, which has tradeoffs of course, but what it buys you is the ability to replicate across different versions. Pg's logical replication still has a bunch of sharp edges. Ok this rant is long enough already, but I do want to emphasise that this isn't hating on postgres. I know it's controversial to be recommending mysql over postgres, but I do think the ops concerns win out. Ps the orioledb project is fantastic and I hope it one day becomes the default for postgres.
  - wbl 3y ago
    Isn't working in the first place an ops concern?
  - 3y ago
- deleted 3y ago
  [deleted]
- lmm 3y ago
  > Why would anyone start a new project with MySQL? Is it really superior in anything? It's the most developer-friendly thing out there. Particularly for a datastore CLI, which is inherently something you use rarely, MySQL's is just a lot nicer, more discoverable. I think it has the least bad HA story among (free) traditional SQL-RDBMSes too (not that I understand why anyone would start a new project on a traditional SQL-RDBMS at all).
- otabdeveloper4 3y ago
  > Is it really superior in anything? Yes, its replication support out of the box is decades ahead of Postgres. (Mongodb has an even better replication story than Myqsl, but Mongo isn't a real database.)
- bojanz 3y ago
  I am going to be controversial for a hot second, and say that in many ways MySQL is a more advanced and better implemented database at its core. Disclaimer: as a developer I love and prefer Postgres. But I've been on many projects where MySQL won for ops-related reasons. Postgres has a MVCC implementation that is recognized as inferior[0] to what MySQL and Oracle do, and requires dealing with vacuuming and all of its related problems. Postgres has a process-based connection model that is recognized as less optimal than the thread-based one that MySQL has. There are ongoing efforts[1] to move Postgres to a thread-based model but it's recognized as a large and uncertain undertaking. Other commenters have also explained the still very noticeable difference in replication support, the lack of query planner hints, the less intuitive local tooling. One thing to keep in mind is that both databases keep evolving, and old prejudices won't take us far. Postgres is improving its performance and replication support with each release. MySQL 8.0 added atomic DDL and a new query planner (MariaDB did their own query planner rework in 11.0, widening their differences). Both are improving their observability. So the race is far from over. But I definitely wouldn't count MySQL out. [0] https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... [1] https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...
- misiek08 3y ago
  I don't want to be subjective or start any war, but only to broaden my horizons and perspective. Saying this I want to clear one thing - I use multiple engines, trying to best match one for the problem I'm solving. What do you recommend these days?
beltsazar 3y ago
I understand why the default transaction isolation level of most DBMS is weaker than serializable (it's for benchmark purposes), but I'd argue the best default is serializable. Most DBMS users don't even know there are many consistency models [1]. They expect transactions to "just work," i.e. to appear to have occurred in some total order, which is the definition of serializability [2]. And to some who know when to use a weaker isolation level for better performance can always set it per transaction [3]. --- [1] https://jepsen.io/consistency https://jepsen.io/consistency [2] https://jepsen.io/consistency/models/serializable https://jepsen.io/consistency/models/serializable [3] https://www.postgresql.org/docs/16/sql-set-transaction.html https://www.postgresql.org/docs/16/sql-set-transaction.html
- Thaxll 3y ago
  It has a very high cost in terms of performance.
  - eatonphil 3y ago
    Is there any measurement of the impact you could point at? For example, I imagine that it depends on the workload. If the workload isn't contentious SERIALIZABLE might not make a big difference? Then again if the workload isn't contentious maybe it doesn't matter? Either way, I'd love to see numbers. Not because I don't believe anyone but I'm just curious what ballpark we're talking about. Edit: Also, SQLite and Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable.
    - bennysaurus 3y ago
      You're right it is workload dependent. If you're low write but read heavy, you won't see huge differences in performance between RR and Serializable, so it can make sense to shift exclusively to that. The last benchmark here shows some of that with Postgres if you're looking for numbers (not an exhaustive test by any stretch): https://lchsk.com/benchmarking-concurrent-operations-in-postgresql https://lchsk.com/benchmarking-concurrent-operations-in-post... SQLite is single writer, so transaction isolation is easy, writes are linear by their very nature. Cockroach does some really funky stuff, but its serialization guarantees are only within certain conditions. Traditionally it has also had low write throughput compared to other systems, mainly due to its distributed nature. Jepsen touches on that here https://jepsen.io/analyses/cockroachdb-beta-20160829 https://jepsen.io/analyses/cockroachdb-beta-20160829 though things have vastly improved since then. To your earlier point, it may not even matter depending on the workload, or if you're aware of your database limitations. In cases where it does matter then being aware of the limitations of something like Repeatable Read makes the trade-off worth it.
klysm 3y ago
In my experience, most developers don't even consider isolation level in the first place and just take whatever the default is. Any race conditions are met with an 'oh that's weird', and then they move on.
- beltsazar 3y ago
  Exactly! That's why I just commented [1] that the default isolation level should be serializable. [1] https://news.ycombinator.com/item?id=38696421 https://news.ycombinator.com/item?id=38696421
  - klysm 3y ago
    Completely agree, I've made that exact same argument on HN before.
- nordsieck 3y ago
  I wish I could argue with you, but the highly successful early years of MongoDB proves your point nicely.
  - Thaxll 3y ago
    Mongodb did not have those concerns because work on a single document is atomic and there are no joins.
    - aphyr 3y ago
      This depends on what you mean by "atomic". Prior to 5.0, MongoDB's defaults were to use a sub-majority write concern. This allowed all kinds of interesting atomicity violations, even on single documents. For instance, you could write a value, some clients might read it, and then your write would be silently lost as if it never happened. Clients could disagree on whether the write happened or not. A single client could observe, then un-observe the write. It gets weird. :-)
      - 3y ago
amluto 3y ago
How does append (a) map onto actual SQL operations on the given tables? Are the TEXT fields being used as lists? Also… I’ve been issues in MySQL repeatable read mode where a single SELECT, selecting a single row, returned impossible results. I think it was: SELECT min(value), max(value) FROM table WHERE id = 1; where id is a primary key. I got two different values for min and max. That was a fun one.
- deleted 3y ago
  [deleted]
- aphyr 3y ago
  Yup! See https://jepsen.io/analyses/mysql-8.0.34#list-append https://jepsen.io/analyses/mysql-8.0.34#list-append, which also has a link to the code: https://github.com/jepsen-io/mysql/blob/4c239cb5c66a7f1a55fa02ce4c9f43b7a70e9d0b/src/jepsen/mysql/append.clj#L33 https://github.com/jepsen-io/mysql/blob/4c239cb5c66a7f1a55fa... This isn't CONCAT-specific, BTW--we just use CONCAT because it allows us to infer anomalies in linear, rather than exponential time. Same kinds of behaviors manifest with plain old read/write registers.
baq 3y ago
how about that, I planned to do some work today. aphyr, thank you. call me maybe and later jepsen.io have been consistently some of the best content I've ever read on the internet.
- aphyr 3y ago
  Aw shucks, thanks :-)
  - don_neufeld 3y ago
    Seriously, thanks for what you do! I’ve been reading your stuff for almost 10 years and doing work at this level of rigor makes the world a better place.
  - shepherdjerred 3y ago
    I don't use Jepsen, but I love your blog! Your "x the Technical Interview" series are my absolute favorite.
deleted 3y ago
[deleted]
PeterCorless 3y ago
How much of what is contained within this analysis of MySQL is going to be the same-same for MariaDB, given that it uses InnoDB as the default storage engine?
- dijit 3y ago
  I think they've been diverged long enough that we can consider them separate products at this point. (14 years!) You wouldn't assume that Plex and XMBC shared much compatibility despite forking around the same time.
  - PeterCorless 3y ago
    I guess I was wondering how much of this behavior is endemic to MySQL, per se, and how much was endemic to InnoDB.
- mdaniel 3y ago
  I don't want to speak out of school, because I've never tried to boot up Jepsen for anything, but in theory the purpose of publishing the code for the experiment is that one can replicate its findings in your own environment to see if it impacts you. Yes, I'd guess that custom FUSE will be a PITA to configure but my experience with the AWS RDS setups for kicking the tires on MariaDB is (ahem) just money versus costing huge amounts of glucose
  - aphyr 3y ago
    The FUSE stuff actually isn't too bad--Jepsen goes to a lot of trouble to make all this stuff automatic. The test harness pulls dependencies, compiles LazyFS, and mounts the filesystem for you. Just pass `--lazyfs` at the CLI. :-)
- semiquaver 3y ago
  Per the article, MariaDB was also tested. > We designed a small test suite for MySQL using the Jepsen testing library at version 0.3.4. We used the mysql-connector-j JDBC adapter as our client. We tested MySQL 8.0.34, and MariaDB 10.11.3 on Debian Bookworm. Our tests ran against a single MySQL node as well as binlog-replicated clusters with one or two read-only followers, without failover. We also ran our test suite against a hosted MySQL service: AWS’s RDS Cluster, using the “Multi-AZ DB Cluster” profile. This is the recommended default for production workloads, and offers a binlog-replicated deployment of MySQL 8.0.34 where secondary nodes support read queries. Most concerning to me is how practically none of these had anything to do with “distributed computing”. It seems that MySQL in single-server mode is still liable to corrupt data with nontrivial workloads.
pella 3y ago
FOSSDEM-2024 : Isolation Levels and MVCC in SQL Databases: A Technical Comparative Study // Oracle, MySQL, SQL Server, PostgreSQL, and YugabyteDB. https://fosdem.org/2024/schedule/event/fosdem-2024-3600-isolation-levels-and-mvcc-in-sql-databases-a-technical-comparative-study/ https://fosdem.org/2024/schedule/event/fosdem-2024-3600-isol...
- hmottestad 3y ago
  The speaker is a developer advocate working for YugabyteDB. How does this relate to Kyle's work?
dasmoop 3y ago
The RDS replication that stopped working after 5min messing with it, with no alert of failed health check is a bit worrying...
- mdaniel 3y ago
  Obviously the devil's in the details, and it's almost impossible to troubleshoot from a screencast, but my experience has been that AWS is generally pretty liberal with the CloudWatch Metrics, but does place the onus upon the user to dig through the 150++ of them to read the docs to find the one that matters. They also claim <https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.Monitoring https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...> there's a console table cell for the replication status, but my experience with the console is that often one must opt-in to having that column shown which is suboptimal :-( That "shared responsibility model," they lean on it heavily
- mianos 3y ago
  I can assure you, you can not trust any AWS health checks to be a primary alert for something down. You have to do it all yourself, on host, or inside the container. AWS/Rackspace support just say: "It's your problem as we don't manage what is inside the AWS service".
PeterZaitsev 3y ago
Facinating read. I think it is a great illustration to show how many "practically working systems" can be built on the foundation exhibiting so many consistency artifacts
- klysm 3y ago
  Most systems are practically broken and it’s worked around by human factors
hipadev23 3y ago
"SELECT ... FOR UPDATE" seems to be the answer to all these issues right? Lock the rows you're going to be updating and suddenly everything works as advertised.
- xxpor 3y ago
  If you want performance to completely tank, sure.
  - hipadev23 3y ago
    Afaik, it only locks the row(s) in question. Do you have a setup where the same row is being updated by multiple clients all the time?
    - jtc331 3y ago
      SELECT FOR UPDATE means two queries can’t _read_ the same row at the same time, right?
      - hipadev23 3y ago
        To my understanding, yes. If you have broad select queries across all the rows, that would slow things down quite a bit.
        dilyevsky 3y ago
        You can use SKIP LOCKED normally. That’s how popular queue implementations usually work. I believe pg even calls this out in their docs
    - quickthrower2 3y ago
      SQL Server will escalate locks. Because keeping a million rows locked is expensive.
    - dig1 3y ago
      This can easily happen if you have multiple services that can write/update in the same table/rows and rely on database coordination instead of using an external queue like Kafka. Postgres (presumably other databases) can propagate these locks to table locks [1] and cause contention for the whole infra. [1] https://blog.heroku.com/curious-case-table-locking-update-query https://blog.heroku.com/curious-case-table-locking-update-qu...
rob-olmos 3y ago
Related: ACIDRain for the repeatable read without explicit "select .. for update" locking gotcha gift that still keeps on giving: https://news.ycombinator.com/item?id=20027532 https://news.ycombinator.com/item?id=20027532
tzone 3y ago
I have been advocating for the longest time that "repeatable read" is just a bad idea. Even if implementations were perfect. Even when it works correctly in the Database, it is still very tricky to reason about when dealing with complex queries. I think two isolation levels that make sense are either: * read committed * serializable You either go all the way to have a serializable setup, where there are no surprises. OR, you go in read committed direction where it is obvious that if you want have a consistent view of the data within a transaction, you have to lock the rows before you start reading them. Read committed is very similar to just regular multi-threaded code and its memory management, so most engineers can get a decent intuitive sense for it. Serializable is so strict that it is pretty hard to make very unexpected mistakes. Anything in-between is a no man's land. And anything less consistent than Read Committed is no longer really a database.
- klysm 3y ago
  But you wouldn’t need to lock them if repeatable read actually worked
  - tzone 3y ago
    That is sort of my point. I think "repeatable read" is a fools gold. You think you wont need to do locking, but it is too easy to make incorrect assumptions about what guarantees "repeatable read" provides and you can make very subtle mistakes which leads to rare, extremely hard to diagnose correctness issues. Repeatable read type of setups also make it much easier to accidentally create much longer running transactions, and long running transactions/too many concurrent open transactions/etc can create really unexpected, very hard to resolve performance issues in the long run for any database.
    - karmakaze 3y ago
      I agree that "read committed" is clearer in knowing what you're getting than "repeatable read". The latter can be convenient and workable if you accept that you will need to do locking to avoid write skew, etc. I've used both "read committed" and "repeatable read" with MySQL and learned to deal with each in their own way. The problem I've seen is with large/long-lived transactions that impact performance, where the solution is to divide writes into smaller transactions in the design--"read committed" does tend to encourage smaller transactions.
Corrado 3y ago
I appreciate the write-up and the nod to AWS RDS. However, I was wondering if there was any focus on AWS Aurora (MySQL)? For those that don't know, AWS build a protocol compatible database platform that pretends to be MySQL or PostgreSQL. It would be interesting to see if Aurora MySQL has the same "features" as RDS or even MariaDB.
- Reubend 3y ago
  No, because that would be a totally different DB engine with different concurrency issues. Although that would also be super interesting to read, and my intuition is that because Aurora is a much newer DB, it probably has some subtle issues that haven't been discovered yet versus MySQL, which is quite old by now.
  - evanelias 3y ago
    Aurora MySQL is based heavily on MySQL/InnoDB's codebase. It's not a complete reimplementation from scratch. My guess would be that it exhibits some or all of these same issues [edit to add: see footnote 2]. With a single-node cluster, I don't ever recall reading anything about Aurora offering different MVCC or isolation level semantics than upstream InnoDB. AWS documentation says "These isolation levels work the same in Aurora MySQL as in RDS for MySQL" [1] and keep in mind standard non-Aurora RDS is much closer to unmodified upstream MySQL. That said, there are some unique wrinkles in Aurora's cluster behavior due to the shared storage. For example, if you keep the default isolation level of repeatable read, long-running queries on Aurora Replicas will inherently block purge of old-row versions on the whole cluster. In contrast, a traditional MySQL replica set (using async binlog replication) does not behave that way, because each replica has its own storage and own purge threads. [1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.IsolationLevels.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... [2] Re-reading the Jepsen results, it appears all of these anomalies come from the exact same documented InnoDB behavior: "If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows" as per https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-re... -- and presumably Aurora maintains the same behavior, since otherwise it would break compatibility with MySQL in extremely subtle and confusing ways.
PeterZaitsev 3y ago
What would be helpful is not just comparison to theoretical definition to isolation modes but rather comparison to other popular relational databases - PostgreSQL, MS SQL, Oracle ? Something developers need to mind if they want to assure compatibility
- eatonphil 3y ago
  Beyond the Kleppmann Hermitage work that is linked in this post? https://github.com/ept/hermitage https://github.com/ept/hermitage
  - nop_slide 3y ago
    Woah that is a great resource, I've been looking for something like this which shows an easy way to exemplify the various anomalies. Thanks for highlighting it!
camgunz 3y ago
> In 2022 Jepsen commissioned the University of Porto’s INESC TEC to develop LazyFS: a FUSE filesystem for simulating the loss of un-fsynced writes I love this; what a great example of pushing the state of the art forward. Kudos!
karmakaze 3y ago
My takeaways: > 4.2 Recommendations > The core problem is that MySQL claims to implement Repeatable Read but actually provides something much weaker. We see two avenues to resolve this problem. > The first is to keep MySQL’s behavior as it is, and to clearly document the consistency model “Repeatable Read” actually provides. There is precedent in other databases: PostgreSQL’s Repeatable Read is actually Snapshot Isolation, and exhibits behaviors which violate PL-2.99 Repeatable Read. However, PostgreSQL’s documentation eventually mentions that their Repeatable Read implementation is actually Snapshot Isolation. MySQL could similarly document that their “Repeatable Read” means “Read Committed, plus some sort of guarantees that hold until the transaction writes something, at which point mysteries occur.” A precise characterization of those mysteries would be most welcome. Calling what MySQL's "Repeatable Read" as "Read Committed plus..." would be more confusing as even the simplest repeated read without mutations wouldn't work as expected. The documentation should be more upfront about how MySQL "Repeatable Read" doesn't mean what might be expected. In the meantime keep the "MySQL consistent read documentation"[0] close by. > The second option is to treat these behaviors as bugs and fix them. Jepsen would be delighted if MySQL and other vendors were to commit to providing PL-2.99 Repeatable Read. However, even satisfying the incomplete, ambiguous ANSI definition of Repeatable Read would be an improvement over current affairs. I doubt this would be feasible with the extent of deployment. At scale bug-fixes are bugs in themselves. The best that could be done is to create distinct isolation levels for the existing MySQL-RR and the compliant RR isolation levels (somewhat akin to the utf8/utf8mb4 evolution). [0] https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-re...

364 points by aphyr 3y ago

Serious question. I have this question for, like 20 years already. Why would anyone start a new project with MySQL? Is it really superior in anything? I'm in industry for 20+ years and as far as I remember MySQL was always the worst and most popular RDBMS at any given moment.

willvarfar 3y ago
Not up to date, but a decade ago MySQL supported pluggable storage engines and so had had some good non-default choices. It was possible to fit really big databases onto small boxes using tokudb, for example. This doesn't explain why it was so popular for starting new small projects, but people were also choosing mongodb at that time too, so ymmv :) Nowadays postgres has grown a lot of features but I believe it is still behind on built-in compression? Added: this old blog post of mine is still getting traffic 10 years later; probably still valid https://williame.github.io/post/25080396258.html https://williame.github.io/post/25080396258.html
Keyframe 3y ago
Once upon a time it was easi-ish to scale out, and was simple to use and fast. There was Percona as well. These days, who knows? Might still be true.
WJW 3y ago
- It works well enough. - It scales up fine for 99+% of companies, and for the ones that need to scale beyond that there are battle tested solutions like Vitess. - It is what people know already so they don't have to learn anything new. Same reason why people still make new websites in PHP I guess. It's not fancy but it works fine and won't bring any unwelcome surprises.
gog 3y ago
We use it, we know it and can troubleshoot it if needed, it satisfies our needs and it works. What more do you need? It also works for others, Github for example. The only thing I am missing at the moment is a native UUID type so I don't have to write functions that convert 16bit binary to textual representation and back when examining the data manually on the server.
- dijit 3y ago
  I strongly dislike how people look to github as an example, its the highest appeal to authority. I know facebook uses mysql, but I also know that it is a bastardised custom version that has known constraints and has limited use (no foreign keys for example). I spoke to the DBA who first deployed MySQL at Github and the vibe I got from him immediately was that he had doubled down on his prejudice: which is fine, but its not ok to ignore that it can be a lot of effort to work around issues with any given technology. For a great example of what I mean: most people wouldn’t choose PHP for a new project (despite it having improved majorly) - the appeal to authority there is to say “it works for Facebook” without mentioning “Hack” or the myriad of internal processes to avoid the warts of PHP. That a large headcount company can use something does not make it immune from criticism.
  - capableweb 3y ago
    > most people wouldn’t choose PHP for a new project Is this really true? I used to be a full-time PHP developer but I personally don't touch that language anymore. But it's still very popular around the world, I've seen multiple projects start this year use PHP, because that's the language the founders/most developers in the company are familiar with. Probably depends a lot on where in the world you're located. Last Stack Overflow survey had ~20% of the people answering the survey saying that they still use PHP in some capacity.
    - deleted 3y ago
      [deleted]
    - sroussey 3y ago
      The beauty of PHP is that it is stateless and the end of the run, everything is freed. It is difficult to have memory leaks. Personally, I like using Typescript/Javascript on both front end and backend, but I don’t look down at PHP backends at all. And it’s come a long way as a language. I’ve been a fan of rolling your own stdlib as the semantics there are old and weird, but vscode tells you so who cares anymore.
  - stephenr 3y ago
endorphine 3y ago
As opposed to what? Postgres? Isn't InnoDB most performant for read-heavy apps?
- dijit 3y ago
  MyISAM is actually considerably faster (than InnoDB) for read heavy apps. InnoDB is comparatively slow, but you get much better transactionality (IE; something that is much closer to ACID compliance). Row level locking is faster for inserts than table level locking, but table level locking is faster for reads than row level locking. Regardless: Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. (not that Postgresql is the only alternative btw).
  - VintageCool 3y ago
    There is no excuse to be using MyISAM instead of InnoDB in 2023. It was a scarcely forgivable mistake in 2013. The read performance advantages of MyISAM are solved better by using SSDs instead of HDDs. MyISAM will cost you dearly when performing actions like "trying to create a new replica of an existing database".
  - menaerus 3y ago
    MyISAM is not a transactional storage engine even to begin with, so saying that you get "much better transactionality with InnoDB" or "MyISAM is actually considerably faster" is either wrong or at best comparing apples to oranges. > Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. Strange take since a deadlock is rather an exceptional event you want never to occur so deadlocking, in algorithm design, wouldn't be considered a reason one would say that the implementation does not "scale with the core count". Whether or not the algorithm scales with the core count is for many other different reasons but not deadlocks. Considering the "scale with the core count" design problem, Postgres process-per-connection architecture makes it a much less viable option than, say, MySQL so this is wrong as well.
    - 3y ago
Topgamer7 3y ago
MySQL has some aggregation performance over postgres. Having done a recent migration of an application two things that come to mind are: - its case insensitive by default, which can make filtering simpler, without having to deal with a duplicate column where all values are lower/upper cased. - MySQL implements loose index scan and index skip scan, which improves performance of a number of join aggregation operations (https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan)
- dpratt 3y ago
  | its case insensitive by default This is obviously up for debate, but subjectively I find this to be an absolutely terrible design decision.
  - hu3 3y ago
    I agree it's debatable. And not intuitive at first. With that said, in all my years and thousands of tables across multiple jobs, I have yet to see a single case where I had to change a table to be case sensitive. So I guess for me it is a sensible default.
dissident_coder 3y ago
First, MySQL is the "devil you know". If you've spent a decade working exclusively with MySQL quirks, you're just gonna be more comfortable with it regardless of quality. MySQL also tends to be faster for read-heavy workloads and simple queries. Also replication is easier to setup with MySQL in my (outdated) experience, even though it's gotten better with Postgres recently and I haven't really been able to compare them myself since I'm just using Amazon RDS Postgres these days and haven't had the need to setup master-master replication (which is the pain point in postgres, and was pretty straightfoward with mysql the last time I worked with it). Setting up read-replicas with postgres is still ezpz. Postgres specific features tend to be much better than MySQL ones, Postgresql JSON(b) support blows MySQL out of the water. And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. If you don't need that kind of stuff, you might be fine - but I would hate to hit a wall in my application where I want to reach for it and it's not there. MySQL used to be the only game in town, so it was the "default" choice - but IMO postgres has surpassed it.
- darrenf 3y ago
  > And as far as I can remember MySQL still doesn't support partial/expression indexes, which is a deal breaker for me. Especially in my json heavy workloads where being able to index specific json paths is critical for performance. Do generated column indexes meet this need? CREATE TABLE json_with_id_index ( json_data JSON, id INT GENERATED ALWAYS AS (json_data->"$.id"), INDEX id (id) ) https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/8.0/en/create-table-seconda...
  - simcop2387 3y ago
    Looks like that would work as an expression index, though i can't tell at a glance if this requires the column to also be stored which would increase storage size (but probably isn't a huge problem if it is). But that likely won't work for dealing with the partial index case where you're only wanting to keep the ones that aren't null in the index to reduce the size (and speed up null/not null checks).
Thaxll 3y ago
Your memory is failing you maybe you don't remember not too long ago when PG did not have any replications built-in.
- mrkeen 3y ago
  Not too long ago MySQL didn't have transactions. Edit: I would just love a comment from the person who thinks 'missing feature in the past' is wrong, unfair or irrelevant as a reply to a 'missing feature in the past' comment.
  - evanelias 3y ago
    There's a non-trivial nine-year difference between the things you're describing: the InnoDB storage engine was released in 2001. Postgres gained built-in replication in 2010. That said, personally I wouldn't describe either of these as "not too long ago". Technology rapidly changes and many things from either 2001 or 2010 are considered rather old.
Freeaqingme 3y ago
Also from an operations point of view it's quite easy to manage. I'm not that experienced with Postgresql, but my understanding is that until recently you had to vacuum it every once in a while. Besides, it's also using some kind of threading model that most people handle by putting a proxy in front of Postgres to keep connections open. Also, Mysql has had native replication for a very long time, including Galera which does two-step commit in a multimaster cluster. Although Postgres is making some headway in this regard, it is my impression that this is only quite recent and not yet fully up to par with Mysql yet.
- stonemetal12 3y ago
  >my understanding is that until recently you had to vacuum it every once in a while. You still do. The auto Vacuum daemon was added in 2008ish, so it isn't too bad. Just more complexity to manage. > it's also using some kind of threading model It does a process per connection just like web servers did back in the day when C10k was a thing. A lot of the buffers are configured per connection so you can get bigger buffers if you keep the number of connections small.
- dgellow 3y ago
  I think what you reference is known as Transaction ID Wraparound, Postgres still needs to be vacuumed to avoid that problem: https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql https://www.crunchydata.com/blog/managing-transaction-id-wra...
adontz 3y ago
Just want to add, that comparing to postgresql is a very modern view. There were other databases, not popular today, but quite popular back in the day. To name a few: DB2, InterBase, Firebird, Paradox, Access, SQL Server Compact. MySQL was a really shitty database in early 2000s, still THE most popular.
viraptor 3y ago
Others mentioned a few reasons already, but compared to postgres (because typically that's the other option) I'll add index selection. Even with the available plugins and stats and everything, I don't want to in an emergency situation spend time trying to indirectly convince postgres that it should use a different index. "A query takes 20x the time and you can't force it back immediately" is a really bad failure mode.
deleted 3y ago
[deleted]
turtles3 3y ago
This is a great question, and if the choice is between mysql and postgres, I would like to make the case that despite the current popular momentum behind postgres, mysql is a better default. Please note I'm not saying mysql is better, but in the absence of any other criteria, I would suggest stating with mysql. I have a few reasons for this view, but they mostly revolve around operational complexity. From a developer's point of view postgres is fantastic. Far saner SQL dialect, tons of great features. When it comes to operations though, that's where mysql has the edge, and ops is half of using a database - it's an important facet for a business to consider. As other commenters have mentioned, postgres requires careful tuning of the autovacuum process, otherwise it can't keep up as the workload grows. Postgres has a far more advanced query planner, but it comes at the cost of potentially blowing up your app at 3am, and it gives you no tools to patch in a quick fix while you address the root cause. This frankly ignores the reality of operating a business. Sometimes you need a quick fix, even if that might lead to users developing bad habits. Yes there is the pg_hint_plan extension, but that still only helps you later after the problem had happened. You can't pin a query plan. To me the ideal situation would be for postgres to continue to use the old query plan, but emit some structured log to tell you it thinks it's now suboptimal. But I digress. Thirdly, postgres has no way to have an index clustered table. This lets you trade a small cost on write for greater page locality when reading related rows. Postgres let's you do this as a one time operation that takes the table offline for the duration, which isn't sufficient if you need it. Fourthly, mysql is still easier to upgrade. You will need to upgrade your database at some point. Mysql has great support for upgrade in place, as well as using replication to build a new db. Mysql replication has always been logical replication, which has tradeoffs of course, but what it buys you is the ability to replicate across different versions. Pg's logical replication still has a bunch of sharp edges. Ok this rant is long enough already, but I do want to emphasise that this isn't hating on postgres. I know it's controversial to be recommending mysql over postgres, but I do think the ops concerns win out. Ps the orioledb project is fantastic and I hope it one day becomes the default for postgres.
- wbl 3y ago
  Isn't working in the first place an ops concern?
- 3y ago
deleted 3y ago
[deleted]
lmm 3y ago
> Why would anyone start a new project with MySQL? Is it really superior in anything? It's the most developer-friendly thing out there. Particularly for a datastore CLI, which is inherently something you use rarely, MySQL's is just a lot nicer, more discoverable. I think it has the least bad HA story among (free) traditional SQL-RDBMSes too (not that I understand why anyone would start a new project on a traditional SQL-RDBMS at all).
otabdeveloper4 3y ago
> Is it really superior in anything? Yes, its replication support out of the box is decades ahead of Postgres. (Mongodb has an even better replication story than Myqsl, but Mongo isn't a real database.)
bojanz 3y ago
I am going to be controversial for a hot second, and say that in many ways MySQL is a more advanced and better implemented database at its core. Disclaimer: as a developer I love and prefer Postgres. But I've been on many projects where MySQL won for ops-related reasons. Postgres has a MVCC implementation that is recognized as inferior[0] to what MySQL and Oracle do, and requires dealing with vacuuming and all of its related problems. Postgres has a process-based connection model that is recognized as less optimal than the thread-based one that MySQL has. There are ongoing efforts[1] to move Postgres to a thread-based model but it's recognized as a large and uncertain undertaking. Other commenters have also explained the still very noticeable difference in replication support, the lack of query planner hints, the less intuitive local tooling. One thing to keep in mind is that both databases keep evolving, and old prejudices won't take us far. Postgres is improving its performance and replication support with each release. MySQL 8.0 added atomic DDL and a new query planner (MariaDB did their own query planner rework in 11.0, widening their differences). Both are improving their observability. So the race is far from over. But I definitely wouldn't count MySQL out. [0] https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... [1] https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd...
misiek08 3y ago
I don't want to be subjective or start any war, but only to broaden my horizons and perspective. Saying this I want to clear one thing - I use multiple engines, trying to best match one for the problem I'm solving. What do you recommend these days?

beltsazar 3y ago

I understand why the default transaction isolation level of most DBMS is weaker than serializable (it's for benchmark purposes), but I'd argue the best default is serializable. Most DBMS users don't even know there are many consistency models [1]. They expect transactions to "just work," i.e. to appear to have occurred in some total order, which is the definition of serializability [2]. And to some who know when to use a weaker isolation level for better performance can always set it per transaction [3]. --- [1] https://jepsen.io/consistency https://jepsen.io/consistency [2] https://jepsen.io/consistency/models/serializable https://jepsen.io/consistency/models/serializable [3] https://www.postgresql.org/docs/16/sql-set-transaction.html https://www.postgresql.org/docs/16/sql-set-transaction.html

Thaxll 3y ago
It has a very high cost in terms of performance.
- eatonphil 3y ago
  Is there any measurement of the impact you could point at? For example, I imagine that it depends on the workload. If the workload isn't contentious SERIALIZABLE might not make a big difference? Then again if the workload isn't contentious maybe it doesn't matter? Either way, I'd love to see numbers. Not because I don't believe anyone but I'm just curious what ballpark we're talking about. Edit: Also, SQLite and Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable.
  - bennysaurus 3y ago
    You're right it is workload dependent. If you're low write but read heavy, you won't see huge differences in performance between RR and Serializable, so it can make sense to shift exclusively to that. The last benchmark here shows some of that with Postgres if you're looking for numbers (not an exhaustive test by any stretch): https://lchsk.com/benchmarking-concurrent-operations-in-postgresql https://lchsk.com/benchmarking-concurrent-operations-in-post... SQLite is single writer, so transaction isolation is easy, writes are linear by their very nature. Cockroach does some really funky stuff, but its serialization guarantees are only within certain conditions. Traditionally it has also had low write throughput compared to other systems, mainly due to its distributed nature. Jepsen touches on that here https://jepsen.io/analyses/cockroachdb-beta-20160829 https://jepsen.io/analyses/cockroachdb-beta-20160829 though things have vastly improved since then. To your earlier point, it may not even matter depending on the workload, or if you're aware of your database limitations. In cases where it does matter then being aware of the limitations of something like Repeatable Read makes the trade-off worth it.

klysm 3y ago

In my experience, most developers don't even consider isolation level in the first place and just take whatever the default is. Any race conditions are met with an 'oh that's weird', and then they move on.

beltsazar 3y ago
Exactly! That's why I just commented [1] that the default isolation level should be serializable. [1] https://news.ycombinator.com/item?id=38696421 https://news.ycombinator.com/item?id=38696421
- klysm 3y ago
  Completely agree, I've made that exact same argument on HN before.
nordsieck 3y ago
I wish I could argue with you, but the highly successful early years of MongoDB proves your point nicely.
- Thaxll 3y ago
  Mongodb did not have those concerns because work on a single document is atomic and there are no joins.
  - aphyr 3y ago
    This depends on what you mean by "atomic". Prior to 5.0, MongoDB's defaults were to use a sub-majority write concern. This allowed all kinds of interesting atomicity violations, even on single documents. For instance, you could write a value, some clients might read it, and then your write would be silently lost as if it never happened. Clients could disagree on whether the write happened or not. A single client could observe, then un-observe the write. It gets weird. :-)
    - 3y ago

amluto 3y ago

How does append (a) map onto actual SQL operations on the given tables? Are the TEXT fields being used as lists? Also… I’ve been issues in MySQL repeatable read mode where a single SELECT, selecting a single row, returned impossible results. I think it was: SELECT min(value), max(value) FROM table WHERE id = 1; where id is a primary key. I got two different values for min and max. That was a fun one.

deleted 3y ago
[deleted]
aphyr 3y ago
Yup! See https://jepsen.io/analyses/mysql-8.0.34#list-append https://jepsen.io/analyses/mysql-8.0.34#list-append, which also has a link to the code: https://github.com/jepsen-io/mysql/blob/4c239cb5c66a7f1a55fa02ce4c9f43b7a70e9d0b/src/jepsen/mysql/append.clj#L33 https://github.com/jepsen-io/mysql/blob/4c239cb5c66a7f1a55fa... This isn't CONCAT-specific, BTW--we just use CONCAT because it allows us to infer anomalies in linear, rather than exponential time. Same kinds of behaviors manifest with plain old read/write registers.

baq 3y ago

how about that, I planned to do some work today. aphyr, thank you. call me maybe and later jepsen.io have been consistently some of the best content I've ever read on the internet.

aphyr 3y ago
Aw shucks, thanks :-)
- don_neufeld 3y ago
  Seriously, thanks for what you do! I’ve been reading your stuff for almost 10 years and doing work at this level of rigor makes the world a better place.
- shepherdjerred 3y ago
  I don't use Jepsen, but I love your blog! Your "x the Technical Interview" series are my absolute favorite.

deleted 3y ago

[deleted]

PeterCorless 3y ago

How much of what is contained within this analysis of MySQL is going to be the same-same for MariaDB, given that it uses InnoDB as the default storage engine?

dijit 3y ago
I think they've been diverged long enough that we can consider them separate products at this point. (14 years!) You wouldn't assume that Plex and XMBC shared much compatibility despite forking around the same time.
- PeterCorless 3y ago
  I guess I was wondering how much of this behavior is endemic to MySQL, per se, and how much was endemic to InnoDB.
mdaniel 3y ago
I don't want to speak out of school, because I've never tried to boot up Jepsen for anything, but in theory the purpose of publishing the code for the experiment is that one can replicate its findings in your own environment to see if it impacts you. Yes, I'd guess that custom FUSE will be a PITA to configure but my experience with the AWS RDS setups for kicking the tires on MariaDB is (ahem) just money versus costing huge amounts of glucose
- aphyr 3y ago
  The FUSE stuff actually isn't too bad--Jepsen goes to a lot of trouble to make all this stuff automatic. The test harness pulls dependencies, compiles LazyFS, and mounts the filesystem for you. Just pass `--lazyfs` at the CLI. :-)
semiquaver 3y ago
Per the article, MariaDB was also tested. > We designed a small test suite for MySQL using the Jepsen testing library at version 0.3.4. We used the mysql-connector-j JDBC adapter as our client. We tested MySQL 8.0.34, and MariaDB 10.11.3 on Debian Bookworm. Our tests ran against a single MySQL node as well as binlog-replicated clusters with one or two read-only followers, without failover. We also ran our test suite against a hosted MySQL service: AWS’s RDS Cluster, using the “Multi-AZ DB Cluster” profile. This is the recommended default for production workloads, and offers a binlog-replicated deployment of MySQL 8.0.34 where secondary nodes support read queries. Most concerning to me is how practically none of these had anything to do with “distributed computing”. It seems that MySQL in single-server mode is still liable to corrupt data with nontrivial workloads.

pella 3y ago

FOSSDEM-2024 : Isolation Levels and MVCC in SQL Databases: A Technical Comparative Study // Oracle, MySQL, SQL Server, PostgreSQL, and YugabyteDB. https://fosdem.org/2024/schedule/event/fosdem-2024-3600-isolation-levels-and-mvcc-in-sql-databases-a-technical-comparative-study/ https://fosdem.org/2024/schedule/event/fosdem-2024-3600-isol...

hmottestad 3y ago
The speaker is a developer advocate working for YugabyteDB. How does this relate to Kyle's work?

dasmoop 3y ago

The RDS replication that stopped working after 5min messing with it, with no alert of failed health check is a bit worrying...

mdaniel 3y ago
Obviously the devil's in the details, and it's almost impossible to troubleshoot from a screencast, but my experience has been that AWS is generally pretty liberal with the CloudWatch Metrics, but does place the onus upon the user to dig through the 150++ of them to read the docs to find the one that matters. They also claim <https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.Monitoring https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...> there's a console table cell for the replication status, but my experience with the console is that often one must opt-in to having that column shown which is suboptimal :-( That "shared responsibility model," they lean on it heavily
mianos 3y ago
I can assure you, you can not trust any AWS health checks to be a primary alert for something down. You have to do it all yourself, on host, or inside the container. AWS/Rackspace support just say: "It's your problem as we don't manage what is inside the AWS service".

PeterZaitsev 3y ago

Facinating read. I think it is a great illustration to show how many "practically working systems" can be built on the foundation exhibiting so many consistency artifacts

klysm 3y ago
Most systems are practically broken and it’s worked around by human factors

hipadev23 3y ago

"SELECT ... FOR UPDATE" seems to be the answer to all these issues right? Lock the rows you're going to be updating and suddenly everything works as advertised.

xxpor 3y ago
If you want performance to completely tank, sure.
- hipadev23 3y ago
  Afaik, it only locks the row(s) in question. Do you have a setup where the same row is being updated by multiple clients all the time?
  - jtc331 3y ago
    SELECT FOR UPDATE means two queries can’t _read_ the same row at the same time, right?
    - hipadev23 3y ago
      To my understanding, yes. If you have broad select queries across all the rows, that would slow things down quite a bit.
      - dilyevsky 3y ago
        You can use SKIP LOCKED normally. That’s how popular queue implementations usually work. I believe pg even calls this out in their docs
  - quickthrower2 3y ago
    SQL Server will escalate locks. Because keeping a million rows locked is expensive.
  - dig1 3y ago
    This can easily happen if you have multiple services that can write/update in the same table/rows and rely on database coordination instead of using an external queue like Kafka. Postgres (presumably other databases) can propagate these locks to table locks [1] and cause contention for the whole infra. [1] https://blog.heroku.com/curious-case-table-locking-update-query https://blog.heroku.com/curious-case-table-locking-update-qu...

rob-olmos 3y ago

Related: ACIDRain for the repeatable read without explicit "select .. for update" locking gotcha gift that still keeps on giving: https://news.ycombinator.com/item?id=20027532 https://news.ycombinator.com/item?id=20027532

tzone 3y ago

I have been advocating for the longest time that "repeatable read" is just a bad idea. Even if implementations were perfect. Even when it works correctly in the Database, it is still very tricky to reason about when dealing with complex queries. I think two isolation levels that make sense are either: * read committed * serializable You either go all the way to have a serializable setup, where there are no surprises. OR, you go in read committed direction where it is obvious that if you want have a consistent view of the data within a transaction, you have to lock the rows before you start reading them. Read committed is very similar to just regular multi-threaded code and its memory management, so most engineers can get a decent intuitive sense for it. Serializable is so strict that it is pretty hard to make very unexpected mistakes. Anything in-between is a no man's land. And anything less consistent than Read Committed is no longer really a database.

klysm 3y ago
But you wouldn’t need to lock them if repeatable read actually worked
- tzone 3y ago
  That is sort of my point. I think "repeatable read" is a fools gold. You think you wont need to do locking, but it is too easy to make incorrect assumptions about what guarantees "repeatable read" provides and you can make very subtle mistakes which leads to rare, extremely hard to diagnose correctness issues. Repeatable read type of setups also make it much easier to accidentally create much longer running transactions, and long running transactions/too many concurrent open transactions/etc can create really unexpected, very hard to resolve performance issues in the long run for any database.
  - karmakaze 3y ago
    I agree that "read committed" is clearer in knowing what you're getting than "repeatable read". The latter can be convenient and workable if you accept that you will need to do locking to avoid write skew, etc. I've used both "read committed" and "repeatable read" with MySQL and learned to deal with each in their own way. The problem I've seen is with large/long-lived transactions that impact performance, where the solution is to divide writes into smaller transactions in the design--"read committed" does tend to encourage smaller transactions.

Corrado 3y ago

I appreciate the write-up and the nod to AWS RDS. However, I was wondering if there was any focus on AWS Aurora (MySQL)? For those that don't know, AWS build a protocol compatible database platform that pretends to be MySQL or PostgreSQL. It would be interesting to see if Aurora MySQL has the same "features" as RDS or even MariaDB.

Reubend 3y ago
No, because that would be a totally different DB engine with different concurrency issues. Although that would also be super interesting to read, and my intuition is that because Aurora is a much newer DB, it probably has some subtle issues that haven't been discovered yet versus MySQL, which is quite old by now.
- evanelias 3y ago
  Aurora MySQL is based heavily on MySQL/InnoDB's codebase. It's not a complete reimplementation from scratch. My guess would be that it exhibits some or all of these same issues [edit to add: see footnote 2]. With a single-node cluster, I don't ever recall reading anything about Aurora offering different MVCC or isolation level semantics than upstream InnoDB. AWS documentation says "These isolation levels work the same in Aurora MySQL as in RDS for MySQL" [1] and keep in mind standard non-Aurora RDS is much closer to unmodified upstream MySQL. That said, there are some unique wrinkles in Aurora's cluster behavior due to the shared storage. For example, if you keep the default isolation level of repeatable read, long-running queries on Aurora Replicas will inherently block purge of old-row versions on the whole cluster. In contrast, a traditional MySQL replica set (using async binlog replication) does not behave that way, because each replica has its own storage and own purge threads. [1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.IsolationLevels.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... [2] Re-reading the Jepsen results, it appears all of these anomalies come from the exact same documented InnoDB behavior: "If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows" as per https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-re... -- and presumably Aurora maintains the same behavior, since otherwise it would break compatibility with MySQL in extremely subtle and confusing ways.

PeterZaitsev 3y ago

What would be helpful is not just comparison to theoretical definition to isolation modes but rather comparison to other popular relational databases - PostgreSQL, MS SQL, Oracle ? Something developers need to mind if they want to assure compatibility

eatonphil 3y ago
Beyond the Kleppmann Hermitage work that is linked in this post? https://github.com/ept/hermitage https://github.com/ept/hermitage
- nop_slide 3y ago
  Woah that is a great resource, I've been looking for something like this which shows an easy way to exemplify the various anomalies. Thanks for highlighting it!

camgunz 3y ago

> In 2022 Jepsen commissioned the University of Porto’s INESC TEC to develop LazyFS: a FUSE filesystem for simulating the loss of un-fsynced writes I love this; what a great example of pushing the state of the art forward. Kudos!

karmakaze 3y ago

My takeaways: > 4.2 Recommendations > The core problem is that MySQL claims to implement Repeatable Read but actually provides something much weaker. We see two avenues to resolve this problem. > The first is to keep MySQL’s behavior as it is, and to clearly document the consistency model “Repeatable Read” actually provides. There is precedent in other databases: PostgreSQL’s Repeatable Read is actually Snapshot Isolation, and exhibits behaviors which violate PL-2.99 Repeatable Read. However, PostgreSQL’s documentation eventually mentions that their Repeatable Read implementation is actually Snapshot Isolation. MySQL could similarly document that their “Repeatable Read” means “Read Committed, plus some sort of guarantees that hold until the transaction writes something, at which point mysteries occur.” A precise characterization of those mysteries would be most welcome. Calling what MySQL's "Repeatable Read" as "Read Committed plus..." would be more confusing as even the simplest repeated read without mutations wouldn't work as expected. The documentation should be more upfront about how MySQL "Repeatable Read" doesn't mean what might be expected. In the meantime keep the "MySQL consistent read documentation"[0] close by. > The second option is to treat these behaviors as bugs and fix them. Jepsen would be delighted if MySQL and other vendors were to commit to providing PL-2.99 Repeatable Read. However, even satisfying the incomplete, ambiguous ANSI definition of Repeatable Read would be an improvement over current affairs. I doubt this would be feasible with the extent of deployment. At scale bug-fixes are bugs in themselves. The best that could be done is to create distinct isolation levels for the existing MySQL-RR and the compliant RR isolation levels (somewhat akin to the utf8/utf8mb4 evolution). [0] https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-re...