roxen.lists.roxen.general

Subject Author Date
Re: query-cache-size = 32M (Was: Re: Lots of patches that can be integrated into Roxen 5.0 in a heartbeat) Kai Voigt <k[at]123[dot]org> 09-01-2009
Hi,

4MB will be a sufficient size for the query cache for probably >90% of  
all installations. MySQL's default size for the query cache is 0  
Bytes, so you'd need to enable it if you want/need it.

My suggestion is to not make the default setting too big, but let  
people adjust it to their needs.

It would be interesting to see the output of "SHOW GLOBAL STATUS LIKE  
'qcache_%'" on a standard installation of Roxen with moderate load to  
find out if 4MB are a good default.

Kai (<kai[at]mysql.com>)


Am 09.01.2009 um 14:01 schrieb Martin Stjernholm:

> Fredrik Noring is responsible for that code, so I'm splitting off this
> issue and cc'ing him.
>
> For reference, Stephen's proposed patch is this:
>
>  --- a/server/base_server/roxenloader.pike
>  +++ b/server/base_server/roxenloader.pike
>  @@ -1992,7 +1992,7 @@ void low_start_mysql( string datadir,
>                       "set-variable = max_allowed_packet=16M\n"
>                       "set-variable = net_buffer_length=8K\n"
>                       "query-cache-type = 2\n"
>  -                    "query-cache-size = 32M\n"
>  +                    "query-cache-size = 4M\n"
>   #ifndef UNSAFE_MYSQL
>                       "local-infile = 0\n"
>   #endif
>
> "Stephen R. van den Berg" <<srb[at]cuci.nl>> wrote:
>
>>>> commit baaca0f710c9a95ac5921f5b9d1fd44d26b4fa34
>>>>    Saner default for mysql querycache
>>>> Roxen should do the caching, not mysql.
>>
>>> Not sure about the repercussions to change this. There are many
>>> situations when a Roxen level cache isn't viable (e.g. external
>>> clients, or because it'd be too much extra effort to write one).
>>
>> External clients on the local mysql database of Roxen?
>> Is that something that happens a lot in your applications?
>
> Not a lot, but it does happen. We might run external pike processes
> for some tasks, and there are customers who use the mysql server for
> their own ends (even though we don't recommend that practice).
>
>> With respect to "too much effort"...
>> The MySQL query cache is useful if one has queries with longer than
>> trivial runtimes and comparatively small resultsets.  The way Roxen
>> uses the local MySQL database, this never happens.  The queries:
>> - Either have very short runtimes because they are indexed properly.
>> - Or if they run longer, then the query is some kind of status/ 
>> maintenance
>>  query which is not intended to be called a lot in rapid succession.
>> - Or have very large resultsets, but should not be cached in that  
>> case,
>>  since they are not performance-critical (the problem is the large
>>  resultset to begin with).
>>
>> So that would imply that maintaining the query cache in MySQL is  
>> close to
>> useless for the Roxen local database.
>>
>> Any caching needed will be done by the OS due to filesystempages  
>> left in RAM.
>> Caching in MySQL as well, will just keep around that data in memory  
>> *twice*.
>
> It's not clear to me how generally applicable this argument is, and
> what the conventional wisdom on mysql performance tuning says. But I
> can point out that the mysql server is used for a lot more than you
> see in WebServer. We have some Roxen modules that maintains pretty
> large and complex databases.
>
>

-- 
Kai Voigt
<k[at]123.org>