Database¶

Genius Server supports different types of databases. This section explains how to configure the communication between the Genius Server and the chosen database.

Encryption¶

Microsoft JDBC Driver 4.0 for SQL server provides a database connection through SSL encryption. To allow encrypted connections, enter the following property strings in JDBC Driver and URL fields:

JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://<database-ip>:1433;databaseName=<database-name>;encrypt=true;trustServerCertificate=false;instance=<yourinstance>

In production environments use trustServerCertificate=true; as a parameter in the JDBC URL field.

For further details, refer to the Microsoft documentation .

To add a trusted certificate to the Genius Server, refer to the Certificate Provider (Conf) section.

JDBC Connection¶

JDBC Driver: the Java DataBase Connectivity (JDBC) Driver class to interact with a database.
JDBC URL: the connection string to connect to a database.
Database User: the user authorized to connect to the configured database.
Database Password: the password to access the database. To change the password, press the Change password button and enter the new password twice.

[Advanced] Frontend Connection Pool¶

Initial pool size: the initial number of connections allocated at the server start up. The value should lie between Min Pool Size and Max Pool Size.
Min pool size: the minimum number of connections that can be established.
Max pool size: the maximum number of connections that can be established.
Max idle time: the maximum number of seconds after which a connection is discarded if unused. If this is set to zero, the connection is never discarded.
Checkout timeout: the number of milliseconds that a client waits for a connection to be checked-in or acquired when the pool is exhausted. Zero means to wait indefinetely. Any positive value will lead to a time-out after the configured time and an SQL exception.
Max Statements Per Connection: the maximum number of c3p0 cached statements per connection. If it is set to zero, the statement caching is disabled.
Idle connection test period: the number of seconds after which a check on all idle connections is run. If set to 0, no checks are run.
Acquire increment: this setting determines how many connections at a time c3p0 tries to acquire when the pool is out of slots.
Acquire Retry Attempts: this setting defines how many times c3p0 tries to acquire a new connection from the database before giving up. If this value is less than or equal to zero, c3p0 keeps trying to fetch a connection indefinitely.
Acquire Retry Delay: the number of milliseconds c3p0 waits between acquire attempts.
Break After Acquire Failure: if checked, the DataSource considers itself broken after a failed round of connection attempts, and next connection requests fail immediately.
Max Connection Age: the number of seconds after which the connection lifetime ends and is destroyed. If it is set to zero, connections are not destroyed.
Max Idle Time Excess: the number of seconds of grace period before a connection is discarded after the Max idle time is reached. If it is set to zero, the excess idle time is disabled.
Test Connection on Check-in: if checked an asynchronous test will be performed at every connection check-in. It will check with the connection pool if the connection is valid.
Test Connection on Checkout: if checked, an asynchronous test will be performed at every connection check-out. It will check with the connection pool if the connection is valid.
Unreturned Connection Timeout: the number of seconds after which an unreturned connection (a connection that remains checked-out) is destroyed. If it is set to zero, unreturned connection timeout is disabled. If a non-zero value is set, it should be a value higher than any connection, which should reasonably be checked-out. Otherwise, the pool occasionally kills the connections in active use.
Debug Unreturned Connection: if checked, and if Unreturned Connection Timeout is set to a positive value, the pool captures the stack trace (through an exception) of all connection checkouts. The stack traces are printed at the unreturned checked-out connections timeout. This is intended to debug applications with connection leaks. Use it for applications that occasionally fail to return connections, which leads to the pool growth, and eventually exhaustion (when the pool hits Max Pool Size with all connections checked-out and lost). This parameter should only be set while debugging, as capturing the stack trace slows down every connection check-out.
Number Helper Threads: c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. Spreading these operations over multiple threads can significantly improve performance, allowing multiple operations to be performed simultaneously.
Statement Cache Num Deferred Close Threads: if set to a value greater than 0, the statement cache tracks connections when they are in use, and destroys statements when their parent connections are no longer in use.
Max Administrative Task Time: the number of seconds after which c3p0 tries to interrupt an apparently stuck task. It is rarely used.
Privilege Spawned Threads: if checked, c3p0-spawned threads have java.security.AccessControlContext associated with c3p0 library classes. This setting can help if there are problems with the garbage collector or if client threads lack sufficient permission to perform operations which are required by c3p0.
Context Class Loader Source: it specifies how a context class loader is determined. It can be caller, library, none. Caller means that c3p0 inherits the context class loader from the client thread, which has initialized the pool. Library means that c3p0 loads the context class loader. None means that any context class loader is set.
Property Cycle: the maximum number of seconds after which user configuration constraints are enforced. It determines how frequently Max Connection Age, Max Idle Time, Max Idle Time Excess Connections, Unreturned Connection Timeout are enforced.

[Advanced] Backend Connection Pool¶

