[horde] Horde/MySQL problem - Resolved

[Gottschalk, David]

Andrew,
  Thanks for the tip(s). The problem was in fact that my horde_datatree_attributes table was not updated properly, and was missing one index. I recently had updated horde and all the sub-applications, and was not going to be using the datatree anymore. I didn't realize though that the groups were stored in the datatree. I've disabled them now anyways. 

Thanks all for the help! Greatly appreciated. The MySQL server is now functioning happily with no load! : )

David Gottschalk 
Emory University
UTS Messaging Team



-----Original Message-----
From: horde-bounces at lists.horde.org [mailto:horde-bounces at lists.horde.org] On Behalf Of Gottschalk, David
Sent: Wednesday, September 02, 2009 3:51 PM
To: Andrew Morgan
Cc: horde at lists.horde.org
Subject: Re: [horde] Horde/MySQL problem

That's interesting.

I'm running Horde Version 3.3.2.

Should I be upgrading to the absolute latest?

David Gottschalk
UTS Email team
david.gottschalk at emory.edu


-----Original Message-----
From: Andrew Morgan [mailto:morgan at orst.edu]
Sent: Wednesday, September 02, 2009 3:44 PM
To: Gottschalk, David
Cc: horde at lists.horde.org
Subject: Re: [horde] Horde/MySQL problem

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

This e-mail message (including any attachments) is for the sole use of
the intended recipient(s) and may contain confidential and privileged
information.  If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, distribution
or copying of this message (including any attachments) is strictly
prohibited.

If you have received this message in error, please contact
the sender by reply e-mail message and destroy all copies of the
original message (including attachments).
-- 
Horde mailing list - Join the hunt: http://horde.org/bounties/#horde
Frequently Asked Questions: http://horde.org/faq/
To unsubscribe, mail: horde-unsubscribe at lists.horde.org