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) Arjan van Staalduijnen <Arjan[dot]van[dot]Staalduijnen[at]rtl[dot]nl> 09-01-2009
I don't know if this piece of code can be overruled by some manually
edited config file, but otherwise I am not a big fan of this change at
all. Some people might prefer not using MySQL's cache too much and
instead have roxen do some caching, but for the situation I am using it
it is exactly the opposite. I have boosted our query-cache-size of our
local databases to 256M for a better performance - anything less will
not be enough here.


Regards,

Arjan


-----Oorspronkelijk bericht-----
Van: Martin Stjernholm [mailto:<mast[at]roxen.com>] 
Verzonden: Friday, January 09, 2009 1:01 PM
Aan: Stephen R. van den Berg
CC: <roxen[at]roxen.com>; <grubba[at]roxen.com>; Fredrik Noring
Onderwerp: query-cache-size = 32M (Was: Re: Lots of patches that can be
integrated into Roxen 5.0 in a heartbeat)

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.


__________________________________________________________
Deze e-mail en de inhoud is vertrouwelijk en uitsluitend bestemd voor de
geadresseerde(n). Indien u niet de geadresseerde bent van deze e-mail verzoeken
wij u dit direct door te geven aan de verzender door middel van een reply e-mail
en de ontvangen e-mail uit uw systemen te verwijderen. Als u geen geadresseerde
bent, is het niet toegestaan om kennis te nemen van de inhoud, deze te kopieren,
te verspreiden, bekend te maken aan derden noch anderszins te gebruiken.

The information contained in this e-mail is confidential and may be legally
privileged. It is intended solely for the addressee. If you are not the intended
recipient, any disclosure, copying, distribution or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful. Please notify
us immediately if you have received it in error by reply e-mail and then delete
this message from your system.
__________________________________________________________