TIBCO Spotfire Statistics Services (TSSS) throws OutOfMemoryError during DbCleanupService run.

Date Posted: 2019-01-10
Product: TIBCO Spotfire®

Problem:

Solution:

As part of scheduled clean up activity, the DbCleanupService that is part of the Periodic cleanup component in the TIBCO Spotfire Statistics Services (TSSS) architecture gets triggered periodically. The frequency to run DbCleanupService is controlled by the db.cleanup.time.to.live.days property, which is set in the spserver.properties file.

By default the value for the property is set to 7 days. That means 7 days has to elapse before a job in the TSSS job database is considered old enough to delete when the scheduled cleanup runs. If the TSSS instance has a considerable number of jobs submitted (approximately several thousand), during a DbCleanupService run Java heap memory may cross its maximum limit, throwing a “java.lang.OutOfMemoryError: GC overhead limit exceeded” error in the SplusServer.log. That means the amount of Java Heap memory configured to use by TSSS, either the default or a custom configured value, is not sufficient to perform cleanup of thousands of jobs submitted over the last 7 days.

The following error can be seen in SplusServer.log:

2018-08-03 12:56:01,797 | INFO  |  | DbCleanupService | Cleaning the jobs database
2018-08-03 12:56:33,940 | ERROR |  | TransactionInterceptor | Application exception overridden by rollback exception
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [delete from SPLUS_JOBS where TIME_CREATED < ? and not STATUS in(0,1,100,101)]; SQL state [90108]; error code [90108]; Out of memory.; SQL statement:
delete from SPLUS_JOBS where TIME_CREATED < ? and not STATUS in(0,1,100,101) [90108-129]; nested exception is org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement:
delete from SPLUS_JOBS where TIME_CREATED < ? and not STATUS in(0,1,100,101) [90108-129]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	-
	-
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement:
delete from SPLUS_JOBS where TIME_CREATED < ? and not STATUS in(0,1,100,101) [90108-129]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:317)
	at org.h2.message.DbException.get(DbException.java:157)
	-
	-
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:812)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
	... 25 more
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded

There are multiple ways of resolving the issue. Try one of the following two options:

Option 1.

One way to address an OutOfMemoryError during DbCleanupService is to increase the Java maximum memory heap size in the TSSS server process.

On Linux, this can be done by changing the X_MEM property in the SPSERVER_HOME/init.d/spserver script.
For example, if existing values for X_MEM are (-Xms128M -Xmx512M), then to start, double the values as (-Xms256M -Xmx1024M).
Save the file after the change and restart TSSS on Linux.

On Windows, you can specify memory specific java properties inside (SPSERVER_HOME/tomcat/bin/TSSS<service_name>w.exe).
Here, SPSERVER_HOME is server installation directory.

Launch executable with elevated permissions with "Run as administrator". For example:
User-added image

Save the changes and restart TSSS.

Option 2.

Modify the DbCleanupService trigger frequency from its default value of 7 days to a lower value.
For example, set it to 3 by adding the following property in spserver.properties.