Benchmark Spanner Change Watcher

Google Cloud Spanner Change Watcher is an open-source library for Google Cloud Spanner for watching and publishing changes from a Cloud Spanner database. The examples in this article require version 1.1.0 or higher of Spanner Change Watcher.

The library comes with a sample Benchmark application that can be used to try out different configurations for a Change Watcher to test the corresponding performance and load. The benchmark application uses a single example table with a secondary index that are both automatically created by the benchmark application for simplicity. The table and secondary index correspond with the recommended setup in this article, and it is recommended to read that article before continuing with this article to understand how the sample table is set up.

This article shows a number of examples on how to use this Benchmark application to test different configurations.

Running the Benchmark Application

The Benchmark Application is located in the spanner-change-watcher-samples project in the repository and is a ready-to-run Java console application. It has two required command line parameters:

• --instance: The Cloud Spanner instance to use
• --database: The Cloud Spanner database to use

All other parameters are optional and can be used to try out different configurations and write loads.

The database, table, and secondary index will all be created automatically if these do not already exist. The benchmark application will start two processes internally:

1. An Updater: The updater will write random changes to the data table to simulate write load on the table. The write load of the updater can be configured by setting the transactionsPerSecond and mutationsPerTransaction parameters. The default settings will use a low write throughput of 1 transaction per second and 5 mutations per transaction, giving a total of 5 mutations per second on average.
2. A Watcher: The watcher will poll the data table for changes that are written by the updater. The watcher will count the number of changes that it has seen, but otherwise it will not do anything with the changes. The default setup of the watcher will be equal to the recommended settings for watchers for large tables in this article.

Running Benchmark Application using Maven

The simplest way to run the benchmark application on your local development machine is to use the Maven exec plugin. Executing the benchmark application in your IDE is also possible, but not recommended, because of the limited console support in most IDEs.

Navigate to the spanner-change-watcher/samples/spanner-change-watcher-samples directory and execute the following command:

mvn exec:java -Dexec.args="-i my-instance -d watcher-benchmark-db"

This will start the benchmark application using the Cloud Spanner instance 'my-instance' and database 'watcher-benchmark-db' with otherwise default options. 'my-instance' must be an existing instance.

Benchmark Application Output

Running the benchmark application with default settings should give you an output that looks approximately like this:

Benchmark Application Output with Default Settings

The meaning of the above out is:

1. Test duration: The amount of time that the benchmark has been running.
2. # Transactions: The number of write transactions that the application has executed so far.
3. # Mutations: The total number of mutations that the application has executed so far.
4. # Received changes: The total number of changes that the watcher has received so far. This number will be equal to or a little less than the number of mutations that has been executed when you start the benchmark with a fresh database. Later runs can show a larger number of changes received than the number of mutations executed. This happens if you stop the benchmark application while there are mutations that have not yet been reported. These changes will then be reported during the next benchmark run.
5. Change notification latency: The number of seconds between the commit timestamp of a change and the moment the change is reported.
6. Avg poll time last minute: The average number of seconds that a poll-for-changes query lasted on the database. This value is read from the SPANNER_SYSQUERY_STATS_TOP_MINUTE table.
7. Spanner CPU time last minute: A calculated value that indicates the fraction of the total available time of a single-CPU the total of all poll queries required. This value is calculated as (avg_query_time * execution_count) / 60 seconds . The value is not necessarily equal to the actual CPU load on Cloud Spanner.

Testing Different Configurations

The benchmark application supports a large number of command line parameters that can be used to try different watcher configurations and write throughput. The examples below have been executed on a single-node regional Cloud Spanner instance. The results on your own setup could differ based on both node count and regional vs. multi-regional setup.

The most important configuration options are:

