后端开发人员如何理解 SQL 查询将在“产品”上运行良好？ 在大型或快速发展的公司中，并非每个人都可以使用“产品”。 通过访问，并非所有请求都可以轻松检查，创建数据库副本通常需要数小时。 为了解决这些问题，我们创造了一个人工DBA——Joe。 目前已经在多家公司成功落地，帮助了十几家开发者。
视频：
大家好！ 我的名字是阿纳托利·斯坦斯勒。 我在一家公司工作 . 我们致力于通过消除开发人员、DBA 和 QA 与 Postgres 工作相关的延迟来加快开发过程。
我们有很棒的客户，今天报告的一部分将专门介绍我们在与他们合作时遇到的案例。 我将谈谈我们如何帮助他们解决相当严重的问题。
当我们在开发和进行复杂的高负载迁移时，我们会问自己一个问题：“这个迁移会成功吗？”。 我们使用审查，我们使用更有经验的同事、DBA 专家的知识。 他们可以判断它是否会飞。
但如果我们可以在全尺寸副本上自己测试它，也许会更好。 今天我们将讨论现在有哪些测试方法以及如何更好地完成测试以及使用哪些工具。 我们还将讨论这些方法的优缺点，以及我们可以在这里解决的问题。
有谁直接在prod上做过索引或者做过改动？ 相当的。 这对谁造成了数据丢失或停机？ 然后你知道这种痛苦。 感谢上帝，还有备份。
第一种方法是在产品中进行测试。 或者，当开发人员坐在本地机器上时，他有测试数据，有某种有限的选择。 我们推出产品，我们得到了这种情况。
很痛，很贵。 最好不要这样做。
最好的方法是什么？
让我们进行分期并在那里选择产品的某些部分。 或者充其量，让我们采用真正的产品，所有数据。 在本地开发完成后，我们将额外检查阶段性。
这将使我们能够消除一些错误，即防止它们出现在产品中。
有什么问题？
- 问题是我们与同事分享这个阶段。 而且经常发生的情况是您进行了某种更改，bam-没有数据，工作就付诸东流了。 分段是数 TB。 而且你必须等待很长时间才能再次上升。 我们决定明天完成它。 就是这样，我们有了发展。
- 当然，我们有很多同事在那里工作，有很多团队。 而且必须手动完成。 这很不方便。
值得一提的是，如果我们想对数据库进行一些更改，接触数据，更改结构，我们只有一次尝试，一次机会。 如果出现问题，如果迁移中出现错误，那么我们将不会快速回滚。
这比以前的方法要好，但仍然很有可能在生产中出现某种错误。
是什么阻止我们给每个开发人员一个测试台，一个全尺寸的副本？ 我认为很明显是什么阻碍了我们。
谁拥有超过 TB 的数据库？ 超过一半的房间。
很明显，当生产量如此之大时，为每个开发人员保留机器是非常昂贵的，而且还需要很长时间。
我们的客户已经意识到在全尺寸副本上测试所有更改非常重要，但他们的数据库不到 XNUMX TB，而且没有资源为每个开发人员保留一个测试台。 因此，他们必须将转储本地下载到他们的机器上并以这种方式进行测试。 这需要很多时间。
即使你在基础设施内部做，那么每小时下载 200 TB 的数据就已经很不错了。 但他们使用逻辑转储，他们从云端下载到本地。 对于他们来说，速度大约是每小时 XNUMX GB。 从逻辑转储转储、汇总索引等仍然需要时间。
但他们使用这种方法是因为它可以让他们保持产品的可靠性。
我们可以在这里做什么？ 让我们让测试台变得便宜，并为每个开发人员提供他们自己的测试台。
这是可能的。
在这种方法中，当我们为每个开发人员制作精简克隆时，我们可以在一台机器上共享它。 例如，如果您有一个 10TB 的数据库并想将它提供给 10 个开发人员，则您不需要拥有 XNUMX 个 XNUMXTB 的数据库。 您只需要一台机器即可为使用一台机器的每个开发人员制作隔离的精简副本。 稍后我会告诉你它是如何工作的。
真实例子：
-
数据库 - 4,5 太字节。
-
我们可以在 30 秒内获得独立副本。
您不必等待试验台并取决于它有多大。 你可以在几秒钟内得到它。 它将是完全隔离的环境，但它们之间共享数据。
这很棒。 在这里，我们谈论的是魔法和平行宇宙。
在我们的例子中，这可以使用 OpenZFS 系统。
OpenZFS 是一种写时复制文件系统，支持开箱即用的快照和克隆。 它可靠且可扩展。 她很容易管理。 它实际上可以部署在两个团队中。
还有其他选择：
-
LVM，
-
存储（例如 Pure Storage）。
我所说的数据库实验室是模块化的。 可以使用这些选项来实现。 但目前，我们专注于 OpenZFS，因为 LVM 存在一些问题。
怎么运行的？ 我们不会在每次更改数据时都覆盖数据，而是通过简单地标记此新数据来自新的时间点、新的快照来保存它。
并且在未来，当我们想要回滚或者我们想要从某个旧版本创建一个新的克隆时，我们只需说：“好的，给我们这些标记为这样的数据块。”
这个用户将使用这样的数据集。 他会逐渐改变它们，制作自己的快照。
我们将分支。 在我们的案例中，每个开发人员都将有机会拥有他自己编辑的克隆，并且共享的数据将在每个人之间共享。
要在家里部署这样的系统，需要解决两个问题：
-
第一个是数据的来源，您将从哪里获取数据。 您可以设置生产复制。 我希望您已经可以使用您配置的备份。 WAL-E、WAL-G 或 Barman。 即使您正在使用某种云解决方案，如 RDS 或 Cloud SQL，您也可以使用逻辑转储。 但我们仍然建议您使用备份，因为使用这种方法您还将保留文件的物理结构，这将使您能够更接近您在生产中看到的指标，以便发现那些存在的问题。
-
第二个是您要举办数据库实验室的地方。 它可以是云，也可以是内部部署。 这里有一点很重要，ZFS 支持数据压缩。 它做得很好。
想象一下，对于每个这样的克隆，根据我们对基础所做的操作，某种类型的开发将会增长。 为此，dev 也需要空间。 但由于我们以 4,5 TB 为基础，ZFS 会将其压缩为 3,5 TB。 这可能因设置而异。 而且我们还有开发空间。
这样的系统可以用于不同的情况。
-
这些是用于查询验证和优化的开发人员、DBA。
-
这可以在 QA 测试中使用，以在我们将其推出到产品之前测试特定的迁移。 我们还可以使用真实数据为 QA 建立特殊环境，他们可以在其中测试新功能。 而且这将需要几秒钟而不是等待数小时，在其他一些不使用精简副本的情况下可能需要数天。
-
还有一个案例。 如果公司没有设置分析系统，那么我们可以隔离产品库的精简克隆，并将其提供给可用于分析的长查询或特殊索引。
使用这种方法：
-
“产品”出错的可能性很低，因为我们测试了全尺寸数据的所有更改。
-
我们有一种测试文化，因为现在您不必为自己的展台等待数小时。
-
而且没有障碍，测试之间没有等待。 其实你可以去看看。 随着我们加快发展，这种方式会更好。
-
重构会更少。 更少的错误最终会出现在产品中。 稍后我们将重构它们。
-
我们可以扭转不可逆转的变化。 这不是标准方法。
- 这是有益的，因为我们共享测试平台的资源。
已经很好了，但还有什么可以加速的？
得益于这样的系统，我们可以大大降低进入此类测试的门槛。
现在有一个恶性循环，当开发人员为了访问真正的全尺寸数据时，必须成为专家。 必须信任他有这样的访问权。
但是如果没有它如何成长。 但是，如果您只有非常少量的测试数据可用怎么办？ 那么你将得不到任何真正的体验。
如何跳出这个圈子？ 作为第一个界面，方便任何级别的开发人员，我们选择了 Slack bot。 但它可以是任何其他接口。
它允许你做什么？ 您可以进行特定查询并将其发送到数据库的特殊通道。 我们将在几秒钟内自动部署一个精简克隆。 让我们运行这个请求。 我们收集指标和建议。 让我们展示一个可视化。 然后这个克隆将保留下来，以便可以以某种方式优化这个查询，添加索引等。
Slack 也为我们提供了开箱即用的协作机会。 由于这只是一个渠道，您可以在此类请求的线程中开始讨论此请求，联系您的同事，公司内部的 DBA。
但是，当然有问题。 因为这是真实世界，而且我们使用的是同时托管许多克隆的服务器，所以我们必须压缩克隆可用的内存量和 CPU 能力。
但是要使这些测试合理，您需要以某种方式解决这个问题。
很明显，重要的一点是相同的数据。 但我们已经拥有了。 而我们想要实现相同的配置。 而我们可以给出这样一个几乎一模一样的配置。
拥有与生产中相同的硬件会很酷，但它可能会有所不同。
让我们记住 Postgres 是如何使用内存的。 我们有两个缓存。 一种来自文件系统，另一种来自本机 Postgres，即共享缓冲区缓存。
重要的是要注意共享缓冲区缓存是在 Postgres 启动时分配的，具体取决于您在配置中指定的大小。
第二个缓存使用所有可用空间。
而当我们在一台机器上进行多次克隆时，事实证明我们逐渐填满了内存。 从好的方面来说，共享缓冲区缓存占机器上可用内存总量的 25%。
事实证明，如果我们不更改此参数，那么我们将只能在一台机器上运行 4 个实例，即所有此类精简克隆中的 4 个。 当然，这很糟糕，因为我们希望拥有更多。
但另一方面，Buffer Cache是用来执行索引查询的，也就是说，这个计划取决于我们的缓存有多大。 而如果我们只取这个参数并减少它，那么我们的计划就会有很大的改变。
例如，如果我们在 prod 上有一个很大的缓存，那么 Postgres 会更喜欢使用索引。 如果没有，那么就会有 SeqScan。 如果我们的计划不一致，那又有什么意义呢？
但是这里我们得出一个结论，其实Postgres中的plan并不依赖于plan中Shared Buffer中指定的具体大小，而是依赖于effective_cache_size。
Effective_cache_size 是我们可用的缓存的估计数量，即 Buffer Cache 和文件系统缓存的总和。 这是由配置设置的。 而且这个内存没有分配。
由于这个参数，我们可以欺骗 Postgres，说我们实际上有很多可用数据，即使我们没有这些数据。 因此，计划将与生产完全吻合。
但这会影响时间。 我们通过时间优化查询，但重要的是时间取决于许多因素：
-
这取决于当前生产的负载。
-
这取决于机器本身的特性。
这是一个间接参数，但实际上我们可以根据此查询为获得结果而读取的数据量进行精确优化。
如果您希望时间接近我们将在产品中看到的时间，那么我们需要采用最相似的硬件，甚至可能更多，以便所有克隆都适合。 但这是一个妥协，即你会得到相同的计划，你会看到一个特定的查询将读取多少数据，你将能够得出这个查询是好（或迁移）还是坏的结论，它仍然需要优化.
我们来看看Joe具体是如何优化的。
让我们接受来自真实系统的请求。 在本例中，数据库为 1 TB。 我们想计算点赞次数超过 10 次的新帖子的数量。
我们正在向通道写入消息，已为我们部署了一个克隆。 我们将看到这样的请求将在 2,5 分钟内完成。 这是我们注意到的第一件事。
B Joe 将根据计划和指标向您展示自动推荐。
我们将看到查询处理了太多数据以获取相对较少的行数。 并且需要某种专门的索引，因为我们注意到查询中过滤的行太多。
让我们仔细看看发生了什么。 事实上，我们看到我们已经从文件缓存甚至磁盘中读取了将近 142 GB 的数据。 这并不好，因为我们只有 XNUMX 行。
而且，看起来，我们在这里进行了索引扫描，应该很快就能完成，但是由于我们过滤掉了太多行（我们不得不计算它们），所以请求慢慢地完成了。
由于查询中的条件和索引中的条件部分不匹配，因此计划中发生了这种情况。
让我们尝试使索引更精确，然后看看查询执行如何变化。
索引的创建花费了相当长的时间，但是现在我们查看查询，发现时间不是 2,5 分钟，而是 156 毫秒，已经足够了。 而我们只读取了 6 兆字节的数据。
而现在我们只使用索引扫描。
另一个重要的故事是我们希望以更易于理解的方式呈现该计划。 我们已经使用火焰图实现了可视化。
这是一个不同的要求，更加强烈。 我们根据两个参数构建火焰图：这是特定节点在计划和时间中统计的数据量，即节点的执行时间。
在这里，我们可以将特定节点相互比较。 并且它们中的哪一个花费更多或更少会很清楚，这在其他渲染方法中通常很难做到。
当然，每个人都知道 explain.depesz.com。 这种可视化的一个很好的特点是我们保存了文本计划，并将一些基本参数放入表格中，以便我们进行排序。
尚未深入研究该主题的开发人员也使用 explain.depesz.com，因为他们更容易找出哪些指标重要，哪些不重要。
有一种新的可视化方法 - 这是 explain.dalibo.com。 他们做了一个树可视化，但是很难将节点相互比较。 在这里你可以很好地理解结构，但是，如果有一个大的请求，那么你将需要来回滚动，而且还有一个选项。
合作
而且，正如我所说，Slack 为我们提供了合作的机会。 例如，如果我们遇到一个复杂的查询，不清楚如何优化，我们可以在 Slack 的一个线程中与我们的同事澄清这个问题。
在我们看来，对全尺寸数据进行测试很重要。 为此，我们制作了更新数据库实验室工具，该工具以开源形式提供。 您也可以使用 Joe 机器人。 您可以立即接受并在您的位置实施。 所有指南都可以在那里找到。
还需要注意的是，解决方案本身并不是革命性的，因为有Delphix，但它是一个企业解决方案。 它是完全封闭的，非常昂贵。 我们专门研究 Postgres。 这些都是开源产品。 加入我们！
这是我结束的地方。 谢谢你！
问题
你好！ 感谢您的报告！ 非常有趣，尤其是对我来说，因为我前段时间解决了同样的问题。 所以我有很多问题。 希望我至少能得到其中的一部分。
我想知道你如何计算这个环境的地方？ 该技术意味着在某些情况下，您的克隆可以增长到最大尺寸。 粗略地说，如果您有一个 10 TB 的数据库和 10 个克隆，那么很容易模拟每个克隆权衡 XNUMX 个唯一数据的情况。 你如何计算这个地方，也就是你所说的三角洲，这些克隆人将生活在那里？
好问题。 在这里跟踪特定的克隆很重要。 如果一个克隆有一些太大的变化，它开始增长，那么我们可以首先向用户发出警告，或者立即停止这个克隆，这样我们就不会出现失败的情况。
是的，我有一个嵌套问题。 也就是你如何保证这些模块的生命周期？ 我们有这个问题和一个完全不同的故事。 这是怎么发生的？
每个克隆都有一些 ttl。 基本上，我们有一个固定的 ttl。
如果不是秘密，该怎么办？
1 小时，即空闲 - 1 小时。 如果它没有被使用，那么我们就敲它。 但这并不奇怪，因为我们可以在几秒钟内培育出克隆体。 如果您再次需要它，请。
我对技术的选择也很感兴趣，因为，例如，我们出于某种原因并行使用多种方法。 为什么选择 ZFS？ 为什么不使用 LVM？ 您提到 LVM 存在问题。 有什么问题？ 在我看来，就性能而言，最佳选择是存储。
ZFS 的主要问题是什么？ 您必须在同一台主机上运行的事实，即所有实例都将存在于同一操作系统中。 并且在存储的情况下，可以连接不同的设备。 瓶颈只是存储系统上的那些块。 技术选择的问题很有趣。 为什么不是 LVM？
具体的，我们可以在meetup上讨论LVM。 关于存储 - 它只是昂贵。 我们可以在任何地方实施 ZFS 系统。 您可以将其部署在您的机器上。 您可以简单地下载存储库并进行部署。 如果我们谈论 Linux，则几乎所有地方都安装了 ZFS。 也就是说，我们得到了一个非常灵活的解决方案。 ZFS 本身提供了很多开箱即用的功能。 你可以上传多少数据就多少，连接大量的磁盘，还有快照。 而且，正如我所说，它易于管理。 也就是说，使用起来似乎非常愉快。 他接受了考验，他已经很多岁了。 他有一个正在成长的非常大的社区。 ZFS 是一个非常可靠的解决方案。
Nikolai Samokhvalov：我可以进一步评论吗？ 我叫尼古拉，我们和阿纳托利一起工作。 我同意存储很棒。 我们的一些客户有 Pure Storage 等。
Anatoly 正确地指出我们专注于模块化。 并且在未来，您可以实现一个接口——拍摄快照、制作克隆、销毁克隆。 一切都很容易。 如果是的话，存储也很酷。
但是 ZFS 对每个人都可用。 DelPhix 已经够用了，他们有 300 个客户端。 其中，fortune 100有50个客户，也就是针对NASA等，是时候让大家掌握这个技术了。 这就是我们拥有开源核心的原因。 我们有一个不开源的接口部分。 这是我们将展示的平台。 但我们希望每个人都可以访问它。 我们想进行一场革命，让所有测试人员停止在笔记本电脑上猜测。 我们必须编写 SELECT 并立即发现它很慢。 停止等待 DBA 告诉您这件事。 这是主要目标。 我认为我们都会做到这一点。 我们让每个人都拥有这个东西。 因此 ZFS，因为它将随处可用。 感谢社区解决问题和拥有开源许可证等*
问候！ 感谢您的报告！ 我叫马克西姆。 我们处理过同样的问题。 他们自己决定。 您如何在这些克隆之间共享资源？ 每个克隆都可以在任何给定时间做自己的事情：一个测试一个东西，另一个测试另一个，有人建立索引，有人有繁重的工作。 而且如果还可以按CPU划分，那么按IO，怎么划分？ 这是第一个问题。
第二个问题是关于看台的不同之处。 假设我这里有 ZFS，一切都很好，但是产品上的客户端没有 ZFS，而是 ext4，例如。 在这种情况下如何？
问题非常好。 我提到了这个问题，因为我们共享资源。 解决方案是这样的。 想象一下，您正在测试分期。 您也可以同时遇到这样的情况，即有人给一个负载，另一个人。 结果，您会看到难以理解的指标。 甚至同样的问题也可能出现在产品上。 当你想检查某个请求时发现它有问题——它运行缓慢，那么实际上问题不在请求中，而是存在某种并行负载的事实。
因此，在这里重要的是要关注计划是什么、我们将在计划中采取哪些步骤以及我们将为此收集多少数据。 例如，我们的磁盘会加载一些东西，这会特别影响时间。 但是我们可以通过数据量来估计这个请求的负载情况。 同时进行某种执行并不那么重要。
我有两个问题。 这是非常酷的东西。 是否存在生产数据至关重要的情况，例如信用卡号？ 是否已经准备好了一些东西或者它是一个单独的任务？ 第二个问题 - MySQL 是否有类似的东西？
关于数据。 我们将进行混淆直到我们这样做。 但是如果你部署的正是 Joe，如果你不给开发人员访问权限，那么就没有访问数据的权限。 为什么？ 因为乔不显示数据。 它只显示指标、计划，仅此而已。 这是故意的，因为这是我们客户的要求之一。 他们希望能够在不让每个人都访问的情况下进行优化。
关于 MySQL。 该系统可用于在磁盘上存储状态的任何事物。 因为我们在做 Postgres，所以我们现在首先为 Postgres 做所有的自动化。 我们希望自动从备份中获取数据。 我们正在正确配置 Postgres。 我们知道如何使计划匹配等。
但是由于系统是可扩展的，它也可以用于MySQL。 而且还有这样的例子。 Yandex 有类似的东西，但他们不会在任何地方发布。 他们在 Yandex.Metrica 中使用它。 还有一个关于 MySQL 的故事。 但技术是相同的，ZFS。
感谢您的报告！ 我也有几个问题。 您提到克隆可用于分析，例如在那里构建额外的索引。 您能详细介绍一下它是如何工作的吗？
我会立即问第二个问题，关于看台的相似性，计划的相似性。 该计划还取决于 Postgres 收集的统计数据。 你怎么解决这个问题？
根据分析，没有具体案例，因为我们还没有用过，但是有这样的机会。 如果我们谈论的是索引，那么想象一下，一个查询正在查询一个有数亿条记录的表和一个通常不会在 prod 中建立索引的列。 我们想在那里计算一些数据。 如果这个请求发送到prod，那么有可能在prod上很简单，因为请求会在那里处理一分钟。
好吧，让我们做一个不可怕的瘦克隆，停几分钟。 为了使阅读分析更加舒适，我们将为我们对数据感兴趣的那些列添加索引。
每次都会创建索引？
你可以让我们接触数据，制作快照，然后我们将从这个快照中恢复并驱动新的请求。 也就是说，您可以这样做，以便您可以使用已经附加的索引来培育新的克隆。
至于关于统计的问题，如果我们从备份中恢复，如果我们做复制，那么我们的统计将是完全一样的。 因为我们拥有完整的物理数据结构，也就是说，我们也会将数据与所有统计指标一起带来。
这是另一个问题。 如果您使用云解决方案，那么那里只有逻辑转储可用，因为谷歌、亚马逊不允许您获取物理副本。 会有问题。
感谢您的报告。 这里有两个关于 MySQL 和资源共享的好问题。 但是，事实上，这一切都归结为这样一个事实，即这不是特定 DBMS 的主题，而是整个文件系统的主题。 并且，相应地，资源共享问题也应该从那里解决，而不是在它是 Postgres 的最后，而是在文件系统中，在服务器中，例如。
我的问题有点不同。 它更接近于多层数据库，其中有好几层。 例如，我们设置了一个 100 TB 的图像更新，我们正在复制。 我们专门将此解决方案用于数据库。 正在进行复制，正在更新数据。 有 XNUMX 名员工并行工作，他们不断推出这些不同的镜头。 该怎么办？ 怎么保证没有冲突，他们启动了一个，然后文件系统变了，这些图片都去了？
他们不会去，因为这就是 ZFS 的工作方式。 我们可以在一个线程中单独保存因复制而产生的文件系统更改。 并保留开发人员在旧版本数据上使用的克隆。 它对我们有用，一切都井井有条。
事实证明，更新将作为附加层进行，所有新图片都会基于这一层，对吧？
来自先前复制的先前层。
之前的层会脱落，但它们会参考旧层，它们会从更新中收到的最后一层获取新图像吗？
一般来说，是的。
那么结果我们将有一个无花果层。 随着时间的推移，它们将需要被压缩？
是的，一切都是正确的。 有一些窗口。 我们保留每周快照。 这取决于你有什么资源。 如果您有存储大量数据的能力，您可以将快照存储很长时间。 他们不会自行消失。 不会有数据损坏。 如果快照已过时，就像我们认为的那样，即这取决于公司的政策，那么我们可以简单地删除它们并释放空间。
您好，感谢您的举报！ 关于乔的问题。 你说客户不想让每个人都能访问数据。 严格来说，如果一个人有了Explain Analyze的结果，那么他就可以窥视数据了。
就像那样。 例如，我们可以写：“SELECT FROM WHERE email = to that”。 也就是说，我们不会看到数据本身，但是我们可以看到一些间接的迹象。 必须了解这一点。 但另一方面，一切都在那里。 我们有日志审计，我们可以控制其他同事，他们也可以看到开发人员在做什么。 如果有人试图这样做，那么安全部门就会找到他们并解决这个问题。
下午好感谢您的报告！ 我有一个简短的问题。 如果公司不使用 Slack，现在是否有任何绑定，或者开发人员是否可以部署实例以将测试应用程序连接到数据库？
现在有 Slack 的链接，即没有其他信使，但我真的很想也支持其他信使。 你能做什么？ 您可以在没有 Joe 的情况下部署 DB Lab，借助 REST API 或借助我们的平台，创建克隆并连接 PSQL。 但如果您准备好让您的开发人员访问数据，就可以做到这一点，因为将不再有任何屏幕。
我不需要这一层，但我需要这样的机会。
那么是的，它可以完成。
来源： habr.com