SAP HANA (SPS <=12) does asynchronous IO for reading and writing from storage. It is very important to track this asynchronous behavior. In this blog, I will describe how to track it and how to fine tune it using hdbparam. In fact, if this is not properly tuned this could slow your SAP HANA system that means right from restarts, to table loads to savepoints, delta merges etc operations will be slowed drastically. When all these mission critical operations start lagging it will eventually cause SAP HANA system to slow down. This means that SAP HANA is having synchronous behavior, which is unacceptable.
Asynchronous IO performance can be tracked using the field TRIGGER_RATIO in the system view SYS.M_VOLUME_IO_TOTAL_STATISTICS. The value of this should be closer to 0 to 0.1 ideally which means SAP HANA system is exhibiting asynchronous behavior.
Execute the following query on this view to check the TRIGGER_RATIO:
SELECT HOST, PORT, TYPE, PATH, CONFIGURATION, TRIGGER_WRITE_RATIO, TRIGGER_READ_RATIO from SYS.M_VOLUME_IO_TOTAL_STATISTICS
If the trigger ratio is close to 1 then it means it behaves more like synchronous IO, that is: triggering an IO request takes just as long as executing it and hence very prone to performance degradation of HANA system. In such cases, we need to fine tune those paths (file system) using the hdbparam.This changes I/O behavior to enhance the database for work with the file system and storage. On pure storage you need to set the following hdbparam:
async_read_submit=off
async_write_submit_blocks=new
For more information regarding these parameters, please refer to SAP Note 1943937.
In order to use these parameters in HANA, you need to execute the following
commands in the Linux shell as <sid>adm user.
hdbparam –paraset fileio “[<path>]”. async_write_submit_blocks=new
To set async_write_submit_blocks for Data persistence: (Execute the above select query on the view M_VOLUME_IO_TOTAL_STATISTICS to get the information on the
path and trigger ratios.)
hdbparam –paraset fileio “[/hana/data/P66/mnt00001/hdb00002/]”. async_write_
submit_blocks=new
To find out more information on fine tuning HANA on Pure Storage please check the following best practices document:
