SAP Hana Logbackup and Savepoints
This article describes the background and effects of the default log area handling in SAP HANA. As most of this is database administrator (DBA) knowledge, SEP AG does not claim and will take no responsibility for the technical correctness of the content on this page. However, we have done our best to collect this information.
Note that once a log segment is full and a new one is created, the automatic log backup mechanism is triggered by the database. This cannot be prevented and is a forced behaviour in order to ensure that the database does not have to stop executing transactions due to lack of space in the transaction log. Seen from a backup perspective, this means that a log backup always occurs automatically when a log segment is complete.
SAP HANA savepoints
As mentioned, all database contents reside in the main memory of the server(s). This would lead to severe data loss in case of a system power-off because the RAM is a fluctuent type of memory and loses its state upon voltage loss. To prevent this and make the whole database persistent, SAP HANA periodically writes a state of the in-memory database data to disk.
This action is called savepoint creation and it happens in a 5-minutely manner by default. The difference between a savepoint and the transaction records is that a savepoint contains the current state of the data with no history while the transaction log contains the history of transactions since the last savepoint or, respectively, data file update.
SAP HANA transaction log area
SAP HANA, as an in-memory database, holds all database contents in RAM. Therefore, transactions are also executed there. To ensure that the data persists in case of an outage (be it planned maintenance or just a power cutoff), SAP HANA writes the transactions to disk log area simultaneously. More information regarding this feature can be found in the SAP HANA documentation or on this blog.
The database manages its transaction log in so called log segments, as the linked article describes. A log segment is a file of a configuration-defined size (default: 1 GB) which holds the transactions in a range of transaction numbers. All segments together make up the log area. Once a log segment reaches its configuration-defined limit, a new segment is opened by the database as long as there is enough space left in the configuration-defined size of the log area as a whole. More information about log segments can be found here. This mechanism should not be mistaken with the principle of database savepoints.
The following figure shows the principle of the log area with its segments and thresholds:
Transaction log backup
Once a log segment is full and a new one is created, the automatic log backup mechanism is triggered by the database. This cannot be prevented and is a forced behaviour in order to ensure that the database does not have to stop executing transactions due to lack of space in the transaction log. Seen from a backup perspective, this means that a log backup always occurs automatically when a log segment is complete. The database also tries to perform a log backup if the size of the log area as a sum (all transaction logs since the last savepoint) grows over a defined threshold. This is a rather severe status because if the log backups frequently fail in this case, the database will soon stop to execute database transactions (at least writing transactions) and business will be affected similar to a downtime.
Another event where a log backup occurs is the configurable maximum time between automatic log backups. This can be configured via the SAP HANA global.ini persistence configuration section and is also configurable graphically via the SAP HANA Studio or DBA Cockpit. The default value is 15 minutes, which means that even if no new transactions were committed or a log segment was completed, the transactions are being backed up and a new log segment is created. Therefore the value can and should be adjusted based on the Recovery Point Objective (RPO) and number of database transactions per time. All of this ends up in the following events to trigger a log backup:
- Log segment is full.
- Log area size threshold is reached.
- Configured time is over (default: 15 minutes).
The database will always use the configured way to perform a log backup. The destination type is also defined in the database configuration section which can be reached as described in the general guide to SAP HANA backup with SEP sesam.
Relevant configuration sections
To adjust SAP HANA and its savepoint and/or log area handling to your needs, you might want to check out the following sections of the SAP HANA configuration:
For system databases:
For tenant databases:
Possible values are:
It is needless to say that you should be aware of what you are doing whenever you change the log mode. This can render your database unable for point-in-time recovery as changing the log_mode to overwrite will not force log segments to be backed up after completion but to be overwritten with newer transactions. Therefore, it is strongly advised that only log_mode normal should be used in production. However, on development or testing environments (if no log backup is needed), it might be a useful setting. After changing the log_mode parameter, the database has to be restarted.
Log backup interval mode
This comes in place when the amount of transactions is rather predictable and you (the DBA) can ensure that the log area will not reach its limit by a specified amount of time. In case you want to perform log backups in a more timely-structured manner, you can change the so called log backup inteval mode.
The setting can be found here:
For system database:
For tenant databases:
Possible values are:
Log segments are backed up immediately after a log segment is completed or when the configured service timeout has been reached. This is the default setting and makes sense for most environments since it provides the highest flexibility and least risk of jamming the database due to log area full situations.
Log segments regardless of their state (writing, closed, or truncated) will be backed up after the configured service timeout has been reached. You can use this setting if you need to have the log backups at fixed times. However, this might affect the Recovery Point Objectives (RPOs) because transactions are only backed up after certain time intervals. One thing to consider is that the potential loss of transactions may exceed the configured log backup service timeout by two times.
Savepoint interval configuration
By default, SAP HANA writes its changed database contents to disk every 5 minutes. If you want to change this, use this configuration parameter:
The value reflects the amount of time between to savepoints in seconds.
|Increasing this value will affect the startup time of SAP HANA and might affect the database state after a restart of the database. The maximum value for this parameter is 7200 seconds.|
One typical objective to adjust this value can be runtime performance optimization, because more disk performance can be used for database transaction recording rather than for savepoint creation.