How to tune DB2_FMP_COMM_HEAPSZ for federation FMP

Created by Tony Liu on Tue, 09/01/2020 - 09:54
Published URL:
https://www.ibm.com/support/pages/node/6325293
6325293

Release Notes


Abstract

Before IBM® Db2® V11.5.4 the default FMP communication buffer size is 60KB. Federation server improves the performance by increasing the default FMP communication buffer size to 2MB. Such changes affect the upper limit of db2fmp processes or threads. This article explains how to tune the configurations of IBM® Db2® to adapt these changes.

Content

With more customers start to using IBM®Db2® federation server, the performance of federation server becomes the major impact to the customer. While we examine the potential performance issue, we found that the FMP communication buffer is one of them. After analysis of some typical nickname query, we found transporting the data from Db2® FMP process or thread to Db2® engine are the bottleneck. The FMP process or thread is responsible to fetching data from remote database, and then transporting the data through FMP communication buffer to Db2® engine for further processing. Normally the ODBC/JDBC driver supports block fetching and the fetching size can be determined by the ODBC/JDBC client (federation). Federation determines the fetching size by the FMP communication buffer size. So given a large table to be fetched, since the FMP communication buffer size is too small, the result sets must be transported multiple times. And this multiple time transportation involves multiple times of context switch between Db2® engine and FMP process or thread, which are the bottleneck of overall nickname query. So we decided to increase the default FMP buffer size.

Before Db2® V11.5.4, the default FMP communication buffer size is determined by this database management configuration parameter ASLHEAPSZ. The default value of ASLHEAPSZ is 15. This parameter controls the communication buffer size of all kinds of Db2® FMP including federation FMP. In Db2® V11.5.4, the default value of federation FMP communication buffer size is not determined by ASLHEAPSZ and has been changed to 2MB. 2MB is the minimal value of federation FMP communication buffer size, if the value of ASLHEAPSZ is larger than 512 (2MB) then the federation FMP communication buffer size still determined by ASLHEAPSZ.

The reason of setting the default value of federation FMP communication buffer size to 2MB is that we have done some performance testing and comparison between different size of FMP communication buffer, 2MB is the best one in terms of balancing the buffer size and performance.

When customer upgrades Db2® version to Db2® V11.5.4, given these changes, they might need to tune some parameters to adapt these changes. Especially after upgrading "Out of memory" error might occur if there are lots of Db2® FMP processes or threads involved in customer's Db2® instance. For example, the following messages in db2diag.log file show this error.

  • 2020-08-24-07.00.53.504439-300 E394315E820 LEVEL: Warning PID: 19274 TID: 48112894732032 PROC: db2sysc 0 INSTANCE: db2inst1 NOD: 000 DB: SAMPLE APPHDL: 0-59604 APPID: xxx.xxx.xxx.xxx UOWID: 12 ACTID: 4 AUTHID: DB2INST1 HOSTNAME: XXX EDUID: 18335 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe: 30 DATA #1: Out of memory failure for Undefined Heap on node 0. Requested block size: 4202516 bytes. Physical heap size: 260898816 bytes. Configured heap size: 23003136 bytes. Unreserved memory used by heap: 0 bytes. Unreserved memory left in set: 0 bytes.
  • 2020-08-24-07.00.53.504599-300 I395136E599 LEVEL: Error PID: 19274 TID: 48112894732032 PROC: db2sysc 0 INSTANCE: db2inst1 NODE: 000 DB: SAMPLE APPHDL: 0-59604 APPID: xxx.xxx.xxx.xxx UOWID: 12 ACTID: 4  AUTHID: DB2INST1 HOSTNAME: XXX EDUID: 18335 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe: 910 DATA #1: LocalOOM FODC Event being generated due to Out of Memory error
  • 2020-08-24-07.01.26.562487-300 I423859E720 LEVEL: Event PID: 19274 TID: 48112894732032 PROC: db2sysc 0 INSTANCE: db2inst1 NODE: 000 DB: SAMPLE APPHDL: 0-59604  APPID: xxx.xxx.xxx.xxx UOWID: 12 ACTID: 4  AUTHID: DB2INST1 HOSTNAME: XXX  EDUID: 18335 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe: 20 STOP: AUTOMATIC FODC: Memory: success IMPACT: None DATA #1: String, 122 bytes Check /db2home/db2inst1/sqllib/db2dump/FODC_Memory_2020-08-24-07.00.53.504671_19274_18335_000/ for diagnostic information.
  • 2020-08-24-07.01.26.562597-300 I424580E814 LEVEL: Error PID: 19274 TID: 48112894732032 PROC: db2sysc 0 INSTANCE: db2inst1 NODE: 000 DB: SAMPLE APPHDL: 0-59604  APPID: xxx.xxx.xxx.xxx UOWID: 12 ACTID: 4  AUTHID: DB2INST1   HOSTNAME: XXX EDUID: 18335 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe: 50 MESSAGE: Insufficient memory available for IPC communication with the db2fmp process. Use the DB2_FMP_COMM_HEAPSZ registry variable to adjust the amount of memory available for fenced routines. DATA #1: Hex dump, 4 bytes 0x00002BC227FFC1B0: 3B00 0F8B;...
  • 2020-08-24-07.01.26.562668-300 E425395E754 LEVEL: Warning PID: 19274 TID: 48112894732032 PROC: db2sysc 0 INSTANCE: db2inst1 NODE: 000 DB: SAMPLE APPHDL: 0-59604  APPID: xxx.xxx.xxx.xxx UOWID: 12 ACTID: 4 AUTHID: DB2INST1  HOSTNAME: XXX  EDUID: 18335 EDUNAME: db2agent (SAMPLE) 0 FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe: 50 MESSAGE: ADM11002E Insufficient shared memory available for communication with the db2fmp process. Use the DB2_FMP_COMM_HEAPSZ registry variable to increase the amount of shared memory available for fenced routines.
