关于mysql:为啥Uber的程序员把数据库从🐘PostgreSQL换成了🐬MySQL

(原文来自Uber Engineering, 原文链接点这，作者为Evan Klitzke)

简介

Uber的晚期的架构设计是由Python编写的一体式后端架构，并应用Postgres进行数据长久化。时至今日，Uber的架构已产生了巨大变化，曾经成为了业界微服务和新型数据平台的模板。具体来说，以前大多状况下咱们会优先思考Postgres，但当初咱们放弃了Postgres而抉择应用Schemaless。这是一种基于MySQL的新型的数据库分片技术。
在本文中，咱们将探讨在团队在应用Postgres中发现的一些毛病，并解释为何要放弃Postgres转而在MySQL上构建Schemaless和其余相干的后端服务决定。

Postgres的体系结构

在咱们刚应用postgres的时候意识到了一些Postgres的问题：

• 单次操作导致磁盘的屡次写入
• 数据冗余
• 表数据容易净化问题
• 蹩脚的MVCC
• 版本升级艰难

咱们通过postgres对磁盘上的表和索引的剖析来进一步意识这些问题，并与MySQL(InnoDB)进行比照。另外，咱们的所有剖析基于Postgres 9.2版本。据我所知，以下将要探讨的内容在较新的Postgres版本(此处我比照了下日期指的应该是指v10.1)中并未产生显著的改变。

磁盘存储形式(on-disk format)

关系数据库必须提供一些要害的性能，诸如：

• 增删改查
• 提供进行数据结构更改的性能
• 提供多版本并发管制（MVCC）
• 最重要的是如何使这些性能协同工作

Postgres的外围设计之一便是一种不可变行数据，在Postgres中被称为“元组”(tuple)。这些元组被定义为惟一标识称之为ctid。ctid示意了元组在磁盘上的地位（物理盘偏移）。多个ctid能够潜在地形容单个行（例如，当出于MVCC目标而存在该行的多个版本时，或者当主动真空解决尚未回收该行的旧版本时）。有组织的元组的汇合形成一个表。表本身具备索引，这些索引被结构成一些特定的数据结构(如B-trees)，并将索引字段映射到ctid 无效。
通常，用户是在应用中是觉察不到ctid的，然而理解它们的工作原理将有助于理解Postgres的磁盘存储构造。要查看行的以后ctid能够输出如下命令：

uber@[local] uber=> SELECT ctid, * FROM my_table LIMIT 1;

-[ RECORD 1 ]--------+------------------------------

ctid                 | (0,1)

为了进一步解释，让咱们思考以用户表为示例。对于每个用户，咱们都有一个自增的ID作为主键，其余内容包含名字、姓氏以及出世年份。咱们还在用户的全名（名字和姓氏）上定义了一个二级索引，并为出世年份定义了另一个二级索引。创立这样的表的DDL可能是这样的：

CREATE TABLE users (

    id SERIAL,

    first TEXT,

    last TEXT,

    birth_year INTEGER,

    PRIMARY KEY (id)

);
 CREATE INDEX ix_users_first_last ON users (first, last);
 CREATE INDEX ix_users_birth_year ON users（birth_year);

留神此表定义的三个索引：主键索引加上咱们定义的两个二级索引。
上面咱们插入一些数据，该数据由一些驰名的数学家组成:

如前所述，每一条数据都对应着一个隐式具惟一的，不通明的ctid。因而，咱们能够这样思考表的外部示意模式：

将id 映射到ctid的主键索引如图所示：

B树是在主键字段定义的，并且每个节点都保留了ctid的 值。留神，在这种状况下，因为应用了自增的id ，因而B树中字段的程序恰好与表中的程序雷同。
二级索引看起来都差不多。次要区别在于字段的存储程序不同，因为B树必须像字典一样程序组织。每个索引以名字结尾，指向字母表的顶部：

同样，birth_year的索按升序排列，如图所示：

在这两种状况下，二级索引中的ctid 字段在字典上都没有减少，这与自增的主键是不同的。
假如咱们须要更新该表中的一条记录。举个栗子，假如咱们要更新出世年份字段，以估算al-Khwārizmī(代数之父-阿尔·花剌子模)的出世年份公约前770。像我方才所说的，元组是不变的。因而，为了更新记录，咱们向表中插入一个新的元组。这种新的元组有一个新的CTID ，定义为I 。Postgres须要将I处的元组与D处的旧元组辨别开。在Postgres的外部，每个元组都存储一个版本字段和一个指向前一个元组的指针（如果有的话）。因而，表的新构造如图所示：

