Connection Pooling in der DLE

Ab der Version 1.6.6 der DLE wird ein Connection Pooling unterstützt.
Dazu kann anstatt der DefaultJDBCConnectionFactory die DLEPooledJDBCConnectionFactory angegeben werden. Mit dieser Factory Klasse werden die Datenbankverbindungen (und wenn gewünscht, auch Statements) in einem Pool verwaltet und je nach Bedarf aus diesem bezogen.
Außerdem wird seit der Version 1.6.6. per Default auf dem Client eine DefaultJDBCConnectionFactory, auf dem Server eine DLEPooledJDBCConnectionfactory verwendet, falls keine Factory explizit in der Konfiguration angegeben wurde.
Zudem sind mit der Version 1.6.6. die Parameter „IsDLEManaged“ und „JDBCDriver“ nur noch optional in der Konfiguration nötig.
Damit beschränkt sich ab 1.6.6. die Konnektor Definition eigentlich nur noch auf die Parameter JDBCUser, JDBCPassword (oder EndodedPassword), JDBCURL, Dialect und TransactionLevel.
Für das Connection Pooling sind auch für alle möglichen Parameter Default Werte gesetzt, die für die meisten Serveranwendungen passen sollten.
Eine Reihe von Parametern kann den Pool aber auch auf die individuellen Anforderungen anpassen.
Siehe dazu die beiden folgenden Abschnitte, je nach DLE-Version.
Verbindungen werden aus dem Pool bezogen, sobald eine DLE Session den ersten Datenzugriff über einen gepoolten Konnektor macht.
Die Verbindung wird in dem Moment in den Pool zurückgegeben, sobald die DLE Session beendet wird. Entweder durch ein Log-out der API, oder durch einen Idle-Event.
Die DPE Queues geben die Datenbankverbindung nach erfolgtem DPE-Durchlauf zurück in den Pool.
In einer Webanwendung ist dabei zu beachten, dass jeder Request-Response Zyklus einer Anmeldung- und Abmeldung entspricht. Eine gepoolte Datenbankverbindung ist hier also nur so lange reserviert, wie die HTTP Anfrage dauert. Gerade für Webanwendungen bringt das Pooling also einen enormen Performancegewinn bei gleichzeitiger Ressourcenschonung.
Die Einstellungen und Statistiken des Connection Pooling sind in der DLE Server Konsole unter dem Menüpunkt „ServerInfo“ ersichtlich. Die ServerInfo kann auch direkt abgerufen werden unter der URL localhost/ShowServerInfo. (Host und Port sind gegebenenfalls anzupassen).
Bei einem Server Refresh werden die Pooling-Parameter neu gelesen und die Pools neu erstellt. Bestehende Verbindungen („Leased“ Connections) bleiben dabei so lange bestehen, bis diese in den (nun nicht mehr aktiven) alten Pool zurückgegeben werden.
Der Connection Pool ist JMX-Fähig, das heißt, er kann über JMX administriert werden.

Pool-Einstellungen in der DLE ab Version 2.1.2

Ab DLE-Version 2.1.2 wurde die im Hintergrund verwendete Pool-Bibliothek auf das Nachfolgeprodukt aktualisiert. Dadruch ergeben sich andere Konfigruationseinstellungen.

Dieser Pool verwendet im Gegensatz zum Vorgänger (bis inkl. DLE  2.1.1) keine Partitionen mehr. Weiters wird auch auf das Caching von Statements verzichtet, da dies bei den meisten Datenbanken durch den nativen Treiber übernommen wird und dort wesentlich effizienter abgewickelt werden kann.

Die Funktionalität der Dokumentation von lang-dauernden SQL-Abfragen wird durch den neuen Pool nicht mehr unterstützt, es wurde aber ein Ersatz im DLE-Kern geschaffen. Siehe dazu den Parameter LongQueryThreshold.

