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

121–130 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#121

Post by losvedir »

xaprb wrote: post #110
Illniyar wrote: post #85

Earlier quoted context omitted.

"surprised that some NoSQL DB could do a query on hundred million rows in a few seconds" The tone seems to suggest this is fast, a simple index query in an RDBMS of even a hundred million rows will take milliseconds on even a weak computer.

Justify that with actual math, please? I don't think you know how computers (processors, bus, memory access, etc) work. How exactly do you think an RDBMS can query an index at a hundred billion rows per second?

The parent said a query of a hundred million rows, i.e.: `select * from users where id = ?` when there are a hundred million users. That's obviously not scanning a hundred million rows, since the index will be a b-tree which by the power of logarithms is not so many levels deep.

Like Illniyar, that's also what I interpreted GP to mean when they said:

> "surprised that some NoSQL DB could do a query on hundred million rows in a few seconds"

which is not fast at all. However, if it actually did something on a hundred million rows that would be impressive. Not sure exactly sure which they meant, though.

Re: Why Uber Engineering Switched from Postgres to MySQL

#122

Post by [deleted] »

xaprb wrote: post #110
Illniyar wrote: post #85

Earlier quoted context omitted.

"surprised that some NoSQL DB could do a query on hundred million rows in a few seconds" The tone seems to suggest this is fast, a simple index query in an RDBMS of even a hundred million rows will take milliseconds on even a weak computer.

Justify that with actual math, please? I don't think you know how computers (processors, bus, memory access, etc) work. How exactly do you think an RDBMS can query an index at a hundred billion rows per second?

[deleted]

Re: Why Uber Engineering Switched from Postgres to MySQL

#123

Post by forgotpwtomain »

spotman wrote: post #120
jedberg wrote: post #116

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

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

Yes, 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).

Re: Why Uber Engineering Switched from Postgres to MySQL

#124

Post by spotman »

verelo wrote: post #118
fjordan wrote: post #76

Earlier quoted context omitted.

Tools such as the percona toolkit ( https://www.percona.com/software/mysql-tools/percona-toolkit ) provide the ability to perform safe and performant online alters for MySQL. Behind the scenes it actually creates a new table with the new schema and utilizes triggers to apply writes to the new table as the original data is being copied. Once it completes the table is renamed and the triggers are removed. Percona also…

While this is true, i don't personally think Percona tools alone makes these changes 'safe'. You can create scenarios where performance is so degraded that you introduce replication delay or service interruptions (and yes the tools have ways to work around that, but it's a significant challenge to tune this correctly on production), data can be lost during the table switch over, and the complication of managing a pro…

Your absolutely correct that it is not risk free, however, if you do not use foreign keys (which no huge scale mysql install like schemaless does) and you know what your doing (read: calculating the expected load, adding throttles and sanity checks, backups, etc) online alters are extremely feasible with mysql and folks have been doing them for years.

I know first hand shops like FB use a similar method in production.

Also, big note, the whole point of "schemaless" (And other things, like fb's friend feed architecture) is that you don't make schema changes.

Re: Why Uber Engineering Switched from Postgres to MySQL

#125

Post by pbreit »

thinkingkong wrote: post #11

Posts like this are important. We too often rely on a buzz-word heuristic and that's how you end up with dozens of random technologies that are harder to maintain and don't necessarily solve any of your problems. This method is good, because it shows that when you understand the problem the right way, you can find the right solution, even if by popularity it looks like a "step backwards" Massive Kudos.

The problem is that people might get the wrong impression that this is relevant to them when for 99% of people it is not.

Re: Why Uber Engineering Switched from Postgres to MySQL

#126

Post by spotman »

forgotpwtomain wrote: post #123
spotman wrote: post #120

Earlier quoted context omitted.

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

Yes, 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).

Sure, but the show must go on.

Re: Why Uber Engineering Switched from Postgres to MySQL

#127

Post by mandeepj »

MikeKusold wrote: post #100
jobu wrote: post #91

Earlier quoted context omitted.

> 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 was at MesosCon and ended up talking to some Uber people. They are currently using Cassandra in prod. I can't speak as to why they use MySQL the way they do though.

Thanks for sharing this. I was wondering - the limitations they have listed could be easily overcome with cassandra

Re: Why Uber Engineering Switched from Postgres to MySQL

#128

Post by yeukhon »

jedberg wrote: post #116

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

I 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?

Re: Why Uber Engineering Switched from Postgres to MySQL

#129

Post by manigandham »

They should use SQL Server (which has great replication abilities, although horizontal scale out is still difficult) or MemSQL (which is distributed, scalable, and can do everything they need).

Or use Cassandra which is a perfect fit (or ScyllaDB which is a better version of it).

This all sounds like an aversion to just paying for or using better products when the problem is easily solved.

Re: Why Uber Engineering Switched from Postgres to MySQL

#130

Post by gkop »

tracker1 wrote: post #83
rspeer wrote: post #63

Earlier quoted context omitted.

Except it's MySQL, so by the time you read it out it says: id first last birth_year 1 Blaise Pascal 1623 2 Gottfried Leibniz 1646 3 Emmy Noether 1882 4 Muhammad al-KhwÄ�rizmÄ« 780 5 Alan Turing 1912 6 Srinivasa Ramanujan 1887 7 Ada Lovelace 1815 8 Henri Poincaré 1854 (I know, I know. It is possible to configure MySQL encodings correctly. And given that they've put a lot of engineering thought into choosing MySQL, th…

Don't know if it's still an issue, but I once setup mysql, with an id of binary(16) to store UUIDs, and used 0-padded conversions for legacy id's... when trying to port over existing records I discovered the index on a binary field was a "case-insensitive" textual index by default, so started getting conflicts when the ascii equivalent of "a" and "A" as a byte was inserted, etc. This was about 15 or so years ago, but…

I wouldn't be surprised if that behavior remains the same. MySQL seems to be fine with long-standing bugs and other sub-optimal behavior hanging around, eg. https://bugs.mysql.com/bug.php?id=20786 .
Post reply on HN