The reason of the "Out of memory" error is the default size of federation FMP communication buffer has been changed to 2MB, which is larger than previous value 60KB. Prior Db2® V11.5.4, the calculation method of the upper limit of Db2® FMP that an Db2® instance allowed is: 
Number of db2fmp processes + Number of db2fmp threads = DB2_FMP_COMM_HEAPSZ / (2 * ASLHEAPSZ)
In Db2® V11.5.4, since the default federation FMP communication buffer size is not determined by ASLHEAPSZ, the calculation method changes to: 
Number of db2fmp processes + Number of db2fmp threads = DB2_FMP_COMM_HEAPSZ / (2 * 512)
Note, this calculation is base on the assumption that all Db2® FMP is Federation FMP. We can see the upper limit of Db2® FMP is less than the number prior Db2® V11.5.4. After upgrading to Db2® V11.5.4, given the same Db2® FMP requirement, customer will hit the "Out of memory" error.
To resolve this issue, this registry variable "DB2_FMP_COMM_HEAPSZ" can be tuned to larger value. The default value of this registry variable is 20MB, but the heap is allowed to grow up to 256MB (65536 * 4K), the upper limit is 1GB. (For Db2 version later than v11.5.6, it is able to set the upper limit bigger than 1GB, see IT36093 for details). According to the actual Db2® FMP requirement, customer can set this registry variable to any value that within the upper limit.
A new registry variable "DB2_FED_FMP_COMM_BUF_SIZE" is introduces in Db2® V11.5.5.1. This variable can control the federation FMP communication buffer size, the unit of this variable is 4KB. Customer can set this variable to specify the Federation FMP communication buffer size. For example, "db2set DB2_FED_FMP_COMM_BUF_SIZE=15" will make the Db2®  behaves same with Db2® V11.1.
Note: This technote applies to wrappers with DB2_FENCED Y option, because the federation FMP is only used by fenced wrappers.

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Connectivity-\u003EFederation"},{"code":"a8m500000008PktAAE","label":"Routines - Stored Procedures and UDF"},{"code":"a8m500000008PmhAAE","label":"Routines - Stored Procedures and UDF-\u003Edb2fmp"},{"code":"a8m0z0000001gNyAAI","label":"Runtime"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.5.4;and future releases"}]

Document Information

Modified date:
08 October 2024

UID

ibm16325293

Page Feedback