## 3.4 I/O Related Root Causes and Solutions
This section covers troubleshooting of I/O performance problems. Although SAP HANA is an in-memory database, I/O still plays a critical role for the performance of the system.
From an end user perspective, an application or the system as a whole runs slowly, is unresponsive or can even seem to hang if there are issues with I/O performance. In the Disk Volume Monitor available in the Disk Usage tile in SAP HANA cockpit you can see the attached volumes and which services use which volumes. For details of the attached volumes, such as files and I/O statistics, select a row.
In certain scenarios data is read from or written to disk, for example during the transaction commit. Most of the time this is done asynchronously but at certain points in time synchronous I/O is done. Even during asynchronous I/O it may be that important data structures are locked.
Examples are included in the following table.
| Scenario                        | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|---------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Savepoint                       | A savepoint ensures that all changed persistent data since the last savepoint gets written to disk. The SAP HANAdatabase triggers savepoints in 5 minutes intervals by default. Data is automatically saved from memory to the data volume located on disk. Depending on the type of data the block sizes vary between 4 KB and 16 MB. Savepoints run asynchronously to SAP HANAupdate operations. Database update transactions only wait at the critical phase of the savepoint, which is usually taking some microseconds. |
| Snapshot                        | The SAP HANAdatabase snapshots are used by certain operations like backup and system copy. They are created by triggering a system wide consistent savepoint. The system keeps the blocks belonging to the snapshot at least until the drop of the snapshot. Detailed information about snapshots can be found in the SAP HANAAdministration Guide .                                                                                                                                                                         |
| Delta Merge                     | The delta merge itself takes place in memory. Updates on column store tables are stored in the delta storage. During the delta merge these changes are applied to the main storage, where they are stored read optimized and compressed. Right after the delta merge, the new main storage is persisted in the data volume, that is, written to disk. The delta merge does not block parallel read and update transactions.                                                                                                  |
| Write Transactions              | All changes to persistent data are captured in the redo log. SAP HANAasynchronously writes the redo log with I/O orders of 4 KB to 1 MBsize into log segments. Transactions writing a commit into the redo log wait until the buffer containing the commit has been written to the log volume.                                                                                                                                                                                                                               |
| Database restart                | At database startup the services load their persistence including catalog and row store tables into memory, that is, the persistence is read from the storage. Additionally the redo log entries written after the last savepoint have to be read from the log volume and replayed in the data area in memory. When this is finished the database is accessible. The bigger the row store is, the longer it takes until the system is available for operations again.                                                        |
| Failover (Host Auto-Fail- over) | On the standby host the services are running in idle mode. Upon failover, the data and log volumes of the failed host are automatically assigned to the standby host, which then has read and write access to the files of the failed active host. Row as well as column store tables (the latter on demand) must be loaded into memory. The log entries have to be replayed.                                                                                                                                                |
| Takeover (System Replica- tion) | The secondary system is already running, that is the services are active but cannot accept SQL and thus are not usable by the application. Just like in the database restart (see above) the row store tables need to be loaded into memory from persistent storage. If table preload is used, then most of the column store tables are already in memory. During takeover the replicated redo logs that were shipped since the last data transport from primary to secondary have to be replayed. |
| Data Backup                     | For a data backup the current payload of the data volumes is read and copied to the backup storage. For writing a data backup it is essential that on the I/O connection there are no collisions with other transactional operations running against the database.                                                                                                                                                                                                                                 |
| Log Backup                      | Log backups store the content of a closed log segment. They are automatically and asyn- chronously created by reading the payload from the log segments and writing them to the backup area.                                                                                                                                                                                                                                                                                                       |
| Database Recovery               | The restore of a data backup reads the backup content from the backup device and writes it to the SAP HANAdata volumes. The I/O write orders of the data recovery have a size of 64 MB. Also the redo log can be replayed during a database recovery, that is the log backups are read from the backup device and the log entries get replayed.                                                                                                                                                    |
In the following table the I/O operations are listed which are executed by the above-mentioned scenarios, including the block sizes that are read or written:
I/O pattern
Savepoint,
Snapshot,
Delta merge
## Write transactions
Table load:
DB Restart,
Failover,
Takeover
Data Backup
Data Volume
WRITE
4 KB - 16 MB asynchronous bulk writes, up to 64 MB (clus-
tered Row Store super blocks)
READ
4 KB - 16 MB blocks, up to 64
MB (clustered Row Store super blocks)
READ
4 KB - 16 MB blocks, up to 64 MB (clustered Row Store super blocks) are asynchronously copied to '[data] backup buffer' of 512 MB
Log Volume (redo log)
## WRITE
OLTP - mostly 4 KB log write I/
O performance is relevant
OLAP - writes with larger I/O
order sizes
READ
Backup Medium
WRITE
in up to 64 MB blocks from
'[data] backup buffer'
| I/O pattern       | Data Volume   | Log Volume (redo log)                                          | Backup Medium                                          |
|-------------------|---------------|----------------------------------------------------------------|--------------------------------------------------------|
| Log Backup        |               | READ asynchronously copied to '[data] backup buffer' of 128 MB | WRITE in up to 64 MBblocks from '[data] backup buffer' |
| Database Recovery | WRITE         | READ                                                           | READ                                                   |