This post is about one of the fundamentally important properties of a database: how IO is done. The test case I studied is doing a simple full table scan of a single large table. In both Oracle and postgres the table doesn’t have any indexes or constraints, which is not a realistic example, but this doesn’t change the principal topic of the study: doing a table scan.
I used a publicly available dataset from the US bureau of transportation statistics called FAF4.5.1_database.zip
The zipped file is 347MB, unzipped size 1.7GB.
In both cases Oracle Linux 7.7 (64 bit) is used, running in VirtualBox, with the storage being a USB3 SSD. Number of CPUs is 4, memory size is 6G. Filesystem type: xfs.
The Oracle version used is Oracle 19.5, the Postgresql version used is 12.1.
For Postgresql, the postgresql.conf file is not changed, except for max_parallel_workers_per_gather which is set to 0 to make postgres use a single process.
For Oracle, the parameters that I think are important: filesystemio_options=’setall’. Oracle is used filesystem based (so no ASM).
This is the table definition for Oracle:
create table faf451 (
fr_origin varchar2(3),
dms_orig varchar2(3),
dms_dest varchar2(3),
fr_dest varchar2(3),
fr_inmode varchar2(1),
dms_mode varchar2(1),
fr_outmode varchar2(1),
sctg2 varchar2(2),
trade_type varchar2(1),
tons number,
value number,
tmiles number,
curval number,
wgt_dist number,
year varchar2(4)
);
This is the table definition for Postgresql:
create table faf451 (
fr_origin varchar(3),
dms_orig varchar(3),
dms_dest varchar(3),
fr_dest varchar(3),
fr_inmode varchar(1),
dms_mode varchar(1),
fr_outmode varchar(1),
sctg2 varchar(2),
trade_type varchar(1),
tons double precision,
value double precision,
tmiles double precision,
curval double precision,
wgt_dist double precision,
year varchar(4)
);
In order for the data to be easy loadable into postgres using copy from, I had to remove ‘””‘ (double double quotes) for the empty numeric fields. In oracle I could say “optionally enclosed by ‘”‘”. For Oracle I used an external table definition to load the data.
Now, before doing any benchmarks, I have an idea where this is going. Oracle is using direct IO (DIO) so linux page cache management and “double buffering” are avoided. Also, oracle will be doing asynchronous IO (AIO), which means submitting is separated from waiting for the notification that the submitted IOs are ready, and on top of that oracle will submit multiple IO requests at the same time. And again on top of that, oracle does multi-block IO, which means that instead of requesting each 8K database block individually, it will group adjacent blocks and request for these in one go, up to a size of combined blocks of 1MB, which means it can requests up to 128 8K blocks in one IO. Postgres will request every block synchronous, so 1 8K block at a time, and waiting for each request to finish. That makes me have a strong idea where this is going.
It should be noted that postgres explicitly is depending on the operating system page cache for buffering as a design principle. Because of DIO, blocks that are read by oracle are not cached in the operating system page cache.
I executed my benchmark in the following way:
– A run for every size is executed 5 times.
– At the start of every run for a certain size (so before every “batch” of 5 runs), the page cache is flushed: (echo 3 > /proc/sys/vm/drop_caches).
– Before each individual run, the database cache is flushed (systemctl restart postgresql-12 for postgres, alter system flush buffer_cache for oracle).
I started off with 2G from the dataset, and then simply performed a ‘copy from’ again to load the same dataset into the table in postgres. Oracle required a bit more of work. Oracle was able to save the same data in way less blocks; the size became 1.18G. In order to have both postgres and oracle scan the same amount of data, I calculated roughly how much rows I needed to add to the table to make it 2G, and copied that table to save it as a 2G table, so I could insert that table to increase the size of the test table by 2G. This way in both oracle and postgres I could test with a 2G table and add 2G at a time until I reached 20G.
These are the results. As you can see in the legenda: oracle is orange, postgres is blue.
(click graph to load full picture)
What we see, is that postgres is a bit slower with the first run of 5 for the smaller dataset sizes, which becomes less visible with larger datasets.
Also, postgres is way faster if the dataset fits into the page cache and it has been read into it. This is logical because postgres explicitly uses the page cache as a secondary cache, and the test is the only activity on this server, so it hasn’t been flushed by other activity.
What was totally shocking to me, is postgres is performing alike oracle and both roughly are able to perform at the maximum IO speed of my disk: 300MB/s, especially when the dataset is bigger, alias beyond the page cache size.
It wasn’t shocking that oracle could reach the total bandwidth of the disk: oracle uses all the techniques to optimise IO for bandwidth. But how can postgres do the same, NOT deploying these techniques, reading 8K at a time??
The first thing to check is whether postgres is doing something else than I suspected. This can simply be checked with strace:
poll_wait(3, [{EPOLLIN, {u32=18818136, u64=18818136}}], 1, -1) = 1
recvfrom(11, "Q\0\0\0!select count(*) from faf451"..., 8192, 0, NULL, NULL) = 34
lseek(20, 0, SEEK_END) = 335740928
lseek(20, 0, SEEK_END) = 335740928
kill(1518, SIGUSR1) = 0
pread64(5, "\f\0\0\0\310ILc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846061568) = 8192
pread64(5, "\f\0\0\0HcLc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846069760) = 8192
pread64(5, "\f\0\0\0\260|Lc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846077952) = 8192
pread64(5, "\f\0\0\0000\226Lc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846086144) = 8192
…etc…
The above strace output shows only 4 rows of pread64() calls, but this goes on. So no “secret” optimisation there.
Luckily, my VM has a new enough version of Linux for it to be able to use eBPF, so I can use biosnoop. Biosnoop is a tool to look at IO on one of the lower layers of the linux kernel, the block device interface (hence ‘bio’). This is the biosnoop output:
# /usr/share/bcc/tools/biosnoop
TIME(s) COMM PID DISK T SECTOR BYTES LAT(ms)
0.000000000 postmaster 4143 sdb R 66727776 708608 5.51
0.006419000 postmaster 4143 sdb R 66731720 77824 11.06
0.006497000 postmaster 4143 sdb R 66734432 786432 11.03
0.011550000 postmaster 4143 sdb R 66731872 1310720 16.17
0.013470000 postmaster 4143 sdb R 66729160 1310720 18.86
0.016439000 postmaster 4143 sdb R 66735968 1310720 14.61
0.019220000 postmaster 4143 sdb R 66738528 786432 15.20
Wow…so here it’s doing IOs of up to 1MB! So somewhere between postgres itself and the block device, the IOs magically grew to sizes up to 1MB…that’s weird. The only thing that sits between postgres and the block device is the linux kernel, which includes page cache management.
To get an insight into that, I ran ‘perf record -g -p PID’ during the scan, and then perf report to look at the recorded perf data. This is what is I found:
Samples: 21K of event 'cpu-clock', Event count (approx.): 5277000000
Children Self Command Shared Object Symbol ◆
- 41.84% 3.63% postmaster libpthread-2.17.so [.] __pread_nocancel ▒
- 38.20% __pread_nocancel ▒
- 38.08% entry_SYSCALL_64_after_hwframe ▒
- 37.95% do_syscall_64 ▒
- 35.87% sys_pread64 ▒
- 35.51% vfs_read ▒
- 35.07% __vfs_read ▒
- 34.97% xfs_file_read_iter ▒
- 34.69% __dta_xfs_file_buffered_aio_read_3293 ▒
- 34.32% generic_file_read_iter ▒
- 21.10% page_cache_async_readahead ▒
- 21.04% ondemand_readahead ▒
- 20.99% __do_page_cache_readahead ▒
+ 14.14% __dta_xfs_vm_readpages_3179 ▒
+ 5.07% __page_cache_alloc ▒
+ 0.97% radix_tree_lookup ▒
+ 0.54% blk_finish_plug ▒
If you look at rows 13-15 you see that the kernel is performing readahead. This is an automatic function in the linux kernel which looks if the requests are sequential of nature, and when that’s true performs readahead, so that the scan is made faster.
Frits Hoogland Weblog 