在分布式SQL数据库中,我们如何将在线服务潜伏期从1.11降低到123.6ms
#开源 #sql #database #distributedsystems

tl; dr:

这篇文章讲述了分布式数据库上的网站如何减少在线服务潜伏期从1.11 s降低到417.7 ms,然后减少到123.6 ms 。我们发现,在整个优化过程中,可以应用一些关于MySQL的教训。但是,当我们优化分布式数据库时,我们需要考虑更多。

OSS Insight网站实时显示GitHub事件的数据更改。它由TiDB Cloud驱动,弹性量表和实时分析的MySQL兼容分布式SQL数据库。

最近,为了节省成本,我们尝试使用较低规格的机器,而不会影响查询效率和用户体验。但是我们的网站和查询响应减慢了。

Image description

存储库分析页面正在加载,加载和加载

我们如何在分布式数据库上解决这些问题?我们可以使用我们在mySQL上学习的方法吗?

分析SQL执行计划

为了识别慢速SQL语句,我们使用TIDB Cloud的诊断页面按平均延迟来对SQL查询进行排序。

例如,在API服务器收到请求后,它执行以下SQL语句以获取vscode repository中的问题数:

SELECT
    COUNT(DISTINCT number)
FROM github_events
WHERE
    repo_id = 41881900     -- vscode
    AND type = 'IssuesEvent';

但是,如果开源存储库很大,则此查询可能需要几秒钟或更长时间才能执行。

使用EXPLAIN ANALYZE来解决查询性能问题

在MySQL中,当我们解决查询性能问题时,我们通常使用EXPLAIN ANALYZE <sql>语句查看SQL语句的执行计划。我们可以使用执行计划来找到问题。 tidb的作品。

我们执行了EXPLAIN语句:

EXPLAIN ANALYZE SELECT
    COUNT(DISTINCT number)
FROM github_events
WHERE
    repo_id = 41881900     -- vscode
    AND type = 'IssuesEvent';

结果表明查询需要1.11秒才能执行。

Image description

查询结果

您可以看到TIDB的koude0语句执行结果与MySQL完全不同。 TIDB的执行计划使我们对如何执行此SQL语句有了更清楚的了解。

执行计划显示:

  • 此SQL语句分为几个子任务。有些在root节点上,另一些则在koude5节点上。
  • 查询从partition:issue_event partition表中获取的数据。
  • 此查询通过索引index_github_events_on_repo_id(repo_id)进行了范围扫描。这让查询缩小数据扫描此过程仅采用 59 ms。这是多个并发任务的执行时间的总和。
  • 除了IndexRangeScan外,查询还使用了TableRowIDScan 此扫描进行 4.69 S ,多个并发子任务的执行时间总和。

从上面的执行时间中,我们确定查询性能瓶颈在TableRowIDScan步骤中。

我们重新播放了EXPLAIN ANALYZE语句,发现查询第二次更快。为什么?

为什么TableRowIDScan花了这么长时间?

要找到TableRowIDScan花了这么长时间的原因,我们需要对TIDB的基础存储的基本知识。

在tidb中,表的数据条目和索引存储在键值对中的tikv节点上。

  • 对于索引,键是索引值和row_id(对于非簇索引)或主键(对于群集索引)的组合。 row_id或主键指示数据存储的位置。
  • 对于数据输入,键是表ID和row_id或主键的组合。值部分是这一行数据的组合。

此图显示了如何在执行计划中执行IndexLookup

Image description

这是逻辑结构,而不是物理存储结构。

在上面的查询中,TIDB使用查询条件repo_id=41881900来滤除与次级索引index_github_events_on_repo_id中存储库相关的所有行号row_id。查询需要数字column数据,但是辅助索引不提供。因此,tidb必须执行IndexLookup才能根据获得的row_idTableRowIDScan步骤)在表中找到相应的行。

行可能散布在不同的数据块中,并存储在硬盘上。这会导致TIDB执行大量的I/O操作来读取来自不同数据块甚至不同的机器节点的数据。

为什么EXPLAIN ANALYZE第二次更快?

EXPLAIN ANALZYE的执行结果中,我们看到与TableRowIDScan步骤对应的“执行信息”列包含此信息:

block: {cache_hit_count: 2755559, read_count: 179510, read_byte: 4.07 GB}

