In this paper, the author: Markus Winand, the original link: www.yumpu.com/en/document/view/53683323/migrating-uber-from-mysql-to-postgresql
Gao Qiang, co-founder of DBAplus community, specializes in the implementation, operation and troubleshooting of Oracle, AIX, Linux, PostgreSQL and DB2 products. I have participated in the implementation and operation and maintenance of many provincial government projects, and have rich experience in operation and maintenance.
Editor’s note
In piecesIn piecesIn piecesIn pieces In pieces
Uber’s big move to switch its database from PostgreSQL to MySQL has caused a heated discussion in the industry and has been analyzed in detail. To this end, the DBAplus community also translated the latest information to share with everyone, hoping to discuss and improve together.
As an independent third party public welfare community, we have no intention of supporting anyone or not. As long as it is purely technical discussion, we welcome everyone to reply and discuss in the comment area and express their opinions.
In piecesIn piecesIn piecesIn pieces In pieces
A few days ago Uber posted an article titled “Why Uber Switched from PostgreSQL to MySQL.” I didn’t read the article right away because I was busy with my family. But my inbox was filled with messages about the issue — like “Is PostgreSQL really that lame?” As far as I know, PostgreSQL isn’t that bad, so these emails make me wonder what the hell is going on with this article. Let me analyze the problems in Uber’s article.
In this article, I’ll explain why I don’t think Uber’s article should serve as a general recommendation for database selection, why MySQL might be a good fit for Uber, and why a successful switch might cause more problems than just measuring data storage.
About Update operations
The first problem, highlighted in the Uber article but not fully detailed, is that PostgreSQL always updates all relevant indexes on the table when updating row records. MySQL, which uses InnoDB, only needs to update the index containing the changed field. PostgreSQL uses a method that results in more disk I/O (” write magnification “) by updating non-indexed columns. If this is a big problem for Uber, the update operation could be a big part of their overall load.
However, there is a problem with the Uber article, which does not mention PostgreSQL’s heap-only Tuples(HOT) feature. PostgreSQL source code (address: https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT) with specifications, HOT for individual cases such as “when a row is repeatedly updating, And there is no data change on the index column. In this case, PostgreSQL can be implemented by updating the database when the old and new row versions are stored on the same page without touching any indexes. In this case, you can set fillfactor to adjust the retention ratio of update space. Assuming that Uber engineers know about this means that HOT has not solved their problem, since these high-frequency update operations involve at least one index column.
This assumption is also supported by the sentence in the article: “If we have a table with a dozen (12) indexes defined, updates in a field involving only one index will be reported to all 12 indexes in order to pass the Ctid of the new data row”. He explicitly states that “only one index is involved” this is a marginal case – only one index – otherwise PostgreSQL’s HOT feature would solve the problem.
Author’s side note: I’m really curious to see if they use fewer indexes – it was a challenge for me to redesign the indexes. However, there is a very strong possibility that these indexes are used too conservatively and sparingly to serve their important purpose.
About SELECT operation
Another description of Uber’s use case also caught my attention: The article explains that MySQL’s InnoDB uses clustered indexes and admits that “this reference means InnoDB has a slight disadvantage to PostgreSQL when it comes to sub-keyword searches because InnoDB must use two indexes and Postgres only uses one.” I’ve written about this problem before: (” The clustered Index Penalty “) in context of SQL Server. http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key).
What caught my attention was Uber’s description of the index penalty as a “minor flaw.” In my opinion, this is a considerable disadvantage if you run a lot of queries on the sub-index. If this is just a minor flaw for Uber, it may imply that their index usage is fairly low. That is, Uber scans almost entirely with primary keys (and then penalizes non-clustered indexes). Notice THAT I wrote “find”, not “query”. The reason is that the clustered index penalty affects any statement that uses the WHERE clause — not just SELECT. It also affects update operations that are often based on primary keys.
Finally, there’s another omission that hints at my question about Uber: They didn’t mention PostgreSQL’s limited pure index scanning. Especially in a database with a heavy update task, PostgreSQL pure index scans do not perform very well. I have even said that this is a problem that affects most of my clients. I wrote a blog back in 2011. In 2012 PostgreSQL 9.2 got Limited Support of Index-only Scans were scans (only valid for most static data). In 2014, and I even on PgCon put forward in this respect I care: https://wiki.postgresql.org/wiki/Pgcon2014unconferenceBitmapIndexOnlyScan.
But Uber did not complain about the problems. So query speed is not their problem. My guess is that the query speed has been fixed by work on feplicas (mentioned later) and possibly restrictions on primary keys.
So far, Uber’s use case seems to be a better fit for key-value storage. Take it a step further: InnoDB is a very robust and popular key-value storage engine. There are even a number of packages that bind InnoDB to some (very limited) front-end SQL: MySQL and MariaDB are the most popular, I think.
But seriously: if you need basic key-value storage and occasionally run a simple SQLchaxun,MySQL (or MariaDB) is the logical choice. My guess is that this is at least a better option than random NoSQL key and value stores, satisfying an initial need to provide a limited SQL-like query language. Uber, on the other hand, has built their own thing (” Schemaless “) on InnoDB and MySQL.
The index balance
A final note about indexing in the article: the term “rebalancing” is used in the b-tree index. Unfortunately, wikipedia articles are not always suitable for database indexing, because wikipedia maintains algorithms that describe at least half of a node’s requirements. To improve concurrency, PostgreSQL uses Lehman, Yao’s variant B-trees, which increases this requirement and thus allows for sparse indexes. As a side note, PostgreSQL still removes empty pages from the index. But this is only one side of the problem.
What really worries me is this sentence: “One of the fundamental problems with B-trees is that they must be rebalanced periodically…” I want to be clear: this is not a cyclical trip that needs to be run every day. Index balance is maintained (or worse, huh?) with each index change. . But the article goes on to say “… And these balancing operations can completely change the structure of the tree because the subtree is moved to a new location on the hard disk.” If you now think that “rebalancing” involves a lot of data movement, you’ve misunderstood it.
The most important operation in b-tree is node splitting. As you might guess, a node split occurs when the node is unable to host the new entry that belongs to it. To give you a ballpark figure, one occurrence might involve 100 insertions. Node split allocates a new node, moving half of the entries to the new node and connecting it to the preceding, following, and parent nodes. Here Lehman, Yao omitted a lot of locking. In some cases, new nodes cannot be added directly to the parent node because the parent node does not have enough space to hold child entries. In this case, the parent node is split and everything repeats.
In the worst case, the split goes up to the root node, where a new root node is placed on top of the root node as it splits. In this case, a B-tree had gone deeper. Note that effective root splitting makes the whole tree shallower and balanced. However, this does not mean a lot of data movement. In the worst case, you might have three nodes per level and a new root node. Be clear: most real-world indexes have no more than five levels. Even more explicitly: in the worst-case scenario — root splitting — five splits may occur for a billion inserts.
In other cases it may not be necessary to traverse the entire tree. After all, index maintenance is not “periodic,” not even very frequent, and never changes the structure of the tree. At least the physical structure on the disk.
Physical copy
In this article, I focus on the next major aspect of PostgreSQL: physical replication. Another reason the article mentions index “rebalancing” is that Uber once encountered a PostgreSQL replication Bug that corrupted downstream server data (the Bug “only partially affected Postgres version 9.2 and has long since been fixed”).
Because PostgreSQL 9.2 only provides physical replication, a replication bug “could cause a large portion of binary trees to become completely invalid.” In detail: if a node split is copied out incorrectly, it will no longer point to the correct child node, and that child node will become invalid. This is absolutely true, as in “if there are bugs, bad things happen”. You don’t need to change a lot of data to break the tree structure: a small pointer is enough.
The Uber article mentions other issues with physical replication: 1. Huge replication traffic; 2. Partly due to write magnification caused by update; 3, and the upgrade of the database version requires the shutdown window. Although the first one makes sense to me, I really disagree with the second one. (But it’s been mentioned on the PostgresqL-hacker mailing list)
According to the PostgreSQL documentation, there are two ways to handle this problem :(1) delay stream replication and provide a configurable timeout so that read transactions can complete normally. If a query does not complete in time, kill the query and continue to apply stream replication. (2) Configuration replication provides feedback to the master about the query being run so that the master does not vacuum any row versions that the slave still needs. The Uber article mentions the first approach, but never the second. Instead, the article blamed its developers.
About developers
“For example, a developer has some code for emailing receipts to users,” the article crowed. Depending on how it is written, the code may implicitly have a database transaction that holds the session until the message is finished sending. While it’s not good to have your code holding database transactions while performing user-independent I/O blocking, the reality is that most developers are not database experts and may not all understand this problem, especially with ORM, which can obscure many low-level details, such as transactions.”
Unfortunately, I understand and agree with this argument. Not only are most developers not database experts, BUT I would go so far as to say that most engineers know too little about databases, because every developer who touches SQL needs to know about transactions — not just database experts.
SQL training for programmers is my current job. I’ve done this for companies of all sizes. If there’s one thing I know for sure, it’s the realization that these SQL are very poorly written. The “open transactions” question in this article confirms to me that almost no developer is aware of the existence of read-only transactions. Most developers only know that transactions can be used for rollback. I’ve met a lot of developers who have this misconception, so I’ve prepared a slideshow to explain it and have uploaded it to curious readers.
About success
This is the last question I want to write about: the more people a company employs, the closer its performance will be to its average. Hyperbole, if you hired the entire planet, you would get exactly the average of all people. Hiring more people really just increases the sample size.
Two ways to break this rule are:
-
Hiring only the best developers is difficult because it can take a long time to find the right people.
-
Hire medium level people and provide training. It will take a long time for a new employee to get started, and it may require existing employees to spend time training new employees. The common problem for both approaches is time. Because your business needs to grow fast, you probably don’t have much time to wait, so you hire developers who don’t know much about databases (2014 rule of thumb). In other words, it’s easier to change technology than people for a company that wants to grow fast.
Success factors also influence the requirements of the technology stack over time. In the early stages, entrepreneurs need out-of-the-box technologies that are immediately available and flexible enough to be used in their business. SQL is a good choice because it’s really flexible (you can query your data any way you want) and it’s easy to find someone who knows at least a little bit about SQL. Good, let’s get started! That’s the end of the story for many, if not most, companies. Even if these companies achieve average success and their business grows, they will likely remain stuck with limited SQL database usage forever.
A lucky handful of entrepreneurs eventually go beyond using SQL. As of the time of the event, they had access to more (almost unlimited?) And then something wonderful happened: they realized that they could solve a lot of problems if they replaced the generic database and customized it for the system. This is when a new NoSQL database is born. At Uber, they call him Schemaless.
Uber’s selection of database
So far, I don’t believe Uber needs to replace PostgreSQL with MySQL as their article suggests. It looks like they’re actually replacing PostgreSQL with their own custom solution, which happens to be MySQL/InnoDB’s current lock support.
It seems that the article simply explains that MySQL/InnoDB is a better fit than PostgreSQL as a Schemaless data back end. For those using Schemaless, follow Uber’s advice.
Sadly, there are places in the article that are not detailed and leave the reader with the impression that PostgreSQL is terrible. It doesn’t mention why they required Schemaless that year and moved from MySQL to PostgreSQL in 2013.
Recent Activities:
Gdevops Global Agile Operations Summit guangzhou Station
