SQL_NO_CACHE

Hi, I was hoping that using SQL_NO_CACHE would help me bypass the
query cache, but judging from the below it doesn't. What can I do to
avoid the query cache?

Thanks.

Morten


mysql> select count(*) from users where email = 'hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (7.22 sec)

mysql> select count(*) from users where email = 'hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.45 sec)

mysql> select count(*) from users where email = 'hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.45 sec)

mysql> select SQL_NO_CACHE count(*) from users where email = 'hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.43 sec)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

Keep in mind the file system caches too, so it might be working, but the
file access is still getting put in memory. You should disable that too
if you really want consistent results.

In Linux you can dump the file system cache between each query, I have
no clue how to do it in windows or other systems, although Unix stuff is
probably similar.

-Micah

On 03/04/2009 07:27 AM, Morten wrote:
>
> Hi, I was hoping that using SQL_NO_CACHE would help me bypass the
> query cache, but judging from the below it doesn't. What can I do to
> avoid the query cache?
>
> Thanks.
>
> Morten
>
>
> mysql> select count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (7.22 sec)
>
> mysql> select count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.45 sec)
>
> mysql> select count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.45 sec)
>
> mysql> select SQL_NO_CACHE count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.43 sec)
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

On Wed, Mar 4, 2009 at 10:27 AM, Morten wrote:
> Hi, I was hoping that using SQL_NO_CACHE would help me bypass the query
> cache, but judging from the below it doesn't.

You probably just brought the data into the cache and are not hitting
the query cache.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

On Wed, 4 Mar 2009, Morten wrote:

> Hi, I was hoping that using SQL_NO_CACHE would help me bypass the query
> cache, but judging from the below it doesn't. What can I do to avoid the
> query cache?
>
> Thanks.
>
> Morten
>
>
> mysql> select count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (7.22 sec)
>
> mysql> select count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.45 sec)
>
> mysql> select count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.45 sec)
>
> mysql> select SQL_NO_CACHE count(*) from users where email = 'hello';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.43 sec)


Hi

SQL_NO_CACHE means that the query result is not cached. It does not mean
that the cache is not used to answer the query.

You may use RESET QUERY CACHE to remove all queries from the cache and
then your next query should be slow again. Same effect if you change
the table, because this makes all cached queries invalid. But why do you
want to do this?

Regards,
Thomas Spahni


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

On Wed, Mar 4, 2009 at 11:23 AM, Thomas Spahni wrote:
> SQL_NO_CACHE means that the query result is not cached. It does not mean
> that the cache is not used to answer the query.

Oh, right, he's looking for this:

SET SESSION query_cache_type=off;

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

If SQL_NO_CACHE is specify, the cache will never be used :

The Query Cache behaviour is quite simple, it uses the exact given query
syntax as a hash to search into the query cache;
it means writing 'select' or 'SELECT' is different. It also means adding
SQL_NO_CACHE will search in the cache for a different query string,
which will never be cached :)

Jocelyn



Le 04/03/2009 17:39, Perrin Harkins a écrit :
> On Wed, Mar 4, 2009 at 11:23 AM, Thomas Spahni wrote:
>
>> SQL_NO_CACHE means that the query result is not cached. It does not mean
>> that the cache is not used to answer the query.
>>
>
> Oh, right, he's looking for this:
>
> SET SESSION query_cache_type=off;
>
> - Perrin
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

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)







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

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)
>
>
>
>
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

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)
>>
>>
>>
>>
>>
>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

Just curious : if there's no index on the column why don't you try to
add one ? That's probably why it takes a lot of time on the production
machine.

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)
>
>
>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

On Mar 4, 2009, at 8:38 PM, Jocelyn Fournier wrote:

> Just curious : if there's no index on the column why don't you try
> to add one ? That's probably why it takes a lot of time on the
> production machine.

Hehe.. I can understand why you ask, I over simplified the question
which was wrong of me. I'm just using this specific column because I
was playing around while trying to figure out why the first query was
slow and the subsequent snappy.

Br,

Morten



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL_NO_CACHE

On Wed, Mar 4, 2009 at 8:42 PM, Morten Primdahl wrote:
> around while trying to figure out why the first query was slow and the
> subsequent snappy.

Given that you posted that a MySQL restart does not change anything,
but a system restart does, I put my money on the filesystem cache
having buffered your data/index file on the second access.


--
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org