[horde] Horde/MySQL problem

[lst_hoe02 at kwsoft.de]

Zitat von "Gottschalk, David" <dgottsc at emory.edu>:

> 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.

Recent Horde Versions can move "Groups" and "Permissions" out of  
datatree which should perform better and lead to more simple queries.  
You should also check if you have configured caching to reduce the  
load further.

Use the supplied database creation scripts to check if all index are  
set correct, this is sometimes a problem if the installation is  
updated and not all relevant scripts have been run.

Last but not least. Your Database can get slow on load, but it should  
not stop working at all. Check if you have enough memory/diskspace and  
have a look at the error logs of the DB.

Regards

Andreas