我们认为这与tikv有关。 TIKV从磁盘中读取大量数据块。因为从 disk 中读取的数据块在第一次执行中被缓存在内存中,因此可以直接从内存而不是直接读取275万个数据块从硬盘中检索。这使得TableRowIDScan的步骤更快,并且查询总体上更快。

但是,我们认为用户查询是随机的。例如,用户可能会从vscode存储库查找数据,然后转到kubernetes存储库。 TIKV的内存无法缓存所有驱动器中的所有数据块。因此,这并不能解决我们的问题,但它提醒我们,当我们分析SQL执行效率时,我们需要排除缓存效应。

使用覆盖索引避免执行TableRowIDScan

我们可以避免在IndexLookup中执行TableRowIDScan吗?

在MySQL中,覆盖索引可防止索引过滤后的索引查找。我们想将其应用于OSS Insight。在我们的TIDB数据库中,我们尝试创建一个复合索引以实现索引覆盖。

当我们创建具有多个列的复合索引时,我们需要注意列顺序。我们的目标是允许尽可能多的查询使用复合索引,以帮助这些查询尽快缩小数据扫描的范围,并在查询中提供尽可能多的字段。当我们创建一个复合索引时,我们遵循此顺序:

  1. 具有很高差异化的列,可以用作WHERE语句的等效条件,例如repo_id
  2. 没有高分化但可以用作WHERE语句的等效条件,例如typeaction
  3. 可以用作WHERE语句的范围查询条件的列,例如created_at
  4. 冗余列未用作滤波器条件,但在查询中使用,例如numberpush_size

我们使用CREATE IDNEX语句在数据库中创建复合索引:

CREATE INDEX index_github_events_on_repo_id_type_number ON github_events(repo_id, type, number);

当我们创建索引并再次运行SQL语句时,查询速度明显更快。我们通过EXPLAIN ANALYZE查看了执行计划,发现执行计划变得更简单。 IndexLookupTableRowIDScan步骤消失了。 查询仅处理417.7 ms

Image description

解释查询的结果。此查询费用为417.7 ms

因此,我们知道我们的查询可以通过在新索引上执行IndexRangeScan来获取所需的所有数据。该复合索引包括number字段,因此TIDB无需执行IndexLookup即可从表中获取数据。这减少了许多I/O操作。

Image description

indexrangescan在非群集表中

推动计算以进一步减少查询延迟

对于需要获得270,000行数据的查询,417.7 ms的执行时间很短。但是我们可以改善时间吗?

我们认为这取决于将计算和存储层分开的TIDB架构。这与mysql不同。

在tidb中:

  • tidb-server节点计算数据。它对应于执行计划中的根。
  • tikv-server节点存储数据。它对应于执行计划中的cop[tikv]

通常,SQL语句分为多个步骤,以通过计算和存储节点的合作执行。

当我们在本文中执行SQL语句时,TIDB从tikv-server获得了github_events表的数据,并在tidb-server上执行了计数函数的汇总计算。

SELECT
    COUNT(DISTINCT number)
FROM github_events
WHERE
    repo_id = 41881900     -- vscode
    AND type = 'IssuesEvent';

执行计划表明,当TIDB执行IndexReader时,tidb-server需要通过网络读取tikv-server的270,000行数据。这很耗时。

Image description

tidb-server从tikv-server读取270,000行数据

我们如何避免如此大的网络传输?尽管查询需要获得大量数据,但最终计算结果仅为数字。我们可以在tikv-server上完成COUNT的聚合计算,并将结果仅返回到tidb-server

tidb通过tikv-server上的coprocessor实现了这一想法。此优化过程称为计算下降。

执行计划表明我们的SQL查询没有这样做。为什么?我们检查了TIDB文档,并了解到:

通常,带有DISTINCT选项的聚合功能在单线程执行模型中在TIDB层中执行。

这意味着我们的SQL语句无法使用计算下降。

SELECT
    COUNT(DISTINCT number)
FROM github_events
WHERE
    repo_id = 41881900     -- vscode
    AND type = 'IssuesEvent';

因此,我们删除了DISTINCT关键字。

对于github_events表,一个问题仅生成一个使用IssuesEvent type和opened操作的事件。我们可以通过添加action = 'opened'的状况来获得唯一问题的总数。这样,我们不需要使用DISTINCT关键字进行重复数据解复。

