[esb-issues] [JBoss JIRA] Commented: (JBESB-1675) Possible Juddi Performance problems

[Kevin Conner (JIRA)]

    [ https://jira.jboss.org/jira/browse/JBESB-1675?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12422914#action_12422914 ] 

Kevin Conner commented on JBESB-1675:
-------------------------------------

I am still going through the unregister process but, to give a flavour of the extent of the issue, here is the SQL summary for unregistering the (single) EPRs of the standard eight services.

      8 sql is: DELETE FROM BINDING_CATEGORY WHERE BINDING_KEY=?
      8 sql is: DELETE FROM BINDING_DESCR WHERE BINDING_KEY=?
      8 sql is: DELETE FROM BINDING_TEMPLATE WHERE BINDING_KEY=?
      8 sql is: DELETE FROM INSTANCE_DETAILS_DESCR WHERE BINDING_KEY=?
      8 sql is: DELETE FROM INSTANCE_DETAILS_DOC_DESCR WHERE BINDING_KEY=?
      8 sql is: DELETE FROM TMODEL_INSTANCE_INFO_DESCR WHERE BINDING_KEY=?
      8 sql is: DELETE FROM TMODEL_INSTANCE_INFO WHERE BINDING_KEY=?
      8 sql is: INSERT INTO AUTH_TOKEN (AUTH_TOKEN,PUBLISHER_ID,PUBLISHER_NAME,CREATED,LAST_USED,NUMBER_OF_USES,TOKEN_STATE) VALUES (?,?,?,?,?,0,1)
     16 sql is: SELECT AUTHORIZED_NAME,OPERATOR FROM BUSINESS_ENTITY WHERE BUSINESS_KEY=?
      8 sql is: SELECT AUTHORIZED_NAME,OPERATOR,NAME,LANG_CODE,OVERVIEW_URL,DELETED FROM TMODEL WHERE TMODEL_KEY=? AND DELETED IS NULL
      8 sql is: SELECT B.BINDING_KEY,B.LAST_UPDATE FROM BINDING_TEMPLATE B WHERE B.SERVICE_KEY = ? ORDER BY B.LAST_UPDATE DESC
    104 sql is: SELECT BINDING_KEY,ACCESS_POINT_TYPE,ACCESS_POINT_URL,HOSTING_REDIRECTOR FROM BINDING_TEMPLATE WHERE SERVICE_KEY=?
    120 sql is: SELECT BUSINESS_KEY FROM BUSINESS_SERVICE WHERE SERVICE_KEY=?
      8 sql is: SELECT * FROM BUSINESS_ENTITY e, BUSINESS_SERVICE s, BINDING_TEMPLATE t WHERE s.SERVICE_KEY = t.SERVICE_KEY AND e.BUSINESS_KEY = s.BUSINESS_KEY AND t.BINDING_KEY=? AND e.PUBLISHER_ID=?
     52 sql is: SELECT LANG_CODE,DESCR, BINDING_DESCR_ID FROM BINDING_DESCR WHERE BINDING_KEY=? ORDER BY BINDING_DESCR_ID
      8 sql is: SELECT LANG_CODE,DESCR, BUSINESS_DESCR_ID FROM BUSINESS_DESCR WHERE BUSINESS_KEY=? ORDER BY BUSINESS_DESCR_ID
    104 sql is: SELECT LANG_CODE,DESCR, SERVICE_DESCR_ID FROM SERVICE_DESCR WHERE SERVICE_KEY=? ORDER BY SERVICE_DESCR_ID
      8 sql is: SELECT LANG_CODE,NAME, BUSINESS_NAME_ID FROM BUSINESS_NAME WHERE BUSINESS_KEY=? ORDER BY BUSINESS_NAME_ID
    112 sql is: SELECT LANG_CODE,NAME, SERVICE_NAME_ID FROM SERVICE_NAME WHERE SERVICE_KEY=? ORDER BY SERVICE_NAME_ID
      8 sql is: SELECT LAST_USED FROM AUTH_TOKEN WHERE AUTH_TOKEN=?
      8 sql is: SELECT LINE,KEY_NAME,KEY_VALUE, ADDRESS_LINE_ID FROM ADDRESS_LINE WHERE BUSINESS_KEY=? AND CONTACT_ID=? AND ADDRESS_ID=? ORDER BY ADDRESS_LINE_ID
      8 sql is: SELECT M.TMODEL_KEY,M.LAST_UPDATE,M.NAME,M.DELETED FROM TMODEL M WHERE M.DELETED IS NULL AND M.NAME = ? ORDER BY M.NAME DESC,M.LAST_UPDATE DESC
      8 sql is: SELECT PUBLISHER_ID,PUBLISHER_NAME FROM AUTH_TOKEN WHERE AUTH_TOKEN=?
      8 sql is: SELECT PUBLISHER_NAME,EMAIL_ADDRESS,IS_ADMIN,IS_ENABLED,MAX_BUSINESSES,MAX_SERVICES_PER_BUSINESS,MAX_BINDINGS_PER_SERVICE,MAX_TMODELS FROM PUBLISHER WHERE PUBLISHER_ID=?
     60 sql is: SELECT SERVICE_KEY,ACCESS_POINT_TYPE,ACCESS_POINT_URL,HOSTING_REDIRECTOR FROM BINDING_TEMPLATE WHERE BINDING_KEY=?
      8 sql is: SELECT SERVICE_KEY FROM BUSINESS_SERVICE WHERE BUSINESS_KEY=?
      8 sql is: SELECT S.SERVICE_KEY,S.LAST_UPDATE FROM BUSINESS_SERVICE S,SERVICE_CATEGORY C WHERE C.SERVICE_KEY = S.SERVICE_KEY AND ((C.TMODEL_KEY_REF = ? AND C.KEY_VALUE = ?)) ORDER BY S.LAST_UPDATE DESC
      8 sql is: SELECT S.SERVICE_KEY,S.LAST_UPDATE,N.NAME FROM BUSINESS_SERVICE S,SERVICE_NAME N WHERE N.SERVICE_KEY = S.SERVICE_KEY AND ((UPPER(NAME) = ?)) AND S.SERVICE_KEY IN (?,?,?,?,?,?,?,?) ORDER BY N.NAME DESC,S.LAST_UPDATE DESC
     52 sql is: SELECT TMODEL_KEY,OVERVIEW_URL,INSTANCE_PARMS, TMODEL_INSTANCE_INFO_ID FROM TMODEL_INSTANCE_INFO WHERE BINDING_KEY=? ORDER BY TMODEL_INSTANCE_INFO_ID
     52 sql is: SELECT TMODEL_KEY_REF,KEY_NAME,KEY_VALUE, CATEGORY_ID FROM BINDING_CATEGORY WHERE BINDING_KEY=? ORDER BY CATEGORY_ID
      8 sql is: SELECT TMODEL_KEY_REF,KEY_NAME,KEY_VALUE, CATEGORY_ID FROM BUSINESS_CATEGORY WHERE BUSINESS_KEY=? ORDER BY CATEGORY_ID
    104 sql is: SELECT TMODEL_KEY_REF,KEY_NAME,KEY_VALUE, CATEGORY_ID FROM SERVICE_CATEGORY WHERE SERVICE_KEY=? ORDER BY CATEGORY_ID
      8 sql is: SELECT TMODEL_KEY_REF,KEY_NAME,KEY_VALUE, IDENTIFIER_ID FROM BUSINESS_IDENTIFIER WHERE BUSINESS_KEY=? ORDER BY IDENTIFIER_ID
      8 sql is: SELECT TOKEN_STATE FROM AUTH_TOKEN WHERE AUTH_TOKEN=?
      8 sql is: SELECT USE_TYPE,EMAIL_ADDRESS, EMAIL_ID FROM EMAIL WHERE BUSINESS_KEY=? AND CONTACT_ID=? ORDER BY EMAIL_ID
      8 sql is: SELECT USE_TYPE,PERSON_NAME, CONTACT_ID FROM CONTACT WHERE BUSINESS_KEY=? ORDER BY CONTACT_ID
      8 sql is: SELECT USE_TYPE,PHONE_NUMBER, PHONE_ID FROM PHONE WHERE BUSINESS_KEY=? AND CONTACT_ID=? ORDER BY PHONE_ID
      8 sql is: SELECT USE_TYPE,SORT_CODE,TMODEL_KEY, ADDRESS_ID FROM ADDRESS WHERE BUSINESS_KEY=? AND CONTACT_ID=? ORDER BY ADDRESS_ID
      8 sql is: SELECT USE_TYPE,URL, DISCOVERY_URL_ID FROM DISCOVERY_URL WHERE BUSINESS_KEY=? ORDER BY DISCOVERY_URL_ID
      8 sql is: UPDATE AUTH_TOKEN SET LAST_USED=?,NUMBER_OF_USES=NUMBER_OF_USES+1 WHERE AUTH_TOKEN=?

So there are 136 access made to register the eight services and 1016!!!! accesses to unregister the eight services.

> Possible Juddi Performance problems
> -----------------------------------
>
>                 Key: JBESB-1675
>                 URL: https://jira.jboss.org/jira/browse/JBESB-1675
>             Project: JBoss ESB
>          Issue Type: Bug
>      Security Level: Public(Everyone can see) 
>          Components: Registry and Repository
>    Affects Versions: 4.2.1
>            Reporter: Tom Cunningham
>            Assignee: Kevin Conner
>             Fix For: 4.4
>
>         Attachments: esb-util-cache-src.jar, esb-util-cache.sar, server.rar
>
>
> From forum post :
> We are not using juddi directly, I do not know if it is a core required component of ESB. After a few runs, we are having performance problems that seems to be related to juddi. It happens only on shutdow/startup operations. The time spent to create the auth tokens (sorry, what are they used for anyway??) are taking longer and longer. JBoss takes over 2 minutes (in a 8 processor machine??) just to delete the template bindings for juddi on the shutdown process. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira