為什麼在 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 後看到輕微的減速，但事實並非如此。

請閱讀此處：

<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() 也會給出類似的結果。

問題：

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 查詢會逐漸變慢的詳細內容。更多資訊請關注PHP中文網其他相關文章！