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

[Didi Rieder]

Quoting 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,

thanks for you answer.
I'm already using the native SQL driver and it is better than the  
datatree system, but unfortunately not enough :-(

Do you use read/write splitting with your master and slave setup?
...hmmm with the one single slave for reads that wouldn't help much, right?

Our system is the following:
- 2 dedicated main mailservers with ~15.000 mailboxes (and ~15 more  
smaller ones < 30 user / server)
- 4 dedicated and loadbalanced webservers with apache + php 5.2.6 +  
xcache + memcached on each and serving only horde
- one dedicated SQL-Server (the above mentioned Sun 4600) also just  
serving horde no other data. This server is running mySQL 5.0.51a 32 bit

I think from the hardware site this should be enough.

Could you please post or send me your my.cnf file.

Avoiding creation and fetching of default data would indeed help.  
Interestingly, turba doesn't do any default share queries when sharing  
is disabled.

Didi

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