Error: Unable to open database connection. Network error IOException: Address already in use

Document created by chris_stevens Employee on May 11, 2016
Version 1Show Document
  • View in full screen mode

Error

Unable to open database connection. Network error IOException: Address already in use.

 

Cause

You may get this error when trying to access a local database server on Windows.

 

Solution

When running a large volume of data through maps that have multiple functions, Windows does not close connections fast enough which may cause the Network I/O exception.

  • Try stopping the Atom(s), wait a few minutes, then restart them. This could release some of the connections being made to the database.
  • Try restarting the database service the Atom(s) are connecting to
  • Modify the following two system key values in the Windows registry

The error occurs because the database cannot open enough connections for the number of queries being requested. It is possible that the Atom(s) have reached the limit of the number of connections that can be handled by the database with Atomsphere.

 

MaxUserPort

This one modifies the range of ports that Windows uses to open connections. By default it only allows up to the port 5000. By modifying this value, Windows will be able to open up more ports before having to recycle back to the beginning. Every connection uses a port, so it starts at 1025 and goes up to this value. When it reaches the max value it goes back to 1025 and tries to open up that port again.

 

System Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

Name: MaxUserPort

Type: REG_DWORD

Value: 5000-65534

 

TcpTimedWaitDelay

This will "release" closed ports faster. By default Windows leaves a port in a TIME_WAIT state for 240 seconds. This can cause problems if the MaxPort value is set to where a new connection will use an "older" port that has not been removed from TIME_WAIT state yet. By decreasing this value, you allow the connections to be released faster.

 

System Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\ParametersValue

Name: TcpTimedWaitDelay

Data: REG_DWORD

Value: 30-300

  • Enable Connection Pooling on all Database Connections

This should help Atomsphere to better manage the available connections. In addition, you may be able to change existing functions so that they no longer make calls against a database. For example, any SQL lookups you may be using to perform if/else or case logic, can probably be converted to a Javascript Function in Atomsphere.

 

Any changes made to the process (either enabling connection pooling or changing functions) would need to be deployed for them to take affect in the Manage executions.

 

You can enable the Connection Pooling by editing your Database Connection. Once in the Database Connection, you will go to the Connection Pool tab. There, check the Enable Pooling box, configure the following:

  • Maximum Connections to 200
  • Minimum Connections to 0
  • Maximum Idle Time to 140
  • Leave When Exhausted Action at Wait for connection
  • Maximum Wait Time to 140.

After making these changes, deploy the processes using that connection so they are updated with these new settings.

 

If you need to roll back these changes, you have a couple of options:

  • Edit the Connection, go to the Connection Pool tab, and uncheck the Enable Pooling option. Save everything, then deploy the processes again.
  • Re-deploy a previous version of the process that was not using a connection with pooling enabled.
1 person found this helpful

Attachments

    Outcomes