YBIO on OCI PostgreSQL
Tags: postgres, oracle, oci
Published: 2023-11-24 19:56:54

In the past, I utilized [ybio](https://github.com/FranckPachot/ybio) to compare the performance of Amazon Aurora on Intel and Graviton. I'll use the same tool to create a workload on Oracle PostgreSQL managed service and analyze the available performance metrics.

I create one table and insert rows by batch
```sh
cd ~ && git clone https://github.com/FranckPachot/ybio.git && cd ybio
export PGHOST=10.0.1.3
psql < ~/ybio/ybio.sql
psql <<<"call setup(tab_rows=>100000000,batch_size=>100000)"
```

It starts by inserting at a rate of 300k rows/s:
![setup start 307370 rows/s](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vdb2k3z0zytg6m4ik9eo.png)
Then about 175k rows/s:
![setup end 174870 rows/s](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gpu9ixxzgsy4ozfnidhp.png)

Note that you cannot compare with Amazon Aurora, which is multi-AZ. My deployment in Oracle Cloud in a single Availability Domain like most of the regions in OCI.

A few metrics are exposed, and some are not very interesting, like Memory Utilization (I'm surprised to see 80% used and then only 20% available) or Buffer Cache Hit Ratio (useless measure, especially with PostgreSQL, which does all buffered I/O):
![10GB written](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ndjoxqsy8kxefhvugfk3.png)

My bulk insert has written about 10GB on disk, at 13k IOPS and 120 KB/s (which sounds strange as I expect 8k writes)
![13k IOPS 120KBPS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w7j2rtmegta3v431vvaw.png)

Once completed, I start one job updating random block ranges:
```sql
psql <<<"call runit(tab_rows=>100000000,batch_size=>100000,pct_update=>100,run_duration=>'600 minutes')"
```
It updates about 170k rows/s when started and quickly runs at 110k rows per second:
![168837 rows/s](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lgk4x3hwmbgmj9yj8xg6.png)

The working set fits in the shared_buffers (set to 16GB).

## Read replica

After running two hours, I add a new node (read replica)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cq4w3bpln6wzv1wz9anx.png)
It takes a few minutes to create
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xvpyp5oyuqxoptsrrj1w.png)
This didn't change the throughput on the primary
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x2fq6aacd05m2oh4offx.png)
With and without replica, the storage is replicated and starting a read replica doesn't change anything.

On the read replica, I've run a `select *` two times to show the cold start (96 seconds to read 100 million rows) and faster response time with all in cache (12 seconds):
![select *](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oyt4s40dfcqgfs3n2zf0.png)

## Create and load 10 tables
To check the wait events, I run the table creation from 10 sessions in parallel:
```sql
for i in {0..9}
do
psql <<<"call setup(tab_rows=>100000000,tab_num=>$i,batch_size=>100000)" &
done
```
It started at 100k rows per second per job quickly decreasing to 2000 rows per second (which means 20000 rows per second in total from 10 jobs):
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/blr3v7xm97bns71g3fs0.png)


![20 write IOPS, ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0bm4jb8rnpp5zsc939jp.png)

I use the following to query `pg_stat_activity`:

```sql
select pid,usename, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query
from pg_stat_activity where application_name='psql' order by query_start;
```

![LWLock:WALWrite, LWLock:WALInsert, IO:WALSync](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p1ceb1wgsx56prczesjb.png)

The wait events are the usual suspect when many sessions compete in writing their Write Ahead Logging (WAL) 
`LWLock:WALWrite`, `LWLock:WALInsert`, `IO:WALSync`

This confirms that the architecture is like PostgreSQL and not like Aurora (which has its own [IO:XactSync](https://dev.to/aws-heroes/aws-aurora-io-xactsync-is-not-a-postgresql-wait-event-3gio) as it writes the WAL to remote storage)

Unfortunately, lot of information is hidden from `pg_stat_activity` and I cannot enable it:
```sql
postgres=> set track_activities=on;
ERROR:  permission denied to set parameter "track_activities"
postgres=>
```

### Update workload with random reads

To test another workload, I created one table again, but larger to be sure that it doesn't fit in shared buffers:
```sql
psql <<<"call setup(tab_rows=>1000000000,batch_size=>100000)"
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ye87tjga3y8n4042j6bg.png)

and run updates but row-by-row rather than batched:
```sql
psql <<<"call runit(tab_rows=>1000000000,batch_size=>1,pct_update=>100,run_duration=>'600 minutes')" 
```

![895 rows/s](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ymir2azhpqjqsbod9ts.png)

I sample `pg_stats_activity` during 15 seconds:
```sql
create temporary table psa_history as
select now(),psa.* from pg_stat_activity psa
 where null is not null;
truncate table psa_history;
insert into psa_history
select now(),psa.* from pg_stat_activity psa
 where pid !=pg_backend_pid()
;
\watch i=0.05 c=300
select count(*), state, wait_event_type, wait_event, query, pid, usename, backend_start
from psa_history where application_name='psql'
group by         state, wait_event_type, wait_event, query, pid, usename, backend_start
order by 1 desc;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/57y2c33v1fklxopc3tfl.png)

In addition to the I/O write for the WAL, we see most of the sessions waiting on read I/O as I reading randomly within a working set that is larger than the shared buffers.

I've tested the same with batched updates, to reduce the WAL contention:
![batch_size=>100000 3007 rows/s](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/reh4j7ujra383e7lf9vr.png)

![IO:DataFileRead](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xegabfxbrm2itvm10jn1.png)

Overall, this looks like the regular PostgreSQL behavior with a monolithic WAL stream and writing to network storage. The main optimization is the storage which stores the PostgreSQL pages with redundancy (like Oracle ASM is you want a simple comparison) and which can be opened by physical standby read replicas when they need to read a block.