Thursday Jun 14, 2007

JDBC Connection Pool Templates - GlassFish

JDBC Connection Pool Templates for GlassFish

Templates to create JDBC Connection Pool & Resource


Often, setting up connection pool (datasource) against various databases will require knowledge on
mandatory property names to successfully connect to database.

GlassFish V2 has templates to create connection pool & resource against various databases & jdbc drivers.

Once appropriate values for properties like, user, password, databaseName, serverName are set in the template,  they
can be used multiple times.

GlassFish V2 has templates available in GF_INSTALL_DIR/lib/install/templates/resources/jdbc

How to use :


1)  copy the jdbc-driver to AS_INSTALL_DIR/domains/<DOMAIN_NAME>/lib/ext
2)  restart appserver
3)  edit the appropriate jdbc-driver template to fill username, password, hostname, databasename, portnumber etc.,
3a) [Optional] select appropriate resource-type & datasource-classname. Templates are provided with the list of
    datasource-classnames for respective resource-types of the jdbc-vendor, if available.
4)  asadmin add-resources jdbc-driver-template.xml (eg: AS_INSTALL_DIR/lib/install/templates/resources/jdbc/javadb_type4_datasource.xml)
5)  asadmin ping-connection-pool connection-pool-name

For more information, refer GF_INSTALL_DIR/lib/install/templates/resources/jdbc/README and respective datasource-template.xml files.

These templates can be used against GlassFish V1 also.

Templates :


Database Vendor
JDBC Driver Vendor
datasource-template-file
JavaDB / Derby
Derby
javadb_type4_datasource.xml
Oracle
Oracle Thin Driver
oracle_type4_datasource.xml
Oracle
Inet Driver
oracle_inet_datasource.xml
Oracle
Sun bundled DataDirect Driver
(available in SJSAS EE builds)
oracle_dd_datasource.xml
MySQL
MySQL Connector/J
mysql_type4_datasource.xml
PostgreSQL
PostgreSQL Driver
postgresql_type4_datasource.xml
DB2
Sun bundled DataDirect Driver
(available in SJSAS EE builds)
db2_dd_datasource.xml
DB2
IBM DB2 JDBC type-4 driver
db2_jcc_datasource.xml
Sybase
Sun bundled DataDirect Driver
(available in SJSAS EE builds)
sybase_dd_datasource.xml
Sybase
Inet Driver
sybase_inet_datasource.xml
Sybase
jConnect Driver
sybase_jconn_datasource.xml
Microsoft SQL Server
Sun bundled DataDirect Driver
(available in SJSAS EE builds)
microsoft_dd_datasource.xml
Microsoft SQL Server Inet Driver
microsoft_inet_datasource.xml
Microsoft SQL Server jTDS Driver
microsoft_jtds_datasource.xml
Microsoft SQL Server 2000
Microsoft
microsoft_sqlserver2000_datasource.xml
Microsoft SQL Server 2005 Microsoft
microsoft_sqlserver2005_datasource.xml

The templates listed above will be available in GlassFish v2 b51

Similarly, templates for other databases can be done.
If you have a template for a particular database that is not listed here,
please post it to dev@glassfish.dev.java.net or users@glassfish.dev.java.net

Friday Jun 01, 2007

Monitoring JDBC Connection Pool - GlassFish

Monitoring jdbc connection pool in GlassFish
Monitoring JDBC Connection pool will provide statistics on the usage of connection pool.
This can be used to tune the connection pool according to the application usage.

Monitoring JDBC Connection pool can be done in the following ways.

1) Admin console (GUI)
2) CLI
3) Programatically using AMX

1) Admin console


Configuration > Monitoring > Set Jdbc Connection Pool to HIGH
and save.
enable monitoring for jdbc connection pool

Common Tasks > View Monitoring Data > (RHS) Monitor > Resources
will list the connection pools that are referred by the application.
This will list the monitoring statistics of jdbc-connection-pools that are used by the application.

Monitoring data


2) CLI

Using dotted-names-command we can enable monitoring levels for various modules.

To find the monitoring levels dotted-name:
GF_HOME/bin/asadmin list \*monitor\*
server.monitoring-service
server.monitoring-service.module-monitoring-levels


