Cloud Spanner — Performance comparison between SQL and READ
Cloud Spanner provides two APIs to query data: SQL API and READ API.
SQL API is more expressive, since it supports ordering, aggregation and filtering.
Instead, the READ API is very limited so you just can make simple read operations such as filtering by key, range scans or search a list of keys (either by Primary Key or Secondary Index).
In terms of functionality, SQL provides portability and covers all query operations.
But what about performance? Is there any benefit in using READ API (in their limited use cases) that justify deciding to use this method over SQL?
Let’s check it out.
Tests description
I’ll run the below tests using SQL API (ExecuteStreamingSql) and then READ API (StreamingRead). So, I can compare performance (latency, cpu usage, etc) for both APIs processing the same kind of workload:
- Search for a single key.
- Search ten rows using range scan.
- Search ten rows using a list of keys (non-consecutive keys).
Search for a single key
Results using SQL
Query:
select * from usertable where id=@id CPU usage:
QPS:
Latency:
Results using READ
Read snippet:
with database.snapshot() as snapshot:
keyset = spanner.KeySet(keys=[[keys]])
results = snapshot.read(
table="usertable", columns=("id", "field0", "field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9"), keyset=keyset
)CPU usage:
QPS:
Latency:
In this first test there is no difference in performance between READ and SQL.
Comparing the results above (CPU usage, latency and QPS) both API had pretty similar results.
Let’s take a look to built-in tables statistic:
SQL:
spanner> select TEXT, EXECUTION_COUNT,AVG_LATENCY_SECONDS, AVG_CPU_SECONDS, AVG_ROWS,AVG_BYTES, AVG_REMOTE_SERVER_CALLS from spanner_sys.query_stats_top_10minute where text_fingerprint=-1202358415038276773 order by interval_end desc ;
+---------------------------------------+-----------------+---------------------+-----------------+----------+-------------+-------------------------+
| TEXT | EXECUTION_COUNT | AVG_LATENCY_SECONDS | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_REMOTE_SERVER_CALLS |
+---------------------------------------+-----------------+---------------------+-----------------+----------+-------------+-------------------------+
| select * from usertable where id=@id | 2759475 | 0.000537 | 0.000155 | 1.000000 | 1048.726664 | 0.000000 |
| select * from usertable where id=@id | 2760211 | 0.000538 | 0.000155 | 1.000000 | 1048.741727 | 0.000000 |
| select * from usertable where id=@id | 2758933 | 0.000540 | 0.000155 | 1.000000 | 1048.787426 | 0.000000 |
+---------------------------------------+-----------------+---------------------+-----------------+----------+-------------+-------------------------+READ:
spanner> select READ_COLUMNS from spanner_sys.read_stats_top_10minute where fprint=-7565956582432637727 limit 1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| READ_COLUMNS |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [usertable._exists, usertable.field0, usertable.field1, usertable.field2, usertable.field3, usertable.field4, usertable.field5, usertable.field6, usertable.field7, usertable.field8, usertable.field9] |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
spanner> select EXECUTION_COUNT,AVG_CPU_SECONDS, AVG_LEADER_REFRESH_DELAY_SECONDS, AVG_ROWS, AVG_BYTES from spanner_sys.read_stats_top_10minute where fprint=-7565956582432637727 order by interval_end desc ;
+-----------------+-----------------+----------------------------------+----------+-------------+
| EXECUTION_COUNT | AVG_CPU_SECONDS | AVG_LEADER_REFRESH_DELAY_SECONDS | AVG_ROWS | AVG_BYTES |
+-----------------+-----------------+----------------------------------+----------+-------------+
| 2773258 | 0.000149 | 0.000381 | 1.000000 | 1055.838502 |
| 2712693 | 0.000150 | 0.000384 | 1.000000 | 1055.862151 |
| 2732716 | 0.000151 | 0.000380 | 1.000000 | 1055.890736 |
+-----------------+-----------------+----------------------------------+----------+-------------+We can see a slightly lower difference in CPU seconds for READ ( a minimum difference of 2%: 0.155ms vs 0.150ms), which it’s accurate with the database statistics seen before.
Search ten rows using range scan
Results using SQL
SQL:
select * from usertable where id between @min and @maxCPU usage:
QPS:
Latency:
Results using READ
Read snippet:
with database.snapshot() as snapshot:
keylist = spanner.KeyRange(start_closed=min,end_closed=max)
keyset = spanner.KeySet(ranges=[keylist])
results = snapshot.read(
table="usertable", columns=("id", "field0", "field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9"), keyset=keyset
)CPU usage:
QPS:
Latency:
In this test there was a significant difference in CPU usage. The workload using READ consumed 30% less CPU compared to SQL.
Let’s see the tables statistic:
SQL:
spanner> select TEXT, EXECUTION_COUNT,AVG_LATENCY_SECONDS, AVG_CPU_SECONDS, AVG_ROWS,AVG_BYTES, AVG_REMOTE_SERVER_CALLS from spanner_sys.query_stats_top_10minute where text_fingerprint=-9022242565576020242 order by interval_end desc;
+--------------------------------------------------------+-----------------+---------------------+-----------------+-----------+--------------+-------------------------+
| TEXT | EXECUTION_COUNT | AVG_LATENCY_SECONDS | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_REMOTE_SERVER_CALLS |
+--------------------------------------------------------+-----------------+---------------------+-----------------+-----------+--------------+-------------------------+
| select * from usertable where id between @min and @max | 2584896 | 0.000691 | 0.000305 | 10.000000 | 10448.088457 | 0.000000 |
| select * from usertable where id between @min and @max | 2581368 | 0.000687 | 0.000298 | 10.000000 | 10448.102625 | 0.000000 |
| select * from usertable where id between @min and @max | 2607578 | 0.000691 | 0.000311 | 10.000000 | 10448.054219 | 0.000000 |
| select * from usertable where id between @min and @max | 2632707 | 0.000684 | 0.000310 | 10.000000 | 10448.161806 | 0.000000 |
+--------------------------------------------------------+-----------------+---------------------+-----------------+-----------+--------------+-------------------------+READ:
spanner> select EXECUTION_COUNT,AVG_CPU_SECONDS, AVG_LEADER_REFRESH_DELAY_SECONDS, AVG_ROWS, AVG_BYTES from spanner_sys.read_stats_top_10minute where fprint=-7565956582432637727 order by interval_end desc limit 4;
+-----------------+-----------------+----------------------------------+-----------+--------------+
| EXECUTION_COUNT | AVG_CPU_SECONDS | AVG_LEADER_REFRESH_DELAY_SECONDS | AVG_ROWS | AVG_BYTES |
+-----------------+-----------------+----------------------------------+-----------+--------------+
| 2634410 | 0.000169 | 0.000382 | 10.000000 | 10449.680733 |
| 2618642 | 0.000168 | 0.000381 | 10.000000 | 10449.833036 |
| 2629173 | 0.000167 | 0.000382 | 10.000000 | 10450.021690 |
| 2680598 | 0.000168 | 0.000377 | 10.000000 | 10450.068267 |
+-----------------+-----------------+----------------------------------+-----------+--------------+The avg_cpu_seconds for READ shows an improvement of 40% over SQL (0.16ms vs 0.311ms). Confirming the difference observed before.
Search ten rows using a list of keys
Results using SQL
SQL:
select * from usertable where id in UNNEST(@id)CPU usage:
QPS:
Latency:
Results using READ
Python snippet:
with database.snapshot() as snapshot:
keyset = spanner.KeySet(keys=keys)
results = snapshot.read(
table="usertable", columns=("id", "field0", "field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9"), keyset=keyset
)CPU usage:
QPS:
Latency:
In this last test, the difference in CPU and latency between both APIs was really significant.
The CPU usage using SQL was 100% higher compared to the workload using READ.
Also, there is a huge difference in CPU compared to previous test running range scan.
Let’s check these results in details to understand the reason for this increment in CPU:
SQL
spanner> select TEXT, EXECUTION_COUNT,AVG_LATENCY_SECONDS, AVG_CPU_SECONDS, AVG_ROWS,AVG_BYTES, AVG_REMOTE_SERVER_CALLS from spanner_sys.query_stats_top_10minute where text_fingerprint=9143951130769197872 order by interval_end desc ;
+--------------------------------------------------+-----------------+---------------------+-----------------+-----------+--------------+-------------------------+
| TEXT | EXECUTION_COUNT | AVG_LATENCY_SECONDS | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_REMOTE_SERVER_CALLS |
+--------------------------------------------------+-----------------+---------------------+-----------------+-----------+--------------+-------------------------+
| select * from usertable where id in UNNEST(@id) | 1087848 | 0.002391 | 0.001532 | 10.000000 | 10391.000000 | 4.000000 |
| select * from usertable where id in UNNEST(@id) | 1084418 | 0.002337 | 0.001515 | 10.000000 | 10391.000000 | 4.000000 |
| select * from usertable where id in UNNEST(@id) | 1085617 | 0.002317 | 0.001512 | 10.000000 | 10391.000000 | 4.000000 |
+--------------------------------------------------+-----------------+---------------------+-----------------+-----------+--------------+-------------------------+Here we can understand why CPU usage increased so much in comparison to workload doing range scan.
This time, queries accessed multiple splits per execution (avg_remote_server_calls 4) when in previous workloads queries just accessed one split per execution. This is because for this test I used distributed keys instead of sequential keys.
READ
spanner> select EXECUTION_COUNT,AVG_CPU_SECONDS, AVG_LEADER_REFRESH_DELAY_SECONDS, AVG_ROWS, AVG_BYTES from spanner_sys.read_stats_top_10minute where fprint=-7565956582432637727 order by interval_end desc limit 5;
+-----------------+-----------------+----------------------------------+----------+-------------+
| EXECUTION_COUNT | AVG_CPU_SECONDS | AVG_LEADER_REFRESH_DELAY_SECONDS | AVG_ROWS | AVG_BYTES |
+-----------------+-----------------+----------------------------------+----------+-------------+
| 4449029 | 0.000139 | 0.000313 | 2.499999 | 2607.249400 |
| 4434743 | 0.000141 | 0.000316 | 2.500001 | 2607.250602 |
| 4458957 | 0.000141 | 0.000311 | 2.500000 | 2607.249643 |
+-----------------+-----------------+----------------------------------+----------+-------------+This is really interesting, according to these statistics my reads have scanned an average of 2.5 rows (instead of 10 rows) per execution. Also, if we pay attention to execution_count it is pretty high ( 7.5k QPS).
Based on these results, I’ve re-checked my tests and I could confirm that my reads return exactly 10 rows.
Analyzing these metrics in details, and considering that keys are distributed across 4 splits I could find an explanation for this inconsistency: these avg/counts metrics are calculated for every “execution” (In this case: execution equal to avg_remote_calls) and not for the read shape itself:
AVG_ROWS (2.5) * 4 executions = 10 rows
AVG_BYTES(2600) * 4 executions = 10400 bytes
EXECUTION_COUNT(4458957) / 4 executions = 1112257 (1.8k QPS)We must do the same number to obtain the real value for avg_cpu_seconds:
AVG_CPU_SECONDS (0.000143 seconds) * 4 executions = 0.000572 seconds ( 0.57ms)Now, comparing this value with the avg_cpu_seconds for query statistics we confirm the huge improvement in CPU usage for READ(0.57ms) against SQL(1.5ms).
Results summary
In the table above, we can see clearly the difference in CPU usage and Latency between both APIs. Where there is a significant improvement in CPU usage for range scan and multi-splits queries using READs.
Conclusions
- For single key search there is no difference in performance.
- For range scan/list of keys there is an important improvement in CPU usage using READ API compared to SQL API.
- This improvement became more significant where these reads are across multiple splits.
- Based on this reduction in CPU usage, READ provides the capability to increase our read throughput per node against SQL.
- As a trade off, we have to keep in mind that read statistics are not accurate when reads access multiple splits.
