Slow User Lookup

[bprucha]

Anyone else experience slow user lookup?  A lookup of a single user takes about 10 seconds from any place other than the "Manage System Users" section.  The "Manage System Users" lookup comes back immediately.  I am running Alfresco Community 2.1 and have a little over 1000 users.  I am syncing the user and group accounts using a custom ExportSource similar to org.alfresco.repo.security.authentication.ldap.LDAPGroupExportSource and LDAPPersonExportSource except that I am using JAAS authentication and syncing with a database rather than an LDAP server, if that makes any difference.

Any responses saying you do or don't have any slowdown with over 1000 users would be appreciated so I can confirm I have a problem with my configuration.  Of course solutions are welcome as well (:


Thanks,

Brett

[bprucha]

Some more info on my problem.

I isolated the code by looking up the org.alfresco.web.bean.GroupsBean pickerCallback which is executed when looking up a user to add to a group.  I placed this code into an Alfresco embedded application and ran it on my workstation.  The code is:

package mil.navy.scr;

import java.util.Date;
import java.util.List;

import javax.transaction.UserTransaction;

import org.alfresco.model.ContentModel;
import org.alfresco.repo.security.authentication.AuthenticationComponent;
import org.alfresco.service.ServiceRegistry;
import org.alfresco.service.cmr.repository.NodeRef;
import org.alfresco.service.cmr.repository.NodeService;
import org.alfresco.service.cmr.security.PermissionService;
import org.alfresco.service.cmr.security.PersonService;
import org.alfresco.service.namespace.NamespaceService;
import org.alfresco.service.transaction.TransactionService;
import org.alfresco.util.ApplicationContextHelper;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;

public class Test {

    private static Log s_logger = LogFactory.getLog(Test.class);

    private PersonService personService;

    private NodeService nodeService;

    public void setPersonService(PersonService personService)
    {
        this.personService = personService;
    }
    
    public void setNodeService(NodeService nodeService)
    {
        this.nodeService = nodeService;
    }

    public void startTest() throws Exception {
         ApplicationContext context = ApplicationContextHelper.getApplicationContext();
   
        
         UserTransaction tx = null;
         AuthenticationComponent authComponent = (AuthenticationComponent) context.getBean("AuthenticationComponent");
         authComponent.setSystemUserAsCurrentUser();
         
         Date start = new Date();
         System.out.println("Looking up users: " + Long.toString(start.getTime()));

         try
         {
            TransactionService txs = (TransactionService) context.getBean("transactionComponent");
            tx = txs.getUserTransaction();
            tx.begin();
            
            // build xpath to match available User/Person objects
            ServiceRegistry services = (ServiceRegistry) context.getBean(ServiceRegistry.SERVICE_REGISTRY);
            NodeRef peopleRef = personService.getPeopleContainer();
            String xpath = "*[like(@" + NamespaceService.CONTENT_MODEL_PREFIX + ":" + "firstName, '%Doe%', false)" +
                    " or " + "like(@" + NamespaceService.CONTENT_MODEL_PREFIX + ":" + "lastName, '%Doe%', false)]";
            
            List<NodeRef> nodes = services.getSearchService().selectNodes(
                  peopleRef,
                  xpath,
                  null,
                  services.getNamespaceService(),
                  false);
            
            for (NodeRef personRef : nodes)
            {
               String username = (String)this.nodeService.getProperty(personRef, ContentModel.PROP_USERNAME);
               if (PermissionService.GUEST_AUTHORITY.equals(username) == false)
               {
                  String firstName = (String)this.nodeService.getProperty(personRef, ContentModel.PROP_FIRSTNAME);
                  String lastName = (String)this.nodeService.getProperty(personRef, ContentModel.PROP_LASTNAME);
                  
                  System.out.println(lastName + ", " + firstName);
               }
            }
            
            // commit the transaction
            tx.commit();
         }
         catch (Exception err)
         {
            try { if (tx != null) {tx.rollback();} } catch (Exception tex) {}
            throw err;
         }
         finally {
            Date stop = new Date();
            System.out.println("Done: " + Long.toString(stop.getTime()));
            System.out.println(Long.toString(stop.getTime() - start.getTime()) + " ms\r\n");
            
            authComponent.clearCurrentSecurityContext(); 
         }
    }

    /**
    * @param args
    */
   public static void main(String[] args) throws Exception {
      ApplicationContext context = ApplicationContextHelper.getApplicationContext();
      Test test = (Test) context.getBean("scrTest");
      test.startTest();
      test.startTest();
      test.startTest();
      test.startTest();
      test.startTest();
      test.startTest();
      test.startTest();
   }

}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

And the output is:

11:53:54,842 INFO  [service.descriptor.DescriptorService] Alfresco started (Community Network): Current version 2.1.0 (482) schema 64 - Installed version 2.1.0 (482) schema 64
Looking up users: 1204908835323
Doe, John
Done: 1204908918947
83624 ms

Looking up users: 1204908918952
Doe, John
Done: 1204908989867
70915 ms

Looking up users: 1204908989872
Doe, John
Done: 1204909059687
69815 ms

Looking up users: 1204909059692
Doe, John
Done: 1204909129566
69874 ms

Looking up users: 1204909129571
Doe, John
Done: 1204909199414
69843 ms

Looking up users: 1204909199420
Doe, John
Done: 1204909268074
68654 ms

Looking up users: 1204909268079
Doe, John
Done: 1204909337184
69105 ms
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This was run on a Dual Core AMD 2.3GHz with 2GB of RAM with a SQL Server back end on a Quad Core Intel Xeon 3GHz with 2GB of ram.  The machine running the code was hitting 45% CPU usage during the lookup while the database CPU was below 3% usage.

[loftux]

Just a thought, ms sql server sometimes has problems with multiple cores
http://www.google.com/search?q=ms+sql+server+%27multiple+processor%27+performance
Looks like it's not this affecting your installation with only 3% cpu usage, but in order to rule out you can try running on the sql server with only 1 core.

Regards,
Peter Löfgren

[bprucha]

After doing some more digging I have discovered that the probelm comes down to the repository XPath search language implimentation.  The XPath query executes a database to get all the users.  Then for each individual user another query is executred trying to get child associations so over 1000 queries are being made to the database.  The loop going over every user is also doing a regualr expression comparisons between the search string and first and last names.  Then when the node references mathing the criteria are returned more database lookups are done to get the first and last name properties of the node ref.  On top of that the Jaxen version adding the XPath support is beta and upgrading to the latest stable can't be done because one of the extension functions in the beta doesn't exists any more.  I hacked my way around that by removing the dependence on the extended function but that didn't help the XPath search any.

The Manage System Users page uses the Lucene language.  The only database commands that are executed are the ones to find the first and last name of the node references that match the criteria.  But even that's not very good if the search returns allot of node references.  The first and last name properties for each user are done in separate queries rather than being returned in a single lookup.

Any developers care to comment on how to get the first and last names of users that match a criteria in a single lookup?