Better Db2 backup runtimes on AIX: Using encryption for backups and databases

Intro and Problem Statement

As we live in challenging times, are leaving the traditional on-premises datacenter and are using more and more cloud-based IT offerings, increased operational security is one of the topics of today. An easy to achieve but powerful option for databases to improve security is to encrypt the database and database backups. Therefore, an increasing number of Db2 customers are encrypting their database using Db2 native encryption for data in rest. Other encryption options are not in focus for this document (e.g.: Encrypted File Systems or IBM InfoSphere Guardium).

However, adding encryption is not for free. Some of you may be facing slower backup runtimes on AIX and an increase in CPU usage. We did a series of tests to be able to give recommendations to minimize these impacts. Basically, we found two options to lower the impact. First, we changed the AIX malloc (memory allocation) settings and second, we manually varied the values for the backup parallelization and backup buffers. Both methods delivered good results.

Background

Db2 native encryption provides a built-in encryption capability to protect database backup images and key database files from inappropriate access while they are at rest on external storage media.

Db2 native encryption uses a two-tier approach to data encryption. Data is encrypted with a Data Encryption Key (DEK), which is in turn encrypted with a Master Key (MK). The encrypted DEK is stored with the data while the MK is stored in a keystore external to Db2.

Important: Do not lose access to your master key (MK). If you lose access to the MK, you irrevocably lose access to the data in your encrypted database or database backup.

Db2 native encryption uses the cryptographic capabilities of the IBM Global Security Kit (GSKit) underneath. Each physical I/O request from Db2 when data flows from or to disk decryption or encryption must take place. This leads to additional overhead in CPU and process scheduling. As said before: Encryption is not for free, but the impact can be influenced.

Test Environment

HW and SW Setup

The environment to test the influence of encryption on the database backup runtime and CPU consumption was based on the following components:

• POWER8 LPAR: 4 dedicated physical CPUs, SMT4, 64GB memory
• AIX: 7200-05-03
• IBM XIV storage system
• Db2: 11.5.6, database size: 270GB
• Database overall compression ratio: 52%
• Storage paths: 4
• Number of tablespaces: 46

Backup setup

Backup parallelization and buffer

The backup tests were run as offline backups with no additional load on the AIX LPAR. The backup files were written to an IBM XIV disk subsystem which provided four disks – one for each backup file. We used three different variations of the backup command to influence the parallelism how the data is read from disk.

• • Db2 automatic settings for parallelization (15 or 30) and buffer (30 or 60). The lower values are valid for encrypted backups the higher values for unencrypted backups.
  • Manual settings for parallelization of 8 and buffer of 16.
  • Manual settings for parallelization of 4 and buffer of 8.

The parallelization is defined by the parallelism parameter in the backup command and reflects how many tablespaces are being backed up in parallel. The number of buffers should be chosen at least twice the number of tablespaces (backup targets).

Backup commands

In the graphics we show the four results of the following backup commands. 

1. Reference backup: unencrypted backup of an unencrypted database

db2 “backup db vfb to /backup5, /backup6, /backup7, /backup8”

2. Encrypted backup of an encrypted database using the database chosen settings for parallelization and buffer – automatic settings.

db2 "backup db vfb to /backup5, /backup6, /backup7, /backup8 encrypt encrlib libdb2encr.a encropts 'MASTER KEY LABEL=encry_back_test_nx842'"

3. Encrypted backup of an encrypted database, manually setting parallelization to 8 and the buffer to 16

db2 "backup db vfb to /backup5, /backup6, /backup7, /backup8 with 16 buffers parallelism 8 encrypt encrlib libdb2encr.a encropts 'MASTER KEY LABEL=encry_back_test_nx842'"

4. Encrypted backup of an encrypted database, manually setting parallelization to 4 and the buffer to 8

db2 "backup db vfb to /backup5, /backup6, /backup7, / backup8 with 8 buffers parallelism 4 encrypt encrlib libdb2encr.a encropts 'MASTER KEY LABEL=encry_back_test_nx842'"

Optimized AIX malloc settings

To avoid the runtime penalty which we found during our measurements apply the recommendations from the following IBM Support Document:

Malloc heap contention may cause performance degradation when using DB2 on AIX with specific features

We need to change the default AIX memory allocation and allow multiple heap usage for the IBM GSKit – which is used by Db2 for encrypting and decrypting. That way serialization in the memory allocation is avoided at high concurrency levels.

The following three changes are needed for optimized performance: These changes do not relate to Db2 which has its own memory handling.

1. Tune the AIX memory allocation settings for the instance owner (db2vfb) and set the following environment variables:

export MALLOCOPTIONS=buckets,multiheap:4
export MALLOCBUCKETS=number_of_buckets:128,bucket_sizing_factor:64,blocks_per_bucket:1024

2. Unlimit the size of the data area of the instance owner in /etc/security/limits:

         db2 vfb:

data = -1

3. Update the Db2 registry and restart the instance:

