[dev] shares performance hit (was Re: disable shares)

[Didi Rieder]

Quoting Didi Rieder <adrieder at sbox.tugraz.at>:

> Quoting Jan Schneider <jan at horde.org>:
>
>> Zitat von Didi Rieder <adrieder at sbox.tugraz.at>:
>>
>>> Hi,
>>>
>>> It would be great to have a configuration option or setting which
>>> allows to completely disable shares?
>>> I mean that all the sql lookups (e.g. for kronolith, mnemo and nag)
>>> are omitted? If the default shares are needed in such a setup the
>>> could be generated dynmaically without doing the time consuming sql
>>> lookups.
>>
>> Lock the default_* preferences.
>
> I did that already but still queries of the following type are made:
> SELECT  s.*  FROM nag_shares s  LEFT JOIN nag_shares_users AS u ON
> u.share_id = s.share_id WHERE s.share_owner = 'riederd at mbox' OR
> s.perm_creator & 4 OR s.perm_default & 4 OR ( u.user_uid =
> 'riederd at mbox' AND u.perm & 4) ORDER BY s.attribute_name ASC;
>>
>>> Shares are nice but if you run a system with >1000 active sessions and
>>> serveral thousands of users they really can hit the performance.
>>
>> That's why we have a native SQL driver now, and share caching.
>
> I know, and it performs better than the datatree but still it is to
> slow for our system/setup. We will move the sql server to a newer
> machine soon, lets see if this helps.

Unfortunately the mirgation to a new mysql-server machine (Sun 4600 4x  
3GHz Dual core Opterons with 16Gb RAM) did not help very much.
Initially it seemed that moving from a Sun Fire v480 (4xSPARC @900Mhz,  
8Gb RAM) to the new system solved the problem.
It was still running fine when there were about 7000 users using the  
system which results in the same number of default entries in the nag,  
mnemo and kronolith shares tables. But after the number increased to  
about 8200 users with about 1000 active sessions the mysql server just  
went down to its knees. (With the old systems ~2000 users were already  
to much)

Does someone have a similar number of users and active sessions and  
uses mysql as data backend and does not see such problems? If yes, I'd  
be very much interested in seeing the relevant horde and mysql-server  
configuration parameters.

Why is it necessary to do all the sql-lookups for default shares if  
sharing is disabled/locked? Wouldn't it be possible to dynamically  
create the results of the lookups by the php code itself without  
asking the database?

Thanks for any hint.

   Didi

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