为了帮助您更好地了解、优化和诊断事务问题，Spanner 允许您访问事务提交统计信息。目前，您可以检索一项事务的变更总数。
何时使用提交统计信息
在以下情况下，了解事务的变更计数可能很有用。
针对往返进行优化
为帮助提高应用的性能，您可以在每个事务中执行尽可能多的工作，以减少到数据库的往返次数。在这种情况下，您希望最大限度地提高每个事务的变更量，同时保持在系统限制内。
要确定每个事务可以提交多少行，同时保持在限制之内，首先在一个事务中提交一行。这为您提供了每行变更计数的基准。然后，用系统限制除以您的基准即可得出每个事务的行数。如需详细了解如何计算变更数，请参阅此备注。
请注意，为往返进行优化并非总是有益，尤其是这会导致更多锁争用。您可以利用锁统计信息排查数据库中的锁冲突。
监控您的事务，以免超出系统限制
随着应用使用量的增加，事务中的变更数量也可能会增加。为了避免达到系统限制并使您的事务最终失败，您可以主动监控一段时间内的变更计数提交统计信息。如果您发现此值对于同一事务增加，可能是如上一节中所述需要重新优化事务。
如何访问提交统计信息
默认情况下，系统不会返回提交统计信息。相反，您需要在每个 CommitRequest 上将 return_commit_stats 标志设置为 true。如果提交尝试超过某个事务允许的最大变更数，则提交失败并返回 INVALID_ARGUMENT 错误。
以下示例展示了如何使用 Spanner 客户端库返回提交统计信息。
检索提交统计信息
以下示例展示了如何使用 Spanner 客户端库获取提交统计信息。
C++
以下代码对 CommitOptions 调用 set_return_stats() 并返回变更计数 6，因为我们在每行中插入或更新 2 行 3 列。
void GetCommitStatistics(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto commit = client.Commit(
spanner::Mutations{
spanner::UpdateMutationBuilder(
"Albums", {"SingerId", "AlbumId", "MarketingBudget"})
.EmplaceRow(1, 1, 200000)
.EmplaceRow(2, 2, 400000)
.Build()},
google::cloud::Options{}.set<spanner::CommitReturnStatsOption>(true));
if (!commit) throw std::move(commit).status();
if (commit->commit_stats) {
std::cout << "Updated data with " << commit->commit_stats->mutation_count
<< " mutations.\n";
}
std::cout << "Update was successful [spanner_get_commit_stats]\n";
}C#
在 C# 中，不直接通过 API 返回提交统计信息。相反，它们由默认日志记录器在信息日志级别记录。
以下代码通过将 SpannerConnectionStringBuilder 上的 LogCommitStats 属性设置为 true，为所有交易启用提交统计信息日志记录。该代码还会实施一个示例日志记录器，用于保留对上次看到的提交响应的引用。然后，系统将从此响应中检索 MutationCount，并予以显示。
using Google.Cloud.Spanner.Data;
using Google.Cloud.Spanner.V1;
using Google.Cloud.Spanner.V1.Internal.Logging;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;
public class LogCommitStatsAsyncSample
{
public async Task<long> LogCommitStatsAsync(string projectId, string instanceId, string databaseId)
{
// Commit statistics are logged at level Info by the default logger.
// This sample uses a custom logger to access the commit statistics.
// See https://googleapis.github.io/google-cloud-dotnet/docs/Google.Cloud.Spanner.Data/logging.html
// for more information on how to use loggers.
var logger = new CommitStatsSampleLogger();
var options = new SessionPoolOptions();
var poolManager = SessionPoolManager.Create(options, logger);
var connectionStringBuilder = new SpannerConnectionStringBuilder
{
ConnectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}",
// Set LogCommitStats to true to enable logging commit statistics for all transactions on the connection.
// LogCommitStats can also be enabled/disabled for individual Spanner transactions.
LogCommitStats = true,
SessionPoolManager = poolManager,
};
using var connection = new SpannerConnection(connectionStringBuilder);
await connection.OpenAsync();
using var cmd = connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (110, 'Virginia', 'Watson')");
var rowCount = await cmd.ExecuteNonQueryAsync();
var mutationCount = logger._lastCommitResponse.CommitStats.MutationCount;
Console.WriteLine($"{rowCount} row(s) inserted...");
Console.WriteLine($"{mutationCount} mutation(s) in transaction...");
return mutationCount;
}
/// <summary>
/// Sample logger that keeps a reference to the last seen commit response.
/// Use the default logger if you only want to log the commit stats.
/// </summary>
public class CommitStatsSampleLogger : Logger
{
internal CommitResponse _lastCommitResponse;
/// <summary>
/// This method is called when a transaction that requested commit stats is committed.
/// </summary>
public override void LogCommitStats(CommitRequest request, CommitResponse response)
{
_lastCommitResponse = response;
base.LogCommitStats(request, response);
}
protected override void LogImpl(LogLevel level, string message, Exception exception) =>
WriteLine(exception == null ? $"{level}: {message}" : $"{level}: {message}, Exception: {exception}");
protected override void LogPerformanceEntries(IEnumerable<string> entries)
{
string separator = Environment.NewLine + " ";
WriteLine($"Performance:{separator}{string.Join(separator, entries)}");
}
private void WriteLine(string line) => Trace.TraceInformation(line);
}
}Go
以下代码设置 ReturnCommitStats 标志并在成功提交事务时输出变更计数。
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func commitStats(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return fmt.Errorf("commitStats.NewClient: %w", err)
}
defer client.Close()
resp, err := client.ReadWriteTransactionWithOptions(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `INSERT Singers (SingerId, FirstName, LastName)
VALUES (110, 'Virginia', 'Watson')`,
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
return nil
}, spanner.TransactionOptions{CommitOptions: spanner.CommitOptions{ReturnCommitStats: true}})
if err != nil {
return fmt.Errorf("commitStats.ReadWriteTransactionWithOptions: %w", err)
}
fmt.Fprintf(w, "%d mutations in transaction\n", resp.CommitStats.MutationCount)
return nil
}
Java
import com.google.cloud.spanner.CommitResponse;
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Mutation;
import com.google.cloud.spanner.Options;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import java.util.Arrays;
public class GetCommitStatsSample {
static void getCommitStats() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
try (Spanner spanner =
SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
final DatabaseClient databaseClient = spanner
.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
getCommitStats(databaseClient);
}
}
static void getCommitStats(DatabaseClient databaseClient) {
final CommitResponse commitResponse = databaseClient.writeWithOptions(Arrays.asList(
Mutation.newInsertOrUpdateBuilder("Albums")
.set("SingerId")
.to("1")
.set("AlbumId")
.to("1")
.set("MarketingBudget")
.to("200000")
.build(),
Mutation.newInsertOrUpdateBuilder("Albums")
.set("SingerId")
.to("2")
.set("AlbumId")
.to("2")
.set("MarketingBudget")
.to("400000")
.build()
), Options.commitStats());
System.out.println(
"Updated data with " + commitResponse.getCommitStats().getMutationCount() + " mutations.");
}
}Node.js
以下代码将设置 returnCommitStats 标志并返回变更计数 6，因为我们会在每行插入或更新 2 行 3 列。
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client.
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
// Instantiate Spanner table objects.
const albumsTable = database.table('Albums');
// Updates rows in the Venues table.
try {
const [response] = await albumsTable.upsert(
[
{SingerId: '1', AlbumId: '1', MarketingBudget: '200000'},
{SingerId: '2', AlbumId: '2', MarketingBudget: '400000'},
],
{returnCommitStats: true}
);
console.log(
`Updated data with ${response.commitStats.mutationCount} mutations.`
);
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}PHP
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Creates a database and tables for sample data.
* Example:
* ```
* create_database($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function get_commit_stats(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$commitStats = $database->runTransaction(function (Transaction $t) {
$t->updateBatch('Albums', [
[
'SingerId' => 1,
'AlbumId' => 1,
'MarketingBudget' => 200000,
],
[
'SingerId' => 2,
'AlbumId' => 2,
'MarketingBudget' => 400000,
]
]);
$t->commit(['returnCommitStats' => true]);
return $t->getCommitStats();
});
print('Updated data with ' . $commitStats['mutationCount'] . ' mutations.' . PHP_EOL);
}Python
Python 客户端库在级别 Info 使用 stdout 记录提交统计信息，而不是直接通过 API 返回。
以下代码通过设置 database.log_commit_stats = True 来为所有事务启用提交统计信息日志记录。该代码还会实施一个示例日志记录器，用于保留对上次看到的提交响应的引用。然后，系统将从此响应中检索 mutation_count，并予以显示。
def log_commit_stats(instance_id, database_id):
"""Inserts sample data using DML and displays the commit statistics."""
# By default, commit statistics are logged via stdout at level Info.
# This sample uses a custom logger to access the commit statistics.
class CommitStatsSampleLogger(logging.Logger):
def __init__(self):
self.last_commit_stats = None
super().__init__("commit_stats_sample")
def info(self, msg, *args, **kwargs):
if kwargs["extra"] and "commit_stats" in kwargs["extra"]:
self.last_commit_stats = kwargs["extra"]["commit_stats"]
super().info(msg)
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id, logger=CommitStatsSampleLogger())
database.log_commit_stats = True
def insert_singers(transaction):
row_ct = transaction.execute_update(
"INSERT Singers (SingerId, FirstName, LastName) "
" VALUES (110, 'Virginia', 'Watson')"
)
print("{} record(s) inserted.".format(row_ct))
database.run_in_transaction(insert_singers)
commit_stats = database.logger.last_commit_stats
print("{} mutation(s) in transaction.".format(commit_stats.mutation_count))
Ruby
以下代码将设置 return_commit_stats 标志并返回变更计数 6，因为我们会在每行插入或更新 2 行 3 列。
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
records = [
{ SingerId: 1, AlbumId: 1, MarketingBudget: 200_000 },
{ SingerId: 2, AlbumId: 2, MarketingBudget: 400_000 }
]
commit_options = { return_commit_stats: true }
resp = client.upsert "Albums", records, commit_options: commit_options
puts "Updated data with #{resp.stats.mutation_count} mutations."