List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:February 24 2012 11:36am
Subject:re: memcache vs.query_cache
View as plain text  
Hi!

>>>>> "Revathi" == Revathi Rangachari <masrrev@stripped> writes:

Revathi> Hi
Revathi> (1) If memcache is available  - in an apache-php-mysql environment, does
> enabling or disabling of query_cache_type in mysql configuration, matter in improving
> performance? 

It depends on how memcache is used and how often you disregard things
from it.

In memcache it's better to store things that doesn't matter if it's a
bit old.

Query cache is better for things that changes 'relatively seldom' (not
more than once a second) as it's 'never out of date'.

It's not useful to cache the same kind of data with both caches...

Revathi> (2) If memcache is enabled and also query_cache_type is enabled, will both
> cache mechanism work or does one take precedence over the other, in serving requests.

As you normally ask memcache first if a query is there, it will take
precedence.

Revathi> (2) I just wanted to understand how these variables query_cache_type,
> tmp_table_size, max_heap_table_size, memory temporary tables influence each other?

tmp_table_size and max_heap_table_size have nothing to with query
cache. These are just restriction for how much memory that
MySQL/MariaDB will use for resolving a query.

Revathi> In a replicated environment where query_cache_type is enabled and the
> tmp_table_size (64MB) and max_heap_table_size ( 1GB) the number of memory temporary tables
> created over a period of half-an-hour is around 2000.

This is depending on your queries.

Revathi> Whereas if query_cache_type is disabled and the tmp_table_size(64 MB) and
> max_heap_table_size 91 GB) the number of temporary tables created over a period of
> half-an-hour is around 6000.

This happens because many of your queries are resolved by the query
cache and thus there is no need to run the query (that apparently is
using temporary tables to resolve the query).

Revathi> Can anyone please let me know what would be the explanation for the above? 
Revathi> Should I bring down values of tmp_table_size and max_heap_table_size?

No.

Regards,
Monty
Thread
MySQL Audit FunctionAruna Karunarathna5 Feb
  • Re: MySQL Audit FunctionAntony T Curtis5 Feb
    • Re: MySQL Audit FunctionPaul DuBois12 Apr
      • Re: MySQL Audit FunctionAntony T Curtis13 Apr
        • Re: MySQL Audit FunctionPaul DuBois13 Apr
          • RE: MySQL Audit FunctionRick James18 Apr
            • Re: MySQL Audit FunctionSergei Golubchik18 Apr
  • re: MySQL Audit FunctionMichael Widenius21 Feb
    • Re: re: MySQL Audit FunctionSergei Golubchik22 Feb
      • memcache vs.query_cacheRevathi Rangachari23 Feb
        • re: memcache vs.query_cacheMichael Widenius24 Feb
          • re: memcache vs.query_cacheRevathi Rangachari25 Feb
          • Re: memcache vs.query_cacheRick James27 Feb
            • query_cache_type and Aborted ClientsRevathi Rangachari1 Mar
              • Re: query_cache_type and Aborted ClientsRick James1 Mar
                • Re: query_cache_type and Aborted ClientsRevathi Rangachari5 Mar
                  • Re: query_cache_type and Aborted ClientsRick James5 Mar
                    • have_query_cache and query_cache_typeRevathi Rangachari6 Mar
                      • Re: have_query_cache and query_cache_typeYves Trudeau7 Mar
                • Slow query log file not getting updatedRevathi Rangachari20 Mar
                  • Re: Slow query log file not getting updatedBaron Schwartz21 Mar