程序 数据初始化 mysql_为啥Uber的程序员把数据库从??PostgreSQL换成了??MySQL？(下)...

(本篇文章原文来自Uber Engineering,原文链接点这，作者为Evan Klitzke。 同上一篇文章一样，会在翻译不通顺处放出原文供大伙自行理解。由于原文较长，所以分为上下两部。

下部较为枯燥，全篇硬货知识。想看上部的话点这。)

上一篇作者提到了postgres的一些缺点，这一部将轻微说一下postgres的其余缺点，着重于MySQL与postgres的对比来说明为啥Uber团队换成了Mysql。

---

主从复制

由于复制发生在磁盘级别，因此将多次写入的问题自然也转化到了物理层。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间同步数据。运行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中的一些高级用法。