db2set DB2ENVLIST="MALLOCOPTIONS MALLOCBUCKETS"

db2stop

db2start

The result of the changed AIX memory allocation settings can be found in the next chapters. They can be quite impressive. On the other hand, the changes in the AIX memory allocation increases the real memory consumption.

As there is additional private memory required the following two serviceability tools should be used with caution:

            Db2: db2pd -memblocks pid=<process id>

            AIX: gencore

Test Scenarios and Results

We tested how the backup performance was influenced by enabling encryption for the database as well as for the backups. To compare the results, we used an unencrypted database and did an unencrypted backup and let Db2 chose the backup parameters.

For the reference backup the Db2 self-tuning algorithms chose 30 for the backup parallelization and 60 for the backup buffers. The runtime was 900 seconds with an average CPU usage of 24,1% (1,7% User and 22,4 % System).

The runtime for the reference backup did not change when manually setting the parallelization and buffer values to parallelization 8 and buffer 6 or parallelization 4 and buffer 8.

Scenario 1: Influence of Backup Parallelization and Buffer settings

The tests in scenario 1 compare the backup runtime and CPU usage of an unencrypted database executing an unencrypted backup with an encrypted database executing an encrypted backup.

Effect on Runtime

Figure 1: Influence of parallelization and buffer settings on the backup runtime of encrypted databases and backups

You can reduce the runtime significantly by reducing the settings for parallelization and buffer to lower values (parallelization 4 and buffer 8) compared to the values generated by Db2 automatically. We were able to reduce the runtime to nearly the same value (954 seconds) as for the reference backup (900 seconds). That way the runtime increase using backup encryption is about 6%.

Effect on CPU Usage

The next two graphics show the CPU consumption of the reference backup (900 seconds) and the one with the shortest runtime (954 seconds):

• reference backup:
  • unencrypted backup
  • malloc=no
  • automatic settings for backup parallelization (30) and buffer (60)
• backup with the shortest runtime:
  •  encrypted backup
  • malloc=no
  • manual backup parallelization (4) and buffer (8) settings

Even though we were able to reduce the runtime to a similar value as without encryption the influence on the CPU consumption is quite big. In our case the CPU consumption increases by 69 percent.

Scenario 2: Optimized AIX Malloc Settings

In scenario 2 we tested how the previously described optimized AIX malloc settings are influencing the backup runtime and the CPU consumption. The setup is the same as in scenario1 and we are again the varying the parallelization and buffers settings.

Effect on Runtime

Figure 4: Influence of the optimized AIX malloc settings on the runtime of encrypted databases and backups

For the runtime we can see similar values independent of the setting for parallelization and buffer. These values are all in the same range as the reference runtime without any encryption. The runtime increase is between 7% and 5%. For the CPU comparison we used the backup with the 7% increase – the one with the automatic setting for parallelization and buffer (961 seconds).

Effect on CPU Usage

For the CPU comparison we used the backup with the 7% increase – the one with the automatic setting for parallelization and buffer (961 seconds).

•  reference backup (900 seconds):
  • unencrypted backup
  • malloc=no
  • automatic settings for backup parallelization (30) and buffer (60)
• backup with the automatic settings (961 seconds):
  • encrypted backup
  • malloc=yes
  • manual backup parallelization (4) and buffer (8) settings

 The influence on the CPU consumption is still quite big but significant smaller than without the optimized AIX malloc settings. In our case the CPU consumption increased by 43 percent.

Exceptional Use Case

The following use case may be not the most usual one as the database will not be secured by encryption. But it may be a first step to use encryption and increase security. We used encryption only for the backups. As they may be transferred to several storage locations and may be used to build test and development systems. This can be implemented without any impact on the end users, and it increase security.

Runtime of Unencrypted Databases with Encrypted Backups

Encrypted backup runtimes stay the same for unencrypted databases independent if the AIX optimized malloc settings are used or not. The runtime is also independent of the settings for parallelization and buffer.

CPU reduction of Unencrypted Databases.

Using the AIX optimized malloc settings for an encrypted backup of an unencrypted database reduces the CPU consumption from 29,7% to 26,6% and comes close to the value of 24,1% for an unencrypted backup. Bottom line the CPU increase could be limited to 10% when encrypting the backup.

Conclusion

On AIX, there is a significant impact on backup processing when using Db2 native encryption and if default settings are used. This impact is showing up in either backup duration or CPU usage.

The backup throughput and so the duration can be optimized by the AIX malloc settings or a lower backup parallelism setting.

The CPU overhead remains with all optimizations enabled but can be reduced using the AIX malloc settings. 

Recommendations

• Use the AIX optimized malloc settings for encrypted databases and rely on the automatic settings for the backup.

• Manually reduce the parallelization and buffer values in the backup command if the AIX optimized malloc settings cannot be used for encrypted databases.

This can be the case if you are already short on memory and therefore additional memory required for service tools is not available.