Search This Blog

Sunday, June 10, 2012

JDBC Monitoring

JDBC Monitoring with LWLST script
One fine morning we (WLA support Team) got an assignment, The summary of the assignment was to find "How the WebLogic server instance performing for a DataSource?". WebLogic 9.x onwards a DataSource is associated with a ConnectionPool (a pool of connections to the DB). If we monitor ConnectionPool, inturn it is nothing but monitoring a DataSource.

Of-course the task is not that easy, I have gone through various WebLogic forums to find a appropriate solution for this task.

Oracle WebLogic provides two ways to Monitor a Datasource
1. Monitoring Datasource server wise
2. Testing the Connection Pool

Here I am publishing the same script which Madan Noru/Satya prepared in the old bea fourms or ObjectMix forums. Only one thing is difference is that displaying pattern, I had created a separate header, so that output looks good to see in a table form. To make this possible I have used C-Style print command from Python Language. This format you can change as per your screen display size.

The script will retrieve the JDBC Connection Pool MBean using adminHome, which is deprecated object in WLST. The output of the script will gives you the values of the following attributes:
  • DataSource Name
  • Maximum Capacity of the Connection Pool at Run-time
  • Active Connections Current Count
  • Active Connections High Count
  • Wait Seconds High Count
  • Waiting For Connection Current Count
  • State of the Connection Pool

#=======================================================
# This script will monitor the JDBC CONNECTION POOL
# more details on this script contact: Pavan Devarkonda
#=======================================================
connect("username","passwd","t3://AdminIP:AdminPort")
try:
 poolrtlist=adminHome.getMBeansByType('JDBCConnectionPoolRuntime')
 print ' '
 print ' '
 print 'JDBC CONNECTION POOLS'
 print ' '
 print 'Name Max Active Active WaitSecs Waiting State'
 print ' capacity Current HighCnt HighCnt Count'

 for poolRT in poolrtlist:
  pname = poolRT.getName()
  pmaxcapacity = poolRT.getAttribute("MaxCapacity")
  paccc = poolRT.getAttribute("ActiveConnectionsCurrentCount")
  pachc = poolRT.getAttribute("ActiveConnectionsHighCount")
  pwshc = poolRT.getAttribute("WaitSecondsHighCount")
  pwfccc = poolRT.getAttribute("WaitingForConnectionCurrentCount")
  pstate = poolRT.getAttribute("State")
  print '%10s %7d %7d %7d %7d %7d %10s' % (pname,pmaxcapacity,paccc,pachc, pwshc,pwfccc,pstate)
  print ' '
except:
  print 'Error:'
  dumpStack()
  pass
disconnect()

In Year 2012 revisting the same script

We have trouble in the produciton environment with JDBC Connection pool overloaded. All the ActiveConnectionCount reaching the MaxCapacity of the Connection pool. As a temprory workaround we need to reset the Connection pool for that movement. Permanent cure is tuning the Connection pool. For both solutions we need active monitoring the JDBC Connection pool.
To monitor this we have revisited the same script. Now adding more saus to the script we need to find that ActiveConnectionCount reaching 85% as threshold limit. Once it reaches 85 or greater then monitoring tool like HP OV/some other tool will reads log file generated by the script and then sends warns message to supporting Middleware Admin when threshold crossed. If you have sendmail service on your machine you can send message fromt he script itself.
To do this we had manupulated the above script according to the requirements
  1. We have specified DataSource that are causing this trouble
  2. Used separate properties file for user credentials.
  3. The output of script is redirected to a file
  4. Managed Server name opposite to the monitor values it is tricky but resolved The new script is

#=======================================================
# This script will monitor the JDBC CONNECTION POOL
# Date :  18 Apr 2012
#=======================================================
import sys
import time
try:
        loadProperties('./DBcheck.properties')
        fp=open('ActiveConn.log','a+')
        Date = time.ctime(time.time())
        admurl='t3://'+admAdrs+':'+admPort
        connect(userConfigFile=UCF, userKeyFile=UKEY, url=admurl)
        poolrtlist=adminHome.getMBeansByType('JDBCConnectionPoolRuntime')
        print ' '
        print 'JDBC CONNECTION POOLS'
        print>>fp, '================================================ '

 for poolRT in poolrtlist:
                pname = poolRT.getName()
                pmaxcapacity = poolRT.getAttribute("MaxCapacity")
                paccc = poolRT.getAttribute("ActiveConnectionsCurrentCount")
        
  if pname == 'myDataSource1' or pname == 'myDS2'' or pname == 'myDS3':
                        server= str(poolRT).split(',')[2].split('=')[1]
                        p=(paccc /(pmaxcapacity * 1.0)) * 100 
                        if p >= 85:
                                print >>fp, 'WARNING: The Active connections are Greater than Threshold 85%'
                        print>>fp, '%24s %15s %18s %7d %7d' % (Date,server,pname,pmaxcapacity,paccc)

