Postgres Pro
Downloads
Home   >   mailing lists

Thread: Improve configurability for IO related behavoir

Improve configurability for IO related behavoir

From
浩辰 何
Date:
Dear developers:

I have some suggestions on PostgreSQL's IO behavior. I am not sure my opinions are right. Many thanks for your time!

It is documented that :
wal_sync_method​ (enum): The open_*​ options also use O_DIRECT if available.
Shall PostgreSQL consider making O_DIRECT configurable? In MySQL, innodb_flush_method​ can change if use  O_DIRECT or not. 
I made some benchmarking on O_DIRECT of MySQL, and I find that sometimes, using O_DIRECT may hurt performance. 
https://ibb.co/BVWnwnh
Comparing the blue (buffered IO) and green (direct IO) lines, I find buffered IO is faster. So I think having such parameter in PostgreSQL 
is reasonable.
On the other hand, wal_sync_method​ only controls how WAL is written to devices, while for data file I notice that PostgreSQL uses 
sync_file_range(2)​. So shall we also make it configurable? I also find that in some systems, open​ with O_SYNC is much more faster
than write​ + fsync​ :
https://ibb.co/f1VsCC1
Furthermore, the results above are also related to IO API supported by OS. MySQL support synchronized IO and Linux libaio​. It seems
that PostgreSQL only supports synchronized IO, so shall we support more IO engines? like io_uring​ which is very popular in recent years.

Thanks & best,
Haochen

Re: Improve configurability for IO related behavoir

From
Thomas Munro
Date:
On Sun, May 29, 2022 at 4:29 AM 浩辰 何 <hehaochen@hotmail.com> wrote:
> Furthermore, the results above are also related to IO API supported by OS. MySQL support synchronized IO and Linux
libaio.It seems 
> that PostgreSQL only supports synchronized IO, so shall we support more IO engines? like io_uring which is very
popularin recent years. 

Hi Haochen,

There is an active project to bring these things to PostgreSQL.
https://wiki.postgresql.org/wiki/AIO has some information and links.
The short version is that there is a development patch set to add
these GUCs:

io_method=worker,io_uring,posix_aio,iocp,...
io_data_direct=on,off
io_wal_direct=on,off

It also adds a bunch of specialised logic that knows how to initiate
IO in key places (scans, writeback, recovery, ...), because it's not
enough to just turn off kernel I/O buffering, we also have to do all
the work the kernel is doing for us or performance will be terrible.