Folgend mögliche Pooleinstellungen in englischer Beschreibung. Es sei an dieser Stelle darauf hingewiesen, dass eine überdimensionierte Konfiguration Performanceinbußen in der Größenordnung bis Faktor 50 mit sich bringen können.

HINWEIS: Die Systemproperty dle.server.cpool.minConnections wird NICHT mehr unterstützt. Es kann über den Parameter PoolMinimumIdle eine Soll-Anzahl für freie Reserve-Verbindungen festgelegt werden. Ein Setzen des Parameters wird aber nicht empfohlen.

LongQueryThreshold-Parameter

Dieser Parameter, ohne Pool-Präfix, gibt die Schwelle an, ab der SQL-Abfragen als Warnungen in die Logfiles geschrieben werden, wenn die Laufzeit die Schwelle überschreitet. Der angegeben Wert wird als Millisekundenangabe ausgewertet. Standardwert bei keiner Angabe: 10000 (Millisekunden). Wird 0 oder ein kleinerer Wert angegeben, dann findet keine Ausgabe einer Logmeldung statt.

ParameterBeschreibung
PoolAutoCommitThis property controls the default auto-commit behavior of connections returned from the pool. It is a boolean value.
PoolConnectionTimeoutThis property controls the maximum number of milliseconds that a client (that's you) will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds)
PoolIdleTimeoutThis property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10000ms (10 seconds). Default: 600000 (10 minutes)
PoolKeepaliveTimeThis property controls how frequently HikariCP will attempt to keep a connection alive, in order to prevent it from being timed out by the database or network infrastructure. This value must be less than the maxLifetime value. A "keepalive" will only occur on an idle connection. When the time arrives for a "keepalive" against a given connection, that connection will be removed from the pool, "pinged", and then returned to the pool. The 'ping' is one of either: invocation of the JDBC4 isValid() method, or execution of the connectionTestQuery. Typically, the duration out-of-the-pool should be measured in single digit milliseconds or even sub-millisecond, and therefore should have little or no noticeable performance impact. The minimum allowed value is 30000ms (30 seconds), but a value in the range of minutes is most desirable. Default: 0 (disabled)
PoolMaxLifetimeThis property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. The minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30 minutes)
PoolConnectonTestQueryIf your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none
PoolMinimumIdleThis property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than maximumPoolSize, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool. Default: same as maximumPoolSize
PoolMaximumPoolSizeThis property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to connectionTimeout milliseconds before timing out. Please read about pool sizing. Default: 10
PoolCatalogThis property sets the default catalog for databases that support the concept of catalogs. If this property is not specified, the default catalog defined by the JDBC driver is used. Default: driver default

Zusätzlich gibt es noch spezielle Einstellungen, die üblicherweise nicht benötigt werden. An dieser Stelle sei auf die Seite github.com/brettwooldridge/HikariCP verwiesen.

 

 

Pool-Einstellungen in der DLE bis Version 2.1.1

Pro Session-Konfiguration und Konnektor wird (bei Verwendung des Connection Pools) ein separater Pool erzeugt. Jeder Pool wiederum kann in mehrere Partitionen aufgeteilt werden. Das Reservieren einer Verbindung kann damit parallelisiert werden. Mehrere Partitionen lohnen sich aber nur, wenn man damit rechnet, das viele Anfragen an eine Datenbankverbindung gleichzeitig an den Server gestellt werden. Per Default wird deshalb auch nur eine Partition pro Pool erstellt.
Die Anzahl der möglichen Datenbankverbindungen, die jede Partition enthalten kann wird über die Parameter PoolMinConnectionsPerPartition und PoolMaxConnectionsPerPartition gesteuert. In einer DLE Server Umgebung orientieren sich die Werte dafür an den Einstellungen des Servers. Per Default wird für den Min Parameter die Anzahl der MinWorker vom Server übernommen. Die maximale Anzahl der Datenbankverbindungen entspricht dem Parameter MaxWorkers plus 3 aus der Server Konfiguration, um die interne DLE Server Session und 2 DPE Queues zu berücksichtigen.