Initial pool size: the initial number of connections allocated at the server start up. The value should be between Min Pool Size and Max Pool Size.
Min pool size: the minimum number of connections that can be established.
Max pool size: the maximum number of connections that can be established.
Max idle time: the maximum number of seconds after which a connection is discarded if unused. If it is set to zero, the connection is never discarded.
Checkout timeout: the maximum number of milliseconds that a new connection can wait to be allocated. If it is set to zero, the connection can wait indefinitely. If the connection isn't allocated at the end of the configured time, an SQLException occurres.
Max Statements Per Connection: the maximum number of c3p0 cached statements per connection. If it is set to zero, the statement caching is disabled.
Idle connection test period: the number of seconds after which a check for idle connections runs.
Acquire increment: this setting determines how many connections at a time c3p0 tries to acquire when the pool is exhausted.
Acquire Retry Attempts: this setting defines how many times c3p0 tries to acquire a new connection from the database before giving up. If this value is less than or equal to zero, c3p0 keeps trying to fetch a connection indefinitely.
Acquire Retry Delay: the number of milliseconds c3p0 waits between acquire attempts.
Break After Acquire Failure: if checked, the DataSource considers itself broken after a failed round of connection attempts, and the next connection requests fails immediately.
Max Connection Age: the number of seconds after which the connection lifetime ends and is destroyed. If it is set to zero, connections are never destroyed.
Max Idle Time Excess: the number of seconds of grace period before a connection is discarded after the Max idle time is reached. If it is set to zero, the excess idle time is disabled.
Test Connection on Check-in: if checked an asynchronous test will be performed at every connection check-in. It will check with the connection pool if the connection is valid.
Test Connection on Checkout: if checked, an asynchronous test will be performed at every connection check-out. It will check with the connection pool if the connection is valid.
Unreturned Connection Timeout: the number of seconds after which an unreturned connection (a connection that remains checked-out) is destroyed. If it is set to zero, unreturned connection timeout is disabled. If a non-zero value is set, it should be a value higher than any connection, which should reasonably be checked-out. Otherwise, the pool occasionally kills the connections in active use.
Debug Unreturned Connection: if checked, and if Unreturned Connection Timeout is set to a positive value, the pool captures the stack trace (through an exception) of all connection checkouts. The stack traces are printed at the unreturned checked-out connections timeout. This is intended to debug applications with connection leaks. Use it for applications that occasionally fail to return connections, which leads to the pool growth, and eventually exhaustion (when the pool hits Max Pool Size with all connections checked-out and lost). This parameter should only be set while debugging, as capturing the stack trace slows down every connection check-out.
Number Helper Threads: c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. Spreading these operations over multiple threads can significantly improve performance, allowing multiple operations to be performed simultaneously.
Statement Cache Num Deferred Close Threads: if set to a value greater than 0, the statement cache tracks connections when they are in use, and destroys statements when their parent connections are no longer in use.
Max Administrative Task Time: the number of seconds after which c3p0 tries to interrupt an apparently stuck task. It is rarely used.
Privilege Spawned Threads: if checked, c3p0-spawned threads have java.security.AccessControlContext associated with c3p0 library classes. This setting can help if there are problems with the garbage collector or if client threads lack sufficient permission to perform operations which are required by c3p0.
Context Class Loader Source: it specifies how a context class loader is determined. It can be caller, library, none. Caller means that c3p0 inherits the context class loader from the client thread, which has initialized the pool. Library means that c3p0 loads the context class loader. None means that any context class loader is set.
Property Cycle: the maximum number of seconds after which user configuration constraints are enforced. It determines how frequently Max Connection Age, Max Idle Time, Max Idle Time Excess Connections, Unreturned Connection Timeout are enforced.

Note

Further details about c3p0 are available at this link <http://www.mchange.com/projects/c3p0/>.

[Advanced] Hibernate¶

Generate statistics: if checked, it generates hibernate statistics in order to verify improvements on performance.
Use query cache: if checked, it enables cache for query results. Users can use it for queries frequently run with the same parameters.
Hibernate 2 level cache: if checked, the hibernate functionality uses the second level cache.
Hibernate dialect: the dialect abstract data type or functionality, which varies across databases.
Autocommit: if checked, uncommitted transactions are autocommitted.
Sensitive usernames: if checked, the username is case sensitive.

Note

See also the Hibernate documentation <http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/performance.html>.

[Advanced] Embedded Database¶

H2 server port: the port number of the H2 database server.
H2 console port: the port number of the H2 console.
H2 server base path: the path to the H2 database file.

[Advanced] Advanced¶

Use Hikari connection pool: if checked, HikaryCP database is used instead of c3p0. This is only to be checked if there are many problems with the database.
Database timezone: the timezone of the database.
Update/Retry on Acquire Locks: if checked, it updates or retries to acquire locks.
Max attempts on Acquire Locks: the maximum number of attempts for acquiring locks.
Milliseconds between Acquire Locks attempts: the number of milliseconds after which another attempt to acquire locks is made.
Default batch size: the amount of rows that are cleaned up in batches. The default amount of rows is 1000.

[Advanced] Maintenance¶

Reorganization indexes: if checked, indexes for reorganization are enabled.

To facilitate the configuration, the tool contains some preset settings for the most common database types. Select the database and configure the URL and the login credentials.

To check the configuration set, press the Test Connection button. The test gives a feedback on the connection performance and the output message states the Connection status, the Server version, the Connection Time and the Query Time. A comment referred to Time fields, which is written in parenthesis reveals the performance of the database.

Test Connection Status
Connection time (comment)
Query time (comment)
Server version

Comment refers the time spent to perform the action (milliseconds):
<50 ms: Fast
>= 50 ms, <=100 ms: Normal, > 100 ms: Slow

If the connection fails, the configuration contains an error. Check the name of the database and the connection string. After a successful attempt, save the configuration by clicking on the Save button.

Hint

Do not forget to click on Save to save the changes. When everything in the config tool is configured, the Genius Server needs to be restarted.