To get monitoring level of various modules :
GF_HOME/bin/asadmin get server.monitoring-service.module-monitoring-levels.\*
server.monitoring-service.module-monitoring-levels.connector-connection-pool = OFF
server.monitoring-service.module-monitoring-levels.connector-service = OFF
server.monitoring-service.module-monitoring-levels.ejb-container = OFF
server.monitoring-service.module-monitoring-levels.http-service = OFF
server.monitoring-service.module-monitoring-levels.jdbc-connection-pool = OFF
server.monitoring-service.module-monitoring-levels.jms-service = OFF
server.monitoring-service.module-monitoring-levels.jvm = OFF
server.monitoring-service.module-monitoring-levels.orb = OFF
server.monitoring-service.module-monitoring-levels.thread-pool = OFF
server.monitoring-service.module-monitoring-levels.transaction-service = OFF
server.monitoring-service.module-monitoring-levels.web-container = OFF

To set monitoring level of jdbc-connection-pool to HIGH :
GF_HOME/bin/asadmin set server.monitoring-service.module-monitoring-levels.jdbc-connection-pool=HIGH
server.monitoring-service.module-monitoring-levels.jdbc-connection-pool = HIGH

To list all dotted-names that contain "oracle" :
GF_HOME/bin/asadmin list --monitor=true \*oracle\*
server.resources.oracledb_type4_pool


