JS7 JobScheduler

Space shortcuts

Page tree

Introduction

The JS7 makes of the Hibernate access layer to support a number of DBMS products, see JS7 - Database.

Optimization of database performance frequently includes both sides, the JOC Cockpit and the database server. The below recommendations are targeted at JOC Cockpit.

JDBC Driver

JS7 ships with a number of JDBC Drivers that are compliant to use with the Open Source license. For the list of JDBC Drivers see JS7 - Database.

We select JDBC Driver versions according to compatibility with supported versions of the DBMS and according to available fixes for bugs and vulnerabilities. There can be reasons why users apply a JDBC Driver of their choice:

  • Database vendors might offer newer versions of JDBC Drivers that bring better performance for specific versions of the DBMS.
  • Database vendors might offer specific settings to improve performance for use of the DBMS. Such settings typically are added to the JDBC URL from query parameters.

Maximum Pool Size

JS7 makes use of a connection pool that will establish a number of parallel connections to the DBMS. The number of connections is specified with the hibernate.cfg.xml configuration file like this:

Hibernate configuration example 
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  ...
  <property name="hibernate.hikari.maximumPoolSize">10</property>
  ...
 </session-factory>
</hibernate-configuration>


The default value of 10 parallel connections works for most situations. However, if a larger number of users connects to JOC Cockpit then it will be preferable to increase the value. Consider that the DBMS must be configured to allow a higher number of parallel sessions and to dispose of resources such as memory to handle the sessions.

Row Fetch Size

The Row Fetch Size determines the number of rows that are returned in a single batch when fetching data from a result set. A higher value reduces the number of round-trips between DBMS and client.

When fetching data from a result set then they are stored in JOC Cockpit's Java heap space. Memory load depends on the data type: a UTF-8 character datatype, for example Oracle®'s NVARCHAR2, will consume 4 bytes per character. For example, a Row Fetch Size of 100 for a query that returns 20 columns each using NVARCHAR2(255) will result in 2 MB heap space consumption.


Hibernate configuration example 
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  ...
  <property name="hibernate.jdbc.fetch_size">100</property>
  ...
 </session-factory>
</hibernate-configuration>


The setting is not available by default. Instead, each JDBC Driver brings its individual default value, for example the Oracle® JDBC Driver's default value is 10.

Users report tremendous performance improvements when increasing the value as it fetches larger chunks of data from result sets with fewer round-trips. There is a price for better performance:

  • Memory requirements of JOC Cockpit will increase. Consider to adjust the -Xmx Java option that specifies the Java heap size of JOC Cockpit, see JS7 - FAQ - Which Java Options are recommended.
  • The setting applies to all queries executed by JOC Cockpit. For queries with small result sets there will be an overhead of unused memory that is allocated by the setting. Queries that provide larger result sets such as queries related to the JS7 - Daily Plan and to the History will benefit from higher values of the setting.
  • The precise value is specific for a DBMS product and for the number of records that are assumed for the Daily Plan and History. The retention period specified for the JS7 - Cleanup Service sets the limits for Daily Plan and History records in the database. It is recommended to increase the value of the setting step-by-step, for example 100, 1000 etc. and to check improvement of JOC Cockpit response times.



  • No labels