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

I have seen the KAIO out of resources error several times on AIX. Consider setting the environment variable IFMX_AIXKAIO_NUM_REQ as described in this article: https://www.ibm.com/support/pages/kaio-out-os-resources-errno-11-aiowrite-returning-eagain You may need to do some tuning of your bufferpools, cleaners and lru queues to make sure that modified pages are being written out efficiently and that the engine can quickly find a place in the bufferpool for the new pages. Run onstat -F | head - do you see any Foreground Writes? ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hi Mike, Thanks for your response. Setting the IFMX_AIXKAIO_NUM_REQ environment variable requires restarting the instance, so we will consider it later. The result of onstat -F is shown below. Foreground writes 0 LRU writes 305769353 Chunk writes 9225920 The settings in onconfig are as follows. CLEANERS 16 BUFFERPOOL size=4k,buffers=2048000, lrus=256, lru_min_dirty=4, lru_max_dirty=5 ------------------------------SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

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-nr65536 echo 131072 >/proc/sys/fs/aio-max-nr # set new maximum And see the currently used level here: $ cat /proc/sys/fs/aio-nr32768 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 ------------------------------

But keep in mind that echo 131072 >/proc/sys/fs/aio-max-nr is not persisent on reboots better put it in /etc/sysctl.conf too! With kind regards / Mit freundlichen Grüßen Rainer von Bongartz --- Die Europäische Kommission hat unter http://ec.europa.eu/consumers/odr/ eine Europäische Online-Streitbeilegungsplattform (OS-Plattform) errichtet. Verbraucher können die OS-Plattform für die außergerichtliche Beilegung von Streitigkeiten aus Online-Verträgen mit in der EU niedergelassenen Unternehmen nutzen. Informationen (einschließlich Pflichtangaben) zu einzelnen, innerhalb der EU tätigen Gesellschaften und Zweigniederlassungen des Konzerns Deutsche Bank finden Sie unter https://www.deutsche-bank.de/Pflichtangaben. Diese E-Mail enthält vertrauliche und/ oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet. The European Commission has established a European online dispute resolution platform (OS platform) under http://ec.europa.eu/consumers/odr/. Consumers may use the OS platform to resolve disputes arising from online contracts with providers established in the EU. Please refer to https://www.db.com/disclosures for information (including mandatory corporate particulars) on selected Deutsche Bank branches and group companies registered or incorporated in the European Union. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

Thanks Art, As it is AIX, the os tuning parameter seems to have to be adjusted in a different way. And since the customer is using the workgroup edition, there is a limit to increasing the buffer size. Therefore, we are considering increasing the number of lru queues and cleaners. Wouldn't excessive lru writes be a problem? ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Hi Rainer, My customer's Informix is running on AIX, so it seems impossible to modify the /proc/sys/fs/aio-max-nr file. Thanks for your help. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Chunk writes are more "efficient" for the OS and the storage, but LRU writes are what keep the server's data moving out to disk. More LRU Writes is better in any OLTP system. Obviously in a Data Warehouse server you want to be going heavier towards 50-50 LRU -vs- Chunk writes, but in an OLTP system you want to be somewhere north of 65 to 80% LRU writes. You should have at least one CLEANER per dbspace for Chunk write efficiency, but one for every 2-4 active LRU queues for LRU write efficiency. Obviously the max number of CLEANERS is lower than the possible number of LRU queues, but get as close as you can. Max it out if you have to in order to keep the dirty queues flushed. For large numbers of users, more LRU queues is critical to reduce waiting for IO's to happen. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------