[imp] my experience

[Jie Gao]

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.

Regards,



Jie