只有存在al-Khwārizmī行的两个版本，索引就必须同时蕴含两个行的条目。为简便起见，咱们省略了主键索引，而在此处仅显示了辅助索引，如图所示：

咱们用红色示意旧版本，用绿色示意新版本。Postgres应用另一个保留行版本的字段来确定哪个元组是最新的。依附这样的字段，数据库能够确定哪个元组能够会不被新版的事务所看到。

Postgres通过将主库上的WAL发送到从库来实现流复制。每个从库在解体复原中都是无效的，通过一直地更新WAL，就像解体后启动一样。流复制和理论场景的解体复原之间的惟一区别就是，处于“热备用”(hot standyby)模式的从库在利用WAL时会提供查问，但实际上处于解体复原状态下的Postgres数据库通常会回绝提供任何查问，直到数据库的实例实现解体复原过程。
因为WAL实际上是为解体复原目标而设计的，所以它蕴含无关磁盘更新的底层信息。WAL的内容处于元组及其磁盘偏移量（即ctids ）的理论磁盘地位的层面上。如果在主库和从库同步之前敞开Postgres主库和从库，那么从库上的理论磁盘内容与主库上的内容将齐全匹配。因而，像rsync之类的工具有可能修复呈现数据净化的从库。
然而，这样的设计导致咱们解决数据时更简单更繁琐。

写入放大(Write Amplification)

Postgres设计的第一个问题在其余状况下称为写放大。通常，写入放大是指将数据写入SSD磁盘时遇到的问题：小的更新（例如，写入几个字节）在转换到物理层时会变得更大，或者说更低廉。在Postgres中也会呈现同样的问题。在后面的示例中，当咱们对al-Khwārizmī的出世年进行了小的逻辑更新时，咱们至多要触发四个底层的更新：

1. 将新的行元组写入表空间
2. 更新主键索引以增加新元组记录
3. 更新first和last的索引以增加新元组记录
4. 更新birth_year 索引以增加新元组记录

实际上，这四个更新仅反映了对主表空间的写操作。这些写操作中的每一个也须要反映在WAL中，因而磁盘上的写操作总数甚至更大。

这里值得注意的是二、三步骤。当咱们更新al-Khwārizmī的出世年份时，咱们实际上没有更改他的主键，也没有更新他的last和first。即便如此，咱们依然通过在数据库中为行记录创立新的元组来更新这些索引。对于具备大量二级索引的表，这些多余的步骤可能会导致查问效率机器低下。例如，如果咱们在一个表上定义了十二个索引，那么将必须对仅由一个索引笼罩的字段的更新流传到其余11个索引中。

主从复制

因为复制产生在磁盘级别，因而将屡次写入的问题天然也转化到了物理层。postgres没有复制一个个数据库改变的记录，例如“将ctid = D的出世年份更改为当初的770”，而是为咱们方才形容的所有四次操作都写入了WAL，并且所有这四个WAL条目都同步给其余的服务。因而，屡次写入问题又转化为了屡次传输的问题，并且Postgres同步的数据流很快变得贼长，并且占用大量带宽。

如果Postgres复制仅产生在单个数据中心内，则同步所需的带宽可能没啥问题。古代网络设备和交换机能够轻松得多解决大量带宽，许多托管服务提供商提供收费或便宜的外部数据中心带宽。然而，当必须在数据中心之间进行复制时，问题会迅速降级。例如，Uber最后在西海岸的托管空间中应用物理服务器。为了容灾的目标，咱们在第二个东海岸托管空间中增加了服务器。在此设计中，咱们在西部数据中心有一个主库和一堆从库，在东部有一个容灾的正本。

级联复制将数据中心间的带宽要求限度为仅在主正本和单个正本之间所需的复制数量，即便第二个数据中心中有很多正本也是如此。然而，Postgres复制协定的详细信息依然可能导致应用大量索引的数据库的数据量微小。购买高带宽的“跨国宽带”(此处指从美国最西边连贯到美国最东边)十分低廉，即便不思考钱的问题，也基本不可能取得具备与本地互连雷同网速的“跨国宽带”。带宽问题也给WAL同步带来了麻烦。除了将所有WAL更新从西海岸发送到东海岸之外，咱们还将所有WAL存档到云存储服务中，两者都提供了额定的保障，即在产生劫难时咱们也能够很快的复原数据，并且存档的WAL能够从数据库快照中调出新的正本。在晚期的顶峰流量期间，咱们存储的Web服务的带宽级别基本不够，无奈跟上写入WAL的速度。

数据净化

