在线广告商正在根据性能数据做出越来越多的决策。无论是选择要投资的受众或创意，还是启用广告系列预算的算法优化，决策都越来越依赖于现成的数据。我们的开发团队构建了强大的工具来帮助我们的客户分析性能数据并做出更好的决策。我们的解决方案包括高度可定制的报告，包括由我们自己极其灵活的查询语言提供支持的向下钻取表和图表。支持查询语言的数据服务处理数TB的数据。除了作为我们面向用户的分析工具的后端，它还支持我们所有的自动优化功能和一些内部BI系统。在这篇博文中，我将向您展示我们如何通过对后端系统使用的数据库进行分片来解决扩展问题。庞大的数据库等于扩展难题我们的分析数据处理服务（称为Distillery）使用PostgreSQL数据库。该服务将JSON格式的查询安全地转换为最终在数据库级别运行的SQL查询。大多数数据处理发生在数据库中，因此Distillery后端主要将我们自己的查询语言翻译成SQL查询。原始API查询很复杂，这使得一些生成的SQL查询变得复杂，并使它们成为数据库级别的要求。因此，当我们在开发报告系统的过程中遇到缩放问题时，我们并不感到惊讶。过去，我们垂直扩展了主副本数据库架构，但很明显我们已经达到了这种方法的极限。我们的数据库在三年的运行中积累了近5TB的数据，变得难以管理。大尺寸使更新繁重的应用程序写入速度变慢并且维护任务难以执行。最后，最大的问题是我们的数据中心无法容纳更大的服务器。解决方案：使用Citus对PostgreSQL数据库进行分片当垂直扩展失败时，我们不得不开始水平扩展我们的报告数据库。这意味着我们需要跨多个数据库服务器拆分数据和处理。我们还必须缩小包含每个单独数据库实例中统计信息的巨大数据库表。这种将数据库数据分割成更小单元的方法称为数据库分片。我们的团队决定使用PostgreSQLCitus插件来处理分片。这不是唯一的选择——我们考虑过使用自定义应用程序级分片，但决定使用Citus插件，因为：我们有很多复杂的查询需要同时使用几个不同的分片。Citus插件自动处理这些复杂的查询并在分片之间分配处理。它还广泛支持我们运行复杂报告查询所需的PostgreSQL功能。此扩展使分片管理相对容易，因此我们不必花费太多精力在单独的数据库实例中管理分片表。Citus是基于协调器（coordinator）和工作者（worker）的PostgreSQL数据库实例。Worker持有数据库表分片，Coordinator计划SQL查询，以便它们可以在Worker中跨多个分片表运行。这允许将大表分布在多个服务器上，形成更小、更易于管理的数据库表。写入较小的表效率更高，因为数据库索引的维护成本更低。此外，写入负载在数据库实例之间并行化和共享。Citus解决了我们最大的两个痛点：写入效率低下和垂直扩展即将结束。Citus的数据库分片带来了额外的好处，因为新架构加快了我们的报告查询。我们的一些查询命中了多个工作实例和分片，Citus扩展可以对其进行优化以跨不同的数据库实例并行运行它们。由于较小的表索引和更多可用于单独工作程序中的查询处理的资源，仅针对单个工作程序进行分片的查询也更快。将大型数据库和复杂的报告查询迁移到这种类型的分片数据库架构并非易事。它涉及仔细的准备和计划，我们将在接下来进行检查。迁移到新数据库过去，我们通过旧的PHP单体运行报告查询。早在数据库扩展问题出现之前，我们就开始使用RubyonRails构建更新的报告后端。在决定只在新后端处理SQL查询迁移后，我们开始逐步淘汰旧后端。这使我们能够专门为Citus优化新的报告查询。它使从应用程序级别的迁移更容易，因为我们只需要迁移此服务以使用Citus分片PostgreSQL。分片数据库对数据库模式有一定的要求。模式必须有一个值作为分片的条件。分片逻辑使用这个值来区分数据在哪个分片上。在Citus-PostgreSQL中，分片是使用表主键来控制的。此复合主键由一个或多个列组成，其中第一个定义的列用作分片值：ALTERTABLEad_statsADDPRIMARYKEY(account_id,ad_id,date);选择create_distributed_table('ad_stats','account_id');--为Citus集群定义分片，这里使用帐户ID列作为分片键，这意味着我们正在根据我们的客户帐户分发数据（单个客户也可以有多个帐户）。这意味着单个帐户的数据驻留在单个表分片中。我们必须确保所有主键都是这种格式，并且该表包含帐户ID信息。我们还必须更改一些外键和唯一约束，因为它们还必须包含分片列。幸运的是，所有这些更改都安全地应用于正在运行的生产数据库，没有任何性能或数据完整性问题，尽管我们不得不进行一些更广泛的数据库索引重建。第二步是使我们的报告后端生成的SQL查询与分片数据库兼容。首先，查询必须在SQLWHERE子句中包含分片值。这意味着，例如，过滤器必须采用以下形式：SELECT*FROMcampaignsWHEREaccount_id='xxx'ANDname='yyy'如果我们没有account_id条件，CitusDistributedQueryPlanner将没有来自哪个分片的信息找到相关行。从所有可能的分片读取不如从单个分片读取有效。此外，Citus对您可以在分片表之间执行的JOIN类型有某些限制。通常JOIN要求分片列表出现在JOIN条件中。例如，这将不起作用：SELECT*FROMcampaignsLEFTJOINadsONcampaigns.id=ads.campaign_idWHEREcampaigns.account_id='xxx'这将导致错误：错误：如果未启用连接，则无法运行外部连接查询分区列&这意味着SQL外部连接需要Citus无法从查询中确定的表分片之间的一对一匹配。因此，查询需要在JOIN条件中包含分片列，Citus可以从中检测到ads表join的范围在一个分片内：SELECT*FROMcampaignsLEFTJOINadsONcampaigns.account_id=ads.account_id--使用分片列ANDcampaigns.id=ads.campaign_idWHEREcampaigns.account_id='xxx'我们进行了各种其他SQL查询优化，以使Citus查询规划器能够有效地运行我们复杂的统计报告查询。例如，我们使用公用表表达式(CTE)组织查询，这允许Citus查询规划器为涉及同时读取多个分片的繁重查询选择最佳计划。这些针对多个账户的查询也在Citus工作集群中高度并行，提高了数据处理效率。此外，我们还为Citus扩展做出了贡献，该扩展增加了对PostgreSQLJSON(B)聚合的支持，我们的报告查询将其用于某些数据预聚合步骤。你可以在Github中查看PR。PR：https://github.com/citusdata/citus/pull/2015新数据库系统正在运行我们的数据库系统已完全从单一主副本配置迁移到协调器+4个工作服务器，每个服务器都进行复制以实现高可用性。这意味着我们包含5TB数据的旧数据库被拆分成一个集群，其中每个数据库服务器拥有大约1TB的数据。Citus允许我们相当容易地添加更多的工作服务器，以随着公司的不断发展进一步拆分公司。我们还可以将具有大量统计数据的要求最苛刻的客户隔离到他们自己的数据库服务器。迁移前的数据库架构。迁移的数据库模式。上图描述了迁移前后的数据库架构。与之前拥有2台大型数据库服务器的状态相比，我们现在总共拥有10台数据库服务器。这些较小的数据库实例更易于管理，因为大部分数据都存在于单独的数据库工作服务器上。协调器持有的数据量较小，例如一些元数据和对分片不敏感的数据。第二张图还显示了我们用来确保在一个数据库实例失败时快速恢复的数据库副本。这种从primarymaster到replica的故障转移由pgpool组件处理。副本还分担主服务器的一些读取负载。最后，我们对数据要求最高的数据透视表报表查询从新数据库系统中获得了2-10倍的性能提升。此功能生成的数据库查询非常复杂，因为我们允许用户自由定义数据的分组、过滤和聚合方式。它还允许查询跨分片自由运行，因为用户可以定义帐户的任意组合。Citus分片数据库的好处真正体现在这些特定的查询中。数据库迁移是必要的，因为我们的遗留数据库基础设施几乎被它生成的复杂查询所淹没。此图显示了在数据库迁移项目期间某些类型查询的性能提高90%的持续时间。
