Skip to Content

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

/wp-content/uploads/2016/08/1_1007371.png

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

/wp-content/uploads/2016/08/2_1007426.png

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:

/wp-content/uploads/2016/08/3_1007427.png

Now validate this change through following command:

hdbparam –paramget fileio.async_read_submit

/wp-content/uploads/2016/08/4_1007428.jpg

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)


/wp-content/uploads/2016/08/5_1007476.png

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.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Jens Gleichmann

    Hi Sheo,

    thanks the details to sync and async I/O. For more details and the latest information you should add the corresponding note 1930979 – Alert: Sync/Async read ratio
    Therefore you should add the default values and the meaning of the auto value for the different filesystems and the depency to the HANA revision in your blog.

    currently in version 11 of the note:
    “Please note that fileio.async_write_submit_active=auto maps to “on” for file systems GPFS and XFS. It maps to “off” for NFS file systems. In case you filesystem is GPFS or XFS, there is no need to change the parameter from “auto” to “on”.

    So in some cases there is no need to change the parameter manually.

    Regards,
    Jens

    (0) 

Leave a Reply