8 ms·
Re: Why Uber Engineering Switched from Postgres to MySQL
- xomateix 10y agoWas down for me, cached version: https://webcache.googleusercontent.com/search?q=cache:https%3A%2F%2Fayende.com%2Fblog%2F175137%2Fre-why-uber-engineering-switched-from-postgres-to-mysql https://webcache.googleusercontent.com/search?q=cache:https%...
- jakozaur 10y agoDiscussion on original blog post was yesterday: https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585
- pella 10y agomore info: Matt Ranney - presentation ( Senior Staff Engineer at Uber ) "Matt Ranney at All Your Base 2015" https://vimeo.com/145842299 https://vimeo.com/145842299 ( 30 min ) keywords: Uber + PostgreSQL + Chaos Monkey-style failure testing "WHAT WILL I LEARN? After this talk, you’ll be able to better assess the risk from the different failure modes of databases in your system’s architecture."
- neilc 10y ago> a lot of the pain the Uber is feeling has to do with the way Postgres has implemented MVCC. Because they write new records all the time, they need to update all indexes, all the time This is not true in general: there are circumstances where Postgres can skip updating indexes when a tuple is updated, assuming the update only changes values in non-indexed columns. See: http://pgsql.tapoueh.org/site/html/misc/hot.html http://pgsql.tapoueh.org/site/html/misc/hot.html https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;h=f12cad44e56c363e62fa617497bbedfe1ba8c1fe;hb=HEAD https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
- nl 10y agoThe Uber article referenced makes it extremely clear they are working on indexed columns - the examples are all about updating the secondary indexed.
- Uberphallus 10y agoI'm curious on the particular usecase that requires slamming indexed columns that often.
- matthewrudy 10y agoImagine the Uber `Rides` table, indexed on `status` Every time a new order is created the status flows from * unassigned * assigned * passenger on board * completed That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good. We had a similar problem with this kind of table, at much lower volume than uber. But only when there was a two column index `status, pickup time` Or `status, created at`
- Uberphallus 10y ago> That should really be an event stream, but if you want to find all `unassigned` orders, an index would be good. No, an index is not good for that, because it only has 4 values (at most 4 times faster than a full table scan), and it hammers the index on every update, using PostgreSQL or not. Same for the two column index: single column indexes on pickup time and creation time are highly selective, and their values are updated basically never, whereas status has low selectivity and updates are common: combining them you don't significantly improve selects on the two columns, and limit the performance edge on time select where time. Status index only performs well when you select one of the values which is is significantly underrepresented in the column (e.g. all except completed). The most performing approach without doing black magic is to split in columns with separate indexes: Assigned Time, Pickup Time, Completed Time. If you want unassigned ones, you select where Assigned Time IS NULL, same for the other columns. Performance on update for most RDBMS blasts, as index updates are now distributed among several, allowing higher concurrency and overall throughput.
- curiousgal 10y agoAre we supposed to know who Ayende is? Please change the title.
- dev360 10y agoAlmost everybody in the .NET community knows who he is.
- soundoflight 10y agoI agree with @dev360. I think it has to do with RavenDB being more common with .NET developers (at least from my experience).
- V-2 10y agoHm, isn't RavenDB a .NET solution? As such how could it not be "more common with .NET developers"?
- philliphaydon 10y agoThe original client was written in C# but I believe there is Java and JavaScript clients now. Also can be queried via http requests.
- sjm-lbm 10y agoRavenDB is just a server application. There's nothing stopping you from connecting to it via, say, Python. It's just much more commonly used in .NET apps.
- moron4hire 10y agoThen count me in the category that requires you to use the qualifier "almost". I've been working in .NET since it was invented and I've never heard of him.
- deleted 10y ago[deleted]
- deleted 10y ago[deleted]
- joering2 10y agoVery nice write-up! Hope this helps them spam the heck out of people who never opted into their advertisement in the first place. /sarcasm I only wish LE would treat CAN-SPAM seriously and put more sources into criminal enforcement.
- philliphaydon 10y agoI don't understand your comment...
- curiousgal 10y agoHN's own r/SubredditSimulator/ I presume.
- emodendroket 10y agoI mean, sure, it's written to advertise his product, but it's still content that is of interest.
- sctb 10y agoPlease don't do this again here. We ban accounts that repeatedly derail threads with off-topic comments.
- mrmondo 10y agoAs someone else mentioned, this is a repost from yesterday - mods?
- Cthulhu_ 10y agoI gather reposts or re-bumps-to-the-front-page are common these days in case good articles don't get the attention they deserve. Which articles deserve attention is, of course, entirely up for debate.
- mrmondo 10y agoAh, actually I totally misunderstood the link and thought it was a genuine re-post from yesterday!
- GordonS 10y agoThis isn't a repost, it's a blog post by Ayende in response to yesterday's article. I thought it worthy of posting since Ayende heads up RavenDB and knows a thing or two about databases and their low level implentation
- mrmondo 10y agoOh my gosh you're totally correct! Sorry for my mistake there.
- gtrubetskoy 10y agoThe article talks about Postgres mostly delegating caching to the OS while dealing with it via system calls (which they describe as "expensive"), while InnoDB has its own implementation and is therefore faster. I wonder if part of the problem is really Linux, not Postgres and that another way they could have addressed this is by trying FreeBSD (which is what a lot of people run Postgres on).
- jasode 10y ago>I wonder if part of the problem is really Linux, not Postgres I'm not a PostgreSQL expert but it seems like it has a different philosophy with OS filesystem buffering. Using Oracle and MS SQL Server as counterexamples... you can configure Oracle to bypass os filesystem buffers by using "raw" disk io. With MS SQL Server, it opens datafiles with CreateFile(,,,FILE_FLAG_NO_BUFFERING) to bypass the Windows NTFS caching. (Microsoft recently ported SQL Server to Linux so not sure what combination of techniques they are doing on there since CreateFile(FILE_FLAG_NO_BUFFERING) is a Win32 api and not relevant to Linux.) The closest approximation PostgreSQL has is the "shared_buffers" parameter but from the documentation[1] I've read, the best practice for that setting is 25% to 40% of main memory. In contrast, Oracle and MS SQL Server db engines with their self-managed buffer pools are designed to use almost 100% of the main memory. Conclusion: PostgreSQL relies on the os filesystem cache and its own db buffer does not supersede the os buffers. Other db engines' buffer pools are designed to bypass the os filesystem cache. Installing PostgreSQL on FreeBSD instead of Linux wouldn't drastically change the recommended range for "shared_buffers". [1]https://www.postgresql.org/docs/9.1/static/runtime-config-resource.html https://www.postgresql.org/docs/9.1/static/runtime-config-re...
- valarauca1 10y ago>Microsoft recently ported SQL Server to Linux so not sure what combination of techniques they are doing on there Open options: int f = open("your file", O_DIRECT|O_DSYNC|O_RDWR); O_DIRECT signals that a file descriptor should by-pass kernel level caching and write directly to the device. O_SYNC signals that a file descriptor calls should not return until all data+metadata has been synced with disk. O_DSYNC does the same as O_SYNC but doesn't force meta-data synchronicity before the block ends. Related Stack Overflow https://stackoverflow.com/questions/5055859/how-are-the-o-sync-and-o-direct-flags-in-open2-different-alike https://stackoverflow.com/questions/5055859/how-are-the-o-sy... Related LWN article https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/
- codeulike 10y agoExplanation: Ayende is a well known programmer in the .NET community. Among other things he works on RavenDB (open-source NoSql db for .NET) and is currently working on a new storage engine for it called Voron. In this post he discusses Uber's blog post about Postgres and MySQL and compares the low level workings of both with what his team is doing with RavenDB and Voron.
- crescentfresh 10y agoThank you for explaining what Voron is.
- gtrubetskoy 10y agoBTW - "voron" is raven in Russian.
- DocSavage 10y agoAlso, he has published a lot of review of database code. In particular, he has 18 blog entries on the leveldb C++ code base: https://ayende.com/blog/161410/reviewing-leveldb-part-i-what-is-this-all-about https://ayende.com/blog/161410/reviewing-leveldb-part-i-what...
- jsingleton 10y agoI was recently informed [0] about Marten [1,2], which is like RavenDB but uses PostgreSQL for the backend. [0]: https://github.com/jpsingleton/ANCLAFS/pull/12 https://github.com/jpsingleton/ANCLAFS/pull/12 [1]: https://jasperfx.github.io/marten https://jasperfx.github.io/marten [2]: https://github.com/jasperfx/marten https://github.com/jasperfx/marten
- bogomipz 10y agoWhy is there a reference and link to a wikipedia article about a sci-fi novel in the second to last paragraph? Did I miss something? Is this an inside joke?
- codeulike 10y agoGood question. http://www.catb.org/jargon/html/O/on-the-gripping-hand.html http://www.catb.org/jargon/html/O/on-the-gripping-hand.html
- darkhorn 10y agoIf you want performance and the data is not critical like a Facebook post you need to use MySQL. But if you deal with money, like a bank or an ERP then the way to go is PostgreSQL. It should be a known fact for software developers.
- BHSPitMonkey 10y agoAs someone unfamiliar with the inner workings of both, why is that the case?
- ak39 10y agoBecause guaranteed transactions matter. Moving $10 from Bob's account to Jack's involves: 1. Inserting a transaction to remove $10 from Bob's account 2. Inserting a transaction to add $10 to Jack's account. Both 1 and 2 must succeed or fail in total if either of them fail (or both). You can imagine the disaster in banking apps, ERPs and other financial systems when 1 succeeds but 2 fails and the database is happy with that imbalanced state.
- BHSPitMonkey 10y agoMySQL has transactions, so why does this example prove that it's unsafe for critical applications?
- darkhorn 10y agoMySQL does not add new features because it was bought by Oracle. But they improve performance. It is an easy to use minimal database. InnoDB engine is ACID compliant but MySQL's original engine, MyISAM is not ACID compliant. Why not ACID compliant? To improve performance. On the other hand PostgreSQL is ACID compliant from the beginning. And PostgreSQL adds new features every few months. Some of these features that do not exist in MySQL are; IP address data type, serial fields (multiple auto-increments), row level permissions, geographical data types, inheritance, XML data type, custom data types, range data type and things that I don't know but I know that it provides many features of SQL language like intersect, except and merge joins. It is like free Oracle. ACID: https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID
- fritzy 10y agoBoth of these articles fail to mention that as of Postgres 9.5, you can use logical replication rather than WAL. This is why Amazon's RDS now supports multi-region slaving with Postgres.