1. transactionsPerSecond (w): The number of random write transactions that the application should execute per second on average. The transactions will be executed using a thread pool that will schedule transactions at random times that on average will match the number in this configuration.
2. mutationsPerTransaction (m): The number of mutations that each transaction will execute on average. The actual number of mutations per transaction will be in the range [1, 2m], with an average of m.
3. numWatchers (n): The number of different watchers to use. The example table contains a sharding column that contains 37 different values. It is therefore possible to use up to 37 different watchers, each watching a separate set of shard values. The default configuration will use 1 watcher that watches all shards, and this is sufficient for most use cases. If the write throughput exceeds the maximum number of changes that a single watcher can handle, a good next step is to use multiple watchers. The number mutations that a single watcher can handle will depend on the number of nodes in your Cloud Spanner instance. The single node instance in this example can handle approximately 3,000 to 5,000 mutations per second.
4. pollInterval (p): The interval between each poll query. The default is 1 second. Setting this value to a higher interval can reduce the load on your Cloud Spanner instance caused by the watcher, as less poll queries will be executed.
5. limit (l): The maximum number of changes that a poll query will fetch in one poll. The default is 10,000. If a poll returns <limit> number of changes, a new poll query will be scheduled immediately after this poll to fetch the following <limit> changes. Setting limit to a lower value and poll interval to a higher value, can be a good strategy to get a more dynamic polling behavior for tables that receive burst writes. If for example the poll interval is set to 10 seconds and limit to 500, the table will only be polled every 10 seconds, unless the table receives more than 500 mutations in a 10 second interval.

Default (Recommended) Configuration

The default configuration of the SpannerTableTailer that is used by the benchmark applications is as follows:

Default Table Watcher Configuration

This configuration corresponds with the recommended setup that is described in this article.

The following table shows the result of the benchmark application using different write loads and the default configuration for the watcher. The tests were executed against a table that already contained 17 mio records. The need to use a secondary index when polling for changes depends heavily on the total number of records in the table.

|  Load   | -w | -m  | -n |  -p  |   -l  |  Avg   | CPU | Latency |
|---------|----|-----|----|------|-------|--------|-----|---------|
| V light |  1 |   5 |  1 | PT1S | 10000 | 0.0085 | 0%  | 1 sec   |
| Light   |  5 |  20 |  1 | PT1S | 10000 | 0.0191 | 1%  | 1 sec   |
| Medium  | 10 |  50 |  1 | PT1S | 10000 | 0.0575 | 3%  | 1 sec   |
| High    | 25 | 100 |  1 | PT1S | 10000 | 0.2912 | 12% | 2 sec   |
| V high  | 50 | 200 |  1 | PT1S | 10000 | 2.1908 | 69% | *       |
| V high  | 50 | 200 |  2 | PT1S | 10000 | 0.8298 | 42% | 5 sec   |
| V high  | 50 | 200 |  4 | PT1S | 10000 | 0.2791 | 42% | 2 sec   |
| V high  | 50 | 200 |  8 | PT1S | 10000 | 0.1355 | 48% | 1 sec   |

The first four write load scenarios can be handled by one watcher (n = 1). The 'Very high' write load scenario with 50 transactions per second writing 200 mutations each with a total of 10,000 mutations per second, cannot be handled by one watcher in a single-node Cloud Spanner setup. The single watcher will start lagging more and more behind the updates and the latency will continuously increase. Adding a second watcher is enough to get the scenario working, albeit with an average latency of approximately 5 seconds. Adding more watchers will reduce that latency further.

Disable Table Hint / Secondary Index

The previous example uses the default configuration for the Benchmark application. This means that the table watcher will use a table hint that directs Cloud Spanner to use the secondary index while querying. If we remove this table hint, Cloud Spanner will (at the time of writing of this article) by default not use the secondary index. This will have a negative impact on the performance of the poll query. The exact impact will depend on the number of rows in the table. If you are testing this with a newly created table, you will get a lot better results, than if you for example let the benchmark application run for a while with a high write load (e.g. -w 25 -m 100).

The command line parameter to disable the table hint is --disableTableHint.

| Load  | -w | -m | -n |  -p  |  -l   |   Avg   | CPU  |  Latency  |
|-------|----|----|----|------|-------|---------|------|-----------|
| Light |  5 | 20 |  1 | PT1S | 10000 | 85.0711 | 283% | 30-60 sec |
| Light |  5 | 20 |  2 | PT1S | 10000 | 80.8160 | 404% | 30-60 sec |
| Light |  5 | 20 |  8 | PT1S | 10000 | 80.7088 | 929% | > 60 sec  |
| Light |  5 | 20 | 37 | PT1S | 10000 | 81.6249 | 910% | > 60 sec  |

Disabling the table hint will have a dramatic impact on the performance of the table change watcher. The exact values will depend heavily on the number of existing rows in the table that is being watched, as the poll query will use a full table scan instead of using the secondary index.

