Re: [Bodington-developers] Resources and SoftCache

[Matthew B. <mat...@ou...>]

Jon Maber wrote:
> Matthew Buckett wrote:
> 
>> I'm looking at building a container (eg floor, building) that can easily
>> contain 1000s of children.
> 
> Aha! The recycling building!

The recycle building is one but I am looking at developing personal
space for each user and rather than adopting a hack of splitting based
of the beginning of the name eg:

/users/a/adam/
/users/a/alexis/
/users/b/buckett/

I was hoping to support all the resources in one.

>> In the current implementation when a resource
>> is visited (eg /admin/ground/sys) the code does this:
>>
>> First the root resource gets loaded.
>> Then all the primary keys (PK) of the children are loaded.
>> Then each child PK is used to load the full resource.
>> If the name of the child resource matches the first part of the path
>> (admin) then switch to using this resource and load all the children PKs.
>> Continue doing this until the patch stops matching resources or we run
>> out of path.
>>  
>>
> It works well if you navigate through the site since at each level all
> the children need to be loaded in order to list them in the resource
> menu at each level. It also has the side affect of refreshing accesses
> to items in the cache that other users most frequently access. So, for
> example buildings are most frequently 'touched' in the cache and will be
> available.

At the moment Resources never get collected as we have a pin_down table
in resource that every resource loaded by it's primary key gets added to.

>> The problem is that you may well have to load all the child resources
>> just to find the one you are looking for. This probably isn't as bad as
>> it sounds at first as it is likely that the user will have navigated
>> through the resource tree loading them all anyway.
>>
>> I decided to try to switch to using SoftCache and an additional IndexKey
>> on Resource (parent_resource_id, name) so that I could do direct lookups
>> and avoid having to enumerate through all the children.
>>
>> The problem is that the performance from IndexKey lookups from softcache
>> seems to be very poor, of several orders of magnitude worse that
>> PrimaryKey lookups. I realise that IndexKey has more complex hashCode()
>> and equals() implementations that PrimaryKey() and SoftCache has to
>> lookup stuff in several more hashes but I didn't expect things to be
>> this bad. Is this a known problem?
>>  
>>
> It's not a known problem to me. What is the 'where' clause that your
> IndexKey produces?

    public String whereClause()
    {
        if (parent == null)
            return "parent_resource_id IS NULL AND name = "+
SqlDatabase.quotedSQL(child);
        else
            return "parent_resource_id = "+ parent+ " AND name = "+
SqlDatabase.quotedSQL(child);
    }

I have an index created to this effect but I don't believe this is a
database problem.

>> I'm probably going to take a profiler to it so I can get some proper
>> numbers and ideas about where exactly the problem lies but was wondering
>> if anyone else has any ideas.
>>
>>  
>>
> First thing would be to see if the delay is in the Java execution or the
> database query. You could just try the various queries in the pgadmin
> query tool and look at execution time and the execution plan.

It's all Java as I am running on a database with 30 resources at the
moment and looking at the statistics outputted by the logging in
SoftCache most of the lookups hit the cache.

-- 
 -- Matthew Buckett, VLE Developer
 -- Learning Technologies Group, Oxford University Computing Services
 -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/