To get monitoring information of jdbc-connection-pool "oracledb_type4_pool" :
GF_HOME/bin/asadmin get  --monitor=true server.resources.oracledb_type4_pool.\*server.resources.oracledb_type4_pool.averageconnwaittime-count = 0
server.resources.oracledb_type4_pool.averageconnwaittime-description = No Description was available
server.resources.oracledb_type4_pool.averageconnwaittime-lastsampletime = 1180696935585
server.resources.oracledb_type4_pool.averageconnwaittime-name = AverageConnWaitTime
server.resources.oracledb_type4_pool.averageconnwaittime-starttime = 1180696749055
server.resources.oracledb_type4_pool.averageconnwaittime-unit = milliseconds
server.resources.oracledb_type4_pool.connrequestwaittime-current = 0
server.resources.oracledb_type4_pool.connrequestwaittime-description = Provides a range value that indicates the longest, shortest wait times of connection requests since the last sampling. The current value indicates the wait time of the last request that was serviced by the pool.
server.resources.oracledb_type4_pool.connrequestwaittime-highwatermark = 0
server.resources.oracledb_type4_pool.connrequestwaittime-lastsampletime = 1180696935581
server.resources.oracledb_type4_pool.connrequestwaittime-lowwatermark = 0
server.resources.oracledb_type4_pool.connrequestwaittime-name = ConnRequestWaitTime
server.resources.oracledb_type4_pool.connrequestwaittime-starttime = 1180696749055
server.resources.oracledb_type4_pool.connrequestwaittime-unit = milliseconds
server.resources.oracledb_type4_pool.dotted-name = server.resources.oracledb_type4_pool
server.resources.oracledb_type4_pool.numconnacquired-count = 0
server.resources.oracledb_type4_pool.numconnacquired-description = Provides a count value reflecting the number of connections acquired from the pool since the last sampling
server.resources.oracledb_type4_pool.numconnacquired-lastsampletime = 1180696935599
server.resources.oracledb_type4_pool.numconnacquired-name = NumConnAcquired
server.resources.oracledb_type4_pool.numconnacquired-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnacquired-unit = Count
server.resources.oracledb_type4_pool.numconncreated-count = 0
server.resources.oracledb_type4_pool.numconncreated-description = Provides a count value reflecting the number of connections that were created by the pool since the last sampling.
server.resources.oracledb_type4_pool.numconncreated-lastsampletime = 1180696935591
server.resources.oracledb_type4_pool.numconncreated-name = NumConnCreated
server.resources.oracledb_type4_pool.numconncreated-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconncreated-unit = Count
server.resources.oracledb_type4_pool.numconndestroyed-count = 0
server.resources.oracledb_type4_pool.numconndestroyed-description = Provides a count value reflecting the number of connections that were destroyed since the last sampling
server.resources.oracledb_type4_pool.numconndestroyed-lastsampletime = 1180696935590
server.resources.oracledb_type4_pool.numconndestroyed-name = NumConnDestroyed
server.resources.oracledb_type4_pool.numconndestroyed-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconndestroyed-unit = Count
server.resources.oracledb_type4_pool.numconnfailedvalidation-count = 0
server.resources.oracledb_type4_pool.numconnfailedvalidation-description = Provides a count value reflecting the number of connections in the connection pool that failed validation since the start time till the last sample time.
server.resources.oracledb_type4_pool.numconnfailedvalidation-lastsampletime = 1180696935594
server.resources.oracledb_type4_pool.numconnfailedvalidation-name = NumConnFailedValidation
server.resources.oracledb_type4_pool.numconnfailedvalidation-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnfailedvalidation-unit = Count
server.resources.oracledb_type4_pool.numconnfree-current = 8
server.resources.oracledb_type4_pool.numconnfree-description = Provides a count value reflecting the number of connections in the connection pool that are free as of the last sampling
server.resources.oracledb_type4_pool.numconnfree-highwatermark = 0
server.resources.oracledb_type4_pool.numconnfree-lastsampletime = 1180696935590
server.resources.oracledb_type4_pool.numconnfree-lowwatermark = 0
server.resources.oracledb_type4_pool.numconnfree-name = NumConnFree
server.resources.oracledb_type4_pool.numconnfree-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnfree-unit = Count
server.resources.oracledb_type4_pool.numconnnotsuccessfullymatched-count = 0
server.resources.oracledb_type4_pool.numconnnotsuccessfullymatched-description = No Description was available
server.resources.oracledb_type4_pool.numconnnotsuccessfullymatched-lastsampletime = 1180696935601
server.resources.oracledb_type4_pool.numconnnotsuccessfullymatched-name = NumConnNotSuccessfullyMatched
server.resources.oracledb_type4_pool.numconnnotsuccessfullymatched-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnnotsuccessfullymatched-unit = Count
server.resources.oracledb_type4_pool.numconnreleased-count = 0
server.resources.oracledb_type4_pool.numconnreleased-description = Provides a count value reflecting the number of connections released back to the pool since the last sampling
server.resources.oracledb_type4_pool.numconnreleased-lastsampletime = 1180696935586
server.resources.oracledb_type4_pool.numconnreleased-name = NumConnReleased
server.resources.oracledb_type4_pool.numconnreleased-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnreleased-unit = Count
server.resources.oracledb_type4_pool.numconnsuccessfullymatched-count = 0
server.resources.oracledb_type4_pool.numconnsuccessfullymatched-description = No Description was available
server.resources.oracledb_type4_pool.numconnsuccessfullymatched-lastsampletime = 1180696935601
server.resources.oracledb_type4_pool.numconnsuccessfullymatched-name = NumConnSuccessfullyMatched
server.resources.oracledb_type4_pool.numconnsuccessfullymatched-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnsuccessfullymatched-unit = Count
server.resources.oracledb_type4_pool.numconntimedout-count = 0
server.resources.oracledb_type4_pool.numconntimedout-description = Provides a count value reflecting the number of connections in the connection pool that timed out since the start time till the last sample time.
server.resources.oracledb_type4_pool.numconntimedout-lastsampletime = 1180696935602
server.resources.oracledb_type4_pool.numconntimedout-name = NumConnTimedOut
server.resources.oracledb_type4_pool.numconntimedout-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconntimedout-unit = Count
server.resources.oracledb_type4_pool.numconnused-current = 0
server.resources.oracledb_type4_pool.numconnused-description = Provides Connection usage statistics. In addition to number of connections being used currently, this also provides information about the Maximum number of connections that were used(High Watermark).
server.resources.oracledb_type4_pool.numconnused-highwatermark = 0
server.resources.oracledb_type4_pool.numconnused-lastsampletime = 1180696935592
server.resources.oracledb_type4_pool.numconnused-lowwatermark = 0
server.resources.oracledb_type4_pool.numconnused-name = NumConnUsed
server.resources.oracledb_type4_pool.numconnused-starttime = 1180696749055
server.resources.oracledb_type4_pool.numconnused-unit = Count
server.resources.oracledb_type4_pool.numpotentialconnleak-count = 0
server.resources.oracledb_type4_pool.numpotentialconnleak-description = Provides a count value reflecting the number of potential connection leaks
server.resources.oracledb_type4_pool.numpotentialconnleak-lastsampletime = 1180696935601
server.resources.oracledb_type4_pool.numpotentialconnleak-name = NumPotentialConnLeak
server.resources.oracledb_type4_pool.numpotentialconnleak-starttime = 1180696749055
server.resources.oracledb_type4_pool.numpotentialconnleak-unit = Count
server.resources.oracledb_type4_pool.waitqueuelength-count = 0
server.resources.oracledb_type4_pool.waitqueuelength-description = Provides a count value indicating the number of connection requests in the queue waiting to be servied.
server.resources.oracledb_type4_pool.waitqueuelength-lastsampletime = 1180696935597
server.resources.oracledb_type4_pool.waitqueuelength-name = WaitQueueLength
server.resources.oracledb_type4_pool.waitqueuelength-starttime = 1180696749055
server.resources.oracledb_type4_pool.waitqueuelength-unit = Count



