Child pages
  • Database Connections
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

Servoy utilizes a three tier architecture for database access: Clients communicate with the Servoy Application Server and the Application Server communicates with the database.

The Application Server connects to databases through JDBC, a universal Java technology for Java applications to communicate with SQL databases.

For each defined database, the Servoy Application Server manages a pool of database connections, the size of which is configurable, to minimize to overhead of connection creation.

JDBC Drivers

In order to be able to connect to a database, the Servoy Application Server requires a JDBC Driver. JDBC drivers usually come with the database or are provided separately by the database vendor or third party vendors.

Servoy ships JDBC drivers for a several databases. For a full overview of the JDBC drivers shipped with a specific Servoy version, check the Servoy Stack info page in the Programming Reference Guide for the Servoy version used. 

The JDBC drivers are stored in the ../application_server/drivers/ directory.

Additional JDBC Drivers can be manually placed in this directory or can be uploaded through the Upload Library section of the Servoy Admin page. In both scenario's the Application Server requires a restart.

(warning)  While one database is more strict than the other, it is important to use the correct version JDBC Driveror a specific version of the database. Notably Oracle is very strict: even using the JDBC driver of another minor version might cause unexpected results! Refer to the documentation of the database vendor for the correct JDBC driver.

oraclelobfix.jar

The oraclelobfix.jar file in the ../application_server/drivers directory is not an Oracle JDBC driver, but a library containing a fix for an issue in the Oracle JDBC driver. When connecting to Oracle databases, it's still required to add the correct Oracle JDBC driver.

JDBC 3.0 vs. JDBC 4.0

There are two active versions of the JDBC specification, namely version 3 and version 4. The version 4 specification is compatible with Java 6 or higher, whereas version 3 is compatibly with Java 5 and higher. As Servoy is compatibly with Java 5 and higher, Servoy supports JDBC drivers that conform to the JDBC 3.0 specification. 

As of Servoy 5.2.9 Servoy will also support JDBC drivers that conform to the JDBC 4.0 specification. As JDBC 4.0 is not compatible with Java 5, the Servoy Application Server needs to be run under Java 6 or higher in order to be able to use these drivers.

(warning)  Note that JDBC 3.0 and JDBC 4.0 should not be mistaken for JDBC type 3 or 4: JDBC types (1 through 4) are an indication how the communication between the Java process and the database is implemented, whereas JDBC 3.0 or JDBC 4.0 says something about the Java API exposed by the JDBC driver itself.

Connecting to databases

Connections to databases can be made from the Database Server page of the Servoy Admin page. In order to connect to a database, the following information is required:

Setting

Description

Comment

Name

The name by which the database is referenced in Solutions

 

Username

The database username that needs to be used for the connection

 

Password

The password that goes with the database username

 

URL

The JDBC URL through which the database can be accessed

Refer to the database and/or JDBC driver documentation for the URL syntax

Driver

The JDBC Driver classname

Refer to the database and/or JDBC driver documentation for the classname to use

Catalog

The specific catalog to connect to

Not all databases support this option*

Schema

The specific schema to connect to

not all databases support this option*

The JDBC driver for the database to which the connection is made needs to be loaded into the Servoy Application Server, see the #JDBC Drivers paragraph above.

* Catalog & Schema: JDBC defines that a database may have a set of catalog and each catalog may have a set of schema's. However, each database/JDBC driver vendor has interpreted this differently. In general a Catalog contains all the system/metadata tables/views, while the schema contains all the "user" defined tables, views, triggers etc. Within the context of Servoy, the Catalog is hardly used, while the schema setting is used when connection to Oracle

Connection pooling

Clients do not directly access the databases, instead all their query requests are send to the Application Server which then delegates the query to the correct database.

In order to minimize the overhead of connection creation, the Application Server manages a pool of database connections per configured database server. On each database server, there are several settings related to the pool's behavior. These settings are available through the settings for each individual database on the Database Servers page on the Servoy Admin page:

Setting

What is does

Comment

Maximum connections active

Determines the maximum number of connections that will be made to the database simultaneous

If set too low, handling requests towards the database might slow down, as one request needs to wait until another request is processed and the connection is returned to the pool
If set too high, the exceptions might occur if the database cannot handle that many concurrent connections or if more memory is required than is available

Maximum connections idle

Determines the maximum number of unused connections that are in the pool

Active connections that are done processing a request are returned to the connection pool as idle connections. If the number of idle connections goes over the maximum, the connections are removed.
As instantiating new connections takes time, the value shouldn't be too low. On the other side idle connections take up resources, so the number shouldn't be too high either.
Must be lower that the "Maximum connections active" setting

