You have to only peripherally related problems. The issue about not enough KAIO resources is an operating system tuning parameter. You can read the maximum value and set it here:
$ cat /proc/sys/fs/aio-max-nr
65536
echo 131072 >/proc/sys/fs/aio-max-nr # set new maximum
And see the currently used level here:
$ cat /proc/sys/fs/aio-nr
32768
The second issue of bufwaits can be caused by any or all of several issues:
- Not enough buffers (BUFFERPOOL)
- Not enough LRU queues (set in the 'lrus' option to the BUFFERPOOL parameter for each buffer pool).
- LRU flushing is not aggressive enough. (Set in the lru_min_dirty & lru_max_dirty options to the BUFFERPOOL parameter for each buffer pool.) You can get an idea of whether the flushing is sufficiently aggressive by looking at the following:
- onstat -F (you want to see about 2/3 of the IO as lru writes and 2/3 as chunk writes with zero foreground writes
- onstat -R (look for all or most lru queues for each buffer pool to have a lower percentage of dirty buffers than the current lru_max_dirty setting for that pool)
- onstat -p compare bufwaits to (pagereads + bufwrites), the ratio should be lower than 7%. If it's higher, you need more LRU queues to allow more concurrency among your 1000 sessions.
- onstat -g ckp - look for checkpoint wait and block times that are more than a second. That will indicate, again, that buffer flushing is not aggressive enough and too much work is being left for checkpoint time.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Tue March 15, 2022 09:04 AM
From: SangGyu Jeong
Subject: Buffer wait occurs when performing INSERT in multiple sessions
Hello All,
In my customer's system, a buffer wait often occurs when an INSERT statement is performed on the same table in multiple sessions.
In that case, there are about 1000 sessions with the first flag value of B in the onstat -u command.
And the online log file shows the message 'KAIO out of resource'.
Each INSERT statement is a short transaction that inserts 1 row.
Any hints to solve this problem?
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix