[horde] Horde/MySQL problem

[Andrew Morgan]

On Wed, 2 Sep 2009, Gottschalk, David wrote:

> I apologize in advance if this is not a appropriate question for this 
> list, but I'm really not sure where else to post. Any help would be 
> GREATLY appreciated.
>
> I'm having strange difficulties with my instance of Horde/IMP/Mnemo/Nag 
> and a MySQL database server. My configuration is 2 front-end web servers 
> hosting the Horde setup with a single MySQL back-end database.
>
> Everything runs well, except when I start getting queries like this:
>
> SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN 
> horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id LEFT JOIN 
> horde_datatree_attributes a2 ON a2.datatree_id = c.datatree_id WH ERE 
> c.group_uid = 'horde.shares.mnemo' AND (a1.attribute_name = 'owner' AND 
> a1.attribute_value = 'removed at emory.edu')  AND a2.attribute_name = 
> 'name' GROUP BY c.datatree_id, c.datatree_name, c.datatree_order , 
> a2.attribute_value ORDER BY a2.attribute_value ASC;
>
> These queries seem to cause the database to choke when a lot of them 
> come through. Some of them have the group_uid set to horde.shares.nag. I 
> know that Nag and Mnemo use datatree. After a while, MySQL stop 
> responding entirely and it has to be restarted. It appears that this is 
> caused by this types of queries having to be written to tmp disk. I've 
> set my MySQL tmpdir to /dev/shm which is a memory based file system to 
> help improve disk response times.
>
> Anyways, I'm not sure what I can do to improve this. I've already done a 
> ton of MySQL tuning to no avail.

Are you running the latest release of Horde?  I remember having this same 
problem in the past, but it was fixed in one of the recent releases.  I 
also have the original patches that fixed it.

Part of the fix included adding some indexes on the datatree_attributes 
table.  You might check that you have all the indexes from the 
scripts/sql/horde_datatree.mysql.sql file applied.

 	Andy