3) Using AMX :

It is possible to get monitoring information programmatically,
using AMX (Application Server Management Extenstions).
Following sample will get few of the monitoring information of the specified jdbc-connection-pool, (oracledb_type4_pool) periodically.

Dependency : appserv-ext.jar


import javax.management.MBeanServerConnection;
import javax.management.ObjectName;
import com.sun.appserv.management.client.AppserverConnectionSource;
import java.util.TimerTask;
import java.util.Timer;
public class JDBCConPoolMonitoringInfoCollector {
    public static final String[] properties = {"numconnused-current", "numconndestroyed-count",
            "numconncreated-count", "numconnacquired-count", "numconnreleased-count",
            "averageconnwaittime-count", "connrequestwaittime-current", "numconnfailedvalidation-count",
            "numconnnotsuccessfullymatched-count", "numconnsuccessfullymatched-count", "numconntimedout-count",
            "waitqueuelength-count", "numpotentialconnleak-count"};
    public static final String USER_PASSWORD = "adminadmin";
    public static final int JMX_PORT = 8686;
    public static final String DOTTED_NAME_GET = "dottedNameGet";
    public static final String USER = "admin";
    public static final String CONFIG = "server-config";
    public static final String HOST_NAME = "localhost";

    public static void main(String[] args)
            throws Exception {
        JDBCConPoolMonitoringInfoCollector client = new JDBCConPoolMonitoringInfoCollector();
        client.runTest();
    }

    public void runTest() throws Exception {
        Timer timer = new Timer();
        timer.schedule(new Poller(), 1000, 1000 \* 10); //Time Interval
    }

    class Poller extends TimerTask {
        public void run() {
            for (String property : properties) {
                try {
                    int result = getMonitorablePropertyOfConnectionPool("<CONNECTION_POOL_NAME>",
                            property, HOST_NAME, JMX_PORT, USER, USER_PASSWORD, CONFIG);
                    System.out.println(property + " : " + result);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
         System.out.println("-------------------------------------------------------------------");
        }
    }

    public int getMonitorablePropertyOfConnectionPool(String poolName, String property, String hostName, int JMX_PORT, String user, String password, String configName) throws Exception {
        AppserverConnectionSource appserver =
                new AppserverConnectionSource(AppserverConnectionSource.PROTOCOL_RMI, hostName, JMX_PORT, user, password, null);
        MBeanServerConnection connection = appserver.getJMXConnector(false).getMBeanServerConnection();
        ObjectName objectName =
                new ObjectName("amx:j2eeType=X-MonitoringDottedNames,name=na");
        String params[] = new String[]{String.class.getName()};
        Object values[] = new Object[]{"server.resources." + poolName + "." + property};
        javax.management.Attribute returnValue = (javax.management.Attribute) connection.invoke(objectName, DOTTED_NAME_GET, values, params);
        return new Integer(returnValue.getValue().toString());
    }
}


Sample Output :

-------------------------------------------------------------------
numconnused-current : 30
numconndestroyed-count : 276
numconncreated-count : 308
numconnacquired-count : 1033
numconnreleased-count : 1003
averageconnwaittime-count : 44
connrequestwaittime-current : 0
numconnfailedvalidation-count : 0
numconnnotsuccessfullymatched-count : 1197
numconnsuccessfullymatched-count : 884
numconntimedout-count : 0
waitqueuelength-count : 0
numpotentialconnleak-count : 0
-------------------------------------------------------------------
numconnused-current : 30
numconndestroyed-count : 284
numconncreated-count : 316
numconnacquired-count : 1076
numconnreleased-count : 1046
averageconnwaittime-count : 42
connrequestwaittime-current : 0
numconnfailedvalidation-count : 0
numconnnotsuccessfullymatched-count : 1233
numconnsuccessfullymatched-count : 922
numconntimedout-count : 0
waitqueuelength-count : 0
numpotentialconnleak-count : 0
-------------------------------------------------------------------


References :



About

jagadish

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today