[imp] my experience

[Kevin M. Myer]

Quoting Jie Gao <J.Gao at isu.usyd.edu.au>:

> Hi All
>
> I'd like to share some of my experience upgrading to:
>
> horde-3.0.7 + imp-h3-4.0.3.
>
> I, and my users, liked the new features, but I immediately ran into a
> huge performance problem. I immediately moved the Mysql server to a
> separate machine, an Opteron dual-core 2-CPU HP machine with 8GB of
> memory.  And it still could not handle the load the mysql server put on
> it, with up to 80% of CPU hogged up by the mysql server. The system
> became unusable with over 100 or so users.
>
> We have found the following sql query to seem to be the main culprit:
>
> SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN 
> horde_datatree_attributes a1 ON
> a1.datatree_id = c.datatree_id
> WHERE c.group_uid = 'horde.shares.nag' AND ((a1.attribute_name = 
> 'owner' AND a1.attribute_value =
> 'xxx at imap.xxx.xxx.xxx') OR (
> a1.attribute_name = 'perm_users' AND a1.attribute_key = 
> 'xxx at imap.xxx.xxx.xxx' AND a1.attribute_value
> & 8) OR (a1.attribute_nam
> e = 'perm_creator' AND a1.attribute_value & 8) OR (a1.attribute_name 
> = 'perm_default' AND
> a1.attribute_value & 8))   GROUP BY c.data
> tree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, 
> c.datatree_name, c.datatree_id;
>
> It took 1min30sec to run this query with 200 concurrent users on the
> system.
>
> We tried everything to tune the mysql server and gave it plenty of
> connections, query cache, thread-cache, table cache, etc.  We even put
> a partial index on the column attribute_value of the
> horde_datatree_attributes table, which is a blob/text field.
>
> We have had to roll back and downgrade to an old verion of Hore/IMP.
>
> I hope this will serve as a warning to those people who are thinking of
> upgrading. I didn't think I had one myself.

Horde 3.1 will have some improvements in place to help performance.  A 
big one is gained by splitting the History out of the Datatree and into 
its own separate table.  See http://bugs.horde.org/ticket/?id=2298

Kevin

-- 
Kevin M. Myer
Senior Systems Administrator
Lancaster-Lebanon Intermediate Unit 13  http://www.iu13.org