Avoiding hotspots in pgbench on PostgreSQL or YugabyteDB
Tags: postgres, yugabytedb, sql, database
Published: 2022-03-10 09:57:21

If you use `pgbench` with the default values, and multiple threads, you do not test the scalability of your database because all threads are updating a single row. Depending on the isolation level, either they wait, or they fail, on concurrent access. With the YugabyteDB version of `pgbench`, the threads do not fail because a retry logic is implemented, but, with the pgbench application design, this uses more resources to provide a lower throughput. 
This post proposes minimal changes to the database schema, without changing the application code, to get is scalable. I'm running this with YugabyteDB and `yb_bench` to use the retry logic, but the same can be done with PostgreSQL and `pgbench`.
## Start YugabyteDB
I'll test on a local database:
```bash
docker run -d --name yb -p7000:7000 -p5433:5433 \
 yugabytedb/yugabyte:latest bin/yugabyted start \
 --daemon=false

docker exec -it yb postgres/bin/ysqlsh -h localhost
```
You are at the `psql` prompt, `ysqlsh` is just a rename of it in YugabyteDB, to make it easy to run both in one environment (and with the YugabyteDB default port 5433). In the same way, `ysql_bench` is a rename of `pgbench` with a few improvements, like the retry logic (see a [previous post](https://blog.dbi-services.com/ysql_bench/)). 

## ysql_bench -i

Here is the initialization:

```bash
\! postgres/bin/ysql_bench -i
```
With all defaults, this creates the tables and loads them with a scale of 1 (one branch).
```
yugabyte=# \! postgres/bin/ysql_bench -i
dropping old tables...
NOTICE:  table "ysql_bench_accounts" does not exist, skipping
NOTICE:  table "ysql_bench_branches" does not exist, skipping
NOTICE:  table "ysql_bench_history" does not exist, skipping
NOTICE:  table "ysql_bench_tellers" does not exist, skipping
creating tables (with primary keys)...
generating data...
100000 of 100000 tuples (100%) done (elapsed 5.58 s, remaining 0.00 s)
done.
```
## "simple update" benchmark

I'm running the simple benchmark (`builtin: simple update`) for 10 seconds (`-T 10`) with 10 threads (`-c 10`) showing the per-statement statistics (`-r`) and no vacuum (`-n`) as I'm running on YugabyteDB.
```bash
\! postgres/bin/ysql_bench -nrT10 -c10 -N
```
Here is the result - about 1700 transactions per second
```
yugabyte=# \! postgres/bin/ysql_bench -nrT10 -c10 -N
transaction type: <builtin: simple update>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
batch size: 1024
duration: 10 s
number of transactions actually processed: 17613
number of errors: 1 (0.006%)
maximum number of tries: 1
latency average = 5.684 ms (including errors)
tps = 1759.193190 (including connections establishing)
tps = 1762.223472 (excluding connections establishing)
statement latencies in milliseconds and errors:
         0.006                     0  \set aid random(1, 100000 * :scale)
         0.001                     0  \set bid random(1, 1 * :scale)
         0.001                     0  \set tid random(1, 10 * :scale)
         0.001                     0  \set delta random(-5000, 5000)
         0.214                     0  BEGIN;
         2.328                     0  UPDATE ysql_bench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.845                     1  SELECT abalance FROM ysql_bench_accounts WHERE aid = :aid;
         1.306                     0  INSERT INTO ysql_bench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.881                     0  END;

```
With `-N` the updates are only per-account and each thread touches a different account as they are picked at random. Each statement execution is in millisecond so adding two more should stay in several hundreds of transactions per second.

## "TPC-B (sort of)" benchmark

Without `-N` there are additional updates to maintain the per-teller and per-branch balance:
```bash
\! postgres/bin/ysql_bench -nrT10 -c10
```
The result is very different here, with less that 100 transactions per second:
```
yugabyte=# \! postgres/bin/ysql_bench -nrT10 -c10
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
batch size: 1024
duration: 10 s
number of transactions actually processed: 932
number of errors: 9729 (91.258%)
maximum number of tries: 1
latency average = 9.397 ms (including errors)
tps = 93.027536 (including connections establishing)
tps = 93.208873 (excluding connections establishing)
statement latencies in milliseconds and errors:
         0.013                     0  \set aid random(1, 100000 * :scale)
         0.001                     0  \set bid random(1, 1 * :scale)
         0.001                     0  \set tid random(1, 10 * :scale)
         0.001                     0  \set delta random(-5000, 5000)
         0.211                     0  BEGIN;
         2.602                     0  UPDATE ysql_bench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.976                     0  SELECT abalance FROM ysql_bench_accounts WHERE aid = :aid;
         1.914                  1805  UPDATE ysql_bench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.915                  6839  UPDATE ysql_bench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.357                     0  INSERT INTO ysql_bench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.299                  1085  END;
```
The important message is in `number of errors`. `pgbench` would have stopped the thread on first error, which is not an option with higher isolation level than read committed. `ysql_bench` adds a retry logic, but with no retry by default, and then reporting the errors (` Operation failed. Try again: ... Conflicts with higher priority transaction: ...` or ` Operation expired: Transaction ... expired or aborted by a conflict: 40001` or `Operation failed. Try again: Value write after transaction start: { physical: ... } >= { physical: ... }: kConflict` depending where the conflict occurs).

## Optimistic locking retry logic

Here is the same attempting at most 10 retries (`--max-tries=10`):
```
yugabyte=# \! postgres/bin/ysql_bench -nrT10 -c10 --max-tries=10
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
batch size: 1024
duration: 10 s
number of transactions actually processed: 913
number of errors: 691 (43.080%)
number of retried: 1282 (79.925%)
number of retries: 8839
maximum number of tries: 10
latency average = 62.579 ms (including errors)
tps = 90.957832 (including connections establishing)
tps = 91.109947 (excluding connections establishing)
statement latencies in milliseconds, errors and retries:
         0.012                     0                     0  \set aid random(1, 100000 * :scale)
         0.001                     0                     0  \set bid random(1, 1 * :scale)
         0.001                     0                     0  \set tid random(1, 10 * :scale)
         0.001                     0                     0  \set delta random(-5000, 5000)
         0.229                     0                     0  BEGIN;
         2.623                     0                     0  UPDATE ysql_bench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         1.008                     0                     0  SELECT abalance FROM ysql_bench_accounts WHERE aid = :aid;
         1.953                   138                  1632  UPDATE ysql_bench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.993                   491                  6174  UPDATE ysql_bench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.394                     0                     0  INSERT INTO ysql_bench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.314                    62                  1033  END;
```
The throughput is not better. Retry is OK only when transaction conflicts are rare, which is the reason to prefer optimistic locking. But here, all transactions are updating the same row in `ysql_bench_branches`, and with only 10 rows in `ysql_bench_tellers` the probability of conflict is high. Especially when transactions are doing many roundtrips to the server, and are not using prepared statements: the longer the elapsed time of the transaction is, the higher the probability of conflicts.

## Scalability is also an application design concern

In short, the database can scale-up with more CPU and memory, and, with YugabyteDB, can even scale-out. But the application must be designed to scale and this is not the case for the default `pgbench`. Using prepared statements and doing only one roundtrip per transaction is well known. In this post I'll focus on table design.

## More rows on hotspot tables

The idea is simple: to avoid contention on one single row, let's have multiple rows. 

I create tables with an additional `h` column that is included into the primary key:
```sql
create table h_ysql_bench_tellers(
 h int, tid int, bid int, tbalance int, filler text
 ,primary key(h,tid)
);
create table h_ysql_bench_branches (
 h int, bid int, bbalance int, filler text
 ,primary key(h,bid)
);
```
I fill this table with the rows from the original tables, but 100 rows for each:
```sql
insert into h_ysql_bench_tellers
select generate_series(0,99), * from ysql_bench_tellers;
insert into h_ysql_bench_branches 
select generate_series(0,99), * from ysql_bench_branches;
drop table ysql_bench_branches;
```
I simplified things here in the idea that we start with an initialized database where all balances are at zero. If not, you should change so that the total doesn't change, putting the balance in only one bucket, and zero for the other 99 ones.

Then, a view will replace the tables that are updated by the application. It will update one of the 100 rows, depending on the `pg_backend_pid()` here but you can use whatever distributes the load evenly:
```sql
create or replace view ysql_bench_branches as 
 select * from h_ysql_bench_branches
 where h=pg_backend_pid()%100;
drop table ysql_bench_tellers;
create or replace view ysql_bench_tellers as 
 select * from h_ysql_bench_tellers
 where h=pg_backend_pid()%100;
```

## Running with no application change

Now running the same as above:
```bash
\! postgres/bin/ysql_bench -nrT10 -c10 --max-tries=10
```

The thoughput is now 10x higher:
```
yugabyte=# \! postgres/bin/ysql_bench -nrT10 -c10 --max-tries=10
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
batch size: 1024
duration: 10 s
number of transactions actually processed: 9629
number of retried: 1 (0.010%)
number of retries: 1
maximum number of tries: 10
latency average = 10.399 ms
tps = 961.624034 (including connections establishing)
tps = 963.739905 (excluding connections establishing)
statement latencies in milliseconds and retries:
         0.009                     0  \set aid random(1, 100000 * :scale)
         0.001                     0  \set bid random(1, 1 * :scale)
         0.001                     0  \set tid random(1, 10 * :scale)
         0.001                     0  \set delta random(-5000, 5000)
         0.221                     0  BEGIN;
         2.563                     0  UPDATE ysql_bench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.950                     0  SELECT abalance FROM ysql_bench_accounts WHERE aid = :aid;
         2.030                     0  UPDATE ysql_bench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         1.969                     0  UPDATE ysql_bench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         1.330                     0  INSERT INTO ysql_bench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.088                     1  END;
```
The number of retries is minimal. It can increase with more connections, but the number of rows in `ysql_bench_branches` and `ysql_bench_tellers` can also be increased. Of course, better run the benchmark longer, I'm in the same ballpark with 500 seconds:
```
yugabyte=# \! postgres/bin/ysql_bench -nrT500 -c10
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
batch size: 1024
duration: 500 s
number of transactions actually processed: 383753
number of errors: 14 (0.004%)
maximum number of tries: 1
latency average = 13.030 ms (including errors)
tps = 767.452309 (including connections establishing)
tps = 767.484079 (excluding connections establishing)
```

## Execution plan

It is important to verify that the execution plan scales:
```sql
begin transaction;
explain (costs off, analyze) UPDATE ysql_bench_branches SET bbalance = bbalance + 0 WHERE bid = 1;
rollback;
```
An Index Scan going to exactly one row, and one YugabyteDB tablet (thanks to hash sharding), is scalable:
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Update on h_ysql_bench_branches (actual time=0.823..0.823 rows=0 loops=1)
   ->  Index Scan using h_ysql_bench_branches_pkey on h_ysql_bench_branches (actual time=0.792..0.794 rows=1 loops=1)
         Index Cond: ((h = (pg_backend_pid() % 100)) AND (bid = 1))
 Planning Time: 5.833 ms
 Execution Time: 14.356 ms
(5 rows)
```
If there's a need for it, we can increase the number of rows, and the `% 100` when taking the modulo.

## Queries

The beauty of it is that I didn't change the application, thanks to the power of SQL, here using views.

But be careful if you have the application querying those views:
```sql
yugabyte=# select * from ysql_bench_branches;
 h | bid | bbalance | filler
---+-----+----------+--------
 1 |   1 |    56792 |
(1 row)

yugabyte=# \c
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# select * from ysql_bench_branches;
 h  | bid | bbalance | filler
----+-----+----------+--------
 87 |   1 |   154853 |
(1 row)

yugabyte=# \c
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# select * from ysql_bench_branches;
 h  | bid | bbalance | filler
----+-----+----------+--------
 99 |   1 |    56792 |
(1 row)
```
The select reads the view, and then shows only the value for the current `pg_backend_pid()`. It is important to understand that this change is transparent to the application because you don't have to change the code, but you must know the queries that are run by other modules.

Here is how to query the balance, from the tables:

```sql
yugabyte=# 
 select bid,sum(bbalance) 
 from h_ysql_bench_branches 
 group by bid;

 bid |   sum
-----+---------
   1 | 5599083
(1 row)

yugabyte=# 
 select tid,bid,sum(tbalance) 
 from h_ysql_bench_tellers 
 group by grouping sets ( (tid,bid) , (bid) )
 order by tid nulls last;

 tid | bid |   sum
-----+-----+----------
   1 |   1 |  2384939
   2 |   1 |  4526402
   3 |   1 |  1442672
   4 |   1 | -1166952
   5 |   1 |  3525803
   6 |   1 | -1542642
   7 |   1 | -1783062
   8 |   1 |  4453583
   9 |   1 | -5990027
  10 |   1 |  -251633
     |   1 |  5599083
(11 rows)
```
The beauty of SQL is that you can be 100% confident in the consistency. The total amount is the same, even with transaction errors and retries, because transactions are ACID.

___
If you need application transparency for queries as well, there are other solutions. It all depends on the knowledge of queries. For example, you can add the `where h=pg_backend_pid()%100` logic with RLS policies, or update the base table though an instead-off trigger, and leave the view showing the balance for all rows.
___
**Update 2022-03-16**
Adding a remark by Adrian on an important point: if you have check constraints, you can't easily enforce them on multi-rows. It is a balance between consistency (the C in ACID, not the C in CAP) and scalability:
{% embed https://twitter.com/adriandozsa/status/1502118050237235217 %}