我正在尝试从表中查询大约 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 后看到轻微的减速,但事实并非如此。
请阅读此处:
<code> 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. </code>
非迭代查询(ReadRow
等)给了我相同的结果。
在 for 循环之外使用 tx := spannerClient.Single()
也会给出类似的结果。
问题:
spannerClient.Single()
,但 goroutine 仍尝试使用相同的会话/事务?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
回到你的具体问题:
it := tx.Query(gCtx, stmt2)
行确实it := tx.Query(gCtx, stmt2)
行确实不执行查询,它只是准备执行查询。第一次调用 row, err := it.Next()
不row, err := it.Next()
时执行。您还可以在记录的执行时间中看到这一点。第一条语句似乎在 30 微秒内执行,这是不可能的。以上是为什么在 golang goroutine 中使用 ReadOnlyTransaction 进行 Spanner 查询会逐渐变慢的详细内容。更多信息请关注PHP中文网其他相关文章!