YBIO on OCI PostgreSQL

In the past, I utilized 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

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:

Then about 175k rows/s:

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):

My bulk insert has written about 10GB on disk, at 13k IOPS and 120 KB/s (which sounds strange as I expect 8k writes)

Once completed, I start one job updating random block ranges:

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:

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

Read replica

After running two hours, I add a new node (read replica)

It takes a few minutes to create

This didn't change the throughput on the primary

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):

Create and load 10 tables

To check the wait events, I run the table creation from 10 sessions in parallel:

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):

I use the following to query pg_stat_activity:

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;

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 as it writes the WAL to remote storage)

Unfortunately, lot of information is hidden from pg_stat_activity and I cannot enable it:

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:

psql <<<"call setup(tab_rows=>1000000000,batch_size=>100000)"

and run updates but row-by-row rather than batched:

psql <<<"call runit(tab_rows=>1000000000,batch_size=>1,pct_update=>100,run_duration=>'600 minutes')" 

I sample pg_stats_activity during 15 seconds:

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;

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:

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.