在高负载条件下，优化关系数据库的复杂性增加了一个数量级，因为购买功能更强大的硬件非常昂贵，并且无法长时间在夜间关闭应用程序以进行长时间的数据库更改过程和数据迁移。
最近，我们讨论了如何
为应用程序优化PHP代码 。 现在轮到文章了，我们如何在不丢失单个请求的情况下，完全改变了Badoo中最重要的数据库的内部结构。
病人
用户数据库或UDB是一项服务，几乎可以启动对Badoo的所有请求。 它解决了几个问题：首先，它是进行授权的主要用户数据的中央存储库（例如，电子邮件，user_id或facebook_id）。 除了存储此数据外，该服务还提供了唯一性控制（因此，具有相同电子邮件，facebook_id等的两个用户无法在系统中注册）。 同样的服务可以提供有关数千个分片中的哪个包含所有其他用户数据的信息。
截至2018年底，UDB存储了8亿多用户的数据，这些用户约占1 TB的磁盘空间。 所有这些都由我们每个数据中心中的成对的主从MySQL服务器提供。 总计，它们每秒处理超过140,000个请求。
UDB的崩溃意味着所有Badoo都无法访问，因为代码无法找到用户数据所在的碎片。 因此，对其可靠性和可用性提出了巨大的要求。
由于这种特殊性，更改存储结构非常昂贵，因此我们在2013年非常重视UDB设计。 但是，随着时间的流逝，需求以及负载曲线都会发生变化。 为了使系统适应新的要求和负载水平，进行了许多小的和简单的更改，但是不幸的是，这种更改远非最有效。 有一天，不是下一次黑客攻击或购买昂贵的硬件，而是更全面地进行优化是明智的。 此外，我们将考虑这条路径的主要阶段。
非侵入式优化
由于数据迁移过程的复杂性，对大型且加载的数据库的结构进行的任何更改都非常昂贵。 因此，首先，您应该用尽所有不会影响数据结构，但仅限于代码和SQL查询的优化选项。 也许这足以将工作量过多的问题推迟几年，这将使您此时对企业做一些更重要的事情。
您越了解系统，就越容易找到这种优化方法。 确保收集了所有可以帮助您的指标。 这不仅与诸如CPU使用率和RAM使用率之类的系统指标有关，还是与特定数据库的指标有关，也与与优化数据库绑定的应用程序的应用程序级指标有关。 不同类型的操作每秒有多少个请求？ 他们的响应时间是多少？ 输入和输出的大小是多少？ 根据这些指标，您可以判断优化是否成功。 您不太可能需要进行优化以稍微减少数据库服务器上的CPU使用率，但同时将应用程序的响应时间增加十倍。
在开始为UDB收集其他应用程序级别的度量标准之后，我们能够更好地了解哪些已执行操作会产生80％的负载，并且是最先研究的候选对象，而哪些则很少使用或根本没有使用。
对最频繁的操作（检索满足特定条件的用户）的详细分析表明，尽管事实上已从数据库请求了所有可用的用户数据，但实际上在95％的情况下，该应用程序仅使用user_id。 只需将这种情况分成单独的API方法（仅从表中提取一列），我们便可以受益于覆盖索引的使用，并从数据库服务器中删除大约5％的CPU负载。
对另一个常见操作的分析表明，尽管实际上是针对每个HTTP请求执行的，但实际上，它检索到的数据非常少。 我们将此请求转换为惰性模型。
在优化项目中，指标的主要目标是更好地了解您的数据库并找到最胖的部分。 花大量时间和精力优化占不到负载配置文件1％的查询是没有意义的。 如果没有允许您了解负载配置文件的指标，请收集它们。 通过代码方面的此类优化，我们设法从80％的数据库消耗中删除了大约15％的CPU使用率。
测试思路
如果要通过更改数据库的结构来优化已加载的数据库，则应从在测试台上检查想法开始，因为即使在理论上看起来非常有前途的优化在实践中也可能不会产生积极的影响（有时甚至可能产生负面影响）。 而且，只有在进行长时间的数据迁移后，您才可能不太想知道这一点。
您的机架配置离生产配置越近，获得的结果越可靠。 重要的一点是要确保支架的正确负载。 运行随机或相同的查询可能导致错误的结果。 最好的选择是使用生产中的真实请求。 对于UDB，我们在生产中每十分之一API读取请求（包括参数）都以文件中的JSON日志形式记录下来。 每天，我们从7亿个请求中收集了65 GB的日志。
我们没有测试记录，与读取请求的数量相比，它很小并且不会影响我们的负载。 但是，您的情况可能并非如此。 如果要向测试平台加载写入请求，则必须收集每个请求，因为跳过写入请求可能会导致测试平台上的一致性错误。
下一步是正确丢失架子上的原木。 我们使用了从
脚本云启动的400个PHP工人，它们从快速队列中读取收集的日志并顺序执行请求。 在这种情况下，队列中将填充另一个严格定义速度的脚本。 为了测试想法，我们使用了x10的速度，乘以我们仅每十分之一请求从生产中收集的事实，得出的RPS数量与生产中的数量相同。
有了这些系数，事实证明，在测试台上所有负载下降的生产日仅用了两个半小时。
因此，例如，我们在查询日志上以x5的速度（生产负荷的50％）运行了半天的第一个测试如下：
可以使用相同的工具进行故障测试：提高速度（并因此提高RPS），直到机架的底座开始退化。 这将使您清楚地了解数据库可以承受的负载量。
在测试了新的数据模式之后，对原始数据库结构进行控制测试也很重要。 如果其结果和当前生产性能有很大不同，则应首先了解原因。 也许测试服务器的配置不正确，您就不能信任负载测试数据。
同样值得确保新代码正常工作。 测试不执行此查询的性能几乎没有意义。 集成测试将为您提供良好的服务，该测试会检查旧API和新API在相同的API调用上是否返回相同的值。
在收到所有想法的结果后，仍然只能选择价格和质量之间达到最佳平衡的选项，并引入新的生产方案。
模式变更
首先，我注意到在不停止服务运行的情况下更改数据方案总是非常困难，昂贵且有风险。 因此，如果您有机会在更改结构的同时停止应用程序，请执行此操作。 不幸的是，对于UDB，我们负担不起。
影响更改电路复杂性的第二个因素是更改的计划规模。 如果对表的所有建议更改都不仅仅是更改（例如，添加一对新索引或列），则可以使用典型过程（例如
pt-online-schema-change和
gh-ost for MySQL或备用slave）将它们启动，然后更改其位置。
在我们的案例中，一个巨大的表的垂直分片（大约十二个较小的表）与其他列和索引以及数据的格式不同，在垂直分片中显示了出色的结果。 使用典型工具进行的这种转换不再可行。 那该怎么办呢？
我们应用了以下算法：
- 我们达到了同时具有旧数据的新旧方案同时存在的状态。 记录在两个版本中进行，同时保证了两个版本中的数据一致性。 我们将在下面详细考虑该项目。
- 逐渐将整个读数切换到新电路，以控制负载。
- 关闭旧方案中的录像并将其删除。
这种方法的主要优点是：
- 安全：有可能立即回滚到最后一个阶段（如果出现问题，只需将读数切换回旧的方案）；
- 数据迁移期间的完全负载控制；
- 无需对旧电路的大表进行大量改动。
但是，也有缺点：
- 在迁移过程中需要将两个版本的模式都保留在磁盘上（如果空间很小并且要迁移的表很大，则可能会出现问题）；
- 许多支持迁移过程的临时代码，这些代码将在完成后被切断；
- 可以通过并行读取两个方案来清洗缓存； 担心新旧版本会争夺RAM，这可能导致服务降级（实际上，这确实增加了额外的负载，但是，由于迁移是在非高峰期进行的，因此这不会给我们造成问题）。
该算法的主要困难是第一点。 我们将详细考虑。
更改同步
静态数据的迁移并不是特别困难。 但是，如果您不能在数据库迁移时仅停止整个记录呢？
有几种方法可以实现新方案的同步：通过滚动日志进行迁移和迁移幂等记录。
迁移数据快照，然后回放以下更改的日志
每个数据更新事务都通过触发器在应用程序级别记录在一个特殊的表中，或者将复制binlog用作日志。 拥有此类日志后，您可以记住该日志中的位置来打开事务并迁移数据快照。 然后剩下的工作就是开始将收集的日志应用到新方案中。 同样，例如，流行的MySQL
Percona XtraBackup备份工具也可以运行 。
在新方案将日志记录到当前记录之后，最关键的阶段开始：您仍然需要在旧方案中暂停记录一小段时间，并确保将整个可用日志应用于新方案，这意味着方案之间的数据是一致的，在应用程序级别，同时启用两个源中的记录。
这种方法的主要缺点是，您将需要以某种方式存储操作日志，该日志本身会在复杂的切换过程中产生负载，并且如果出于某种原因电路不稳定，则有可能打破记录。
等幂记录
这种方法的主要思想是，在更改完全同步之前，开始写入新方案，同时写入旧方案，然后完成剩余数据的迁移。 同样，通常在大表中填充新的列。
同步记录既可以在数据库触发器上也可以在源代码中实现。 我建议您在代码中精确地执行此操作，因为在任何情况下，您最终都必须编写将数据写入新方案的代码，并且在代码端执行迁移将为您提供更多控制权。
需要考虑的重要一点是，在迁移完成之前，新方案将处于不一致状态。 因此，在更新新表导致违反数据库常量（外键或唯一索引）的情况下是可能的，而从当前方案的角度来看，该事务是完全正确的，应该执行。
由于迁移过程，这种情况可能导致良好的事务回滚。 解决此问题的最简单方法是将IGNORE修饰符添加到所有将数据写入新方案的请求中，或拦截此类事务的回滚并在不写入新方案的情况下运行该版本。
在我们的例子中，通过幂等记录的同步算法如下：
- 在兼容模式（IGNORE）中，我们可以与旧方案并行进行新方案的录像。
- 我们运行的脚本逐渐绕过新方案并捕获不一致的数据。 之后，应该同步两个表中的数据，但是由于第1节中可能存在冲突，因此这是不准确的。
- 我们启动数据一致性检查器-打开事务并依次从新方案和旧方案中读取行，并比较它们的对应关系。
- 如果有冲突，我们结束并回到第3段。
- 在检查程序表明两个方案中的数据都已同步之后，则这些方案之间不应再有任何差异，除非我们当然错过了一些细微差别。 因此，我们等待一段时间（例如一个星期），然后进行对照检查。 如果他显示一切都很好，则任务已成功完成，您可以翻译读数。
结果
更改数据格式的结果是，我们能够将主表的大小从544 GB减少到226 GB，从而减少了磁盘上的负载并增加了适合RAM的有用数据量。
总体而言，从项目开始起，使用所有描述的方法，我们就能将高峰流量下的数据库服务器的CPU使用率从80％降低到35％。 随后的压力测试结果表明，以当前的负载增长率，我们可以在现有硬件上保留至少三年。
将一张大表分成几张表可以简化数据库将来进行更改的过程，还可以显着加快一些为BI收集数据的脚本。