Spotfire Server: Information Services shows OutOfMemory error when Redshift Data Sources are used

If your organisation makes use of Amazon Redshift data sources, it’s important to understand how large result sets can impact Information Services in Spotfire. Improper handling of large queries can lead to java.lang.OutOfMemoryError (OOM) and application instability.

Applications that query Redshift may occasionally retrieve millions of rows. By default, the Amazon Redshift JDBC driver may try to buffer a significant portion of these results in the Information Services JVM heap. This can cause:

• Heap exhaustion leading to OOM errors

• Application slowdowns due to excessive garbage collection

• Unstable concurrent query handling, especially with multiple users

In Spotfire, where multiple queries can run simultaneously, even moderately large result sets can quickly consume all available heap memory. 

Configure the Redshift Ring Buffer

The Redshift JDBC driver provides a ring buffer mechanism to control memory usage:

• enableFetchRingBuffer=true
  Activates asynchronous, bounded prefetching of result rows. The driver fetches rows in the background, but never lets memory usage exceed a set limit.

• fetchRingBufferSize=<size>
  Sets the maximum amount of memory allocated to each query’s buffer (e.g., 200M). Once the buffer is full, the driver pauses network fetches until your application consumes some rows.

To implement this change, add these options to the JDBC connection string for the Redshigft Data Source

E.g.
 

jdbc:redshift://redshift.company.us-west-1.redshift.amazonaws.com:9000/dev;enableFetchRingBuffer=true;fetchRingBufferSize=200M

These can also be added to the data source template for Redshift. The Spotfire service will need to be restart if the data source template is changed.