[dev] datatree performance (again)

[Didi Rieder]

Quoting Jan Schneider <jan at horde.org>:

> Zitat von Didi Rieder <adrieder at sbox.tugraz.at>:
>
>> Hi,
>>
>> as far as I know I disabled all shareing prefs for turba, nag and kronolith.
>> IMP also doesn't use tasks.
>> But my database retuns over 32,000 rows for each the folowing queries:
>>
>> select count(*) from horde_datatree where horde_datatree.group_uid =
>> 'horde.shares.nag';
>>
>> select count(*) from horde_datatree where horde_datatree.group_uid =
>> 'horde.shares.kronolith';
>>
>> And I see many queries of the following type:
>>
>> 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.kronolith'
>> AND (
>> (
>> a1.attribute_name = 'owner'
>> AND a1.attribute_value = 'misch at sbox'
>> )
>> OR (
>> a1.attribute_name = 'perm_users'
>> AND a1.attribute_key = 'misch at sbox'
>> 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;
>>
>> 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 xxx'
>> )
>> OR (
>> a1.attribute_name = 'perm_users'
>> AND a1.attribute_key = 'xxx at xxx'
>> 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;
>>
>> Those queries take about 8 seconds to complete and are bringing the
>> MySql server to its knees.
>
> What exactly is the problem with this query? The number of entries in
> the datatree tables, or the number of queries per time? What happens
> if you run the query on the system while it has no load?

I think the number of entries. (But maybe the truth is a mixture of both)

When delete the horde.shares.[nag|kronolith] entries from the database using:

delete horde_datatree,horde_datatree_attributes from  
horde_datatree,horde_datatree_attributes where  
horde_datatree.group_uid = 'horde.shares.nag' and  
horde_datatree_attributes.datatree_id = horde_datatree.datatree_id;

(the same for horde.shares.kronolith)

...and wait until a thausand of default shares are added the query is  
performing well. (The default shares are then added automatically I'll  
have to see how many entries it takes to slow down the whole process)

But I'll keep an eye on it...

Didi


-- 
-------------------------
Didi Rieder
adrieder at sbox.tugraz.at
PGPKey ID: 3431D0B0
-------------------------