[horde] Horde 3.1.3 and datatree related performance problems

[Chris Stromsoe]

On Fri, 17 Nov 2006, Jan Schneider wrote:
> Zitat von Chris Stromsoe <cbs at cts.ucla.edu>:
>
>> If I disable Kronolith, the sidebar loads and all of the applications 
>> work fine.
>>
>> If I disable the datatree, Kronolith works great, but I can't add any 
>> new events to Nag.
>
> This should not happen with the latest Nag version. Do you really use 
> 2.1.2?

cbs:/web/horde > cat nag/lib/version.php
<?php define('NAG_VERSION', 'H3 (2.1.2)') ?>

When horde/config/conf.php has $conf['datatree']['driver'] = 'null' the 
"New Task" link does not show up in the top menu bar.  When I set the 
driver to 'sql' the link for New Task does show up.  This is with all 
stable H3 apps pulled just a few days ago.


>> 2) What do I actually lose if I disable the datatree?
>
> The ability to share calendars, task lists, etc., and set permissions, 
> use groups.

It sounds like disabling the datatree isn't going to work for me, then. 
I don't use groups and am not doing calendar or tasklist sharing right 
now, but I do need to use the permissions system to allow show/read for a 
shared ldap directory from turba and I will be doing shared calendaring in 
the future.


>> 3) What are the above queries trying to do?  Are there any patches 
>> floating around that flatten the datatree queries and do the login as 
>> php code?
>
> In CVS HEAD there is code to cache shares and groups so that at least 
> these queries are only necessary once per login. There are also some 
> patches to flatten the DataTree structure in bugs.horde.org, but these 
> are untested.

I have partially fixed the kronolith problems.  I've added two indexes 
which have cut query time down significantly.

1) There is no index created for horde_datatree(datatree_id).  I added one 
and cut one set of queries from ~30s down to .5s:

CREATE INDEX datatree_id_idx ON horde_datatree (datatree_id);

2) There is a (default) BTREE index for 
horde_datatree_attributes(datatree_id), but the postgresql query planner 
was electing not to use it whenever there were more than 500 to 600 items 
in the IN(*) collection.  I added a hash index and cut another set of 
queries from 5m - 6m down to 10s or 11s:

CREATE INDEX datatree_attribute_id_idx ON horde_datatree_attributes USING 
hash(datatree_id);

That isn't *great*, but is significantly better than I was seeing before. 
I suspect similar indexes may help other people with PG that are having 
performance problems.


Having gotten my SQL problems somewhat solved, I'm now having timeouts in 
the PHP code.

Calls to listCalendars() with $ownersonly = false take forever to 
complete; loading calendars.php takes more than 20 minutes.  Changing 
listCalendars() to force $ownersonly = true fixes the performance problem, 
but I suspect won't be a very useful solution.

Any ideas about where to look to speed things up?



-Chris