homepage Welcome to WebmasterWorld Guest from 54.145.183.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Hard to debug slow queries
caching makes the process impossible
Elric99

5+ Year Member



 
Msg#: 4439082 posted 10:28 am on Apr 10, 2012 (gmt 0)

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

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



 
Msg#: 4439082 posted 4:30 pm on Apr 10, 2012 (gmt 0)

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

5+ Year Member



 
Msg#: 4439082 posted 8:43 am on Apr 11, 2012 (gmt 0)

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

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



 
Msg#: 4439082 posted 3:21 pm on Apr 11, 2012 (gmt 0)

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

5+ Year Member



 
Msg#: 4439082 posted 9:28 am on Apr 12, 2012 (gmt 0)

"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

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



 
Msg#: 4439082 posted 4:02 pm on Apr 12, 2012 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved