8 ms·
> MySQL supports multiple different replication modes: > Statement-based replication replicates logical SQL statements (e.g., it would literally replicate lite
by jedberg 10y ago
> MySQL supports multiple different replication modes:
> Statement-based replication replicates logical SQL statements (e.g., it would literally replicate literal statements such as: UPDATE users SET birth_year=770 WHERE id = 4)
Postgres has that too (using a 3rd party tool, but it's an officially supported tool). We were using it on reddit 10 years ago. It caused a lot of problems. I wouldn't call that an advantage for Mysql.
Honestly, reading this it seems like the summary is: "We don't follow great engineering practices so we need a database more forgiving". Which is fine if that's how you want to run your business, but isn't really the death knell for Postgres.
A specific example:
> This problem might not be apparent to application developers writing code that obscures where transactions start and end. For instance, say a developer has some code that has to email a receipt to a user. Depending on how it’s written, the code may implicitly have a database transaction that’s held open until after the email finishes sending. While it’s always bad form to let your code hold open database transactions while performing unrelated blocking I/O, the reality is that most engineers are not database experts and may not always understand this problem, especially when using an ORM that obscures low-level details like open transactions.
Your developer should understand database transactions. But you should make it easier for them by abstracting it so that they don't have to. And in this particular case, I'd say they shouldn't be using the database to do locking around sending a receipt. It should be put into a queue and that queue should be processed separately, which avoids the transaction problem altogether.
- spotman 10y agoTry enforcing this on teams that use ORMs like hibernate with 500 developers. Super, duper, common issue, you will find this at every large shop at some point in its life time, usually around the time of hiring people and expanding extremely fast, and taking on some tech debt. All functions of extreme scale, hyper growth, and yeah, not following the absolute best practices all the time, but tech debt is like any debt, you get something now, and pay later. If they continue going up and to the right they will be able to afford it.
- forgotpwtomain 10y agoYes, but there is no point in blaming the database for that. It's seriously not a database issue. Particularly Postgres has well exposed internals for monitoring and killing of long running transactions (if you are not setting statement timeout).
- spotman 10y agoSure, but the show must go on.
- nawitus 10y agoThat's what I've often wondered about ORMs. First they try to abstract away all the database internals so that the developer hardly even knows what's happening. However, ultimately the abstraction always ends up having some limitations. In the end the internals need to be exposed, but since your code base is still mostly based around the ORM it can end up being a mess.
- djsumdog 10y agoORMs have mostly been just painful at ever shop I've been at. I've used ActiveRecord, Squirl, Hibernate, django.db .. they're all various level of suck. The one huge advantage of an ORM is the ability to support multiple databases, but that only really works if you can do everything using the ORM. The moment you have a function too complex that you need to write some SQL, now you need some case statements and multiple integration tests for all the database your product needs to support. They remove some boiler plate while adding others. In one of my own projects, I just created several files (pgsql.commands, mysql.commands, etc.), a basic set of classes around them and a base set of commands that will work for all the DBs I wanted to support (so the command files had an inheritance model, albeit only one layer). With all that being said, most ORMs I've used do have explicit transaction support. I know Squirl had a `transaction {}` block you could wrap commands around. Transactions shouldn't be an excuse. They should be off by default and explicit added around blocks of things that need to be atomic. > Try enforcing this on teams that use ORMs like hibernate with 500 developers. I realize this is a hyperbole (I hope) because you really shouldn't have 500 developers all on the same monolithic project (unless you're developing like...the Linux kernel). Getting your team to at least try to implement best practices does take some effort, but with things like weekly demos and code reviewed commits, it's do-able.
- yeukhon 10y agoI was wondering how could that happen? It sounds like someone is trying to do two things in parallel. I would expect def my_view(request): try: receipt = generate_receipt(...) receipt.send_email(...) except SomeKindOfEmailError as e: # okay do something else and this should be synchronous and thus blocking. So to not block, they either wrote co-routines (asynchronous) or execute things in parallel. Have I interpreted their problem incorrectly?
- jedberg 10y agoOpen db connection get data for receipt generate receipt send email write success to database close connection To a junior programmer this would probably look reasonable, and to be fair, it takes some experience and getting burned, or good training, to know it is not.
- nine_k 10y agoThis still can be a pretty reasonable and correct course of action. Everything depends on the amount of data you are locking, and amount of transactions open simultaneously. When there's a noticeable contention due to the number of parallel transactions, one should consider ways to loosen their data guarantees (the status can be unset for some time) and go for an explicit asynchronous approaches.
- yeukhon 10y agoThanks for the illustration. But if Uber team was using ORM, then expect ORM to take care of the pool of connections. get data for receipt would be a SELECT and only write success to database would do INSERT or UPDATE. I expect junior programmer to complete the above in at least two SQL calls. I have a feeling they were trying something smart. EDIT: hmm reading the other commenter above, probably they are trying to lock on the data for full data integrity. Okay. That makes sense then. I was looking at the problem from the wrong angle.
- riking 10y agoGrapevine says that they were holding a transaction open for the entire duration of the Uber ride.
- nine_k 10y agoSending an email to a local MTA should be pretty fast. That is, if you cannot and will not do async stuff in your program, there are ready-made tools that will do that particular thing asynchronously for you, and have been doing so for years (or even decades).
- jedberg 10y agoTrue, but really your local MTA is just acting like a specialized queue, since the first thing it will do is send the message to your relay.
- nine_k 10y agoYes, the whole point is to avoid building your own queue where a ready-made, purpose-made solution already exists.
- jedberg 10y agoWell there are pros and cons. If you have already have a queue structure set up in your environment, it may not be a good idea to have to maintain a second queue structure in the form of a bunch of local MTAs. You'll need monitoring (how many messages are queued in each local MTA?) and a way to keep those MTAs up to date. If you just put an item in to a queue, and then have a specific cluster of machines that does nothing but grab items from that queue and push them through an email infrastructure, then it'll be a lot easier to maintain.
- mml 10y agoExperience with Mysql replication (even simple master/slave) leads me to believe Uber is going to have some rather nasty surprises at some point.
- bogomipz 10y agoCould you elaborate?
- harel 10y agoYou can read it in their next blog post: "How we migrated back to PostgreSql from mySql after migrating from PostgreSql to mySql". My experience with mySql replication was OK, but I did nothing fancy - just master/slave standard stuff. However the database itself was allowing you to do stuff by default (at the time at least) that is really bad. Not to mention scaling while I was using it was pretty much "you're on your own buddy" kind of deal. Today I'm scared to touch mySql because I don't trust Oracle. I use MariaDB on an old project which used mySql but anything new is postgres.
- oneweekwonder 10y agoPersonally I prefer Postgres. But at the shop we use MySQL. One of the leads personally believe in Percona Server[0] and mentions some of the old mysql folks work there. I like their docs and the idea of a more tuned mysql config. [0]: https://www.percona.com/doc/percona-server/5.7/index.html https://www.percona.com/doc/percona-server/5.7/index.html
- rpcope1 10y agoI can second using Percona; I wasn't much of a believer in MySQL until I inherited a medium sized application using Percona 5.7. Once you get past a few of the initial warts (like making sure the database doesn't silently truncate your data--yuck), it's been really performant, stable and impressive (and we're running a setup with a fairly complex schema with master operating at ~3000 QPS average, a slave actively replicating for read load balancing and backup, and Sphinx for FTS). Knowing what to be careful with on MySQL, I would certainly consider using the Percona flavor again in a new application.
- LoSboccacc 10y agoI've tried to setup a replicated postgres with autofailover and it honestly is a pita. the only sources of failover are rando scripts over the internet, that you have to download hammer in to your version dialect and hope you don't trigger one of the many uncovered failover modes. sure log shipping works, but that's far, FAR from a working solution. the gap requires ton of development hour, testing etc. can't really blame people for using things with mature tooling. (but I didn't switch to MySQL that'd be madness! there are plenty of good, replicated redundant stores out there, both nosql and sql)
- fanf2 10y agoDoes repmgr handle most of this?
- LoSboccacc 10y agomost of it. but in the master-slave configuration the clients can only work if connecting to the master, and when the master switch, scripts needs to go to each clients and update their config. not immensely bad if you also use pgbouncer so you can do it on the fly without restarting the whole client, but exceptionally vulnerable to split brains and the like.
- snuxoll 10y agoI've handled this with a DNS CNAME record that points to the active master, though pgBouncer is a valid solution as well.
- dkarapetyan 10y agoUber engineering in general is extremely disappointing. They consistently oversell/overhype mundane processes and technologies both software and otherwise. I suspect this is mostly because they are driven more by marketing and business needs than actual engineering bottlenecks. That is the only way I can explain all the technological missteps they keep bragging about.
- Negitivefrags 10y agoThe actual summary of the article is "The design of Postgres means that updating existing rows is inefficient compared to MySQL". Yes, there were some other points that were just extra annoyances for them but clearly that point was the most important to them. It's what the header image and the first 60% of the article was talking about and yet nobody seems to be engaging with that point in this thread. Is the design choice bad? They never said it was. It's just an engineering trade off. It's very possible that most workloads benefit from this design. But if you workload involves updating lots of existing rows at large scale, then MySQL is going to be a better choice for you.
- jedberg 10y agoBut it's only an issue if you rely on lots of transactions for data consistency and my point was that it sounds like they are relying on transactions too much which is why they need a more "forgiving" database, which is the part I quoted. Also they didn't mention anything about the auto vacuumer, which mostly solved the issue they are talking about. Their lack of mention of the vacuumer and not seeming to know that Postgres supports statement level replication makes me wonder if they took a deep dive into the wrong part of the technology.
- Negitivefrags 10y agoNone of what you said addresses the issue that I (or they) are talking about. On Postgres an update requires a rewrite of every index of the row. On MySQL it only requires an update of the indexes that were touched by the update. If you have a table with 10 indexes then this means doing 10 extra writes physically to the disk.
- dialogbox 10y agoAs I know, not every updates rewrite indexes in PG. It has single page clean up and HOT (heap only tuple) update optimization. Please refer to 64~ page of https://momjian.us/main/writings/pgsql/mvcc.pdf https://momjian.us/main/writings/pgsql/mvcc.pdf.
- 10y ago
- Thaxll 10y agoThe fact that Postgres didn't have official replication feature prior to a recent version is just unacceptable.