在例行数据库扩容的过程中，咱们遇到了Postgres 9.2谬误。从库追随工夫切换不正确，导致其中一些从库谬误地读取了某些WAL记录。因为存在此谬误，本应由版本控制机制标记为非流动的数据实际上并没有被标记。
以下查问阐明了该谬误将如何影响用户示意例：
SELECT * FROM users where ID = 4;
在该谬误的状况下，查问将返回两条记录：原始的al-Khwārizmī行与780 CE出世年份，以及新的al-Khwārizmī行与770 CE出世年份。

这问题可太特么烦人了。首先，咱们无奈得悉这个问题影响了多少数据。从数据库返回的反复的数据导致许多状况下App零碎异样。最终咱们无可奈何增加了防御性编程语句，以检测已知有此问题的表的状况。因为数据净化可能蔓延到了数据服务器，所以在不同的从库上损坏的行也是不同的，这意味着在一个从库上，行X可能是坏的，而行Y是好的，然而在另一从库上，行X可能是好的，而行Y可能是坏的。况且，咱们也不确定数据损坏的从库数量和问题是否会影响了原版的数据。

据目前所知，该问题仅呈现在每个数据库的大量数据上，然而咱们依然十分放心。因为扩容时呈现的数据复制产生在物理级别，因而最终可能会齐全毁坏数据库索引。B树的一个重要个性就是必须定期均衡它们，并且当子树挪动到新的磁盘地位时，这些从新均衡操作可能齐全扭转树的构造。如果挪动了谬误的数据，则可能导致树上大部分数据变为齐全有效。

最初，咱们发现了问题所在并用来确定新降级的的主库没有任何损坏的行。咱们通过从主服务器的快照从新同步所有正本（老吃力了）来修复正本上的损坏问题。

咱们遇到的谬误仅影响了Postgres 9.2的某些版本，并且曾经修复了很长时间。然而，咱们依然发现此类问题很难杜绝。随时可能会公布具备这种相似问题的Postgres新版本，并且因为Postgres的主从之间同步的形式，此问题有可能流传到复制层次结构中的所有数据库中。

MVCC

Postgres并没有真正的反对MVCC。从库利用WAL更新的模式导致它们在任何给定工夫点都具备与主数据库雷同的磁盘数据。这种设计给Uber带来了很多麻烦。
Postgres须要保护MVCC的旧版本的正本。如果进行信息同步时有关上的事务，则如果数据库更新影响事务放弃关上的行，则阻止该更新。(原文：If a streaming replica has an open transaction, updates to the database are blocked if they affect rows held open by the transaction.)
在这种状况下，Postgres将会暂停WAL的程序线程，直到事务完结。如果该事务处理要花费很长时间就会呈现一些问题，因为从库的版本可能重大滞后于主服务器。因而，Postgres应用了一种超时策略应答这种状况：如果事务所波及的WAL的工夫超出设定量，Postgres将间接kill该事务。
这种设计意味着从库通常会比主库落后几秒钟，因而开发人员很容易写出导致事物被kill的代码。对于须要编写事务相干代码的开发人员来说，此问题可能不太显著。例如，假如开发人员须要编写一些代码通过电子邮件将收据发送给用户。依据编写形式的不同，代码可能会隐式地将事务置于关上状态，直到电子邮件实现发送为止。只管在执行不相干的阻塞IO时让代码放弃凋谢的数据库事务并不是一个好方法，但事实上大多数工程师不是数据库专家，可能并不理解这个问题，特地是在应用覆盖了事物底层细节的ORM时。

版本升级

因为同步数据会在物理级别上起工作，因而不可能在不同版本的Postgres间同步数据。运行Postgres 9.3的主库不能同步到运行Postgres 9.2的从库，运行9.2的主库也不能同步到运行Postgres 9.3的从库。
咱们依照以下步骤从一个Postgres GA版本升级到另一个版本：

1. 首先敞开主库
2. 在主库上运行pg_upgrade 的命令。对于体量较大的库而言，这将破费数小时，并且在运行时，无奈与数据库的服务器通信
3. 重启数据库
4. 创立主库的快照。此步骤将复制了主库中的所有数据，因而很可能耗时多个小时
5. 将快照同步给到从库

咱们从Postgres 9.1降级到了Postgres 9.2。然而，该过程破费了许多小时，以至于咱们有力承当再次降级的代价。到Postgres 9.3公布时，Uber的数据曾经有了微小的增长，思考到降级的过程及其漫长，因而，即便以后的Postgres 最新版本是9.5，咱们仍在运行Postgres 9.2。

