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

[duck at obala.net]

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

> 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

Turba does shares only when requested. Other modules relays on them.

I abandoned Apache time ago...

40% of my queries are executed on the salve server 60% on the master  
server as no all modules supports splited read/write. You can even  
trick you conf to randomly switch from splited SQL or use mysqlproxy  
for this.

I suggest not to go directly to trick you SQL settings. My friends  
always tend to directly trick mysql configuration. Then call me for  
help. Can be little hard work, especially if you don't know your data  
and actions on them. Performance is nearly always a problem of  
application architecture and data handling. And tricking the con does  
not solve your problem. Just avoid them... till traffic doesn't grow  
even more or you do not install another application that needs other  
settings. So lets go set by step.

1.
You should first trick your table structure. For example. All horde  
scripts are general only for compatibility reasons. You should trick  
columns types according your data. For example: The numerical columns  
are often INT. But you can minimize your data by setting them to  
MEDIUM/SMALL/TINY UNSIGNED integers wherever is possible. For example  
in a lot of situations your numbers probably won't grow above SMALLINT  
or MEDIUMINT expecially if you set them to be UNSIGNED as you get one  
more bit. And share_id are always positive numbers. This is is because  
mysql will allocate resources according to the predicted data  
retrieved according to rows predicted to be selected and column types.  
Mysql can analyze and purpose the best table structure for you (but  
don't believe it truly as forces ENUMs and other staff that maybe is  
all good for your flexibility).

2. check you mysql log for queries that does not use index.

3. set-up query cache.

4.check your log with mysqldumpslow and try to isolate queries that  
really take long time. Here we, Horde DEV team, will really like to  
help to kill them.

5. Then trick your mysql configuration. Because no you know what is  
going in with your data and what needs to be changed. For example you  
don't know if there is a sorting buffer problem, or no enough  
table/thread cache etc. What your phpMyAdmin “Runtime Information”  
page says? There is are a lot of info regarding you server stats. You  
should check the comments of the settings where there are read  
numbers. But pleas not that are not always true. Some of the number  
get out of bounds as a consequence of a group of other wrong settings  
not directly them.

Duck