How and why to activate asynchronous queuing of IO requests

Why Asynchronous IO:

Undesired synchronous I/O can have a major impact on the HANA performance, especially restart time and table load time for read I/O as well as savepoint and write transaction commit times for write I/O.

HANA uses asynchronous IO to write to and read from disk. Ideally, asynchronous IO should have a trigger ratio close to zero. A trigger ratio close to 1 indicates asynchronous IO that behaves almost 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 activate asynchronous IO for a particular file system/path.
How to Activate Asynchronous IO:

Lets define Trigger_Time, IO_Time and Trigger_Ratio as these are few parameters which define the activation of asynchronous queuing.

TRIGGER_TIME: The time needed for triggering an IO request

IO_TIME: The time for completely finishing an IO request

TRIGGER_RATIO: The ratio TRIGGER_TIME / IO_TIME Trigger ratios are monitored in the System View SYS.M_VOLUME_IO_TOTAL_STATISTICS.

The following select returns all information necessary for checking trigger ratios and further steps:

SELECT HOST, PORT, TYPE, PATH, CONFIGURATION, TRIGGER_WRITE_RATIO, TRIGGER_READ_RATIO from SYS.M_VOLUME_IO_TOTAL_STATISTICS

From the above screen it is clear that we have issue in three of the output for TRIGGER_READ_RATIO which is higher than 0.5. In the screen shot above since TRIGGER_READ_RATIO is high so here we need to activate asynchronous queueing of IO requests for Async Read through the following command executed through <sid>adm at OS level:

hdbparam --paramset fileio"[<path>]". async_read_submit=on

Taking the example of the above screen the command is as follows:

hdbparam --paramset fileio "[/hana/data//mnt00001/hdb00002/]".async_read_submit=on

The output must be as follows:

Now validate this change through following command:

hdbparam --paramget fileio.async_read_submit

Similarly if TRIGGER_WRITE_RATIO is greater than 0.5 for a path , execute the following (one line) commands as <sid>adm in your linux shell:

$ hdbparam --paramset fileio"[<path>]". async_write_submit_active=on         

$ hdbparam --paramset fileio"[<path>]". async_write_submit_blocks=all

WARNING: Please note that this reconfiguration can lead to higher latencies since queueing IO requests incurrs some overhead. If you have changed the configuration for paths used for database logging, please check the commit latency for undesired effects through following select statement. Compare the output from pre-changes to post-changes and see if any bad impact on the system.

SELECT HOST, PORT, TYPE, round(MAX_IO_BUFFER_SIZE / 1024, 3) "Maximum buffer size in KB",    TRIGGER_ASYNC_WRITE_COUNT, AVG_TRIGGER_ASYNC_WRITE_TIME as "Avg Write Time in  Microsecond" from "PUBLIC"."M_VOLUME_IO_DETAILED_STATISTICS" where type = 'LOG'   and VOLUME_ID in (select VOLUME_ID from PUBLIC.M_VOLUMES where SERVICE_NAME = 'indexserver' and  AVG_TRIGGER_ASYNC_WRITE_TIME > 0)

Addition: As of HANA SPS10 the following parameter default value have been changed:

Parameter                                                     Old Value                            New Value

fileio.async_write_submit_active                   auto                                       on

fileio.async_write_submit_blocks                  new                                        all

fileio.async_read_submit                                off                                          on

hdbparam can set defaults by omitting the parameter value.

Example:

$ hdbparam --paramset fileio.async_read_submit=

The upgrade only sets values of unchanged parameters to the new default.

Source: scn.sap.com