该报告提出了一些方法，使 每天有数百万个 SQL 查询时监控其性能，并且有数百台受监控的 PostgreSQL 服务器。
什么样的技术解决方案可以让我们高效地处理如此大量的信息，这如何让普通开发人员的生活变得更轻松？
谁有兴趣？ 具体问题分析和各种优化技术 SQL 查询和解决 PostgreSQL 中的典型 DBA 问题 - 您还可以 关于这个话题。
我的名字是基里尔·博罗维科夫，我代表 。 具体来说，我在我们公司专门从事数据库工作。
今天我将告诉你我们如何优化查询，当你不需要“挑选”单个查询的性能，而是整体解决问题时。 当有数百万个请求时，您需要找到一些 解决方法 这个大问题。
一般来说，我们一百万个客户的张量是 ：企业社交网络、视频通信解决方案、内部和外部文档流解决方案、会计和仓库会计系统……也就是说，这样一个用于综合业务管理的“大型组合”，其中有100多个不同的内部项目。
为了保证它们都能正常工作和开发，我们在全国设有10个开发中心，还有更多 1000 名开发者.
我们从 2008 年就开始使用 PostgreSQL，积累了大量我们处理的数据——客户端数据、统计、分析、来自外部信息系统的数据—— 超过400TB。 仅生产环境就有大约 250 台服务器，我们监控的数据库服务器总共大约有 1000 台。
SQL 是一种声明性语言。 你描述的不是某件事“如何”运作，而是你想要实现“什么”。 DBMS 更清楚如何进行 JOIN - 如何连接表、施加什么条件、什么将通过索引、什么不会……
一些 DBMS 接受提示：“不，在这样那样的队列中连接这两个表”，但 PostgreSQL 无法做到这一点。 这是领先开发人员的自觉立场：“我们宁愿完成查询优化器，也不愿允许开发人员使用某种提示。”
但是，尽管 PostgreSQL 不允许“外部”控制自己，但它完美地允许 看看他内心发生了什么当您运行查询时，以及哪里出现问题。
一般来说，开发人员（对于 DBA）通常会遇到哪些经典问题？ “在这里我们满足了要求，并且 我们一切都很慢，一切都悬而未决，有事正在发生……某种麻烦！”
原因几乎总是相同的：
- 低效的查询算法
开发人员：“现在我通过 JOIN 在 SQL 中给他 10 个表...” - 并期望他的条件将奇迹般地有效“解开”，并且他将快速获得一切。 但奇迹不会发生，任何具有这种可变性的系统（一个 FROM 中有 10 个表）总是会出现某种错误。 [] - 过时的统计数据
这一点与 PostgreSQL 非常相关，当您将大型数据集“倒入”到服务器上并发出请求时，它就会“sexcanits”您的平板电脑。 因为昨天里面有10条记录，今天有10万条记录，但是PostgreSQL还没有意识到这一点，我们需要告诉它这一点。 [] - “插入”资源
您在一台没有足够磁盘、内存或处理器性能的薄弱服务器上安装了一个大型且负载很重的数据库。 仅此而已...某个地方存在一个性能上限，您无法再跨越该上限。 - 闭塞
这是一个难点，但它们与各种修改查询（INSERT、UPDATE、DELETE）最相关——这是一个单独的大主题。
制定计划
...对于其他一切，我们 需要一个计划！ 我们需要查看服务器内部发生了什么。
PostgreSQL 的查询执行计划是用文本表示的查询执行算法的树。 规划者分析的结果恰恰是该算法被认为是最有效的。
每个树节点都是一个操作：从表或索引检索数据、构建位图、连接两个表、连接、相交或排除选择。 执行查询涉及遍历该树的节点。
要获取查询计划，最简单的方法是执行语句 EXPLAIN。 要获取所有真实属性，即在基础上实际执行查询 - EXPLAIN (ANALYZE, BUFFERS) SELECT ....
不好的部分：当你运行它时，它发生在“此时此地”，所以它只适合本地调试。 如果您使用一台处于强数据变化流下的高负载服务器，您会看到：“哦！ 这里我们的执行速度很慢夏 要求。” 半小时、一小时前 - 当您运行并从日志中获取此请求并将其带回服务器时，您的整个数据集和统计信息发生了变化。 您运行它来调试 - 它运行得很快！ 你无法理解为什么，为什么 是 慢慢地。
为了了解请求在服务器上执行的那一刻到底发生了什么，聪明人写道 。 它存在于几乎所有最常见的 PostgreSQL 发行版中，并且可以简单地在配置文件中激活。
如果它意识到某些请求的运行时间超过了您告诉它的限制，它就会执行 该请求的计划的“快照”并将它们一起写入日志中.
现在一切似乎都很好，我们去日志那里看到...... [文本脚布]。 但我们对此无话可说，只能说这是一个出色的计划，因为执行时间为 11 毫秒。
一切似乎都很好——但究竟发生了什么却不得而知。 除了一般时间之外，我们实际上看不到任何东西。 因为看这样一只纯文本的“羔羊”一般是不直观的。
但即使不明显，即使不方便，还有更根本的问题：
- 该节点表示 整个子树的资源总和 在他之下。 也就是说，如果该特定索引扫描下存在某些嵌套条件，则您不能仅找出该特定索引扫描花费了多少时间。 我们必须动态地查看内部是否存在“子项”和条件变量、CTE，并减去“我们心中”的所有这些。
- 第二点：节点上指示的时间为 单节点执行时间。 例如，如果该节点是由于多次循环表记录而执行的，则计划中的循环数（该节点的周期）会增加。 但原子执行时间本身在计划方面保持不变。 也就是说，为了了解该节点总共执行了多长时间，您需要将一件事乘以另一件事 - 再次“在您的头脑中”。
在这种情况下，要了解“谁是最薄弱的环节？” 几乎不可能。 因此，就连开发者自己也在《手册》中写到： “理解计划是一门必须学习、体验的艺术……”.
但我们有 1000 名开发人员，你无法将这种体验传达给每个人。 我、你、他都知道，但那边有人已经不知道了。 也许他会学习，也可能不会，但他现在需要工作——他从哪里获得这种经验？
计划可视化
因此，我们意识到，为了解决这些问题，我们需要 计划的良好可视化.
我们首先“浏览市场”——让我们在互联网上看看到底有什么。
但事实证明，几乎没有相对“活跃”的解决方案或多或少地在开发中——从字面上看，只有一个： 作者：休伯特·卢巴泽夫斯基。 当您在“feed”字段中输入计划的文本表示形式时，它会向您显示一个包含已解析数据的表格：
- 节点自身的处理时间
- 整个子树的总时间
- 检索到的统计预期记录数
- 节点体本身
该服务还能够共享链接存档。 你把你的计划放在那里并说：“嘿，Vasya，这是一个链接，那里出了点问题。”
但也存在一些小问题。
首先，大量的“复制粘贴”。 你拿一块木头，把它插在那里，一次又一次。
其次， 不分析读取的数据量 — 输出相同的缓冲区 EXPLAIN (ANALYZE, BUFFERS)，我们在这里看不到它。 他根本不知道如何拆解它们、理解它们并与它们一起工作。 当您读取大量数据并意识到可能错误分配磁盘和内存缓存时，此信息非常重要。
第三个缺点是这个项目的发展非常薄弱。 提交量非常小，如果每六个月一次就好了，而且代码是用 Perl 编写的。
但这都是“歌词”，我们可以以某种方式接受这一点，但有一件事极大地让我们远离了这项服务。 这些是公共表表达式（CTE）和各种动态节点（如 InitPlan/SubPlan）分析中的错误。
如果你相信这张图，那么每个单独节点的总执行时间大于整个请求的总执行时间。 这很简单 - 该 CTE 的生成时间未从 CTE 扫描节点中减去。 因此，我们不再知道 CTE 扫描本身需要多长时间的正确答案。
然后我们意识到是时候写我们自己的了——万岁！ 每个开发人员都会说：“现在我们要编写自己的，这将非常简单！”
我们采用了 Web 服务的典型堆栈：基于 Node.js + Express 的核心，使用 Bootstrap 和 D3.js 绘制漂亮的图表。 我们的期望是完全合理的 - 我们在两周内收到了第一个原型：
- 自定义计划解析器
也就是说，现在我们可以从 PostgreSQL 生成的计划中解析任何计划。 - 正确分析动态节点 - CTE 扫描、InitPlan、子计划
- 缓冲区分布分析 - 数据页从内存中读取，从本地缓存中读取，从磁盘中读取
- 清楚了
这样就不会在日志中“挖掘”所有这些，而是立即在图片中看到“最薄弱的环节”。
我们得到了类似的东西，其中包括语法突出显示。 但通常我们的开发人员不再使用完整的计划表示，而是使用较短的计划。 毕竟，我们已经解析了所有的数字，并把它们左右扔了，中间我们只留下了第一行，它是什么样的节点：CTE扫描，CTE生成或根据某种符号进行Seq扫描。
这就是我们所说的缩写表示 计划模板.
还有什么方便呢？ 可以很方便地查看总时间的多少份额分配给哪个节点 - 只需将其“粘在一边”即可 饼形图.
我们指向该节点并查看 - 事实证明，Seq Scan 花费了不到总时间的四分之一，剩下的 3/4 是由 CTE Scan 花费的。 恐怖！ 如果您在查询中积极使用 CTE 扫描，这是关于 CTE 扫描“射速”的一个小注释。 它们的速度不是很快——它们甚至不如常规的表扫描。
但通常这样的图表更有趣、更复杂，例如，当我们立即指向一个片段并看到超过一半的时间某些序列扫描“吃掉”时。 而且，里面有某种Filter，很多记录都根据它被丢弃了……你可以直接把这张图扔给开发者说：“Vasya，这里的一切对你来说都很糟糕！ 弄清楚，看看——有什么地方不对劲！”
当然，其中也涉及到一些“耙子”。
我们遇到的第一件事是舍入问题。 计划中每个单独节点的时间以 1 μs 的精度指示。 当节点周期数超过（例如）1000 时，执行 PostgreSQL“在精度范围内”除法后，计算回来时我们得到的总时间“在 0.95 毫秒到 1.05 毫秒之间”。 当计数达到微秒时，没关系，但是当它已经是[毫秒]秒时，在将资源“解绑”到“谁消耗了多少”计划的节点时，您必须考虑此信息。
第二点更复杂，是动态节点之间资源（那些缓冲区）的分配。 这花费了我们原型制作的前 2 周时间以及另外 4 周的时间。
遇到这种问题很容易——我们做了一个 CTE，并假设在其中读到了一些东西。 事实上，PostgreSQL 很“聪明”，不会直接在那里读取任何内容。 然后我们从中取出第一个记录，并将来自同一 CTE 的第一百零一个记录放入其中。
我们查看计划并了解 - 很奇怪，我们在 Seq Scan 中“消耗”了 3 个缓冲区（数据页），在 CTE 扫描中“消耗”了 1 个缓冲区，在第二个 CTE 扫描中又消耗了 2 个缓冲区。 也就是说，如果我们简单地将所有内容相加，我们将得到 6，但从平板电脑上我们只读到 3！ CTE Scan 不会从任何地方读取任何内容，而是直接使用进程内存。 也就是说，这里明显有问题！
事实上，这就是Seq Scan请求的全部3页数据，第1页要求第1次CTE Scan，然后第2页，还有2页给他读，也就是说，总共读取了 3 页数据，而不是 6 页。
这张图让我们认识到计划的执行不再是一棵树，而只是某种无环图。 我们得到了这样的图表，以便我们了解“什么最初来自哪里”。 也就是说，这里我们从pg_class创建了一个CTE，并请求了两次，而当我们第二次请求时，几乎所有的时间都花在了分支上。 显然，读取第 2 个条目比仅从平板电脑读取第 101 个条目要昂贵得多。
我们一时松了口气。 他们说：“现在，尼奥，你懂功夫了！ 现在我们的体验就在您的屏幕上。 现在你可以使用它了。”
日志整合
我们的 1000 名开发人员松了一口气。 但我们知道，我们只有数百台“战斗”服务器，开发人员的所有这些“复制粘贴”一点也不方便。 我们意识到我们必须自己收集。
一般来说，有一个标准模块可以收集统计数据，但是，它也需要在配置中激活 - 这个 。 但他不适合我们。
首先，它在同一数据库中使用不同的方案分配给相同的查询 不同的QueryId。 也就是说，如果你首先这样做 SET search_path = '01'; SELECT * FROM user LIMIT 1;然后 SET search_path = '02'; 并且相同的请求，那么该模块的统计数据将有不同的记录，并且在不考虑方案的情况下，我将无法专门在该请求配置文件的上下文中收集一般统计数据。
阻止我们使用它的第二点是 缺乏计划。 也就是说，没有计划，只有请求本身。 我们看到了什么在放缓，但我们不明白为什么。 在这里，我们回到快速变化的数据集的问题。
还有最后一刻—— 缺乏“事实”。 也就是说，您无法解决查询执行的特定实例 - 没有，只有聚合统计信息。 尽管可以使用此方法，但非常困难。
因此，我们决定反对复制粘贴并开始写作 集电极.
收集器通过 SSH 连接，使用证书与数据库建立安全连接，并且 tail -F 在日志文件中“坚持”它。 那么在本次会议中 我们得到了整个日志文件的完整“镜像”，由服务器生成。 服务器本身的负载很小，因为我们不解析那里的任何内容，我们只是镜像流量。
由于我们已经开始在 Node.js 中编写接口，因此我们继续在其中编写收集器。 而且这项技术已经证明了自己的合理性，因为使用 JavaScript 来处理弱格式的文本数据（即日志）非常方便。 Node.js 基础设施本身作为后端平台，使您可以轻松方便地使用网络连接，甚至任何数据流。
因此，我们“拉伸”两个连接：第一个“监听”日志本身并将其带给我们自己，第二个定期询问基地。 “但是日志显示 oid 123 的标志被阻止了”，但这对开发人员来说没有任何意义，最好询问数据库“OID = 123 到底是什么？” 因此，我们定期向基地询问我们对自己尚不了解的情况。
“只有一件事你没有考虑到，有一种像大象一样的蜜蜂！……”当我们想要监控 10 台服务器时，我们开始开发这个系统。 我们理解中最关键的，出现了一些难以处理的问题。 但在第一季度，我们收到了一百个监控 - 因为系统有效，每个人都想要它，每个人都感到舒服。
这一切都需要加起来，数据流量大且活跃。 事实上，我们监控的、我们能处理的，就是我们使用的。 我们还使用 PostgreSQL 作为数据存储。 没有什么比操作员更快地将数据“倒入”其中了 COPY 还没有。
但简单地“倾倒”数据并不是我们真正的技术。 因为如果一百台服务器上每秒大约有 50k 个请求，那么每天将生成 100-150GB 的日志。 因此，我们必须仔细地“切割”底座。
首先，我们做了 按天划分，因为总的来说，没有人对日期之间的相关性感兴趣。 如果今晚您推出了该应用程序的新版本 - 并且已经有了一些新的统计数据，那么您昨天所拥有的有什么不同呢？
其次，我们学到了（被迫） 使用起来非常非常快 COPY。 也就是说，不仅仅是 COPY因为他比 INSERT，甚至更快。
第三点——我不得不 分别放弃触发器和外键。 也就是说，我们根本没有参照完整性。 因为如果你有一个有一对 FK 的表，并且你在数据库结构中说“这里是一个由 FK 引用的日志记录，例如一组记录”，那么当你插入它时，PostgreSQL一无所有，只剩下如何接受并诚实地去做 SELECT 1 FROM master_fk1_table WHERE ... 与您尝试插入的标识符 - 只是为了检查该记录是否存在，并且您不会在插入时“破坏”该外键。
我们不再需要向目标表及其索引添加一条记录，而是可以从它引用的所有表中读取数据，从而获得额外的好处。 但我们根本不需要这个——我们的任务是以最少的负载尽可能多、尽可能快地记录。 所以 FK - 下来！
下一点是聚合和散列。 最初，我们在数据库中实现它们 - 毕竟，当记录到达时，立即在某种平板电脑中执行它很方便 触发器中的“加一”。 嗯，这很方便，但同样的坏事是——您插入一条记录，但被迫从另一张表中读取和写入其他内容。 而且，你不仅读、写，而且每次都这样做。
现在假设您有一个表，您可以在其中简单地计算通过特定主机传递的请求数： +1, +1, +1, ..., +1。 原则上，你不需要这个——一切皆有可能 收集器内存中的总和 并一次性发送到数据库 +10.
是的，在出现某些问题的情况下，你的逻辑完整性可能会“崩溃”，但这几乎是不现实的情况 - 因为你有一个普通的服务器，它的控制器中有一个电池，你有一个事务日志，一个关于事务日志的日志。文件系统...一般来说，不值得。 运行触发器/FK 所带来的生产力损失不值得您付出的代价。
这与散列相同。 某个请求飞到你面前，你从数据库中计算出某个标识符，写入数据库，然后告诉大家。 一切都很好，直到在录制时，第二个人来找你想要录制同样的东西 - 你被阻止了，这已经很糟糕了。 因此，如果可以将一些ID的生成转移到客户端（相对于数据库），那么最好这样做。
对于我们来说，使用文本中的 MD5 非常完美 - 请求、计划、模板……我们在收集器端计算它，并将现成的 ID“倒入”数据库中。 MD5的长度和每日分区让我们不用担心可能发生的冲突。
但为了快速记录这一切，我们需要修改记录程序本身。
你通常如何写入数据？ 我们有某种数据集，我们将它分成几个表，然后将其复制 - 首先复制到第一个表，然后复制到第二个表，复制到第三个表......这很不方便，因为我们似乎分三步写入一个数据流依次。 不愉快。 可以做得更快吗？ 能！
为此，只需将这些流彼此并行分解就足够了。 事实证明，我们有错误、请求、模板、阻塞……在单独的线程中飞行 - 并且我们并行编写所有这些。 够这个了 为每个单独的目标表保持 COPY 通道持续打开.
也就是说，在收集器处 总有一股溪流，我可以在其中写入我需要的数据。 但是为了让数据库看到这些数据，并且有人不会被困在等待这些数据被写入， COPY 必须以一定的时间间隔中断。 对于我们来说，最有效的时间约为 100 毫秒 - 我们关闭它并立即再次打开同一张表。 如果我们在某些高峰期间没有足够的单一流量，那么我们会汇集到一定的限制。
此外，我们发现对于这样的负载配置文件，当批量收集记录时，任何聚合都是有害的。 经典的邪恶是 INSERT ... VALUES 以及另外 1000 条记录。 因为此时介质上存在写入峰值，而尝试向磁盘写入内容的其他人都将等待。
要消除此类异常，只需不要聚合任何内容， 根本不缓冲。 如果确实发生了磁盘缓冲（幸运的是，Node.js 中的 Stream API 允许您找到答案） - 推迟此连接。 当您收到它再次空闲的事件时，从累积队列中写入它。 当它很忙时，从池中取出下一个空闲的并写入它。
在引入这种数据记录方法之前，我们有大约 4K 的写入操作，这样我们就减少了 4 倍的负载。 现在，由于新的受监控数据库，它们又增长了 6 倍 - 高达 100MB/s。 现在我们存储过去 3 个月的日志，容量约为 10-15TB，希望在短短三个月内任何开发人员都能够解决任何问题。
我们了解问题所在
但仅仅收集所有这些数据是好的、有用的、相关的，但还不够——它需要被理解。 因为每天有数百万个不同的计划。
但数以百万计的人难以管理，我们必须先做“小”。 首先，您需要决定如何组织这个“较小”的事情。
我们确定了三个关键点：
- 谁 发送了此请求
也就是说，它是从什么应用程序“到达”的：网络界面、后端、支付系统或其他东西。 - 哪里 它发生了
在什么具体服务器上？ 因为如果你在一个应用程序下有多个服务器，突然其中一个“变得愚蠢”（因为“磁盘损坏”，“内存泄漏”，其他一些问题），那么你需要专门解决该服务器。 - 如 问题以这样或那样的方式表现出来
为了了解“谁”向我们发送了请求，我们使用标准工具 - 设置会话变量： SET application_name = '{bl-host}:{bl-method}'; — 我们发送发出请求的业务逻辑主机的名称，以及发起请求的方法或应用程序的名称。
在我们传递请求的“所有者”之后，它必须输出到日志 - 为此我们配置变量 log_line_prefix = ' %m [%p:%v] [%d] %r %a'。 对于那些有兴趣的人来说，也许 这是什么意思呢。 事实证明，我们在日志中看到：
- 时间
- 流程和交易标识符
- 数据库名称
- 发送此请求的人的IP
- 和方法名称
然后我们意识到，查看不同服务器之间一个请求的相关性并不是很有趣。 一个应用程序到处都出现同样问题的情况并不常见。 但即使是相同的，也请查看这些服务器中的任何一个。
所以这是剪辑 “一台服务器 - 一天” 事实证明，这对于我们进行任何分析来说都足够了。
第一个分析部分是相同的 “样本” - 计划的简化形式，清除所有数字指标。 第二个切点是应用或者方法，第三个切点是给我们带来问题的具体计划节点。
当我们从特定实例转向模板时，我们立即获得了两个优势：
- 分析对象数量成倍减少
我们必须不再通过数千个查询或计划来分析问题，而是通过数十个模板来分析问题。 - 时间线
也就是说，通过总结某个部分中的“事实”，您可以显示它们在白天的外观。 在这里你可以理解，如果你有某种模式发生，例如，每小时一次，但它应该每天发生一次，你应该思考哪里出了问题 - 谁造成了它以及为什么，也许它应该在这里不应该。 这是另一种非数字的、纯粹视觉的分析方法。
其余的方法基于我们从计划中提取的指标：这种模式发生了多少次，总时间和平均时间，从磁盘读取了多少数据，以及从内存中读取了多少数据......
因为，例如，您来到主机的分析页面，看看 - 有些东西开始在磁盘上读取太多数据。 服务器上的磁盘无法处理它 - 谁从中读取？
您可以按任何列进行排序，并决定您现在要处理的内容 - 处理器或磁盘上的负载，或请求总数...我们对其进行了排序，查看了“顶部”的内容，修复了它并推出了该应用程序的新版本。
您可以立即看到来自请求的具有相同模板的不同应用程序，例如 SELECT * FROM users WHERE login = 'Vasya'。 前端、后端、处理...并且您想知道如果用户不与用户交互，为什么处理会读取用户。
相反的方法是立即从应用程序中看到它做了什么。 例如，前端是这个、这个、这个、这个每小时一次（时间轴有帮助）。 问题立即出现：每小时做一次某事似乎不是前端的工作......
一段时间后，我们意识到我们缺乏聚合 按计划节点统计。 我们仅将那些对表本身的数据执行某些操作的节点与计划隔离（通过索引或不通过索引读取/写入它们）。 其实相对上一张图只增加了一个方面—— 这个节点给我们带来了多少条记录？，以及有多少被丢弃（通过过滤器删除的行）。
你的盘上没有合适的索引，你向它发出请求，它飞过索引，陷入 Seq Scan...你已经过滤掉了除一条之外的所有记录。 为什么每天需要100M过滤记录，把索引汇总起来不是更好吗？
在逐个节点分析了所有计划后，我们意识到计划中的一些典型结构很可能看起来很可疑。 最好告诉开发人员：“朋友，这里你首先按索引读取，然后排序，然后切断” - 通常，有一条记录。
每个编写查询的人可能都遇到过这种模式：“给我 Vasya 的最后一个订单，它的日期。”如果您没有按日期排列的索引，或者您使用的索引中没有日期，那么您将踩一模一样的“耙子”。
但我们知道这是一个“耙子”——所以为什么不立即告诉开发人员他应该做什么。 因此，当现在打开一个计划时，我们的开发人员立即看到一张带有提示的漂亮图片，他们立即告诉他：“你到处都有问题，但它们是这样或那样解决的。”
结果，一开始和现在解决问题所需的经验量大幅下降。 这就是我们拥有的工具。
来源： habr.com