except Exception, e:
        sys.stderr.write('ERROR: %s\n' % str(e))
        print 'Error:', e
        dumpStack()
        pass
fp.close()
disconnect()


-->

Post script actions

  1. Prepare a Shellscript that will let you know howmuch time it will counsume to run py script.
    clear
    date
    $JAVA_HOME/bin/java weblogic.WLST /path/urscript/jdbcmon.py
    date
    
  2. Schedule a scheduler to run this script autosys or crontab that invokes above shell script.
  3. Configure a monitoring tool frequently lookup the logs and send alert messages such as HP OVO or smpt mailing also fine.
What do you think about this new version of script? Write back your comments and suggestions to deliver better. Refernce Object Mix discussion:
http://objectmix.com/weblogic/549153-weblogic-monitoring-script-wlst-2.html -->

19 comments:

  1. Hi Pavan..thnx for the script.

    Can u Please add to this script on which server the jdbc is configured.

    Suppose we have two managed servers.

    ReplyDelete
  2. Durai,

    This script will work for two managed servers too. ServerName can be displayed when deployed jdbc service target compare and then you can display this monitoring output.

    need to look into the MBean in deeper way and need to update. try your end too let me know if you get it. I will try... presently little busy in my work.

    ReplyDelete
  3. I like this script - it tells me essentially what I need at a glance regarding pools in our domain. However, I don't like that the pools are in a somewhat random order each time.
    I would like to be able to alphabetically sort the JDBC connection pools but have been unable to find a sort routine that works in WLST.

    ReplyDelete
  4. @eldamir sort function you can call on list object. If you want sorted list first store in a list lets say 'poolList'. Then apply sort() on it
    poolList.sort()
    now you can go for display acttion...

    Ref: http://wlstbyexamples.blogspot.com/2009/11/jms-monitoring-using-wlst.html

    ReplyDelete
  5. Thanks Pavan. This was very helpful.

    ReplyDelete
  6. Hi Pavan,

    when I run the 1st script, I'm getting below error.

    Problem invoking WLST - Traceback (innermost last):
    (no code object) at line 0
    File "/scripts/jdbcpool_wlst.py", line 17
    for poolRT in poolrtlist:
    ^
    SyntaxError: inconsistent dedent

    Please help me

    ReplyDelete
  7. @MURALI,

    Thanks for the comment.

    This is indentation issue. Please use tab space after for loop line. This is same as C/Java saying that block with {} braces.

    ReplyDelete
  8. I keep getting error for line:
    Date = time.ctime(time.time())

    Error as:

    AttributeError: java package 'weblogic.time' has no attribute 'time'

    ReplyDelete
  9. Hope you didn't copied import line on the top.

    I have executed the same lines as follows:

    wls:/offline> import time
    wls:/offline> d=time.ctime(time.time())
    wls:/offline> print d
    Fri Apr 26 22:22:22 2013

    ReplyDelete
  10. Hi Pavan,

    Do you have any wlst script for testing all the existing data sources connections in a domain.

    thanks,
    teja

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. for managed server name we can use

    pserver = poolRT.getObjectName().getLocation()

    Thanks,
    Mohideen

    ReplyDelete
  13. Pavan, your blog is amazing. I request for your help. I rarely use WLST script as part of my work. Howvver I am stuck with an issue where I need to have the server start class entries in the my config.xml file.

    I need create the following entries in the config.xml file.


    1.jar:2.jar:3.jar




    I request for syntax WLST script that I need to put inside my CreateDomain.py (jython script) to have the entries above appear in the config.xml during the domain creation processs.

    Your help is appreciated.

    Ram Murthy.

    ReplyDelete
    Replies
    1. My previous xml entries were not posted properly. the config.xml file parameters as working (not in xml format)


      left-angle-bracket server-start right angle bracket
      left-angle-bracket class-path right angle bracket
      1.jar: 2:jar: 3.jar
      left-angle-bracket class-path right angle bracket
      left-angle-bracket server-start right angle bracket

      Delete
    2. Ram Murthy, Can you mail me on wlatechtrainer@gmail.com. Your complete question with code snippet. ready to help you out. where you stuck... Don't hesitate to write to us!

      Cheers! Pavan

      Delete
  14. I get the below error, can u suggest ?
    "
    Problem invoking WLST - Traceback (innermost last):
    (no code object) at line 0
    File "/opt/fedex/hub/stage/cimt.build/jdbc_new.py", line 20
    if pname == 'hubCustLocDataSource' or pname == 'myDS2' or pname == 'myDS3':
    ^
    SyntaxError: inconsistent dedent
    "

    ReplyDelete
  15. poolrtlist=adminHome.getMBeansByType('JDBCConnectionPoolRuntime')

    It's not working anymore ...

    NameError: adminHome

    ReplyDelete

Please write your comment here

Popular Posts