Poor performance of Browse Groups in Share

[vamirr]

We've found an oddity with Browse Groups within the Share Admin Console.  This is behavior is being exhibited under 3.4.

The repository for the behavior described below has approximately 3000 users, 4000 groups, 800 sites and 1.5TB of data.

When the admin user clicks on Browse groups, the resulting web control does not populate.   I went looking in the slow query log and found the query below.  Running that query manually in mysql takes 15 seconds to complete.   This would explain why the web control is empty; it gives up before the query completes.  The error in the alfresco.log that occurs some time after each press of browse groups is below.  No other errors exist in the log.

The system has good usability except for this one control, which is completely unusable.   At this point we have no way of creating groups in Share.  Searching groups in the group console works fine.   Creation of groups in Alfresco Explorer works fine.  Listing all groups in Alfresco Explore is also functional.   Similar queries like searching all users and sites execute very quickly. 

Anyone else having these problems?  Any ideas?



Result displayed in Share:
[img]https://lh5.googleusercontent.com/-bIdl8NdotYQ/TvMbzLI9tQI/AAAAAAAAAXQ/PvlMPc9mNWU/s844/browse%25252...[/img]


Browse Groups Query:

select             assoc.id                    as id,             parentNode.id               as parentNodeId,             parentStore.protocol        as parentNodeProtocol,             parentStore.identifier      as parentNodeIdentifier,             parentNode.uuid             as parentNodeUuid,             childNode.id                as childNodeId,             childStore.protocol         as childNodeProtocol,             childStore.identifier       as childNodeIdentifier,             childNode.uuid              as childNodeUuid,             assoc.type_qname_id         as type_qname_id,             assoc.child_node_name_crc   as child_node_name_crc,             assoc.child_node_name       as child_node_name,             assoc.qname_ns_id           as qname_ns_id,             assoc.qname_localname       as qname_localname,             assoc.is_primary            as is_primary,             assoc.assoc_index           as assoc_index              from          (              alf_child_assoc assoc              join alf_node parentNode on (parentNode.id = assoc.parent_node_id)              join alf_store parentStore on (parentStore.id = parentNode.store_id)              join alf_node childNode on (childNode.id = assoc.child_node_id)              join alf_store childStore on (childStore.id = childNode.store_id)          )          LEFT OUTER JOIN          (              alf_child_assoc a              JOIN alf_child_assoc z2 ON (z2.parent_node_id = 455 AND z2.child_node_id = a.parent_node_id )          )          ON (a.child_node_id = childNode.id AND a.type_qname_id = 87)         where             parentNode.id = 455 and             a.child_node_id IS NULL; ‍

Error Message In Log:

2011-12-22 06:44:52,033 WARN  [org.springframework.extensions.webscripts.connector.RemoteClient] Exception during close() of HTTP API connection
ClientAbortException:  java.net.SocketException: Connection reset
        at org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:358)
        at org.apache.tomcat.util.buf.ByteChunk.flushBuffer(ByteChunk.java:432)
        at org.apache.catalina.connector.OutputBuffer.doFlush(OutputBuffer.java:309)
        at org.apache.catalina.connector.OutputBuffer.close(OutputBuffer.java:273)
        at org.apache.catalina.connector.CoyoteOutputStream.close(CoyoteOutputStream.java:104)
        at org.springframework.extensions.webscripts.connector.RemoteClient.service(RemoteClient.java:956)
        at org.springframework.extensions.webscripts.connector.RemoteClient.call(RemoteClient.java:498)
        at org.springframework.extensions.webscripts.connector.HttpConnector.call(HttpConnector.java:136)
        at org.springframework.extensions.webscripts.servlet.mvc.EndPointProxyController.handleRequestInternal(EndPointProxyController.java:273)
        at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
        at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
        at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
        at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
        at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
        at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:195)
        at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:159)
        at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:141)
        at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:90)
        at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:417)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.alfresco.web.site.servlet.SSOAuthenticationFilter.challengeOrPassThrough(SSOAuthenticationFilter.java:611)
        at org.alfresco.web.site.servlet.SSOAuthenticationFilter.doFilter(SSOAuthenticationFilter.java:334)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:555)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:444)
        at org.apache.coyote.ajp.AjpProtocol$AjpConnectionHandler.process(AjpProtocol.java:372)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:396)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:96)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
        at org.apache.coyote.ajp.AjpProcessor$SocketOutputBuffer.doWrite(AjpProcessor.java:1249)
        at org.apache.coyote.Response.doWrite(Response.java:560)
        at org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:353)
        … 46 more

[jjengel11]

was there ever any resolution to this?  i am having the exact same problem where this query takes over 4 minutes to run on my system.  using percona 5.5 and then upgraded to 5.6.  When I hint the query to use a specific index

LEFT OUTER JOIN ( alf_child_assoc a JOIN alf_child_assoc z2 USE INDEX (FK_ALF_CASS_CNODE) ON (z2.parent_node_id = 455 AND z2.child_node_id = a.parent_node_id )     

it runs in under a second.  is there any fix besides using the hint for this dynamically generated query .

I have about 350,000 rows in the alf_child_assoc table.  when the query returns, I get ~ 11,400 results back  

[mrogers]

If it is still a problem in alfresco please make sure a JIRA is logged otherwise it will never get fixed.   I've got a dim memory that this was fixed a few years ago.

[jjengel11]

thanks for the response.  i will log a ticket.  perhaps it is the version of alfresco that we are on that does not have the fix?  i'll submit the ticket and see if they can tell me what version should have the fix.