如果您运行的是Postgres 9.4或更高版本，则能够应用pgologic之类的工具，它为Postgres实现了一个逻辑复制层。应用pgologic，您能够在不同的Postgres版本之间复制数据，这意味着能够进行从9.4到9.5的降级，而不会造成大量的停机工夫。然而该性能依然存在问题，因为它尚未集成到Postgres的”主线”(此处存疑，原文为mainline tree)中，对于在较旧版本上运行Postgres，pgologic并不能提供帮忙。

MySQL的设计结构

除了讲述Postgres的一些局限性之外，咱们还会解释了为什么MySQL会成为Uber Engineering的重要工具。在许多状况下，咱们发现MySQL更适宜咱们的应用。为了了解这些差别，咱们将MySQL的设计结构与Postgres的进行了比照。咱们专门钻研了应用InnoDB的MySQL的底层原理。

InnoDB在磁盘上的工作原理

对于InnoDB磁盘上如何工作的详尽阐述不在本文探讨范畴之内。相同，本文将专一于与Postgres的外围区别。

最重要的架构差别是，尽管Postgres将索引记录间接映射到磁盘上的地位，但InnoDB保护二级构造。InnoDB二级索引记录领有一个指向主键值的指针，而不是持有一个指向磁盘上行地位的指针（就Postgres中的ctid机制一样）。因而，MySQL中的辅助索引将索引键与主键相关联：

为了对（first，last）索引执行索引查找，咱们实际上须要执行两次查找。第一次查找将搜寻表并找到记录的主键。找到主键后，第二次查找将搜寻主键索引以找到该行的磁盘地位。
这种设计意味着在进行非主键查找时，InnoDB绝对于Postgres略有不利，因为必须应用InnoDB搜寻两个索引，而对于Postgres则仅搜寻一个索引。然而，因为数据已规范化，因而更新一行数据仅须要更新新的的索引记录就能够。此外，InnoDB通常会进行行更新。如果出于MVCC的目标，旧事务须要援用一行，则MySQL将旧行复制到称为回滚段的非凡区域中。
让咱们再来关注一下更新al-Khwārizmī的生日时产生的状况。如果有空间，则ID为4 的行中的出世年份字段将被适当地更新。出世年份指数也会更新，以反映新日期。旧行数据将复制到回滚段。主键索引不须要更新，（first ，last ）名称索引也不须要更新。如果此表上有大量索引，则仍只须要更新实际上在birth_year 字段上建设索引的索引。所以说咱们在诸如signup_date ，last_login_time之类的字段上都有索引等等。咱们其实不须要更新这些索引，而Postgres则须要更新。
这种设计还使？和？(原文：vacuuming and compaction)更加无效。在回滚段中间接能够应用所有有资格进行清理的行。相比之下，Postgres的清理过程必须进行全表扫描以辨认已删除的行。

主从复制

MySQL反对多种不同的复制模式：

• 基于语句的复制将复制SQL语句（例如，它将从字面上复制文字语句，例如：UPDATE USER SET birth_year = 770 WHERE id = 4 ）
• 基于行的复制复制更改的行记录
• 混合复制将这两种模式交融在一起

这些模式有各种折衷。基于语句的复制通常是最紧凑的，然而可能须要从库用粗劣的语句来更新大量数据。另一方面，相似于Postgres WAL复制的基于行的复制更为简短，但可导致对从库的更新更效率。

在MySQL中，只有主键索引具备指向行的磁盘偏移量的指针。当波及复制时，这具备重要意义。MySQL复制流仅须要蕴含无关行的逻辑更新的信息。复制更新的各种“更改为行的工夫戳X从T_1至T_2 ” 从库将通过这些语句主动推断出指数的变动是须要进行。

相比之下，Postgres复制流蕴含物理层的更改，例如“在磁盘偏移量 (8,382,491)，写入字节XYZ。”应用Postgres，对磁盘进行的每个物理更改都必须蕴含在WAL流中。较小的逻辑更改（例如更新工夫戳）须要在磁盘上进行许多更改：Postgres必须插入新的元组并更新所有索引以指向该元组。因而，许多更改将放入WAL流中。这种设计差别意味着MySQL复制二进制日志比PostgreSQL的WAL流跟简略。

复制流的工作形式对MVCC如何与从库协同工作具备重要影响。因为MySQL复制流具备逻辑更新，因而正本能够具备真正的MVCC语义；因而，对正本的读取查问不会阻止复制流。相比之下，Postgres WAL流蕴含磁盘上的物理更改，因而Postgres从库无奈利用与读取查问抵触的复制更新，因而它们无奈实现真正的MVCC。

