To help you better understand, optimize, and diagnose transaction issues, Spanner gives you access to transaction commit statistics. Currently, you can retrieve the total number of mutations for a transaction.
When to use commit statistics
Knowing the mutation count for a transaction can be useful in the following scenarios.
Optimize for round trips
To help improve the performance of your application you can reduce the number of round trips to the database by doing as much work as possible in each transaction. In this scenario you want to maximize the number of mutations per transaction, while at the same time staying within system limits.
To determine how many rows you can commit per transaction while staying under the limit, first commit one row in a transaction. This gives you a baseline of the mutation count per row. Then divide the system limit by your baseline to get a rows-per-transaction number. For more information on how mutations are counted, refer to this note.
Note that optimizing for round trips is not always beneficial, particularly if it results in more lock contentions. You can troubleshoot lock conflicts in your database using lock statistics.
Monitor your transactions to avoid hitting system limits
As application usage increases, it's possible that the number of mutations in your transaction also grows. To avoid hitting the system limit and having your transaction eventually fail, you can proactively monitor the mutation count commit statistic over time. If you observe this value increasing for the same transaction, it might be time to re-optimize your transaction as described in the preceding section.
How to access commit statistics
Commit statistics are not returned by default. Instead, you need to set the
return_commit_stats
flag to true on each CommitRequest. If
your commit attempt exceeds the maximum allowable number of mutations for a
transaction, the commit fails and an INVALID_ARGUMENT error is
returned.
Here's an example of how to return commit statistics using the Spanner client libraries.
Retrieve commit statistics
The following sample shows how to get commit statistics using the Spanner client libraries.
C++
The following code calls set_return_stats()
on CommitOptions
and
returns a mutation count of 6, because we are inserting or updating 2 rows and
3 columns in each row.
C#
In C#, commit statistics are not returned directly through the API. Instead, they are logged at the Information log level by the default logger.
The following code enables commit statistics logging for all transactions by
setting the LogCommitStats
property on SpannerConnectionStringBuilder
to
true. The code also implements a sample logger that keeps a reference to the
last seen commit response. The MutationCount
is then retrieved from this
response and displayed.
Go
The following code sets the ReturnCommitStats
flag and prints out the mutation
count when the transaction is successfully committed.
Java
Node.js
The following code sets the returnCommitStats
flag and returns a mutation
count of 6, because we are inserting or updating 2 rows and 3 columns in each
row.
PHP
Python
Instead of returning commit statistics directly through the API, the Python
client library logs them using stdout
at level Info
.
The following code enables commit statistics logging for all transactions by
setting database.log_commit_stats = True
. The code also implements a
sample logger that keeps a reference to the last seen commit response. The
mutation_count
is then retrieved from this response and displayed.
Ruby
The following code sets the return_commit_stats
flag and returns a mutation
count of 6, because we are inserting or updating 2 rows and 3 columns in each
row.