Spotfire® Server timeout while waiting for database connection after 10 seconds

Symptoms

The Spotfire^® Server may become unresponsive in general and users can intermittently experience issues when performing all types of actions such as:

• Running information links
• Opening the Library
• Listing users
• Logging in

The action will hang for about 10 seconds before it is canceled and the following error message will be logged on the server and sometimes be displayed for the end user to see:

 Timeout while waiting for database connection after 10 seconds

The error messages can also be found in the Spotfire server logs:

• Log for Spotfire^® Server 3.X: \tomcat\logs\dss.log
• Log for Spotfire Server 4.5 or greater: \tomcat\logs\server.log

Cause

Many tasks performed by Spotfire Server require a connection to a data source. The data source can either be a database used by an Information Link (i.e. a data source in the Information Designer) or the database that contains the Spotfire Server repository, also known as the "Spotfire database". The error occurs when all available connections to the particular data source are occupied by other tasks and the server cannot get a connection within 10 seconds, which is the default timeout value. The Spotfire Server maintains a connection pool for each data source with a limited number of concurrent connections, defined by the connection pool size.

Resolution

There are basically two ways to solve the issue, either by reducing the peak load on the data source or by increasing the connection pool size. Only the latter alternative is described in this article.

Identifying the data source

First the correct data source must be identified since there is one connection pool per data source. If the error occurs only when running a particular information link, the issue is likely to originate from the data source(s) used by the information link.

You can get more information about the data sources in the following way:

1. Open https://community.spotfire.com/s/ (make sure to replace your_spotfire_server:port with the values for your TIBCO Spotfire server) in a Web Browser and login with a Spotfire admin account.

2. Go to the tab 'Diagnostics'

3. Expand All > TIBCO Spotfire Server > Data Sources:

All active data sources should be listed but data sources which have not been used for a while might not be there. Each entry starts with its name, which is 'Server.Default' for the "Spotfire database", followed by a snapshot of a number of connection pool counters and their values. The most important ones are:

If mostActiveCount is equal to maxConnections it means connection pool was possibly not sufficiently large for a period of time.

If the issue was related to 'Server.Default' a.k.a. the "Spotfire database"

TIBCO Spotfire Server 3.X:

1. Open \tomcat\webapps\META-INF\database.xml in a text editor

2. Increase the value inside the tag

3. Save

4. Restart the server for the changes to take effect

TIBCO Spotfire Server 4.5 or higher:

1. With the server configuration command line tool, export configuration.xml:

    config export-config

    

2. Open the file configuration.xml in a text editor and change the value for max-connections:

   ...
   <data-sources>
       <default>
         <login-timeout>10</login-timeout>
         <connection-timeout>600</connection-timeout>
         <read-only>false</read-only>
         <auto-commit>true</auto-commit>
         <min-connections>5</min-connections>
         <max-connections>40</max-connections>
         <pooling-scheme>WAIT</pooling-scheme>
         <connection-properties>
           <connection-property>
             <key>MaxPooledStatements</key>
             <value>20</value>
           </connection-property>
         </connection-properties>
       </default>
   </data-sources>
   ...

    

3. Import the configuration:

    config import-config --comment="Increased max-connnections for the default data source"     

    

4. Restart the server

If the issue was related to an information link data sources

1. In Spotfire Professional, start 'Information Designer' from the Tools menu

2. Find the data source element and select 'Edit'

3. Increase the maximum number of connections

4. Save

Note: By setting the parameter to zero or a negative value, connection pooling is effectively disabled and new connections will be continuously created, whenever needed.

More information

The following section discusses three additional settings that impact information links only and not other tasks performed by Spotfire Server.

About 'connection-login-timeout'

When executing an information link and all connections slots for a data source are already in use, the request will wait in a queue until one becomes available or cancelled if the connection-login-timeout is reached, which by default is set to 10 seconds. The connection-login-timeout is a Spotfire Server global setting that can be increased. An information link may then take longer to start but it will eventually complete as long as a connection can be allocated within the connection-login-timeout.

For TIBCO Spotfire Server 3.X, it can be changed from \tomca\webapps\spotfire\local-settings.xml:

...
  <setting>
    <category>spotfire.iis.jdbc</category>
    <name>connection-login-timeout</name>
    <user-editable>true</user-editable>
    <value xsi:type="xsd:integer">10</value>
    <description>Login timeout for JDBC database connections.</description>
  </setting>
...

For TIBCO Spotfire Server 4.5 or higher, the same method as described above can be used for exporting and importing the configuration (see section about Server.Default/Spotfire database). First export the configuration.xml file, change the connection-login-timeout and then import it back again.

...
    <!-- Login timeout for JDBC database connections. -->
    <connection-login-timeout>10</connection-login-timeout>
...

About 'max-jobs' and 'thread-pool-size'

When increasing the max number of connections for a data source you should also consider increasing max-jobs and thread-pool-size because they both pose a limit on how many information links that can be run at any point of time. The maximum number of simultaneous information links (running or waiting in line) is limited by the max-jobs setting. In addition, if there are more jobs than threads, the jobs will be waiting in line for an available thread. If all the jobs are taken, then if another user tries to run an information link, that user will get the "too many jobs" error*. The thread-pool-size setting specifies how many threads will be available to handle running information links.

If max-jobs is set to a large number, all users can suffer as it means that they will all have to compete for the available threads so it takes longer to finish running an information link. If thread-pool-size is too high the server or the database you are retrieving data from can get overloaded. You can test gradually increasing values if needed until you find a good balance.

For TIBCO Spotfire Server 3.X they can be changed from \tomca\webapps\spotfire\local-settings.xml:

...
  <setting>
    <category>spotfire.dat</category>
    <name>max-jobs</name>
    <user-editable>true</user-editable>
    <value xsi:type="xsd:integer">10</value>
    <description>Maximum number of concurrent jobs.</description>
  </setting>

  <setting>
    <category>spotfire.dat</category>
    <name>thread-pool-size</name>
    <user-editable>true</user-editable>
    <value xsi:type="xsd:integer">10</value>
    <description>Maximum number of worker threads.</description>
  </setting>
...

For TIBCO Spotfire Server 4.5 or higher, they can be set in configuration.xml (see above for instructions for how to export and import the configuration):

<!-- Maximum number of concurrent jobs. -->
<max-jobs>40</max-jobs>
<!-- Maximum number of worker threads. -->
<thread-pool-size>40</thread-pool-size>

Note: The max number of connections for an individual data source should never be set to a greater value than the max-jobs setting, as this will just result in the "too many jobs" error when the number of current connections exceed the max-jobs setting.

*Example of "Too many jobs" error in server log (TIBCO Spotfire Server 3.3.X):

INFO 2012-11-01 10:42:25,666 [username, #20] api.common.InformationModelServiceCommon: Server too busy to process job.
com.spotfire.ws.api.common.InformationModelWebServiceException: Server busy.
...
Caused by: com.spotfire.ws.dat.DatException: Too many jobs.

Environment

• TIBCO Spotfire^® Server 3.X and newer

Inherently it can also affect any Spotfire client such as:

• TIBCO Spotfire^® Professional
• TIBCO Spotfire^® Web Player
• TIBCO Spotfire^® Automation Services