SELECT
    COUNT(number)
FROM github_events
WHERE
    repo_id = 41881900     -- vscode
    AND type = 'IssuesEvent'
    AND action = 'opened';

我们创建的复合索引缺少action列。这导致查询索引覆盖范围失败。因此,我们创建了一个新的复合索引:

CREATE INDEX index_github_events_on_repo_id_type_action_number ON github_events(repo_id, type, action, number);

创建索引后,我们通过EXPLAIN ANALYZE语句检查了修改后的SQL语句的执行计划。我们发现:

  • 因为我们添加了一个新的过滤器action='opened',所以扫描的行数已从270,000减少到140,000。
  • tikv-server执行了StreamAgg操作员,这是COUNT函数的汇总计算。这表明该计算已被推到TIKV协处理器以执行。
  • tidb-server仅需要通过网络从tikv-server获取两行数据。这大大减少了传输的数据量。
  • 查询仅花费123.6毫秒。
+-------------------------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------+------+

| id                      | estRows | actRows | task      | access object                                                                                                           | execution info                                                                                                                                                                                                                                                                                                                                                           | operator info                                                                             | memory    | disk |

+-------------------------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------+------+

| StreamAgg_28            | 1.00    | 1       | root      |                                                                                                                         | time:123.6ms, loops:2                                                                                                                                                                                                                                                                                                                                                    | funcs:count(Column#43)->Column#34                                                         | 388 Bytes | N/A  |

| └─IndexReader_29        | 1.00    | 2       | root      | partition:issues_event                                                                                                  | time:123.6ms, loops:2, cop_task: {num: 2, max: 123.5ms, min: 1.5ms, avg: 62.5ms, p95: 123.5ms, max_proc_keys: 131360, p95_proc_keys: 131360, tot_proc: 115ms, tot_wait: 1ms, rpc_num: 2, rpc_time: 125ms, copr_cache_hit_ratio: 0.50, distsql_concurrency: 15}                                                                                                           | index:StreamAgg_11                                                                        | 590 Bytes | N/A  |

|   └─StreamAgg_11        | 1.00    | 2       | cop[tikv] |                                                                                                                         | tikv_task:{proc max:116ms, min:8ms, avg: 62ms, p80:116ms, p95:116ms, iters:139, tasks:2}, scan_detail: {total_process_keys: 131360, total_process_keys_size: 23603556, total_keys: 131564, get_snapshot_time: 1ms, rocksdb: {delete_skipped_count: 320, key_skipped_count: 131883, block: {cache_hit_count: 307, read_count: 1, read_byte: 63.9 KB, read_time: 60.2µs}}} | funcs:count(gharchive_dev.github_events.number)->Column#43                                | N/A       | N/A  |

|     └─IndexRangeScan_15 | 7.00    | 141179  | cop[tikv] | table:github_events, index:index_ge_on_repo_id_type_action_created_at_number(repo_id, type, action, created_at, number) | tikv_task:{proc max:116ms, min:8ms, avg: 62ms, p80:116ms, p95:116ms, iters:139, tasks:2}                                                                                                                                                                                                                                                                                 | range:[41881900 "IssuesEvent" "opened",41881900 "IssuesEvent" "opened"], keep order:false | N/A       | N/A  |

+-------------------------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------+------+

将我们学到的知识应用于其他查询

通过我们的分析和优化,查询延迟显着降低:

1.11Sâ417.7ms 123.6 ms

我们将我们学到的知识应用于其他查询,并在github_events表中创建了以下复合索引:

index_ge_on_repo_id_type_action_pr_merged_created_at_add_del

index_ge_on_repo_id_type_action_created_at_number_pdsize_psize

index_ge_on_repo_id_type_action_created_at_actor_login

index_ge_on_creator_id_type_action_merged_created_at_add_del

index_ge_on_actor_id_type_action_created_at_repo_id_commits

这些复合索引涵盖了OSS Insight网站上的存储库分析和个人分析页面中的20多个分析查询。这提高了我们网站的整体加载速度。

我们在MySQL上学习的一些课程可以在整个优化过程中应用。,但是当我们优化A 分布式数据库中的查询性能时,我们需要考虑更多。我们还建议您在TIDB文档中阅读Performance Tuning。这将为您提供更专业,更全面的性能优化指南。

参考