Surprisingly high latencies for selects/inserts in spanner

Question

I am getting latencies around 50-100ms for simple queries in spanner (updates or selects by primary key). Connecting to spanner from the same project/region. Is it expected behavior? I have expected much lower latency for those.

Answer 1

No, the latency for a simple select using the primary key should be a lot lower than that.

I did a quick benchmark based on the information you provided above using the following simple program:

package main

import (
    "context"
    "fmt"
    "math/rand"
    "time"

    "cloud.google.com/go/spanner"
    "github.com/montanaflynn/stats"
    "google.golang.org/api/iterator"
)

func main() {
    fmt.Printf("Simple Spanner benchmarking...
")
    source := rand.NewSource(time.Now().UnixNano())
    rnd := rand.New(source)
    client, err := spanner.NewClient(context.Background(), "projects/my-project/instances/my-instance/databases/my-database")
    if err != nil {
        fmt.Printf("Client creation failed: %v", err)
        return
    }
    var times stats.Float64Data
    for i := 0; i < 25; i++ {
        id := rnd.Int63n(1000) + 100000
        statement := spanner.NewStatement("SELECT * FROM Singers WHERE SingerId=@id")
        statement.Params["id"] = id
        start := time.Now()
        iter := client.Single().Query(context.Background(), statement)
        for {
            row, err := iter.Next()
            if err == iterator.Done {
                break
            }
            if err != nil {
                fmt.Printf("Query failure: %v", err)
                break
            }
            var fullName string
            row.ColumnByName("FullName", &fullName)
            fmt.Printf("Singer name: %s
", fullName)
            elapsed := time.Since(start)
            fmt.Printf("Time: %v
", elapsed)
            times = append(times, float64(elapsed.Milliseconds()))
        }
        iter.Stop()
    }
    median, _ := stats.Median(times)
    avg, _ := stats.Mean(times)
    p90, _ := stats.Percentile(times, 90)
    fmt.Printf("Median: %v
", median)
    fmt.Printf("P90: %v
", p90)
    fmt.Printf("Avg: %v
", avg)
}

The application was executed on the smallest possible Google Cloud Compute Engine VM located in the same region as the Spanner instance. The results were:

Simple Spanner benchmarking...
Singer name: FirstName LastName 100960
Time: 374.627846ms
Singer name: FirstName LastName 100865
Time: 4.102019ms
Singer name: FirstName LastName 100488
Time: 3.479059ms

...

Singer name: FirstName LastName 100542
Time: 3.986866ms
Singer name: FirstName LastName 100822
Time: 3.978838ms
Singer name: FirstName LastName 100235
Time: 4.511711ms
Singer name: FirstName LastName 100020
Time: 3.476673ms
Singer name: FirstName LastName 100234
Time: 3.191529ms
Singer name: FirstName LastName 100219
Time: 4.451639ms

Median: 3
P90: 4
Avg: 18.44

So your execution times around 50-100ms sound like a lot. Normal execution time in this (simple) test case is around 3-4ms for a single-row select (except for the first request, as that also initializes the backing session pool).

• Could it be that your table has a primary key that uses a monotonically increasing value? That could create hotspots in the backing index of the primary key.
• Could it be that you are closing and creating a new client between each query? That would require the session pool to be re-initialized for each new query?
• Are you using a single-use read-only transaction for your queries? Or are you using some other type of transaction to read the data?

---

Could you please provide some additional details on how exactly you are executing the query (preferably with a code sample)?

• Are you using a client library? If so, which one? (Java, Node, Go, ...?)
• Are you only measuring the very first query that you are executing after starting your application? The very first query will be slower than later queries, as the client library will need to first create a session and then execute the query.
• You write that you are connecting from the same project/region. Does that mean that your client code is running on a Google Cloud VM or similar?