Optimizing communication performance
As a database administrator, you want to tune network parameters to optimize
communications performance for the various server connections.
Connections are made to the server or alias, and these are tuned
DBSERVERALIASES server configuration
parameters each correspond to a line in the sqlhosts file. The
protocol (second field) for that line maps the connection to a
NETTYPE entry by matching the protocols.
NETTYPE entries to tune. The
NETTYPE definition is shown in Listing 6.
Listing 6. NETTYPE definition
NETTYPE configuration designates the
virtual-processor class (
VP_class) on which
the poll threads will do their work. Poll threads process incoming
connection requests and pass the requests to listener threads, which
accept the client connection and start an sqlexec thread. Poll threads
can run either inline on CPU virtual processors (VPs) or on NET
(network) VPs. Poll threads generally run more efficiently on CPU VPs
for single-CPU machines. On a multiprocessor computer with a large
number of remote clients, however, running them on a NET VP might get
You can tune
conn_per_thread for the size of the
connection load you expect the database server to handle. One poll
thread is usually sufficient for smaller systems. For systems that can
have 200 or more concurrent network users, better performance might
result from adding more poll threads. With high user load, you might
need to experiment to determine the optimal configuration of poll
threads (whether inline or running on NET VPs).
Note that there is one network virtual processor created for each poll
NETTYPE in Listing 7 configures
3 poll threads on 3 soc virtual processors for a total of 600
Listing 7. Example NET VP definition
The database server can add resources for additional network connections as needed. Listing 7 does not limit the sockets connections to 600. However, in the case of shared memory connections, the number configured is a hard limit.
NETTYPE is not configured, the default
is to run the poll thread for the protocol used for the
DBSERVERNAME server in-line (CPU VP) and
DBSERVERALIASES on network virtual
processors spawned for the designated protocol type. Because one poll
thread runs on one virtual processor, Informix spawns additional
network virtual processors if there are more poll threads configured
than CPU virtual processors.
If you expect a large number of connections, and your operating system
supports fast polling, enable
fast polling your network and optimal connection performance, as shown
in Listing 8.
Listing 8. FASTPOLL command
Listener threads authenticate users, establish connections to the
database server, and start sqlexec threads. You can add listener
threads if you feel that the server is not handling connection
requests satisfactorily. A listener thread is dedicated to the port on
which it runs. You will need an additional
DBSERVERALIASES parameter to specify a
dbservername for each additional port and a corresponding line in the
sqlhosts file with the correct protocol and a unique port.
There might be a situation when a network-interface card for the host computer cannot handle the desired connection throughput. Or, there might be a need to connect the database server to more than one network. In such cases, you can add a network-interface card.
To support multiple network-interface cards, you must assign each card a unique hostname or network address. After you add a network card, add an additional listener thread to run on that card. The database server will use the hostname entry in the sqlhosts file to determine which card to use.
The database server attempts to optimize connection memory use. Client requests prompt network memory buffer allocation from the global memory pool, and when the buffers are freed, they remain in a pool. This prevents unnecessary reallocation. This free buffer pool is available to several protocols, including SOCTCP, IPCSTR, and TLITCP. There is a mechanism for returning buffers from the free pool to the global memory pool to avoid needless allocation of memory when use is down. The server calculates a free buffer threshold, and when that number is exceeded, the buffers are returned to the global pool.
The database server uses the formula in Listing 9 to calculate the threshold for free buffers in the network buffer pool.
Listing 9. Formula for threshold for free buffers
free network buffers threshold = 100 + (0.7 * number_connections)
number_connections value should be the third field of the
The database server sums the calculated number for the various protocols that use the free buffer pool. That sum is compared to the number of buffers in the pool. If the buffer number in the free pool is larger than the summed thresholds, then buffers are returned to the global pool.
Sizing network buffers to accommodate a typical request can improve CPU utilization by eliminating the need to break up a request into multiple messages. Proceed with caution, however, because the database server dynamically allocates network buffers of the indicated sizes for all active connections. If too large a size is configured, then a large amount of memory can be consumed.
The database administrator can control the size of each buffer using either of the following methods, which are explained in detail.
IFX_NETBUF_SIZEenvironment variable, and
b(client buffer size) option in the sqlhosts file or registry
IFX_NETBUF_SIZE environment variable
specifies the size of each network buffer in the common network buffer
pool and the private network buffer pool. The default buffer size is
IFX_NETBUF_SIZE to configure a larger
buffer size reduces the amount of overhead required to receive each
packet. If you know that clients send packets greater than 4KB in size, then
IFX_NETBUF_SIZE to the average
packet size. Clients might send large packets during any of the following
- Table loads
- Rowsize greater than 4KB
- Sending simple large objects
b option for sqlhosts corresponds to the
IFX_NETBUF_SIZE parameter. The value for
the sqlhosts option should typically match the value for
The database server provides a private network buffer pool for each session that uses SOCTCP, IPCSTR, or TLITCP network connections. The buffers in these pools are allocated from the pools mentioned earlier: the global pool and the free pool.
In an environment in which many connections and sessions are constantly active, these private network buffers provide the following advantages:
- Less contention for the common network buffer pool
- CPU resources can be conserved, because it is not necessary to allocate and deallocate network buffers to and from the common network buffer pool for each network transfer. These buffers will be statically available.
The size of the private network pool for each session is specified by
variable. The default size is 1 buffer.
onstat options in Table 4 to monitor the network buffer usage.
Table 4. Monitoring network buffers with onstat
|Option||Output field||Field description|
|onstat -g ntu||q-pvt||Current number and highest number of buffers that are free in the private pool for this session|
|onstat -g ntm||q-exceeds||Number of times the free buffer threshold was exceeded|
onstat -g ntu option displays the
format for the q-pvt output field, as shown in Listing 10.
Listing 10. q-pvt output field
current number / highest number
If the number of free buffers (value in q-pvt field) is consistently 0, you can perform one of the following actions:
- Use the environment variable
IFX_NETBUF_SIZEto increase the size of each buffer
- Use the environment variable
IFX_NETBUF_PVTPOOL_SIZEto increase the number of buffers
The q-exceeds field indicates the number of times that the threshold for the shared network free-buffer pool was exceeded. When this threshold is exceeded, the database server returns the unused network buffers (over this threshold) to the global memory pool. Optimally, this value should always be either 0 or a low number. This is an indicator that the server is not allocating or de-allocating network buffers.
You can use the
onstat command in Listing 11 to see the network buffer size.
Listing 11. Onstat command for network buffer size
onstat -g afr global | grep net
The size field in the output shows the network buffer size in bytes.