Each connection to a database consumes memory and resources both on the Servoy Application Server as well as on the database server/engine side, easily adding up to several Mb of memory usage on both the Application as well as the Database server side. Instantiating a new connection takes time. Thus the two settings above must be balanced to provide the optimal performance, while not consuming too many resources.

When a request from a client needs to be handled, an idle connection is leased from the pool. If there is no idle connection left a new connection will be created, if the "Maximum connections active" value has not been reached. When the request is finished, the connection is returned to the pool. If the "Maximum connections idle" value is exceeded, the returned connection is destroyed.

If the "Maximum connections active" value is reached, no new connections can be leased from the pool. In this case, the request from the client is on hold, until a connection is released to the pool. When this happens, the user will experience a hanging client, until a connection becomes available again and the request can be handled. 

The handling of a single request from a client usually takes only a few milliseconds (see the performance log on the Servoy Admin page for details on query execution times). However, if a Client is using transactions, the connection is leased from the pool for the duration of the transaction. When running solutions that use long running transactions, the connection pool settings need to be adjusted accordingly.

The Database Servers page on the Servoy Admin page shows per database the number of Active and idle connections, compared to their respective maximum value

Dimensioning the connection pool

By default, the maximum active connections setting is set to 10. This could be too low when serving may clients from one Application Server or when the clients do many requests to the database or use long running transactions. As rule of thumb, if the actual used active connection regularly goes above 70% of the maximum a higher number of maximum active connections should be configured.

Database limitations

The maximum number of active connections is also limited by the maximum number of connections the database itself is configured to allow. 

For the bundled PostgreSQL database engine for example, the maximum is 100 connections. However, these 100 connections are for all connections made to the PostgreSQL database server instance. This means that if there are multiple Database Servers defined in the Servoy Application Server which are all hosted on the same PostgreSQL database server instance, the max. 100 connections are for all Database Servers combined. This must be taken into account when setting up the maximun number of active connections. 

Log Server

Servoy has functionality that allows 

Configuring database access

Maximum prepared statements idle

The maximum number of idle prepared statements parameter specifies the maximum number of prepared statements that are pooled per connection. It depends on the JDBC driver and the backend whether the prepared statements are cached on the database side, thereby improving query performance. For some databases, prepared statements are not cached on the server at all, in which case the maximum number of idle prepared statements can be set to 0. On other databases, the prepared statements automatically time out (and thus need to be re-prepared) after a small timeout period (for example 1 minute), in which case a large value of this parameter is also unnecessary. Prepared statements are cached on a LRU basis, which means that when the pool is full, the least recently used prepared statement is removed.

Query validation type & Validation query

Some databases automatically end connections when they have been idle for a certain period of time. To prevent using a pooled connection that was already disconnected by the database, a connection validation method can be specified which checks database connections when they are taken from the pool. There are three methods of validation:

  • exception validation - the exception validation method invalidates a connection whenever an error has occurred (I/O, or SQL) on that connection
  • metadata validation - the meta data validation method asks the database for some database meta data to validate the connection. Note that this is not useful on all databases, as sometimes the JDBC driver returns the meta data without actual communication with the database itself
  • query validation - this method is the safest method (but also has the greatest impact on performance). Whenever a connection is retreived from the pool, the specified query is performed, and the connection is considered valid only if the query succeeds (i.e. no I/O or SQL error occurs). Note that in the worst case, the number of queries on the database doubles, although an efficient query such as 'SELECT 1' has very little performance penalty (the 'SELECT 1' query, even though it is valid SQL92, does not work on all databases)
Data model cloned from

In a SaaS deployment when using a database clone per tenant, any update to the datamodel for a solution on solution import needs to be applied to all database clones. Using this property a database can be marked as a clone of the master database, the database against which the solution was designed. When a solution is imported into the Servoy Application Server that requires changes to the datamodel, the same change will be applied to all clones of the master database.

Enabled

Whether or not this server is enabled

Log Server

Whether or not this server will be used as the log server.

Update Servoy sequences for...

ServerManager.numberOfServers=4
server.0.URL=jdbc\:sybase\:Tds\:localhost\:2638?ServiceName\=servoy_repository&CHARSET\=utf8
server.0.catalog=<none>
server.0.driver=com.sybase.jdbc3.jdbc.SybDriver
server.0.maxConnectionsActive=10
server.0.maxConnectionsIdle=10
server.0.password=encrypted\:aD4kOmHPzcM\=
server.0.schema=<none>
server.0.serverName=repository_server
server.0.userName=DBA
  • No labels