The best way to improve overall performance in a distributed database environment is to eliminate delays from the network. It is common for network administrators to consider a network to be more efficient if it collects as much data as possible between transmissions. This approach doesn't work for applications such as distributed databases because it builds delays into the network. The end-user doesn't see the efficiency of the network, only the delays.
Most network devices have delay parameters, and most of them default to values that are very bad for distributed databases. To improve performance you should locate these parameters and if possible, set them to zero. In addition you should ensure that the buffer size on the device is large enough to prevent retransmits due to lost data. For instance, UNIX systems typically have a Transmit or Receive queue depth default of 32. For better results, set the queue depth to 150. A corresponding parameter on DLC settings is the Receive Depth, which should also be 150.
The IOBUF parameter is set too low at most sites. It is usually set at 500, but experience has shown that a value of 3992 works best if you are moving large amounts of data, especially for channel connections such as ESCON or 3172.
For SNA connections, you should set the Mode Profile of any workstation software to 63. In general, receive pacing values throughout the network should be set to their highest value, so the VPACING and PACING parameters on the DB2 APPL statement, and the PU/LU for the workstation in a switched major mode should also be set to 63. What this will do is allow the amount of message flows before the sender must wait for a response to increase progressively.
On a LAN system the DLC or LLC transmit and receive window sizes can have a dramatic effect on performance. The send value should be set to seven or more, and for most configurations a receive value of four or less works best.
If you are running Ethernet, you should set the TCP segment size to 1500 bytes. On a token ring or FDDI network this value should be 4400 bytes, and if you are using an ESCON adapter with TCP/IP, the segment size should always be 4096.
Finally, for TCP/IP networks, the TCP Send and Receive buffer sizes should be set higher than 32768. A value of 65536 is generally best.
| Note: |
Establishing a connection from the gateway to the server (outbound connection) is much more expensive than establishing a connection from a client to the gateway (inbound connection). In an environment where thousands of clients frequently connect to and disconnect from the server through the gateway, a substantial amount of processing time is spent establishing outbound connections. DB2 Connect provides connection pooling over TCP/IP. When a client requests disconnection from the server, the gateway drops the inbound connection with the client, but keeps the outbound connection to the server in a pool. When a new client comes into the gateway to request a connection, the gateway provides an existing one from the pool thus reducing the overall connection time and saving the high CPU connect cost on the server. For more information about connection pooling under DB2, refer to the DB2 Administration Guide. |
A summary of network performance tuning methods is provided in the
following table.
| What to Look For | Example | Setting | Notes |
| Deliberate Delays | Delay parameters on network devices | Set to 0 | Defaults are usually higher. |
| Buffers | IOBUF parameter | Set up to 3992. | Particularly useful for ESCON or other channel adapter. |
|
| RUSIZE | Optimum size is 4096 | Setting RUSIZE and RQRIOBLK to same size may give best performance. |
|
| Pacing | VPACING, PACING, and Mode Profiles should be set to 63. | Use adaptive pacing where applicable. |
| Adapter Settings | Transmit/Receive queue depth | Recommended value is 150. | Default is usually 32. |
|
| DLC Windowing on SNA | Set transmit window size high (>7). Set receive window size low (eg. at 1), test and increment repeatedly to find ideal value. | Every logical device adds delays. Simply network topology as much as possible. |
| TCP Settings | Segment Sizes | 1500 on Ethernet, 4400 on token ring and FDDI. | ESCON adapters used for TCP/IP should always be set to 4096. |
|
| Send/Receive Space Sizes | Should be 64K for both. | Default is only 8192 for Windows. Can be set in the Windows registry. |
The following considerations relate to the hardware:
Performance improves with a faster transmission medium. For example, the following are some typical raw data transfer rates:
The data transfer rate is limited by the slowest transmission medium in the path to the host or AS/400 database server.
You should carefully plan the memory usage of the network adapter and communication controller. In addition, you should work with a network specialist to ensure that the controller has the capability to handle the extra traffic generated by DB2 Connect.
If data crosses from LAN to LAN, and from one SNA Network to another SNA Network, consider the travel time. Bridges, routers, and gateways will add to the elapsed time. For example, reducing the number of bridges that are crossed reduces the number of hops required for each request.
The physical distance between nodes should also be considered. Even if a message is transferred by satellite, the transfer time is limited by the speed of light (3 * 10**8 m/s) and the round-trip distance between the sender and receiver.
If the bandwidth of the network has been fully utilized, both the response time and the data transfer rate for a single application will decrease.
Congestion can occur in the network when data accumulates at a particular part of the network; for example, at an old NCP with a very small buffer size.
If the error rate of the network is high, the throughput of the network will decrease and this will cause poor performance because of data re-transmission.