MySQL的复制体系结构意味着，如果谬误导致数据净化，则该问题不太可能导致灾难性故障。因为复制产生在逻辑层，因而像从新均衡B树之类的操作永远不会导致索引损坏。一个典型的MySQL复制问题是语句被跳过（或者被运行两次）的状况。这可能导致数据失落或有效，但不会导致大规模的事变。

最初，MySQL的复制体系结构使得在不同的MySQL版本之间进行复制变得简略。如果复制格局产生更改，MySQL仅会扭转其版本。MySQL的逻辑复制格局还意味着存储引擎层中的磁盘更改不会影响复制格局。进行MySQL降级的典型办法是一次将更新利用于一个从库，一旦更新所有从库，便将其中一个晋升为新的主库。这简直能够实现零停机工夫，并且简化了使MySQL放弃最新状态。

其余MySQL设计劣势

到目前为止，咱们专一于Postgres和MySQL的磁盘体系结构。MySQL体系结构的其余一些重要方面也使它的性能显著优于Postgres。

缓存

首先，两个数据库中的缓存工作形式不同。Postgres为外部缓存调配了一些内存，然而与计算机上的内存总量相比，这些缓存通常很小。为了进步性能，Postgres容许内核缓存最近拜访的磁盘数据。例如，咱们最大的Postgres从库具备768 GB的可用内存，然而实际上只有25 GB的内存是Postgres过程内存。

这种设计的问题在于，与拜访RSS内存相比，通过页缓存(page cache)存拜访数据开销更大。为了从磁盘上查找数据，Postgres过程收回lseek（2）和read（2）零碎调用来定位数据。这些零碎调用中的每一个都会引起上下文切换，这比从内存拜访数据的开销更大。实际上，Postgres在这方面甚至还没有齐全优化：Postgres没有利用pread（2）零碎调用，该零碎调用将seek + read 操作合并为一个零碎调用。

相比之下，InnoDB存储引擎以称为InnoDB的形式实现了本人的LRU称为LRU buffer pool。从逻辑上讲，这与Linux分页缓存存类似。尽管比Postgres的设计简单得多，但InnoDB缓存池的设计有很大的劣势：

• 这使得实现自定义LRU成为可能。例如，能够检测出会毁坏LRU并避免其造成破坏性的拜访模式。
• 这能够缩小不必要的上下文切换。通过InnoDB buffer pool拜访的数据不须要任何用户/内核上下文切换。最坏的状况是TLB未命中，但影响不大，能够通过应用大页面来最小化解决。

连贯解决

MySQL通过产生多个连接线程(thread-per-connection)来实现并发连贯。开销绝对较低；每个线程都有一些用于堆栈空间的内存开销，以及一些在堆上调配给特定于连贯的缓冲区的内存。将MySQL扩大到10,000个左右的并发连贯很常见，实际上，咱们零碎的某些MySQL实例上，曾经靠近这个连接数了。

然而，Postgres应用(process-per-connection)设计。出于很多起因，这比MySQL的设计开销大得多。派生新过程比生成新线程占用更多的内存。此外，过程之间的IPC也比线程之间的低廉得多。Postgres 9.2应用System V IPC原语进行IPC而不是轻量级的futex应用线程时，。Futex的速度比System V IPC快，这是因为在通常状况下，futex不受竞争，所以不必进行上下文切换。

除了与Postgres的设计相干的内存和IPC开销外，即便有足够的可用内存，Postgres也不能很好地解决大量连接数。咱们在将Postgres扩大到数百个连贯时遇到了重大问题。只管官网文档没有很具体地阐明为啥，然而它强烈建议采纳过程外连接池机制来扩大到Postgres的大量连贯。因而，应用pgbouncer与Postgres建设连接池后体现就会好很对。然而，咱们后端服务中偶然会呈现应用程序谬误，导致它们关上的流动连贯（通常是“闲暇的事务”连贯）多于服务应应用的谬误，这些问题极大的缩短了咱们的停机工夫。

论断

在Uber成立初期，Postgres为咱们提供了很好的反对，然而随着数据规模的增长，咱们遇到了很多Postgres的问题。时至今日，咱们扔有一些旧的Postgres实例在咱们零碎上运行，然而咱们的大部分数据库都建设在MySQL之上（Schemaless层），或者在某些非凡状况下，例如Cassandra这样的NoSQL数据库。绝大部分状况下咱们对MySQL感到很称心，并且未来咱们可能还会有更新一些文章来介绍它在Uber中的一些高级用法。