三门峡建设网站哪家好,重庆市万州建设工程信息网,网络销售是什么意思,企业网站备案好不好数据库有很多种#xff08;截至 2023 年 4 月有 897 个#xff09;。面对如此多的数据库#xff0c;很难知道该选择什么#xff01;但有一个有趣的现象#xff0c;互联网集体决定新应用程序的默认选择。在 2000 年代#xff0c;传统观点选择 MySQL 是因为像 Google 和 Fa…数据库有很多种截至 2023 年 4 月有 897 个。面对如此多的数据库很难知道该选择什么但有一个有趣的现象互联网集体决定新应用程序的默认选择。在 2000 年代传统观点选择 MySQL 是因为像 Google 和 Facebook 这样的新兴科技明星都在使用它。然后在 2010 年代它是 MongoDB因为非持久 non-durable writes 写入使其成为“具有可扩展性和敏捷性的 Web-Scale”。在过去五年中PostgreSQL 已成为互联网上的宠儿 DBMS。并且有充分的理由它可靠、功能丰富、可扩展并且非常适合大多数操作工作负载。
尽管 OtterTune 非常喜欢 PostgreSQL但它的某些方面并不是很好。因此我们不想像其他人一样再写一篇博客文章来宣扬每个人最喜欢的大象主题(postgresql logo是一个大象) DBMS 的强大功能而是想讨论一件很糟糕的事情PostgreSQL 如何实现多版本并发控制 (MVCC)。我们在卡内基梅隆大学的研究以及在 Amazon RDS 上优化 PostgreSQL 数据库实例的经验表明其 MVCC 实现相对于其他广泛使用的关系 DBMS包括 MySQL、Oracle 和 Microsoft SQL Server中最差的。是的亚马逊的 PostgreSQL Aurora 仍然存在这些问题。
在本文中我们将深入探讨 MVCC它是什么、PostgreSQL 是如何实现的以及为什么它很糟糕。 OtterTune 的目标是减少您对数据库的担忧因此我们对解决这个问题进行了很多思考。我们将在下周的后续文章中介绍 OtterTune 针对 RDS 和 Aurora 数据库自动管理 PostgreSQL MVCC 问题的解决方案。
什么是多版本并发控制
DBMS 中 MVCC 的目标是允许多个查询同时读取和写入数据库而不会在可能的情况下相互干扰。 MVCC 的基本思想是 DBMS 永远不会覆盖现有的行。相反对于每个逻辑行DBMS 维护多个物理版本。当应用程序执行查询时DBMS 根据某些版本号排序例如创建时间戳确定要检索哪个版本来满足请求。这种方法的好处是多个查询可以读取旧版本的行而不会被另一个更新它的操作阻止。当 DBMS 启动该查询的事务时查询会观察数据库的快照快照隔离。这种方法消除了显式记录锁record locks的需要显式记录锁会在写入者修改同一项目时阻止读取者访问数据。
David Reed 1978 年获得麻省理工学院博士学位学界认为他的论文“分布式数据库系统中的并发控制”是第一篇描述 MVCC 的出版物。 MVCC 的第一个商业 DBMS 实现是 20 世纪 80 年代的 InterBase。从那时起过去二十年中创建的几乎所有支持事务的新 DBMS 都实现了 MVCC。
系统工程师在构建支持 MVCC 的 DBMS 时必须做出多项设计决策。从较高的层面来看它可以归结为以下几点
如何存储对现有行的更新。如何在运行时查找查询的正确行版本。如何删除不再可见的过期版本。
这些决定并不相互排斥。就 PostgreSQL 而言正是他们在 20 世纪 80 年代决定处理第一个问题的方式导致了我们今天仍然需要处理的另外两个问题。
在下面讨论中我们将使用以下包含电影信息的表 作为示例。表中的每一行都包含电影名称、发行年份、导演和作为主键的唯一 ID以及电影名称和导演的辅助索引。以下是创建该表的 DDL 命令
CREATE TABLE movies (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,name VARCHAR(256) NOT NULL,year SMALLINT NOT NULL,director VARCHAR(128)
);
CREATE INDEX idx_name ON movies (name);
CREATE INDEX idx_director ON movies (director);该表包含一个主索引 ( movies_pkey ) 和两个BTree辅助索引 ( idx_name 、 idx_director )。
PostgreSQL的多版本并发控制
正如 Stonebraker 1987 年的系统设计文档中所讨论的那样PostgreSQL 从一开始就是为了支持多版本而设计的。 PostgreSQL MVCC 方案的核心思想看似简单当sql更新表中的现有行时DBMS 会复制该行并将更改应用于此新版本而不是覆盖原始行。我们将这种方法称为 仅附加(append-only) 版本存储方案。但正如我们现在所描述的这种方法对系统的其余部分有一些重要的影响。
Multi-Versioned Storage 多版本存储
PostgreSQL 将所有 行版本 存储在同一存储空间的表中。要更新现有元组DBMS 首先从表中获取新行版本的空槽。然后它将当前版本的行内容复制到新版本并将修改应用于新分配的版本槽中的行。您可以在下面的示例中看到这个过程当应用程序对电影数据库执行更新查询以将“Shaolin and Wu Tang”的发行年份从 1985 年更改为 1983 年时 当 UPDATE 语句更改表中的元组时PostgreSQL 会复制该元组的原始版本然后将更改应用到新版本。在此示例中表页 #1 中没有更多空间因此 PostgreSQL 在表页 #2 中创建新版本。
现在两个物理版本代表同一逻辑行DBMS 需要记录这些版本的沿袭历史以便知道将来如何找到它们。 MVCC DBMS 通过单链表创建版本链来实现这一点。版本链仅朝一个方向延伸以减少存储和维护开销。这意味着 DBMS 必须决定使用什么顺序最新到最旧 (newest-to-oldestN2O) 顺序或最旧到最新 (oldest-to-newestO2N)。对于 N2O 顺序每个元组版本都指向其先前版本并且版本链的头部始终是最新版本。对于O2N顺序每个元组版本都指向其新版本而头部是最旧的元组版本。 O2N 方法避免了 DBMS 在每次修改元组时更新索引以指向更新版本的元组。但是DBMS 在查询处理过程中可能需要更长的时间才能找到最新版本可能会遍历很长的版本链。大多数 DBMS包括 Oracle 和 MySQL都实现了 N2O。但 PostgreSQL 在使用 O2N 方面是独一无二的除了 Microsoft 用于 SQL Server 的内存 OLTP 引擎。
下一个问题是 PostgreSQL 如何确定为这些 版本指针 记录什么。 PostgreSQL 中每一行的标头包含下一个版本的元组 id 字段 (t_tcid)如果是最新版本则包含其自己的元组 id。因此如下一个示例所示当查询请求行的最新版本时DBMS 会遍历索引找到最旧的版本然后跟踪指针直到找到所需的版本。 SELECT 查询遍历 索引 以查找 匹配请求的电影名称的元组。索引条目 指向元组的 最旧版本这意味着 PostgreSQL 遵循原始版本中嵌入的版本链来查找新版本。
PostgreSQL 开发人员很早就意识到其 MVCC 方案存在两个问题。首先每次更新时复制整个元组的新副本是昂贵的。其次遍历整个版本链只是为了找到最新版本这是大多数查询想要的是浪费的。当然还存在清理旧版本的问题我们将在下面介绍。
为了避免遍历整个版本链PostgreSQL 为行的 每个物理版本 在表的索引中添加一个条目。这意味着如果逻辑行有五个物理版本则索引中该元组将有最多五个条目在下面的示例中我们看到 idx_name 索引包含位于不同页面上的每个“Shaolin and Wu Tang”行的条目。这样就可以直接访问元组的最新版本而无需遍历长版本链。 在此示例中索引包含“Shaolin and Wu Tang”元组的多个条目每个版本一个。现在PostgreSQL 使用索引来查找最新版本然后立即从表页#2 中检索它而无需遍历从表页#1 开始遍历版本链。
PostgreSQL 尝试通过在与 旧版本相同的磁盘页面块中创建新副本来减少磁盘 I/O从而避免安装多个索引条目并在多个页面上存储相关版本。这种优化称为仅堆元组 (heap-only tuple (HOT)) 更新。如果更新不修改 表索引 引用的任何列并且新版本与旧版本存储在 同一 数据页上如果该页中有空间那么 DBMS 将使用 HOT 方法。现在在我们的示例中更新后索引仍然指向旧版本并且查询通过遍历版本链来检索最新版本。在正常操作期间PostgreSQL 通过删除旧版本来修剪版本链来进一步优化此过程。
版本清除(Version Vacuum )
我们已经确定只要应用程序更新行PostgreSQL 就会创建行的副本。下一个问题是系统如何删除旧版本称为“死元组”。 20 世纪 80 年代的 PostgreSQL 原始版本并没有删除死元组。这个想法是保留所有旧版本允许应用程序执行“时间旅行”查询来检查特定时间点的数据库例如在数据库状态运行 SELECT 查询于上周末存在。但从不删除死元组意味着如果应用程序删除元组表的大小永远不会缩小。这也意味着频繁更新的元组的版本链较长这会减慢查询速度除非 PostgreSQL 添加了索引条目允许查询快速跳转到正确的版本而不是遍历版本链。但现在这意味着索引更大速度更慢并增加了额外的内存压力。希望您现在能够理解为什么所有这些问题都是相互关联的。
为了克服这些问题PostgreSQL 使用 真空(vacuum)过程 来清理表中的死元组。真空对自上次运行以来修改的表页执行顺序扫描并查找过期版本。如果某个版本对任何活动事务都不可见则 DBMS 会认为该版本“已过期”。这意味着当前没有事务正在访问该版本未来的事务将使用最新的“实时”版本。因此删除过期版本并回收空间以供重用是安全的。
PostgreSQL 根据其配置设置定期自动执行此清理过程 (autovacuum)。除了影响所有表的vacuum频率的全局设置之外PostgreSQL还提供了在表级别配置autovacuum的灵活性以微调特定表的过程。用户还可以通过 VACUUM SQL命令手动触发vacuum以优化数据库性能。
为什么 PostgreSQL 的 MVCC 是最差的
我们会直言不讳如果有人今天要构建一个新的 MVCC DBMS他们不应该像 PostgreSQL 那样做例如带有 autovacuum 的append-only存储。在我们 2018 年的 VLDB 论文又名“关于 MVCC 的最佳论文”中我们没有发现另一个 DBMS 以 PostgreSQL 的方式执行 MVCC。它的设计是 20 世纪 80 年代的遗物在 20 世纪 90 年代log-structured系统模式激增之前。
我们来谈谈 PostgreSQL 的 MVCC 出现的四个问题。我们还将讨论为什么其他 MVCC DBMS如 Oracle 和 MySQL可以避免这些问题。
问题#1版本复制
使用 MVCC 中的append-only存储方案如果查询更新元组DBMS 会将其所有列复制到新版本中。无论查询更新单个列还是所有列都会发生这种复制。正如您可以想象的那样仅附加 MVCC 会导致大量数据重复并增加存储需求。这种方法意味着 PostgreSQL 比其他 DBMS 需要更多的内存和磁盘存储来存储数据库这意味着查询速度更慢云成本更高。
MySQL 和 Oracle 不是为新版本复制整个元组而是在新版本和当前版本之间存储一个紧凑的增量可以将其视为 git diff。使用增量意味着如果查询仅更新具有 1000 列的表的元组中的单个列则 DBMS 仅存储包含对该列的更改的增量记录。另一方面PostgreSQL 创建一个新版本其中包含查询更改的一列和其他 999 个未更改的列。我们将忽略 TOAST 属性因为 PostgreSQL 以 不同的方式处理它们。
有人尝试使 PostgreSQL 的版本存储实现现代化。 EnterpriseDB于2013年启动了zheap项目以取代append-only存储引擎以使用增量版本。不幸的是最后一次官方更新是在 2021 年据我们所知这项努力已经失败。
问题#2表膨胀
PostgreSQL 中的过期版本即死元组也比增量版本占用更多的空间。尽管 PostgreSQL 的 autovacuum 最终会删除这些死元组但写入繁重的工作负载可能会导致它们累积的速度快于清理的速度从而导致数据库持续增长。 DBMS 必须在查询执行期间将死元组加载到内存中因为系统将死元组与页中的活元组混合在一起。不受限制的膨胀会导致 DBMS 在表扫描期间产生更多的 IOPS 并消耗更多的内存从而降低查询性能。此外由死元组引起的不准确的优化器统计信息可能会导致糟糕的查询计划。
假设我们的 电影表 有 1000 万个活动元组和 4000 万个死亡元组使得表中 80% 的数据成为过时数据。还假设该表的列数比我们显示的多得多并且每个元组的平均大小为 1KB。在这种情况下活动元组占用 10GB 存储空间而死元组占用约 40GB 存储空间表的总大小为 50GB。当查询对此表执行全表扫描时PostgreSQL 必须从磁盘检索所有 50GB 并将其存储在内存中即使其中大部分已过时。尽管Postgres有一个保护机制来避免顺序扫描污染其缓冲池缓存但它无助于防止IO成本。
即使您确保 PostgreSQL 的 autovacuum 定期运行并且能够跟上您的工作负载这并不总是容易做到请参见下文autovacuum 也无法回收存储空间。 autovacuum 仅删除死元组并重新定位每个页内的活动元组但不会从磁盘回收空页。
当 DBMS 由于缺少任何元组而截断最后一页时其他页仍保留在磁盘上。在上面的示例中即使 PostgreSQL 从 movie 表中删除了 40GB 的死元组它仍然保留了操作系统分配的 50GB 存储空间或者在 RDS 的情况下从 Amazon 分配。要回收并返回此类未使用的空间必须使用 VACUUM FULL 或 pg_repack 扩展将整个表重写到不浪费存储的新空间。如果不考虑对生产数据库的性能影响运行这些操作中的任何一个都不是一件容易的事。它们是资源密集型且耗时的操作会降低查询性能。下图显示了 VACUUM 和 VACUUM FULL 的工作原理。 通过 PostgreSQL 的常规 VACUUM 操作DBMS 仅从每个表页中删除死元组并重新组织它以将所有活动元组放在页的末尾。使用 VACUUM FULLPostgreSQL 会从每个页面中删除死元组将剩余的活动元组合并并压缩到新页面表第 #3 页然后删除不需要的页面表第 #1 / #2 页。
问题#3二级索引维护
对元组的单次更新需要 PostgreSQL 更新该表的所有索引。更新所有索引是必要的因为 PostgreSQL 在主索引和辅助索引中都使用版本的确切物理位置。除非 DBMS 将新版本存储在与先前版本相同的页面中热更新否则系统会对每次更新执行此操作。
回到我们的 UPDATE 查询示例PostgreSQL 通过像以前一样将原始版本复制到新页面来创建新版本。但它还会在表的主键索引 ( movies_pkey ) 和两个辅助索引 ( idx_director 、 idx_name ) 中插入指向新版本的条目。 使用非 HOT 更新的 PostgreSQL 索引维护操作示例。 DBMS 在表第 #2 页中创建元组的新版本然后在所有表的索引中插入指向该版本的新条目。
PostgreSQL 需要在每次更新时修改表的所有索引这会对性能产生一些影响。显然这会使更新查询变慢因为系统必须做更多的工作。 DBMS 需要额外的 I/O 来遍历每个索引并插入新条目。访问索引会在索引和 DBMS 的内部数据结构例如缓冲池的页表中引入锁/闩锁争用。同样PostgreSQL 会对表的所有索引执行此维护工作即使查询永远不会使用它们顺便说一下OtterTune 会自动查找数据库中未使用的索引。这些额外的读写操作在根据 IOPS 向用户收费的 DBMS例如 Amazon Aurora中是有问题的。
如上所述如果 PostgreSQL 可以执行热写入其中新版本与当前版本位于同一页上则可以避免每次更新索引。我们对 OtterTune 客户的 PostgreSQL 数据库的分析表明平均大约 46% 的更新使用 HOT 优化。尽管这是一个令人印象深刻的数字但这仍然意味着超过 50% 的更新要付出这种代价。
有很多用户在 PostgreSQL MVCC 实现的这一方面遇到困难的例子。最著名的证明是 Uber 2016 年关于他们为何从 Postgres 转向 MySQL 的博客文章。他们的写入繁重的工作负载在具有许多二级索引的表上遇到了严重的性能问题。
Oracle和MySQL在MVCC实现中不存在这个问题因为它们的二级索引不存储新版本的物理地址。相反它们存储一个逻辑标识符例如元组 ID、主键然后 DBMS 使用该标识符来查找当前版本的物理地址。现在这可能会使二级索引读取速度变慢因为 DBMS 必须解析逻辑标识符但这些 DBMS 在其 MVCC 实现中具有其他优势可以减少开销。
旁注Uber 的博客文章 中有关 PostgreSQL 版本存储的错误。具体来说PostgreSQL 中的每个元组都存储一个指向新版本的指针而不是之前的版本如博客中所述。这会导致 O2N 版本链排序而不是 Uber 错误声明的 N2O 版本链。
问题#4真空管理
PostgreSQL 的性能在很大程度上依赖于 autovacuum 删除过时数据和回收空间的有效性这就是为什么 OtterTune 在您第一次连接数据库时立即检查 autovacuum 的健康状态。无论您运行的是 RDS、Aurora 还是 Aurora Serverless都没有关系 PostgreSQL 的所有变体都有相同的 autovacuum 问题。
但由于其复杂性确保 PostgreSQL 的 autovacuum 尽可能最佳地运行是很困难的。 PostgreSQL 用于调整 autovacuum 的默认设置并不适合所有表尤其是大型表。例如配置旋钮的默认设置是 20%该旋钮控制在 autovacuum 启动之前 PostgreSQL 必须更新的表的百分比 (autovacuum_vacuum_scale_factor)。此阈值意味着如果表有 1 亿个元组则 DBMS 不会触发 autovacuum直到查询更新至少 2000 万个元组。因此PostgreSQL 可能不必要地在表中长时间保留大量死元组从而产生 IO 和内存成本。
PostgreSQL 中 autovacuum 的另一个问题是它可能会被长时间运行的事务阻塞这可能会导致更多死元组和过时统计信息的积累。未能及时清理过期版本会导致许多性能问题导致更多长时间运行的事务阻塞自动清理过程。这变成了一个恶性循环需要人类通过终止长时间运行的事务来手动干预。
考虑下图该图显示了 OtterTune 客户数据库中两周内死元组的数量 PostgreSQL Amazon RDS 数据库中一段时间内失效元组的数量。
图表中的锯齿图案显示 autovacuum 大约每天执行一次主要清理工作。例如2 月 14 日DBMS 清理了 320 万个死元组。该图实际上是一个不健康的 PostgreSQL 数据库的示例。该图表清楚地显示了死亡元组数量的上升趋势因为自动清理无法跟上。
在 OtterTune我们经常在客户的数据库中看到这个问题。一个 PostgreSQL RDS 实例由于批量插入后的过时统计信息而导致查询长时间运行。此查询阻止了 autovacuum 更新统计信息从而导致更多长时间运行的查询。 OtterTune 的自动运行状况检查发现了问题但管理员仍然必须手动终止查询并在批量插入后运行 ANALYZE。好消息是长查询的执行时间从 52 分钟缩短到了 34 秒。
结束语
在构建 DBMS 时总是需要做出一些艰难的设计决策。这些决策将导致任何 DBMS 在不同的工作负载上表现不同。对于 Uber 特定的写入密集型工作负载PostgreSQL 由于 MVCC 导致的索引写入放大是他们改用 MySQL 的原因。但请不要误解我们的谩骂意味着我们认为你不应该使用 PostgreSQL。尽管它的 MVCC 实现方式是错误的但 PostgreSQL 仍然是我们最喜欢的 DBMS。热爱某件事就是愿意克服它的缺陷参见丹·萨维奇的《入场的代价》。
那么如何解决 PostgreSQL 的怪癖呢好吧您可以花费大量的时间和精力自己进行调整。祝你好运。
我们将在下一篇文章中详细介绍我们可以做什么。
原文地址
