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

[duck at obala.net]

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

> 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


I do. This the reason why I wrote the native SQL driver.

I have up to 1500 users online (with only one site, there are 100  
other more, but they are smaller). I have an master and slave mysql  
installation. The master is a dedicated server. The slave server holds  
even DNS and mails. The web-server is even an dedicated server with.  
It runs Lighttpd 1.5, php 5.2.6 and memcached. A very good mysql  
performance resource is http://www.mysqlperformanceblog.com/. Mysql  
default options are often very “solid” but all depends on what type of  
data you hold and what kind of queries do you run.

But I agree. We should avoid the creation of default data and try to  
minimize/limit the data usage wherever is possible.

How can I help?

Duck