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 :



Comments:

Hi,

very nice feature.
unfortunately it does not work on all servers. I installed via an automated process 8 glassfish servers. On one server it possible to monitor jdbc - on seven not. I'm using for monitoring comandline tools as well as web console.

- I'm using glassfish-installer-v2-solaris-x86.jar on solaris 10 in solaris zones automaticly installed
- the jdbc-connection-pool is up and running
- the monitoring level is set to LOW (HIGH makes no difference)

The Error message is:
com.sun.appserv:category=monitor,name=roadstars,server=server,type=jdbc-connection-pool is not a registered bean

restart of the glassfish server does not help.
I poked around with various commands and setting monitoring level from low to high and back. And suddenly it worked on one more server. But this is not reproducable - even if there are 6 more servers.

Do you have any idea?
I have that problem in two projects using jdk 1.6.0_02 and 1.6.0_03 with oracle and mysql. With postgres I had no problems so far.

Best Regards,
Uwe Bartels

Posted by Uwe Bartels on December 08, 2007 at 09:29 AM IST #

Hi,

We have web applicaiton running on Oracle Application server on RedHat Linux server with JDK1.4 verions.

Can you please let me know if i can use this?

Thanks,
Sudhir

Posted by Sudhir on July 06, 2008 at 11:18 AM IST #

hello,
Do you know how X-MonitoringDottedNames are build?
I mean is there an algoritm to build them from the X-JDBCConnectionPoolMonitor ?

Posted by fbogdan on May 25, 2009 at 05:57 PM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
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