Performance tuning

The simplest statement that can be made about the impact of Db2® native encryption is that it effectively reduces the physical I/O bandwidth on the Db2 system. How your workload reacts to this change determines the impact to its overall performance.

Determining the actual impact on workload depends on a number of factors, such as:
  • The amount and speed of CPU available for encryption and decryption, or the existence of CPU hardware acceleration support.
  • The amount of buffer pool page reuse by the workload or, how often the workload brings in new pages or forces old ones out.
  • The volume of physical read or write operations in the buffer pool relative to the throughput efficiency of the background processes.
  • The amount of non-buffer pool I/O such as LOBs.
The biggest factor in reducing the impact of Db2 native encryption is the existence of hardware acceleration that can be used by Db2. After the use of hardware acceleration, the next best way to further reduce the impact on workload is to do the following, where possible:
  • Shelter the workload from the physical I/O by reducing I/O wait through normal tuning actions where possible. For example, increase the buffer pool size to avoid having queries that are waiting on physical I/O.
  • Introduce parallelism for any work that is doing the physical I/O.
The latter recommendation comes from the fact that the number of cores that are being used for encryption purposes is limited to the actual number of threads that are doing physical I/O. While each thread runs on a single core and uses as much CPU processing power as it can, that core might not be fully used, nor might all available cores be in use. So, it is possible to have a situation where you still have CPU available to do work while your system is encountering more I/O wait time on physical I/O due to encryption. The only way to overcome this behavior is, where possible, to increase the number of threads that are doing the physical I/O. This means that you might find it helpful to increase the parallelism of utilities and the background infrastructure in Db2, such as page cleaners, prefetchers, and so on..

Do a full performance tuning exercise on a newly encrypted system, as new and possibly different bottlenecks could be introduced from the reduced physical I/O volume. Follow the normal Db2 tuning exercises to ensure that I/O latency is reduced. If excess CPU capacity exists, revisit areas where physical I/O bottlenecks or latency exist to see whether parallelism can be increased in those areas. For more information on tuning Db2, refer to Performance Overview and Tuning and Monitoring Database System Performance.