List:General Discussion« Previous MessageNext Message »
From:Micah Date:March 4 2009 5:59pm
Subject:Re: SQL_NO_CACHE
View as plain text  
I did some tests a couple of weeks ago, using using SQL_NO_CACHE and 
clearing out the OS buffer after each query was enough to give me 
consistent results that were based on system load rather than cache 
efficiency. These two are by far the major factors in my experience, 
although no doubt other system setup can have an effect depending on the 
query.

My goal was to test proper tuning parameters, so I didn't want to alter 
these artificially. Depends on the test you're doing.

jocelyn fournier wrote:
> MySQL Key buffer and OS cache could also have an impact.
> Have you tried disabling the Key Buffer first ?
>
>   Jocelyn
>
> Le 04/03/2009 18:26, Morten Primdahl a écrit :
>>
>> Thanks for all the suggestions. The caching must be done somewhere 
>> else. There is no index on the column and there are about 500.000 
>> rows in the table. A MySQL restart doesn't "flush" the cache in play, 
>> but a full restart of my laptop does (OS X).
>>
>> I may be chasing the wrong problem, but we have seen a query take a 
>> lot of time on a production machine and have not been able to pin 
>> point why, as the EXPLAIN looks good and the query is responsive 
>> enough when I run it manually. I was just trying to reproduce that.
>>
>> The below is after a full restart:
>>
>> mysql> SET SESSION query_cache_type=off;
>> Query OK, 0 rows affected (0.00 sec)
>>
>> mysql> select SQL_NO_CACHE count(*) from users where email = 'hello' 
>> AND 456 = 456;
>> +----------+
>> | count(*) |
>> +----------+
>> |        0 |
>> +----------+
>> 1 row in set (28.80 sec)
>>
>> mysql> RESET QUERY CACHE;
>> Query OK, 0 rows affected (0.00 sec)
>>
>> mysql> select SQL_NO_CACHE count(*) from users where email = 'hello' 
>> AND 789 = 789;
>> +----------+
>> | count(*) |
>> +----------+
>> |        0 |
>> +----------+
>> 1 row in set (0.44 sec)
>>
>>
>>
>>
>>
>>
>>
>
>
Thread
SQL_NO_CACHEMorten4 Mar
  • Re: SQL_NO_CACHEMicah Stevens4 Mar
  • Re: SQL_NO_CACHEPerrin Harkins4 Mar
  • Re: SQL_NO_CACHEThomas Spahni4 Mar
    • Re: SQL_NO_CACHEPerrin Harkins4 Mar
      • Re: SQL_NO_CACHEjocelyn fournier4 Mar
        • Re: SQL_NO_CACHEMorten Primdahl4 Mar
          • Re: SQL_NO_CACHEjocelyn fournier4 Mar
            • Re: SQL_NO_CACHEMicah4 Mar
          • Re: SQL_NO_CACHEJocelyn Fournier4 Mar
            • Re: SQL_NO_CACHEMorten Primdahl4 Mar
              • Re: SQL_NO_CACHEJohan De Meersman4 Mar