Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Uber Engineering Switched from Postgres to MySQL

eng.uber.com

Post reply on HN
Print view View original

271–280 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#271

Post by stubish »

jobu wrote: post #91
ledjon wrote: post #51

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-case. Postgres's MVCC is superior (can rollback DDL, can add indexes online, can have open read transactions for a VERY long time without impacting other parts of the system) Postgres supports many types of indexes, not just b-tree. One thing it doesn't have is…

> 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/ ) 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 ( "…

I wouldn't have trusted Cassandra back then either. 0.9, 1.0 or maybe 1.2 was reaching sufficient maturity to actually be recommended. Modern Cassandra has come leaps and bounds, with the 2.x series finally becoming stable this year and just recently 3.0.x finally getting blessed by the community as stable enough for production. And ScyllaDB hot on their heels.

Re: Why Uber Engineering Switched from Postgres to MySQL

#272

Post by zihotki »

scotty79 wrote: post #187

Funny how the article is just: - we used X in a fashion that suited us best - it caused us problems Y because of some technicalities of X - so we switched to Z and we could avoid Y thanks to how Z handles the technicalities differently than Y and the top rated HN comments are: - you used the X wrong - all the technicalities of X that caused you problems Y are actually superior features of X

But think how funny that'd be if we assume that "all the technicalities of X that caused you problems Y are actually superior features of X" is actually true?

Re: Why Uber Engineering Switched from Postgres to MySQL

#273

Post by njs12345 »

carterehsmith wrote: post #212
njs12345 wrote: post #202

Earlier quoted context omitted.

From the MySQL docs: 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 traffi…

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 DM…

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.

Re: Why Uber Engineering Switched from Postgres to MySQL

#274

Post by LoSboccacc »

fanf2 wrote: post #269
LoSboccacc wrote: post #184

Earlier quoted context omitted.

I'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…

Does repmgr handle most of this?

most 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.

Re: Why Uber Engineering Switched from Postgres to MySQL

#275

Post by Terretta »

sriharis wrote: post #244

A common solution to conserve bandwidth is to use compression. This can be done easily in PostgreSQL by using ssh tunnels and turning on compression. I wonder why they didn't try that.

Reading this, I wondered if they deeply understood the difference between bandwidth and latency. I doubt compression would be the thing, as I'd guess they had a latency problem, not a bandwidth problem.

You see this kind of misunderstanding commonly pooled with other "a computer has physically moving parts" misunderstandings like the ORM or connection pooling concerns outlined.

After tyranny of abstractions, nobody knows how the moving parts really work.

// As alternatives given you'd like to keep the immutable data approach which brings a lot of goodness, consider a log-structured file system for the disk concerns, and geo-sensible replication for the latency concerns. At this scale, for near real-time app, bi-coastal DB is a bad model. You shouldn't have all users in SF querying a database in DC. Given the nature of the business model, they can share geographically at one time scale, and roll up and replicate geo diverse data at a high latency leisure.

Re: Why Uber Engineering Switched from Postgres to MySQL

#276

Post by Terretta »

pritambaral wrote: post #131
snarfy wrote: post #111

I wonder how much of this could have been solved by using a different file system. There is all of this talk about the physical layer but no mention of the file system used. > Typically, write amplification refers to a problem with writing data to SSD disks: a small logical update (say, writing a few bytes) becomes a much larger, costlier update when translated to the physical layer. This is exactly the type of probl…

How would any filesystem help with that? SSDs typically write entire blocks, even if the OS asks them to only write a few bytes. That's just how SSDs work.

Rethink the file system. Think log-structured.

Re: Why Uber Engineering Switched from Postgres to MySQL

#277

Post by ChoHag »

1024core wrote: post #234
tormeh wrote: post #148

Earlier quoted context omitted.

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 write success to database close db connection 2 I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me,…

So if the "send email" step fails (temporarily), the next worker to come along will grab the same receipt and send it again? I think a better solution would be to use a centralized queue that actually does the mail sending, and retries in case of failure.

> a centralized queue that actually does the mail sending, and retries in case of failure.

A bit like SMTP?

Re: Why Uber Engineering Switched from Postgres to MySQL

#278

Post by RedCrowbar »

stubish wrote: post #267
jedberg wrote: post #220

Earlier quoted context omitted.

But 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 P…

Both vacuuming and logical replication are discussed in the article. In particular, vacuuming is easier with InnoDB since the changed records all exist in the redo log whereas PostgreSQL needs to scan the whole table. pglogical is mentioned for people running PG9.4+ as a way of doing minimal downtime cross version upgrades, which wasn't an option back with PG9.2 unless you go with something like slony1 or londiste.

> vacuuming is easier with InnoDB ... PostgreSQL needs to scan the whole table

There's been quite a few improvements to VACUUM in 9.6 [1], including avoiding full-table scans.

[1] https://www.postgresql.org/docs/9.6/static/release-9-6.html#...

Re: Why Uber Engineering Switched from Postgres to MySQL

#279

Post by pritambaral »

Terretta wrote: post #276
pritambaral wrote: post #131

Earlier quoted context omitted.

How would any filesystem help with that? SSDs typically write entire blocks, even if the OS asks them to only write a few bytes. That's just how SSDs work.

Rethink the file system. Think log-structured.

Doesn't matter. Even a log-structured fs, when told to write 4 bytes of new data to disk will have to write those 4 bytes immediately and return. If an application asks the OS (via fsync), and the OS asks the fs and the fs doesn't write to disk but tells the OS it did, then the fs just lied and risked data loss.

If power is lost between the fs lying to the OS and its subsequently actually writing to disk, the data that the fs lied about is lost.

You don't want that with a DB on top of it.

Re: Why Uber Engineering Switched from Postgres to MySQL

#280

Post by lazyant »

1024core wrote: post #234
tormeh wrote: post #148

Earlier quoted context omitted.

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 write success to database close db connection 2 I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me,…

So if the "send email" step fails (temporarily), the next worker to come along will grab the same receipt and send it again? I think a better solution would be to use a centralized queue that actually does the mail sending, and retries in case of failure.

not necessarily, in the first write we can update receipt status as "queued" so queue system can update to "sent" or retry
Post reply on HN