为什么在 golang goroutine 中使用 ReadOnlyTransaction 进行 Spanner 查询会逐渐变慢

问题内容

我正在尝试从表中查询大约 10,000 行。在尝试了涉及 limit offset 的各种其他选项并且没有找到所需的成功之后，我尝试在每个 goroutine 中查询单行。思路是每行只需要 ~5ms 来查询和获取，但是一批 10k 会接管 20s。

下面显示的是代码的简化版本：

func queryEmp(IDs[]string, spannerClient *spanner.Client) (Employee,error){
query := "Select name from Employee Where id = @id"

    g, gCtx := errgroup.WithContext(ctx)
    for _, ID := range IDs {
        id := ID
        g.Go(func() error {
    
            tx := spannerClient.Single() 
            defer tx.Close()

            stmt2 := spanner.NewStatement(query)
            stmt2.Params = map[string]interface{}{
                "ID": id,
            }

            qstart := time.Now()
            it := tx.Query(gCtx, stmt2)
            defer it.Stop()
            logrus.Debugf("%s took %v \n", "query execution.", time.Since(qstart))

            for {
                row, err := it.Next()
                if err == iterator.Done {
                    break
                }
                if err != nil {
                    return err
                }

                var eID string
                if err := row.Column(0, &eID); err != nil {
                    return err
                }

            }

            return nil
        })
    }
    err = g.Wait()
}

结果跟踪如下：

{"message":"query execution. took 39.677µs \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 34.125µs \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 26.634µs \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 29.303µs \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
...
...
...
{"message":"query execution. took 188.749562ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 276.424692ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 188.62849ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 217.067524ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 276.949166ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
...
...
...
{"message":"query execution. took 454.64281ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 452.0848ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 525.748738ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 454.704656ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
{"message":"query execution. took 455.4276ms \n","severity":"debug","time":"2023-11-03T20:51:29-04:00"}
...
...
...
{"message":"query execution. took 6.767574136s \n","severity":"debug","time":"2023-11-03T20:52:00-04:00"}
{"message":"query execution. took 6.780578444s \n","severity":"debug","time":"2023-11-03T20:52:00-04:00"}
{"message":"query execution. took 6.785085491s \n","severity":"debug","time":"2023-11-03T20:52:00-04:00"}
{"message":"query execution. took 6.779527006s \n","severity":"debug","time":"2023-11-03T20:52:00-04:00"}

一开始很好，符合预期，但查询时间不断增加。

MaxSessions 和 MinSessions 对于 spannerClient 是 100 所以人们会想象它可能会在 100 后看到轻微的减速，但事实并非如此。

请阅读此处：

Sessions can execute only one transaction at a time. Standalone reads, writes, and queries use a transaction internally, and count toward the one transaction limit.

非迭代查询（ReadRow 等）给了我相同的结果。

在 for 循环之外使用 tx := spannerClient.Single() 也会给出类似的结果。

问题：

1. 这是否意味着尽管在 goroutine 中执行了 spannerClient.Single() ，但 goroutine 仍尝试使用相同的会话/事务？
2. 如何修改上述内容来解决该问题？

正确答案

TLDR：默认最大会话池大小为 400，这意味着并行运行的查询永远不会超过 400 个。您需要增加会话池大小才能实现这种并发性。

<小时/>

首先：我认为并行发送 10,000 个查询以让每个查询读取一行并不是解决您的问题的最有效的解决方案。如果没有其他条件，您可以使用比员工 ID 进行过滤的方法，而这些 ID 分散在各处，那么在表单中创建查询仍然会更有效

select * from employees where id in unnest(@ids)

有关完整示例，请参阅此评论：https:// /github.com/googleapis/google-cloud-go/issues/858#issuecomment-550982307

回到你的具体问题：

1. 您实际上并没有测量执行查询所需的时间。这有点令人困惑，但是 it := tx.Query(gCtx, stmt2) 行确实不执行查询，它只是准备执行查询。第一次调用 row, err := it.Next() 时执行。您还可以在记录的执行时间中看到这一点。第一条语句似乎在 30 微秒内执行，这是不可能的。
2. 这意味着您的客户端上的某些内容限制了您的进度，在这种情况下，我非常确定它是您的会话池的最大大小。默认最大会话池大小为 400。这意味着最多可以并行运行 400 个查询。您看到的等待时间不断增加是因为 goroutine 被放置在等待队列中等待会话变得可用。队列末尾的 goroutine 的等待时间会更长。

以上就是为什么在 golang goroutine 中使用 ReadOnlyTransaction 进行 Spanner 查询会逐渐变慢的详细内容，更多请关注编程网其它相关文章！