Multiplexed connections and CPU utilization

Many traditional nonthreaded SQL client applications use multiple database connections to perform work for a single user. Each database connection establishes a separate network connection to the database server. The multiplexed connection facility provides the ability for one network connection in the database server to handle multiple database connections from a client application.

Multiplexed connections enable the database server to create multiple database connections without consuming the additional computer resources that are required for additional network connections.

When a nonthreaded client uses a multiplexed connection, the database server still creates the same number of user sessions and user threads as with a nonmultiplexed connection. However, the number of network connections decreases when you use multiplexed connections. Instead, the database server uses a multiplex listener thread to allow the multiple database connections to share the same network connection.

To improve response time for nonthreaded clients, you can use multiplexed connections to execute SQL queries. The amount of performance improvement depends on the following factors:
  • The decrease in total number of network connections and the resulting decrease in system CPU time

    The usual cause for a large amount of system CPU time is the processing of system calls for the network connection. Therefore, the maximum decrease in system CPU time is proportional to the decrease in the total number of network connections.

  • The ratio of this decrease in system CPU time to the user CPU time

    If the queries are simple and use little user CPU time, you might experience a sizable reduction in response time when you use a multiplexed connection. But if the queries are complex and use a large amount of user CPU time, you might not experience a performance improvement.

    To get an idea of the amounts of system CPU time and user CPU times per virtual processor, use the onstat -g glo option.

To use multiplexed connections for a nonthreaded client application, you must take the following steps before you bring up the database server:
  1. Define an alias using the DBSERVERALIASES configuration parameter. For example, specify: 
    DBSERVERALIASES ids_mux
  2. Add an SQLHOSTS entry for the alias using sqlmux as the nettype entry, which is the second column in the SQLHOSTS file. For example, specify: 
    ids_mux  onsqlmux  ......

    The other fields in this entry, the hostname and servicename, must be present, but they are ignored.

  3. Enable multiplexing for the selected connection types by specifying m=1 in the sqlhosts file or registry that the client uses for the database server connection.
  4. On Windows™ platforms, you must also set the IFX_SESSION_MUX environment variable.
Warning: On Windows, a multithreaded application must not use the multiplexed connection feature. If a multithreaded application enables the multiplexing option in the sqlhosts registry entry and also defines the IFX_SESSION_MUX environment variable, it can produce disastrous results, including crashing and data corruption.