homepage Welcome to WebmasterWorld Guest from 174.129.74.186
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

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




msg:4439084
 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




msg:4439233
 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




msg:4439483
 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




msg:4439647
 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




msg:4439936
 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




msg:4440070
 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