Über das Parameter PoolStatisticsEnabled können optional zusätzlich Statistiken gesammelt werden, die dann über eine JMX Konsole (JConsole, JVisualVM, oder andere) ausgewertet werden können.

<?xml version="1.0"?>
<DLEConfig>
<Properties />
<Services>
<Service name="defaultConnector" class="at.visionflow.dle.engine.connectors.DLEJDBCConnector">
<Properties>
<Property name="TransactionLevel" value="local"/>
<Property name="Dialect" value="mysql"/>
<Property name="JDBCUser" value="root"/>
<Property name="JDBCPassword" value="admin"/>
<Property name="JDBCURL" value="jdbc:mysql://localhost:3306/DLE"/>
<Property name="PoolMinConnectionsPerPartition" value="5"/>
<Property name="PoolMaxConnectionsPerPartition" value="30"/>
<Property name="PoolPartitionCount" value="3"/> </Properties>
</Service>
</Services>
</DLEConfig>

Im Folgenden die möglichen Connection Pool Parameter, in Englischer Beschreibung:

ParameterBeschreibung
PoolAcquireIncrementWhen the available connections are about to run out, the pool will dynamically create new ones in batches. This property controls how many new connections to create in one go (up to a maximum of PoolMaxConnectionsPerPartition). Default: 2.
PoolPartitionCountSets number of partitions to use. In order to reduce lock contention and thus improve performance, each incoming connection request picks off a connection from a pool that has thread-affinity, i.e. pool[threadId % partition_count]. The higher this number, the better your performance will be for the case when you have plenty of short-lived threads. Beyond a certain threshold, maintenance of these pools will start to have a negative effect on performance (and only for the case when connections on a partition start running out).
Default: 1, minimum: 1, recommended: 2-4 (but very app specific)
PoolMinConnectionsPerPartitionSets the minimum number of connections that will be contained in every partition. Default: 0. In a DLE Server environment, the default will be the number of MinWorkers (Server Property).
PoolMaxConnectionsPerPartitionSets the maximum number of connections that will be contained in every partition. Setting this to 5 with 3 partitions means you will have 15 unique connections to the database. Note that the connection pool will not create all these connections in one go but rather start off with PoolMinConnectionsPerPartition and gradually increase connections as required. Default: 20. In a DLE Server environment, the default will be the number of MaxWorkers (Server Property) plus 3 (to cover the initial internal session and 2 possible DPE queues).
PoolIdleConnectionTestPeriodInMinutesThis sets the time (in minutes), for a connection to remain idle before sending a test query to the DB. This is useful to prevent a DB from timing out connections on its end. Do not use aggressive values here!
Default: 240 min, set to 0 to disable.
PoolIdleMaxAgeInMinutesThe time (in minutes), for a connection to remain unused before it is closed off. Do not use aggressive values here!
Default: 30 minutes, set to 0 to disable.
PoolConnectionTestStatementThe query to send to the DB to maintain keep-alives and test for dead connections. This is database specific and should be set to a query that consumes the minimal amount of load on the server. Examples: MySQL: "/* ping *\/ SELECT 1", PostgreSQL: "SELECT NOW()". If you do not set this, then the pool will issue a metadata request instead that should work on all databases but is probably slower. (Note: In MySQL, prefixing the statement by /* ping *\/ makes the driver issue 1 fast packet instead. See http://blogs.sun.com/SDNChannel/entry/mysql_tips_for_java_developers
PoolStatementsCacheSizeThe number of statements to cache. Default: 50.
PoolReleaseHelperThreadsSets number of helper threads to create that will handle releasing a connection. When this value is set to zero, the application thread is blocked until the pool is able to perform all the necessary cleanup to recycle the connection and make it available for another thread. When a non-zero value is set, the pool will create threads that will take care of recycling a connection when it is closed (the application dumps the connection into a temporary queue to be processed asynchronously to the application via the release helper threads). Useful when your application is doing lots of work on each connection (i.e. perform an SQL query, do lots of non-DB stuff and perform another query), otherwise will probably slow things down. Default: 3.
PoolInitSQLSpecifies an initial SQL statement that is run only when a connection is first created.
PoolCloseConnectionWatchInstruct the pool to create a helper thread to watch over connection acquires that are never released (or released twice). This is for debugging purposes only and will create a new thread for each call to getConnection(). Enabling this option will have a big negative impact on pool performance. Default: False.
PoolLogStatementsEnabledIf enabled, log SQL statements being executed. Default: False.
PoolAcquireRetryDelayInMsSets the number of ms to wait before attempting to obtain a connection again after a failure. Default: 7000.
PoolLazyInitSet to true to force the connection pool to obtain the initial connections lazily. Default: False.
PoolTransactionRecoveryEnabledSet to true to enable recording of all transaction activity and replay the transaction automatically in case of a connection failure. Default: False.
PoolAcquireRetryAttemptsAfter attempting to acquire a connection and failing, try to connect these many times before giving up. Default 5.
PoolDisableJMXSet to true to disable JMX. Default: False.
PoolQueryExecuteTimeLimitInMsQueries taking longer than this limit to execute are logged. The statement is not canceled! Default:
PoolPoolAvailabilityThresholdThe pool watch thread attempts to maintain a number of connections always available (between PoolMinConnections and PoolMaxConnections). This value sets the percentage value to maintain. For example, setting it to 20 means that if the following condition holds: Free Connections / MaxConnections < poolAvailabilityThreshold new connections will be created. In other words, it tries to keep at least 20% of the pool full of connections. Setting the value to zero will make the pool create new connections when it needs them but it also means your application may have to wait for new connections to be obtained at times. Default: 0.
PoolDisableConnectionTrackingIf set to true, the pool will not monitor connections for proper closure. Enable this option if you only ever obtain your connections via a mechanism that is guaranteed to release the connection back to the pool. (Do not set this to false in DLE)
PoolConnectionTimeoutInMsSets the maximum time (in milliseconds) to wait before a call to get a connection is timed out. Default: 120000 ( 0 = wait forever )
PoolCloseConnectionWatchTimeoutInMsSets the no of ms to wait when close connection watch threads are enabled. Default: 0 = wait forever.
PoolStatementReleaseHelperThreadsSets number of statement helper threads to create that will handle releasing a statement. When this value is set to zero, the application thread is blocked until the pool and JDBC driver are able to close off the statement. When a non-zero value is set, the pool will create threads that will take care of closing off the statement asychronously to the application via the release helper threads). Useful when your application is opening up lots of statements otherwise will probably slow things down. Default: 3.
PoolMaxConnectionAgeInSecondsAny connections older than this setting will be closed off whether it is idle or not. Connections currently in use will not be affected until they are returned to the pool. Default: 14400 (4 hours) (0 = never close).
PoolConfigFileIf configured, this will cause the pool to initialize using the configuration file.
PoolServiceOrderValues currently understood are FIFO and LIFO. Default: FIFO.
PoolStatisticsEnabledIf set to true, keep track of some more statistics for exposure via JMX. It will slow down the pool operation. Default: False.
PoolDefaultAutoCommitSets the defaultAutoCommit setting for newly created connections. If not set, use driver default. Do not use this flag with the DLE, as it is set through the transaction level parameter.
PoolDefaultCatalogSets the defaultCatalog setting for newly created connections. If not set, use driver default.
PoolDefaultReadOnlySets the defaultReadOnly setting for newly created connections. If not set, use driver default.
PoolDefaultTransactionIsolationSets the defaultTransactionIsolation. Should be set to one of: NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ or SERIALIZABLE. If not set, will use driver default.