DEV Community

Cover image for YBIO on OCI PostgreSQL
Franck Pachot
Franck Pachot

Posted on • Updated on

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 && cd ybio
export PGHOST=
psql < ~/ybio/ybio.sql
psql <<<"call setup(tab_rows=>100000000,batch_size=>100000)"
Enter fullscreen mode Exit fullscreen mode

It starts by inserting at a rate of 300k rows/s:
setup start 307370 rows/s
Then about 175k rows/s:
setup end 174870 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):
10GB written

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

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')"
Enter fullscreen mode Exit fullscreen mode

It updates about 170k rows/s when started and quickly runs at 110k rows per second:
168837 rows/s

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
It takes a few minutes to create
Image description
This didn't change the throughput on the primary
Image description
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 *

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}
psql <<<"call setup(tab_rows=>100000000,tab_num=>$i,batch_size=>100000)" &
Enter fullscreen mode Exit fullscreen mode

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

20 write IOPS,

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;
Enter fullscreen mode Exit fullscreen mode

LWLock:WALWrite, LWLock:WALInsert, IO:WALSync

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"
Enter fullscreen mode Exit fullscreen mode

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)"
Enter fullscreen mode Exit fullscreen mode

Image description

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')" 
Enter fullscreen mode Exit fullscreen mode

895 rows/s

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;
Enter fullscreen mode Exit fullscreen mode

Image description

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


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.

Top comments (0)