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

251–260 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#251

Post by markpapadakis »

fdr wrote: post #204
markpapadakis wrote: post #179

Great write-up. A few observations: 1. The encoding and translation schemes of Postgres and mySQL/InnoDB are well described in the blog post, and I would also agree that InnoDB’s design is, all things considered, better for all the reasons outlined in the post. 2. I don’t understand why anyone still uses lseek() followed by read()/write() and not pread()/pwrite() syscalls. It’s trivial to replace the pair of calls wi…

w.r.t. 2: https://www.postgresql.org/message-id/6248.1046130083%40sss.... Manfred Spraul writes: > Tom Lane wrote: >> It seems unlikely to me that eliminating lseek on some platforms would >> be worth the hassle of maintaining two code paths. lseek is mighty >> cheap as system calls go. >> > It was considered expensive enough to write a syscall avoidance layer > that caches the file pointer and skips lseek if fpos==o…

This ML thread's only real argument is that some OS/Kernels may not support pread/pwrite. The readahead argument makes little to no sense IMO. Unless there are too many uses of random access IO in the codebase, they should use pread and friends if available there. Especially considering most people run it on Linux not some exotic OS nowadays.

Re: Why Uber Engineering Switched from Postgres to MySQL

#252

Post by exabrial »

My big think with MySQL is that the last time I needed to make this decision (about 3yr ago), the support tooling, community knowledge, and documentation around MySQL was light years ahead of Postgres. There were literally hundreds of MySQL clients and utilities and Postgres was "just a database".

Competition breeds excellence.

Re: Why Uber Engineering Switched from Postgres to MySQL

#253

Post by quotemstr »

> Each of these system calls incurs a context switch

System calls are not context switches. I wish people would distinguish between them. A system call is just a change of privilege level and is efficient --- there's no cache invalidation required on almost any system.

A context switch, on the other hand, involves a call to the scheduler, saving and restoring of much more CPU register state, and various kinds of cache invalidation. (It's even more expensive if you're switching between different processes instead of different threads in the same process.)

The kernel may perform a context switch while executing a system call --- this context switch is what makes blocking calls blocking. But even IO system calls do not necessarily cause context switches, especially in the case where an operation can be satisfied by accessing only the page cache.

tl;dr A system call is not necessarily a context switch

Re: Why Uber Engineering Switched from Postgres to MySQL

#254

Post by srd »

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.

That depends on the implementation of the queue. Most queues have a "failed/retry" concept, where they mark failed jobs to be retried in some set future point in time. So one failed job does not hold up your entire queue processing.

Re: Why Uber Engineering Switched from Postgres to MySQL

#255

Post by pritambaral »

ksec wrote: post #245
pella wrote: post #58

Roadmaps ( PostgreSQL ) 2016-2017-... * Postgres Professional roadmap ( Pluggable storages, Multimaster cluster with sharding, Effective partitioning, Adaptive query planning, Page-level data compression, Connection pooling, Native querying for jsonb with indexing support, ....) https://wiki.postgresql.org/wiki/Postgres_Professional_roadm... * EnterpriseDB database server roadmap ( Parallelism, Replication, Vertical…

Why is there a Postgre Pro and Enterprise DB? Whats the differnce? ( Never heard of PostgrePro till today )

Postgres Professional and Enterprise DB are two companies that provide Prostgres products and services. The linked roadmaps are their respective plans on what they want to add to the open source Postgres database.

Re: Why Uber Engineering Switched from Postgres to MySQL

#256

Post by pritambaral »

vanviegen wrote: post #200

One major advantage of MySQL's clustered indexes the article doesn't mention is that, although secondary key reads may be a little slower, primary key reads will be faster. The row data lives in the primary key index, so there is no need for referencing an additional database page (possibly causing random I/O). This is especially relevant when doing range queries over the primary key. Imagine a table containing billi…

Uhh ... Postgres supports Index-only scans; as long as the data you're asking for is in the index, that is.

So if you have an index on (conversation_id, message_id), and you try to retrieve message ids of a specific conversation, only the index will be touched.

Re: Why Uber Engineering Switched from Postgres to MySQL

#257

Post by manigandham »

brianwawok wrote: post #231
manigandham wrote: post #160

Earlier quoted context omitted.

> basic three requirements SQL Server works just fine and has lots of concurrency control to do whatever they need. And the way they're using the database doesn't seem to really make this an issue outside of their replication. > are really, really expensive This whole janky setup they have sounds even worse. None of the commercial relational databases are really that expensive considering what they offer, and we're t…

Maybe you should call up Google, Facebook,linked in, Twitter, and tell them they all made mistakes and should use MSSQL.

Those are all massive scale tech companies that actually needed to invent many of the datastore technologies used for big data today. Uber is not among them.

But I'm sure you know all this as it seems you work for an enterprise database company that actually makes the exact product Uber should use.

Re: Why Uber Engineering Switched from Postgres to MySQL

#258

Post by kriro »

darkr wrote: post #178
mml wrote: post #157

Earlier quoted context omitted.

Experience with Mysql replication (even simple master/slave) leads me to believe Uber is going to have some rather nasty surprises at some point.

MySQL has had solid and flexible replication options for a long time. Postgres has only just started to catch up in the last couple of years. Don't get me wrong, I would generally choose Postgres over MySQL for an RDBMS with replication requirements these days, but I'm not sure I would have made that same descion a few years ago. There are valid reasons that long established companies such as Google, Twitter, Faceboo…

I guess I'll be "that guy" who brings up non-technical concerns. I'd think very long and hard about using MySQL these days. Oracle is pretty much bizzaro treating it and widening the feature gap between the open source and enterprise version. Wikimedia is using MariaDB so I'd guess it works well for certain high scale use cases.

[I know there's different opinions but I'd rather have a fully open database if I have a choice. For the record I default to PostgreSQL]

Re: Why Uber Engineering Switched from Postgres to MySQL

#259

Post by continuational »

Negitivefrags wrote: post #226
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…

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

10 indexes on one table seems a bit much. It sounds like a table that hasn't been normalized.

Re: Why Uber Engineering Switched from Postgres to MySQL

#260

Post by secure »

spotman wrote: post #228
dunk010 wrote: post #225

Earlier quoted context omitted.

> There are valid reasons that long established companies such as Google, Twitter, Facebook and countless others chose MySQL as their primary data store. I think you're misrepresenting things here. While all do use MySQL for some specific tasks, it's clear that all also have many other central datastores which are not MySQL. In Google's case this is a gross overstatement.

Not really, YouTube runs on MySQL. Pretty sure it's still the largest video site in the world.

At the same time, YouTube is only one of Google’s services, and there is no evidence that the remaining services run on MySQL as well.
Post reply on HN