Disable Shard Provider

We can also disable the shard provider that tells the watcher to only watch for changes that have a value between -37 and 37 in the shard column. This shard provider adds a WHERE ShardId IN (-37, -36, ..., 36, 37) clause to the poll query, which may seem unnecessary at first. It does however make it possible for Cloud Spanner to scan the secondary index a lot more efficiently, as we are effectively telling Cloud Spanner on beforehand that it only needs to look for these specific values. If we omit this, Cloud Spanner first needs to scan the index for all distinct shard values itself, before it can access each of the different parts of the index. Alternatively, Cloud Spanner will just do a full scan of the entire index.

The command line parameter to disable the table hint is --disableShardProvider. Note that the benchmark application will only allow you to disable the shard provider if the number of watchers is 1. Otherwise, you would have multiple watchers watching the same part of the table (being; the entire table). The table hint that instructs Cloud Spanner to use the secondary index is still used, and as it is a null-filtered index, the benchmark application will add a NotNullShardProvider to the watcher. This provider will just add a WHERE ShardId IS NOT NULL clause to the poll query.

|  Load  | -w | -m  | -n |  -p  |  -l   |   Avg   | CPU  | Latency |
|--------|----|-----|----|------|-------|---------|------|---------|
| Light  |  5 |  20 |  1 | PT1S | 10000 | 14.0975 | 140% | 11 sec  |
| Medium | 10 |  50 |  1 | PT1S | 10000 | 13.7384 | 160% | 10 sec  |
| High   | 25 | 100 |  1 | PT1S | 10000 | 14.8597 | 173% | *       |

The watcher will be able to keep up with the low and medium write loads, but the high write load will overwhelm it. The performance in the low and medium write loads are also a lot worse than when using a shard provider that specifies each possible shard value.

Using a NotNullShardProvider instead of a shard provider that specifies all possible values in the shard column can still be useful in some specific scenarios:

• If the shard column can contain any random value instead of only a specific set of values. Using a NotNullShardProvider is in those cases still better than using no shard provider at all.
• If you regularly remove older items from the secondary null-filtered index by setting the commit timestamp to null, the NotNullShardProvider will perform very well, as all non-relevant entries have already been removed from the index. See the relevant section in this article for more information on how to remove old entries from a null-filtered secondary index.

Increase Poll Interval

If your table does not receive a continuous stream of writes, but rather bursts of writes, it can make sense to set a higher poll interval. This will cause a regular poll query to be executed less often, which will reduce the total load on the database. Each poll will always fetch all changes that are available, regardless of the limit that has been configured. The limit will only determine how many changes are fetched in one poll query. If one query returns limit changes, a new poll query will be executed directly to fetch the next set of changes. The following command line arguments show the effect of this:

> mvn exec:java -Dexec.args=" \
> -i my-instance \
> -d my-database \
> -w 0.5 -m 500 -l 1000 -p PT10S"

The change watcher will execute a poll query every 10 seconds with a limit of max 1,000 changes. If the query returns 1,000 changes, a new poll query will be executed immediately to fetch the remaining changes. This will repeat until a poll query has received less than 1,000 changes. The watcher will then wait another 10 seconds before polling again.

Poll every 10 seconds — Higher latency, lower load

This will trade change notification latency for less slightly less load on the backend. The exact difference will depend on the burstyness of the writes in the table. Compare with the below screenshot from a run with the following settings:

> mvn exec:java -Dexec.args=" \
> -i my-instance \
> -d my-database \
> -w 0.5 -m 500 -l 10000 -p PT1S"

Poll every 1 second — Lower latency, (slightly) higher load

Conclusion

The Benchmark application in the samples directory of Spanner Change Watcher can be used to test different configurations for a Spanner Table Watcher in combination with different write loads. The default configuration that is used in the Benchmark application is the recommended setup for large tables that receive a large number of writes. This setup includes:

1. A (computed) shard column that contains a relatively small set of fixed values. The example table uses modulo 19 of a hash of some of the data in the table, which gives a fixed set of shard values in the range [-18, 18].
2. A (null-filtered) secondary index on the shard column and commit timestamp column.
3. A FixedShardProvider with an ARRAY<INT64> that contains all the values in the range [-18, 18].
4. A table hint that forces the use of the secondary index from point 2 above.