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=arch...@jab.org
