[horde] Simple Solution to 'Database performance problem with Horde3/IMP4 with a large user base'

Mon Aug 28 02:23:19 PDT 2006

Thanks to Ioannis Koutsileos, the simple solution I proposed was implemented and has been working great for us for the past week. Hope this helps others as well.

Christopher Akritidis 
Head of O/S & Integration Unit 
O/S & Integration Unit - Hellas On Line 

Tel: +30 213 000 4303 
Fax: +30 210 8056 796 
Mob.: +30 6948 110650 
e-mail: Xristoforos.Akritidis at hol.net <mailto:Xristoforos.Akritidis at hol.net>  

Get the HOL of it

Hellas On Line 
Διεύθυνση: Αγ. Κωνσταντίνου 59-61 (Κτίριο Β), 151 24 Μαρούσι Τηλέφωνο: (+30) 210 8762 000 FAX: (+30) 210 8056 790 URL: www.hol.gr <http://www.hol.gr/>  e-mail: info at hol.gr <mailto:info at hol.gr>  

________________________________

From: Koutsileos Ioannis 
Sent: Friday, August 25, 2006 2:39 PM
To: Akritidis Xristoforos
Subject: RE: Simple Solution to 'Database performance problem with Horde3/IMP4 with a large user base'

In our installation, we managed to improve the efficiency of  horde/imp4 dramatically (e.g. loading Inbox reduced to an average of 8 secs from an earlier average of 50 secs), by splitting the queries generated dynamically in function getByAttributes at /horde/lib/Horde/DataTree/sql.php to simpler ones when we find OR clauses in them.

For example, Horde is using the following query that takes nearly 5.65 sec:

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 = 'cakrit at hol.gr') OR 

 (a1.attribute_name = 'perm_users' AND a1.attribute_key = 'cakrit at hol.gr' AND a1.attribute_value & 2) OR 

 (a1.attribute_name = 'perm_creator' AND a1.attribute_value & 2) OR 

 (a1.attribute_name = 'perm_default' AND a1.attribute_value & 2)

)   GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, c.datatree_name, c.datatree_id;

We build instead 4 queries:       

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 = 'cakrit at hol.gr'  

GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, c.datatree_name, c.datatree_id;

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 = 'perm_users' AND a1.attribute_key = 'cakrit at hol.gr' AND a1.attribute_value & 2

GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, c.datatree_name, c.datatree_id;

.....

.....

We also noticed that in our installation, we always get as a result one row and more over this is always returned by one query (the first split query). 

By taking advantage of this fact we modified /horde/lib/Horde/DataTree/sql.php as shown in the attachment.

We hope this change will help others as well!!

Best Regards

Ιωάννης Κουτσιλέος 
S/W Developer 
O/S & Integration Unit - Hellas On Line 

Tel: +30 210 8762 326 
Fax: +30 213 000 5300 
e-mail: Ioannis.Koutsileos at hol.net <mailto:Ioannis.Koutsileos at hol.net>  

Get the HOL of it

Hellas On Line 
Διεύθυνση: Αγ. Κωνσταντίνου 59-61 (Κτίριο Β), 151 24 Μαρούσι Τηλέφωνο: (+30) 210 8762 000 FAX: (+30) 210 8056 790 URL: www.hol.gr <http://www.hol.gr/>  e-mail: info at hol.gr <mailto:info at hol.gr>  

________________________________

From: Akritidis Xristoforos 
Sent: Wednesday, August 16, 2006 2:45 PM
To: 'horde at lists.horde.org'
Cc: Koutsileos Ioannis
Subject: Simple Solution to 'Database performance problem with Horde3/IMP4 with a large user base'

The DB performance problem with the datatree permission query reported last year has a very simple solution. All you need to do is send many different queries instead of a single one with a bunch of OR statements in it. mysql will not use an index if your statement has OR clauses with the fields comprising the index. 

Example : 

The following query takes 5.65sec in my case:

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 = 'cakrit at hol.gr') OR 

 (a1.attribute_name = 'perm_users' AND a1.attribute_key = 'cakrit at hol.gr' AND a1.attribute_value & 2) OR 

 (a1.attribute_name = 'perm_creator' AND a1.attribute_value & 2) OR 

 (a1.attribute_name = 'perm_default' AND a1.attribute_value & 2)

)   GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, c.datatree_name, c.datatree_id;

The reason is that it uses the wrong key (datatree_group_idx - 11760 rows). When instead I run 4 different queries for each of the OR statements, each query takes under 0.1 sec

Ex: 

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 = 'cakrit at hol.gr'  

GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY c.datatree_order, c.datatree_name, c.datatree_id;

This one uses the datatree_attribute_name_idx, with just one row.

So all one has to do is split the SELECT into 4 queries and join the results. I don't know much about PHP or how Horde works, so it will be a pain for me to provide the fix. But if someone can point me to the part of the code that does it, I will have one of my people do it. 

Christopher Akritidis

Hellas On Line, Greece

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: getByAttributes.txt
Url: http://lists.horde.org/archives/horde/attachments/20060828/250c8171/getByAttributes-0001.txt