Welcome to WebmasterWorld Guest from 54.145.15.213

Forum Moderators: open

Message Too Old, No Replies

Hard to debug slow queries

caching makes the process impossible

     

Elric99

10:28 am on Apr 10, 2012 (gmt 0)

10+ Year Member



Hello,

I'm using mysql slow query log to find problem queries.

The trouble is after one search in PHPMYadmin the result seems to be cached. Subsequent searches appear in .002 seconds.

Adding SQL_NO_CACHE doesn't appear to have any effect.

Is there any way to reliably recreate sloq queries?

Thanks

Tom

whoisgregg

4:30 pm on Apr 10, 2012 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



The query cache will not work if any part of the SELECT is non-deterministic. So, if you use a function like RAND() in your select expression, then that query will never be cached:

SELECT *, RAND() AS cache_buster FROM ...


This shouldn't have a dramatic affect on your overall execution time, nor should it throw off MySQL's other built-in optimizations. Since you can see that the value is changing on each reload, it gives you the confidence that you are not hitting the cache.

Elric99

8:43 am on Apr 11, 2012 (gmt 0)

10+ Year Member



Thanks whoisgregg

Oddly I can't get this to work. Mysql is still reading from the cache for anything other than my first query.

Is there anything else I could try?

whoisgregg

3:21 pm on Apr 11, 2012 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



If the value shown in the `cache_buster` column stays the same on refreshes, then you must be pulling from some other cache... either your browser cache or some caching perhaps at the PHP layer?

If the value shown in the `cache_buster` column is changing but the overall execution time of the search seems curiously faster on subsequent refreshes, than what's probably happening is that the key cache [dev.mysql.com] is now primed (for lack of a better word).

Which is occuring?

Elric99

9:28 am on Apr 12, 2012 (gmt 0)

10+ Year Member



"If the value shown in the `cache_buster` column is changing but the overall execution time of the search seems curiously faster on subsequent refreshes, than what's probably happening is that the key cache [dev.mysql.com] is now primed (for lack of a better word).

Which is occuring? "

This is the scenario I am seeing. Changing rand number but second query is faster.

whoisgregg

4:02 pm on Apr 12, 2012 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You can force the key cache to be restructered by changing the size of the key_buffer_size. These queries change it to 2MB and then restores it to it's default value:

SET GLOBAL key_buffer_size = 2097152; SET GLOBAL key_buffer_size = DEFAULT;


Unfortunately, to really make sure you've destroyed every possible cache layer you practically have to reset your server. :/

If this doesn't help, maybe looking at the query itself will reveal another avenue to explore.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month